Testing with a Real, Throwaway Database
API tests that touch a database get a fresh SQLite file in pytest's tmp_path for every test, with the migrations run and a few rows added in a fixture, and app.dependency_overrides swaps get_db for one that opens that file, so the tests run real SQL without ever touching the real data.
Journeyman · FastAPI · REST API · Updated
After this lesson you can
- Test a database-backed API on a fresh SQLite file in tmp_path, migrated and seeded in a fixture, with get_db swapped through dependency_overrides
- Explain why the course tests against a real, throwaway database instead of mocking it, and check what a request saved by looking in the file
Key takeaways
- Tests that use the app's real database file change your own data, and pass or fail depending on what's in it.
- pytest's tmp_path gives each test a new, empty folder. A fixture makes a database file there, runs the migrations and adds the rows the tests need.
- app.dependency_overrides[get_db] = get_test_db makes every route get its connection from the test's file instead. Clear it after the test.
- A test can look inside the database file after a request, to check what was really saved or deleted.
- A mocked database only answers what the test told it to, so a broken SQL statement still passes. A real throwaway database runs the real SQL and catches it.
Once an API keeps its data in a database, its tests need a database too, and on a team they run hundreds of times a day, on every laptop and on every push. They have to start from the same rows every time, and they must never touch anyone's real data. This lesson builds that.
The example is Rep Counter's workouts API from this chapter, with its get_db dependency and its migrations, and two routes to test: GET /workouts/{workout_id} and DELETE /workouts/{workout_id}.
Tests that touch the real file
Testing an API with TestClient sent requests straight to the app. Do that here, and every request goes through get_db to rep_counter.db, the file you use when you try the API by hand. A test that deletes workout 1 deletes your workout 1. A test that expects Luis's workout fails on a teammate's laptop, where workout 1 is someone else's. The tests need a database of their own, made fresh for every test.
A fresh file for every test: tmp_path
pytest has a built-in fixture for this, tmp_path: ask for it as a parameter, and each test gets a new, empty folder that pytest cleans up for you. A database file there is thrown away with the test.
The test's file has to be opened exactly the way get_db opens the real one. So the first lines of get_db move into a function of their own in database.py, open_db(path), and get_db starts with conn = open_db(DATABASE_PATH):
def open_db(path):
conn = sqlite3.connect(path, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")
return conn
Swapping get_db: dependency_overrides
FastAPI keeps a dictionary on the app, app.dependency_overrides. Put a dependency in as the key and another function as the value, and every route that asked for the first gets the second instead. That's a dependency override. Here's the start of tests/test_workouts.py:
import sqlite3
import pytest
from fastapi.testclient import TestClient
from database import get_db, migrate, open_db
from main import app
client = TestClient(app)
@pytest.fixture(autouse=True)
def test_db(tmp_path):
path = tmp_path / "test.db"
conn = open_db(path)
migrate(conn)
conn.execute("INSERT INTO workouts (member, day) VALUES (?, ?)", ("Luis", "2026-11-03"))
conn.execute(
"INSERT INTO sets (workout_id, exercise, weight, reps) VALUES (?, ?, ?, ?)", (1, "squat", 135, 8)
)
conn.commit()
conn.close()
def get_test_db():
conn = open_db(path)
try:
yield conn
conn.commit()
finally:
conn.close()
app.dependency_overrides[get_db] = get_test_db
yield path
app.dependency_overrides.clear()
Before every test, the fixture makes the file, runs the real migrations on it, and adds one workout with one set: the test's known starting rows, or test fixture. get_test_db is get_db pointed at that file. (It needs no rollback: a connection closed without a commit drops its changes.) The fixture's own yield path hands the file's path to any test that asks for test_db, and after the test, clear() puts the real get_db back. TestClient(app) isn't started with with, so the app's lifespan never runs, and rep_counter.db is never opened.
Tests for a 404 and a delete
The tests themselves look like any TestClient test. A test that names test_db as a parameter gets the file's path, so it can look inside after the request:
def test_get_workout_1_answers_200_with_luis_workout():
response = client.get("/workouts/1")
assert response.status_code == 200
assert response.json() == {"id": 1, "member": "Luis", "day": "2026-11-03"}
def test_get_workout_99_answers_404():
response = client.get("/workouts/99")
assert response.status_code == 404
assert response.json()["detail"] == "There's no workout 99."
def test_delete_workout_1_answers_204_and_deletes_its_sets(test_db):
response = client.delete("/workouts/1")
assert response.status_code == 204
conn = sqlite3.connect(test_db)
sets_left = conn.execute("SELECT COUNT(*) FROM sets").fetchone()[0]
conn.close()
assert sets_left == 0
The last test checks what no answer shows: that ON DELETE CASCADE, with foreign keys turned on, took workout 1's set with it. python -m pytest -v (-v lists each test by name):
collecting ... collected 3 items
tests/test_workouts.py::test_get_workout_1_answers_200_with_luis_workout PASSED [ 33%]
tests/test_workouts.py::test_get_workout_99_answers_404 PASSED [ 66%]
tests/test_workouts.py::test_delete_workout_1_answers_204_and_deletes_its_sets PASSED [100%]
============================== 3 passed in 7.36s ===============================
A test that runs the routes, the data module and the real SQL together is an integration test: it checks the parts work together for real.
Why not mock the database?
Some teams test with a mock: a stand-in that answers whatever the test tells it to. pytest's built-in monkeypatch fixture can swap workouts_data.get_workout for one that always returns Luis's workout:
def test_get_workout_1_with_a_stand_in(monkeypatch):
def stand_in(conn, workout_id):
return {"id": 1, "member": "Luis", "day": "2026-11-03"}
monkeypatch.setattr(workouts_data, "get_workout", stand_in)
response = client.get("/workouts/1")
assert response.status_code == 200
Now put a typo in the real query, FROM workout instead of FROM workouts, and run both tests for workout 1 (-k runs the tests whose names contain the text you give it):
python -m pytest -k get_workout_1
E sqlite3.OperationalError: no such table: workout
workouts_data.py:2: OperationalError
=========================== short test summary info ============================
FAILED tests/test_workouts.py::test_get_workout_1_answers_200_with_luis_workout
================== 1 failed, 1 passed, 2 deselected in 11.68s ==================
The stand-in test still passes: it never ran the SQL, so it can't see the typo. The real-database test caught it. Most of an API's bugs live in its SQL, a column name, a missing WHERE, a cascade that isn't there, so this course always tests against a real, throwaway database.
Check yourself
2 questions to try on the lesson page.
So what
A fresh SQLite file for every test runs the real SQL from a known start, never touches anyone's data, and catches the bugs a mock would wave through.