Day 23: FastAPI. SQL in-memory db. StaticPool.
Hi 👋 I am Hud, a postdoc for engineering data science at the AI Manufacturing Center in Laramie, Wyoming. My funding is running out (AaAaaA !), so while I am actively looking for a new job, instead of doing the 205th coding certificate to prove my worthiness — I thought I’d do design challenges and document how I spend my time upskilling so other engineers can do the same.
Nowadays, certificates are everywhere. Documenting small upskill projects that you can later show off is the best way to get recognition as a professional engineer.
This is day 23 of a 30-day design challenge. Follow along and let me know if you get stuck!
TL;DR tasks
Download the provided code for the challenge.
Task 1: Create a mock database session
- Create a mock database session using a library such as unittest.mock or pytest-mock.
- The mock session should have methods that mimic the behavior of the real database session, but without actually writing to the database.
Task 2: Replace the database session in the test
- In the test case for the
create_eventAPI route, replace the actual database session with the mock session.
Task 3: Test the behavior of the API route
- Call the
create_eventAPI route with the mock session instead of the real session. - Verify that the API route behaves as expected, handling the input data correctly without actually writing to the database.
Task 4: Run the test multiple times
- Ensure that you can run the test multiple times, and it consistently produces the same results.
- The test should not be influenced by any previously run tests, as it does not interact with the database.
The problem
Test-driven development is rather successful. But how do we run tests for APIs and not interact with the production database? How do we test efficiently and avoid creating a new database file everytime we run such tests? We return to our FastAPI example where we transformed raw sql to ORMs from our previous challenge. If you have not done that, I recommend going through that challenge first and return back here once you’re done.
Essentially, instead of just running this method to use the API directly, can we transform the above for testing?
SQLite in-memory database and StaticPool
In-memory database is a database that resides in the main memory of a computer instead of a disk drive. In the following code, the SQLite database is created in memory using the connection string sqlite:///:memory:. This means that the database will be created and stored in the memory of the computer rather than on disk. The purpose of using an in-memory database is to improve the performance of the application by reducing the time it takes to read and write data to disk. Since the database is stored in memory, it can be accessed and manipulated much faster than a traditional disk-based database.
If we didn’t specify poolclass=StaticPool in the code, the default connection pool class of SQLAlchemy would be used. The default connection pool class is QueuePool, which creates a pool of connections to the database and manages them using a queue.
However, when using SQLite in multi-threaded applications, the QueuePool class can cause issues due to SQLite's threading limitations. Therefore, in the selected code, the StaticPool class is used instead of the default QueuePool class. The StaticPool class creates a pool of connections to the database that are not recycled, which is suitable for SQLite in multi-threaded applications.
We essentially created a testing session running at sqlite:///:memory:separate than the main session sqlite:///./events.db . How do we override it so we use the former? We use FastAPI’s feature dependency_overrides that allows you to override a dependency with a new implementation. That’s it!
Running the tests
Once we’ve setup the rest, the tests are then quite simple to do. Here’s something we can test (by transforming the create_event method above to a testing event):
In the terminal, simply run pytest in the terminal and we should see it pass with flying colors (hopefully greens, not reds)! For a challenge to learn how to run poetry and do basic unit tests and pytest, please try our this challenge.
Conclusion
Congratulations! You finished Day 23 from the 30-day design challenge.
If you have reached this far, you know how to:
- Use FastAPI to run tests without touching production database
Check out the day 24 challenge to see even more API tests!
Also, you can access the full 30-day GitHub repository here.
💡 My goal here is to help engineering data scientists upskill in design. I’d like to hear from you! Was this helpful? Anything I can improve? Connect with me on LinkedIn | Medium
