Database Testing with Fixtures

Capsule overview

An integration test that uses the same database as other tests is a time bomb. Test A creates data, Test B depends on it, Test C deletes it — and suddenly Test B fails, not because of a bug in your code, but because of the execution order. Tests sharing database state are fragile, order-dependent, and a nightmare to debug.

This capsule teaches you how to give each test its own clean state using pytest fixtures. You'll use a simple database (dict-based for this module; SQLAlchemy/SQLite in-memory comes in Module 8) so you can focus on the testing patterns, not on the database technology. You'll learn setup/teardown with yield, fixture scope, and how to inject a test database into your FastAPI app with dependency_overrides. By the end, you'll have CRUD tests with complete isolation — every test starts from scratch.

The Problem: Tests That Share a Database

Why dependent tests fail

Imagine three tests that use the same database:

Test A: Creates a user with id=1
Test B: Assumes an item with id=1 exists (created by Test A) ← Fails if A runs afterwards
Test C: Deletes all items
Test B: Fails because id=1 no longer exists ← Fails because of Test C

The symptoms:

• ✅ Tests pass when you run them one by one
• ❌ Tests fail when you run them all together
• ❌ Execution order matters (pytest doesn't guarantee an order)
• ❌ One test "contaminates" the state for the others

The solution: isolation per test

Each test should start with a clean, known state. It shouldn't depend on what previous tests did. It shouldn't affect later tests.

Test A: empty db → creates a user → verifies → [db is discarded]
Test B: empty db → creates an item → verifies → [db is discarded]
Test C: empty db → deletes an item → verifies → [db is discarded]

pytest fixtures let you create a "fresh db" for each test. This module uses a simple dict-based database — the focus is on the testing patterns, not on PostgreSQL or SQLAlchemy.

In-Memory Databases for Testing

Why not use PostgreSQL in tests (for now)

In tests, you want:

• ⚡ Speed: tests should run in seconds
• 🔒 Isolation: each test with its own state
• 📦 Simplicity: no installing servers or configuring connections

PostgreSQL in production is powerful. But for early integration tests, an in-memory database is enough. In Module 8 you'll see testing against a real PostgreSQL when the application is more complex. Here we use a dict-based database that simulates CRUD operations — the pattern is the same.

Dict-based database for this module

# database.py — a simple database to understand fixtures
# (SQLAlchemy/SQLite in-memory comes in Module 8)

def create_db():
    """Creates an empty database (dict)."""
    return {"items": [], "next_id": 1}


def insert_item(db: dict, name: str, price: float) -> dict:
    """Inserts an item and returns the created one."""
    item = {"id": db["next_id"], "name": name, "price": price}
    db["items"].append(item)
    db["next_id"] += 1
    return item.copy()


def get_item(db: dict, item_id: int) -> dict | None:
    """Gets an item by id."""
    for item in db["items"]:
        if item["id"] == item_id:
            return item.copy()
    return None


def update_item(db: dict, item_id: int, name: str | None = None, price: float | None = None) -> dict | None:
    """Updates an item. Returns the updated one or None if it doesn't exist."""
    for item in db["items"]:
        if item["id"] == item_id:
            if name is not None:
                item["name"] = name
            if price is not None:
                item["price"] = price
            return item.copy()
    return None


def delete_item(db: dict, item_id: int) -> bool:
    """Deletes an item. Returns True if it existed."""
    for i, item in enumerate(db["items"]):
        if item["id"] == item_id:
            db["items"].pop(i)
            return True
    return False


def list_items(db: dict) -> list[dict]:
    """Lists all the items."""
    return [item.copy() for item in db["items"]]

This "database" is just a dictionary. You don't need to install anything. The focus is on how to test operations with fixtures — the pattern transfers to SQLAlchemy or any ORM.

Quick comparison: dict vs SQLite in-memory vs PostgreSQL

To learn the patterns, dict-based is ideal: zero configuration, maximum focus on how to structure fixtures and tests.

Database Fixtures with pytest

Basic fixture: an empty db per test

# tests/test_database.py
import pytest
from database import create_db, insert_item, get_item, list_items


@pytest.fixture
def db():
    """A fresh database for each test."""
    database = create_db()
    return database


def test_insert_and_get(db):
    # ARRANGE: empty db (comes from the fixture)
    # ACT
    inserted = insert_item(db, "Widget", 19.99)
    # ASSERT
    assert inserted["id"] == 1
    assert inserted["name"] == "Widget"
    assert inserted["price"] == 19.99

    retrieved = get_item(db, 1)
    assert retrieved is not None
    assert retrieved["name"] == "Widget"


def test_list_empty_db(db):
    assert list_items(db) == []

Every test that uses db receives a new instance. test_insert_and_get and test_list_empty_db don't share state.

Composed fixture: a pre-populated db

@pytest.fixture
def db():
    """A fresh database."""
    return create_db()


@pytest.fixture
def db_with_items(db):
    """A database with example items."""
    db["items"] = [
        {"id": 1, "name": "Item A", "price": 10.0},
        {"id": 2, "name": "Item B", "price": 20.0},
    ]
    db["next_id"] = 3
    return db


def test_get_existing_item(db_with_items):
    item = get_item(db_with_items, 1)
    assert item is not None
    assert item["name"] == "Item A"


def test_update_item(db_with_items):
    from database import update_item
    updated = update_item(db_with_items, 1, name="Item A Updated")
    assert updated["name"] == "Item A Updated"
    assert get_item(db_with_items, 1)["name"] == "Item A Updated"

db_with_items depends on db — pytest resolves the order automatically. Every test that asks for db_with_items gets a db with the same initial data, but each test has its own copy.

Setup and Teardown with yield

Fixtures that need cleanup

Sometimes the setup creates resources that must be released: connections, files, processes. The standard way is to use yield:

@pytest.fixture
def db():
    """A database with explicit teardown."""
    database = create_db()
    yield database
    # Teardown: runs after the test
    database.clear()
    database["items"] = []
    database["next_id"] = 1


def test_something(db):
    insert_item(db, "X", 1.0)
    assert len(list_items(db)) == 1
# Here pytest runs the teardown (after the yield)

In our dict-based db there are no external resources to release, but the pattern is the same one you'll use with SQLAlchemy: yield session → session.close() in the teardown.

autouse: a fixture that runs on its own

If you want a fixture to run in every test in the module, without passing it as a parameter:

@pytest.fixture(autouse=True)
def reset_global_state():
    """Cleans the global state before each test."""
    # Setup
    original = get_global_config()
    yield
    # Teardown
    set_global_config(original)

Use it sparingly. For databases, you usually prefer passing db explicitly — it's clearer which tests use the database.

Scope: function vs module vs session

By default, a fixture has function scope — it's created once per test.

@pytest.fixture(scope="function")  # default
def db():
    return create_db()


@pytest.fixture(scope="module")
def shared_db():
    """A single db for the whole module — watch out for contamination!"""
    return create_db()


@pytest.fixture(scope="session")
def app_config():
    """Config that doesn't change during the entire test session."""
    return {"debug": True}

For test databases, function is the right choice: total isolation.

Where to put the fixtures: conftest.py

When several test folders use the same fixtures, put them in conftest.py:

tests/
├── conftest.py          ← shared fixtures (db, db_with_items)
├── test_database.py     ← tests that use the fixtures
└── integration/
    └── test_api.py      ← also uses db, db_with_items

# tests/conftest.py
import pytest
from database import create_db


@pytest.fixture
def db():
    return create_db()


@pytest.fixture
def db_with_items(db):
    db["items"] = [
        {"id": 1, "name": "Item A", "price": 10.0},
        {"id": 2, "name": "Item B", "price": 20.0},
    ]
    db["next_id"] = 3
    return db

Any test in tests/ or tests/integration/ can use db and db_with_items without importing them: pytest discovers them automatically.

Testing CRUD with Fixtures

Create: insert and verify

import pytest
from database import create_db, insert_item, get_item, list_items


@pytest.fixture
def db():
    return create_db()


def test_create_item_stores_correctly(db):
    item = insert_item(db, "Laptop", 999.99)
    assert item["id"] == 1
    assert item["name"] == "Laptop"
    assert item["price"] == 999.99


def test_create_item_increments_id(db):
    insert_item(db, "A", 1.0)
    item2 = insert_item(db, "B", 2.0)
    assert item2["id"] == 2

Read: verify pre-seeded data

@pytest.fixture
def db_with_items(db):
    db["items"] = [
        {"id": 1, "name": "Item A", "price": 10.0},
        {"id": 2, "name": "Item B", "price": 20.0},
    ]
    db["next_id"] = 3
    return db


def test_read_existing_item(db_with_items):
    item = get_item(db_with_items, 1)
    assert item["name"] == "Item A"
    assert item["price"] == 10.0


def test_read_nonexistent_returns_none(db_with_items):
    assert get_item(db_with_items, 99) is None


def test_list_returns_all(db_with_items):
    items = list_items(db_with_items)
    assert len(items) == 2
    assert items[0]["name"] == "Item A"

Update: modify and verify

from database import update_item


def test_update_item_name(db_with_items):
    updated = update_item(db_with_items, 1, name="Item A Updated")
    assert updated["name"] == "Item A Updated"
    assert get_item(db_with_items, 1)["name"] == "Item A Updated"


def test_update_item_price(db_with_items):
    updated = update_item(db_with_items, 2, price=25.0)
    assert updated["price"] == 25.0


def test_update_nonexistent_returns_none(db_with_items):
    assert update_item(db_with_items, 99, name="X") is None

Delete: remove and verify

from database import delete_item


def test_delete_removes_item(db_with_items):
    result = delete_item(db_with_items, 1)
    assert result is True
    assert get_item(db_with_items, 1) is None
    assert len(list_items(db_with_items)) == 1


def test_delete_nonexistent_returns_false(db_with_items):
    assert delete_item(db_with_items, 99) is False

Dependency Overrides in FastAPI

The problem: the app uses get_db()

In a typical FastAPI app, the endpoints depend on get_db() to get the database connection. In tests, you want to inject your test database instead of the real one.

# main.py
from fastapi import FastAPI, Depends

app = FastAPI()


def get_db():
    """In production, returns a real connection."""
    db = create_connection_to_postgres()
    try:
        yield db
    finally:
        db.close()


@app.get("/items/{item_id}")
def read_item(item_id: int, db=Depends(get_db)):
    item = get_item(db, item_id)
    if item is None:
        raise HTTPException(404)
    return item

The solution: dependency_overrides

# tests/test_api.py
from fastapi.testclient import TestClient
from main import app


def get_test_db():
    """Returns the test database instead of the real one."""
    return test_database


# Before the tests, override the dependency
app.dependency_overrides[get_db] = get_test_db

client = TestClient(app)


def test_read_item(test_database):
    # test_database is a fixture that creates the db
    insert_item(test_database, "Widget", 19.99)

    response = client.get("/items/1")
    assert response.status_code == 200
    assert response.json()["name"] == "Widget"

The trick: get_test_db must return the same instance as the test_database fixture. You can do that with a module variable or with a closure:

# tests/conftest.py
import pytest
from database import create_db

_test_db = None


@pytest.fixture
def test_database():
    global _test_db
    _test_db = create_db()
    yield _test_db
    _test_db = None


def get_test_db():
    return _test_db

To avoid globals, a cleaner way is to use app.dependency_overrides inside the test with a fixture:

@pytest.fixture
def client(db):
    def override_get_db():
        yield db

    from main import app, get_db
    app.dependency_overrides[get_db] = override_get_db
    with TestClient(app) as c:
        yield c
    app.dependency_overrides.clear()

That way each test gets a client that uses its own db fixture.

When not to use dependency_overrides

Don't use overrides for tests that don't require a database. If an endpoint doesn't use get_db, you don't need to inject anything. It's also not a good idea to override too many dependencies in a single test: if you need to mock 5 dependencies, maybe the test is verifying too much. For integration tests with a db, overriding just get_db is usually enough.

Claude Code for Generating Database Tests

Prompt for CRUD tests with fixtures

Generate tests for this API's CRUD operations, using fixtures for a test database.

Requirements:
1. A `db` fixture: an empty database for each test
2. A `db_with_items` fixture: a database with 2 example items (id 1 and 2)
3. Tests for: create (insert and verify), read (get by id, list), update, delete
4. Each test must be independent — it must not depend on the execution order
5. Arrange-act-assert pattern
6. Use the dict-based database (database.py), not SQLAlchemy

database.py code: [paste the content]

Example of the expected response

Claude Code may generate something like:

import pytest
from database import create_db, insert_item, get_item, update_item, delete_item, list_items


@pytest.fixture
def db():
    return create_db()


@pytest.fixture
def db_with_items(db):
    db["items"] = [
        {"id": 1, "name": "Item A", "price": 10.0},
        {"id": 2, "name": "Item B", "price": 20.0},
    ]
    db["next_id"] = 3
    return db


class TestCreate:
    def test_insert_returns_item_with_id(self, db):
        item = insert_item(db, "Test", 5.0)
        assert item["id"] == 1
        assert item["name"] == "Test"

    def test_insert_increments_id(self, db):
        insert_item(db, "A", 1.0)
        item2 = insert_item(db, "B", 2.0)
        assert item2["id"] == 2


class TestRead:
    def test_get_existing_item(self, db_with_items):
        item = get_item(db_with_items, 1)
        assert item["name"] == "Item A"

    def test_get_nonexistent_returns_none(self, db_with_items):
        assert get_item(db_with_items, 99) is None

Adjust the prompt if you need more edge cases or additional fixtures.

Iterating with Claude Code when the tests fail

If Claude Code generates tests that fail because of incorrect data in the fixtures:

1. Identify the failure: Does the test expect data that doesn't exist in db_with_items? Do the IDs not match?
2. Give it context: "The db_with_items fixture has items with id 1 and 2. The test_get_item test expects an item with name 'X' but the fixture has 'Item A'. Fix the test or the fixture."
3. Validate the flow: After the fix, run pytest tests/ and verify that everything passes.

The test → failure → feedback to Claude → fix cycle is central. Don't assume the first generation is perfect.

Exercises

Exercise 1: Basic fixture (Easy)

Create a db fixture that returns an empty database and write a test that inserts an item and verifies that list_items returns it.

import pytest
from database import create_db, insert_item, list_items


@pytest.fixture
def db():
    return create_db()


def test_insert_then_list(db):
    insert_item(db, "Test Item", 42.0)
    items = list_items(db)
    assert len(items) == 1
    assert items[0]["name"] == "Test Item"
    assert items[0]["price"] == 42.0

Exercise 2: Composed fixture (Easy)

Create db_with_items that pre-populates the db with 3 items. Write a test that verifies get_item(db_with_items, 2) returns the second item.

@pytest.fixture
def db():
    return create_db()


@pytest.fixture
def db_with_items(db):
    db["items"] = [
        {"id": 1, "name": "Item 1", "price": 1.0},
        {"id": 2, "name": "Item 2", "price": 2.0},
        {"id": 3, "name": "Item 3", "price": 3.0},
    ]
    db["next_id"] = 4
    return db


def test_get_second_item(db_with_items):
    item = get_item(db_with_items, 2)
    assert item is not None
    assert item["name"] == "Item 2"
    assert item["price"] == 2.0

Exercise 3: Fixture with yield (Medium)

Modify the db fixture to use yield and, in the teardown, "clean" the db (reset items and next_id). Verify that a second test using db starts with an empty db even though the previous test inserted data.

@pytest.fixture
def db():
    database = create_db()
    yield database
    # Teardown
    database["items"] = []
    database["next_id"] = 1


def test_first_test_inserts(db):
    insert_item(db, "A", 1.0)
    assert len(list_items(db)) == 1


def test_second_test_has_empty_db(db):
    # Each test receives a new db instance, so this is empty
    assert list_items(db) == []

Exercise 4: Complete CRUD tests (Medium)

Write 4 tests: one for each CRUD operation (create, read, update, delete), using the db and db_with_items fixtures as appropriate.

def test_create_inserts_and_returns_item(db):
    item = insert_item(db, "New Item", 99.99)
    assert item["id"] == 1
    assert get_item(db, 1)["name"] == "New Item"


def test_read_returns_existing_item(db_with_items):
    item = get_item(db_with_items, 1)
    assert item["name"] == "Item A"
    assert item["price"] == 10.0


def test_update_modifies_item(db_with_items):
    updated = update_item(db_with_items, 1, name="Updated A")
    assert updated["name"] == "Updated A"
    assert get_item(db_with_items, 1)["name"] == "Updated A"


def test_delete_removes_item(db_with_items):
    result = delete_item(db_with_items, 1)
    assert result is True
    assert get_item(db_with_items, 1) is None
    assert len(list_items(db_with_items)) == 1

Exercise 5: Dependency override with FastAPI (Medium-High)

If you have a FastAPI app with a GET /items/{id} endpoint that uses Depends(get_db), write a test that uses dependency_overrides to inject a test database. The test should insert an item, call the endpoint, and verify the response.

# Assuming main.py with:
# app = FastAPI()
# @app.get("/items/{item_id}")
# def read_item(item_id: int, db=Depends(get_db)):
#     ...

from fastapi.testclient import TestClient
from main import app, get_db


@pytest.fixture
def client(db):
    def override_get_db():
        yield db

    app.dependency_overrides[get_db] = override_get_db
    with TestClient(app) as c:
        yield c
    app.dependency_overrides.clear()


def test_get_item_via_api(client, db):
    from database import insert_item
    insert_item(db, "API Item", 33.0)

    response = client.get("/items/1")
    assert response.status_code == 200
    assert response.json()["name"] == "API Item"

Exercise 6: Prompt for Claude Code (Easy)

Write a prompt you'd give Claude Code to generate integration tests that use database fixtures. Include at least 3 specific requirements.

Generate integration tests for the items API in main.py.

Requirements:
1. Use a `db` fixture that creates an empty database per test
2. Use a `db_with_items` fixture with 2 pre-loaded items
3. Use dependency_overrides to inject the test db into the app
4. Tests: GET /items (list), GET /items/1 (by id), POST /items (create), PUT /items/1 (update), DELETE /items/1 (delete)
5. Each test must be independent
6. Arrange-act-assert pattern

Troubleshooting

Problem 1: Tests fail when run together but pass individually

Cause: The tests share state (the same db, a global variable, etc.).

Solution: Make sure each test receives its own instance of db via a fixture with function scope. Don't reuse a db as a module variable across tests.

Problem 2: The db_with_items fixture doesn't have the expected data

Cause: The db fixture that db_with_items uses may be returning a shared reference that another test modified, or the dependency order is wrong.

Solution: db_with_items(db) receives db from the db fixture. Each call to db creates a new instance. Verify that db returns a fresh create_db() every time. If you're using scope="module", change it to scope="function".

Problem 3: dependency_overrides doesn't seem to apply

Cause: The override is applied after the client was already created, or it's cleared before the test finishes.

Solution: Apply the override before creating the TestClient. Use a fixture that does app.dependency_overrides[get_db] = override and then yield TestClient(app), and in the teardown app.dependency_overrides.clear().

Problem 4: get_test_db() returns None

Cause: The get_test_db function is called before the fixture has initialized the variable, or the variable is in the wrong scope.

Solution: Avoid globals. Use a fixture that receives db and defines override_get_db returning that db, and pass that function to dependency_overrides within the test's own context.

Problem 5: The teardown with yield doesn't run if the test fails

Cause: In theory pytest runs the teardown after yield even if there are exceptions. If it doesn't, it may be an old version of pytest or an error in the fixture.

Solution: Update pytest. If you use yield, the block after the yield is the teardown and pytest runs it. For critical resources, consider a @pytest.fixture with a try/finally block if you suspect problems.

Project Connection

In this module's test pyramid project:

• You need database fixtures for the integration tests
• The endpoint tests (GET, POST, PUT, DELETE) must use an isolated test database
• Each test must start with a known state: an empty db or a pre-populated db, depending on the case
• dependency_overrides lets you connect the FastAPI app to the test db without touching production code

The fixtures you define here are the exact pattern you'll scale up in Module 8 with SQLAlchemy and PostgreSQL. The concept is the same: clean setup per test, optional teardown, zero dependencies between tests.

Summary

• ✅ Tests that share a database are fragile and dependent on the execution order
• ✅ Each test should have its own clean state — use fixtures to create a db per test
• ✅ For this module we use a dict-based db; the pattern transfers to SQLAlchemy
• ✅ An empty db and a pre-populated db_with_items are typical fixtures for CRUD
• ✅ yield in fixtures enables teardown; function scope gives total isolation
• ✅ app.dependency_overrides[get_db] injects the test database into FastAPI
• ✅ Claude Code can generate CRUD tests with fixtures if you give it the context of database.py and the requirements

Next capsule: E2E testing — complete user flows via the API (create → read → update → delete).

Additional Resources

1. pytest Fixtures - Official fixtures documentation
2. pytest Fixture Scope - Function, module, session
3. FastAPI Testing: Override dependencies - How to override dependencies in tests
4. Test Database Patterns (Martin Fowler) - Test database patterns in the pyramid
5. SQLite In-Memory Databases - For when you migrate to SQLAlchemy in Module 8
6. pytest Yield Fixtures - Teardown with yield

Module 3, Capsule 04 — Testing with Claude Code Guide