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

By

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.

Key terms

Related