# 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 November 19, 2026

By Gregor Dzierzon

## 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)`:

```python
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`:

```python
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:

```python
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):

```text
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:

```python
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):

```bash
python -m pytest -k get_workout_1
```

```text
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

- [tmp_path](https://www.ask-codey.com/glossary/tmp-path)
- [Dependency Override](https://www.ask-codey.com/glossary/dependency-override)
- [Test Fixture](https://www.ask-codey.com/glossary/test-fixture)
- [Integration Test](https://www.ask-codey.com/glossary/integration-test)
- [Mock](https://www.ask-codey.com/glossary/mock)

## Related

- [Testing Errors, with a Fresh Start for Every Test](https://www.ask-codey.com/lessons/fastapi-testing-errors-and-fresh-state)
- [A Database Connection for Every Request](https://www.ask-codey.com/lessons/fastapi-data-get-db-dependency)
- [Exercise: Workout Log API](https://www.ask-codey.com/lessons/exercise-fastapi-workout-log-api)

Source: https://www.ask-codey.com/lessons/fastapi-data-testing-real-database
