FastAPI Integration Testing — Test Against a Real Database

🎯 What You Will Learn

Unit tests with a mocked database give you a green checkmark and a false sense of safety. Integration tests hit a real database through your real endpoints — and catch the bugs that actually reach production.

Why a mocked DB hides real bugs (see it pass while prod breaks)
Choosing a test database strategy (and why it should match prod)
Swapping the DB with dependency_overrides + TestClient
pytest fixtures for engine, session, and client
Testing full CRUD flows and error paths (404, 422, 409)
Per-test isolation so tests never leak state into each other

Prerequisites: you can write a basic unit test (fastapi-unit-testing) and you have a CRUD app talking to Postgres (fastapi-crud-patterns, fastapi-database-transactions). We'll test a small User API end-to-end.

fastapi-integration/
├── requirements.txt
├── docker-compose.yml       # postgres for local + CI
├── app/
│   ├── __init__.py
│   ├── database.py           # get_session (the thing we override)
│   ├── models.py             # User (email unique)
│   ├── schemas.py            # UserCreate / UserRead
│   └── main.py               # /users endpoints
└── tests/
    ├── __init__.py
    ├── conftest.py           # fixtures: engine, session, client
    └── test_users.py         # the integration tests

Why a Mocked Database Lies to You (See It First)

Here's a "passing" unit test. It mocks the session, so it never runs a single line of SQL — and that's exactly the problem.

tests/test_unit_mocked.py (looks green, proves nothing)

from unittest.mock import MagicMock


def test_create_user_unit():
    fake_session = MagicMock()          # no real database at all
    # ...call the handler with the fake session...
    # assert it "worked"
    assert True  # 🟢 passes forever, even if the real SQL is broken

⚠️

What this misses: a missing UNIQUE index, a wrong column type, a broken join, a migration that never ran, a NOT NULL you forgot. The mock happily returns whatever you tell it, so the test is green while a real request would 500. Mocks test your code's logic; they can't test your code against the database.

	Unit test	Integration test
Database	Mocked / faked	Real (a test DB)
Speed	Microseconds	Milliseconds
Catches SQL / schema bugs	❌ No	✅ Yes
Catches constraint violations	❌ No	✅ Yes
Tests the request → response path	Partly	✅ End-to-end

💡

Ang importante dito: you want both. Unit tests for pure logic (fast, many); integration tests for the wiring that touches the DB (slower, fewer, high-value). This post is about the second kind.

Choosing a Test Database

The golden rule of integration testing: test against the database you run in production. If prod is Postgres, test on Postgres — not SQLite. Your options:

Option	Good for	Trade-off
Dedicated test DB (e.g. app_test on local/CI Postgres)	Real parity, simple, works great in CI	You manage schema setup/teardown
testcontainers-python (spins up Postgres in Docker per run)	Perfect isolation, zero shared state	Needs Docker available; slower cold start
SQLite in-memory	Blazing fast smoke tests	❌ Different SQL dialect — misses Postgres-only bugs

⚠️

SQLite feels tempting (fast, no setup) but it silently accepts SQL Postgres rejects and lacks features you rely on (proper JSONB, certain constraints, ARRAY). Use it only for quick logic smoke tests. For trustworthy integration tests, use Postgres.

We'll use a dedicated Postgres test database — the simplest option that gives real parity.

The Key Trick: dependency_overrides

Your endpoints get their session from a dependency (get_session). FastAPI lets tests override any dependency, so we point the app at the test database without touching a line of app code. This is the whole foundation of testing FastAPI.

app/database.py (the app under test — unchanged)

from sqlmodel import Session, create_engine

from app.settings import settings

engine = create_engine(settings.database_url)


def get_session():
    with Session(engine) as session:
        yield session   # <-- the dependency we'll override in tests

tests/conftest.py (the override, conceptually)

from app.main import app
from app.database import get_session


def get_session_override():
    # return the TEST session instead of the real one
    return test_session


app.dependency_overrides[get_session] = get_session_override
# ... run tests ...
app.dependency_overrides.clear()   # always clean up

💡

Because the override is keyed by the function object get_session, every endpoint that depends on it — directly or through another dependency — now uses your test session. One override, whole app redirected.

pytest Fixtures: engine, session, client

Now the real setup. Three fixtures in conftest.py: a session bound to the test DB (with fresh tables per test for isolation), and a TestClient wired to use it via the override.

tests/conftest.py

import pytest
from fastapi.testclient import TestClient
from sqlmodel import Session, SQLModel, create_engine

from app.database import get_session
from app.main import app

TEST_DATABASE_URL = "postgresql+psycopg://app:app@localhost:5432/app_test"


@pytest.fixture(name="session")
def session_fixture():
    engine = create_engine(TEST_DATABASE_URL)
    SQLModel.metadata.create_all(engine)     # fresh schema for this test
    with Session(engine) as session:
        yield session
    SQLModel.metadata.drop_all(engine)       # tear it all down after
    engine.dispose()


@pytest.fixture(name="client")
def client_fixture(session: Session):
    # the app uses OUR test session for the duration of the test
    app.dependency_overrides[get_session] = lambda: session
    client = TestClient(app)
    yield client
    app.dependency_overrides.clear()

⚠️

Never point tests at your real database. drop_all() would wipe it. Use a dedicated app_test database, and ideally guard it — e.g. assert the URL ends with _test before creating the engine.

💡

Creating and dropping tables per test is the simplest correct isolation and is plenty fast for hundreds of tests. Section 8 shows a faster transaction-rollback variant once your suite grows.

Your First Integration Test

This test sends a real HTTP request through TestClient, which runs your endpoint, which writes to the real test database — then reads it back. A genuine round trip.

tests/test_users.py

def test_create_user(client):
    response = client.post(
        "/users",
        json={"email": "[email protected]", "full_name": "Ada Lovelace", "password": "secret"},
    )

    assert response.status_code == 201
    body = response.json()
    assert body["email"] == "[email protected]"
    assert body["full_name"] == "Ada Lovelace"
    assert "id" in body
    assert "password" not in body        # response_model safety, verified end-to-end

✅

This one test proves a lot: the route exists, the schema validates, the row inserts into a real table, the response model actually strips the password, and a 201 comes back. No mock could vouch for all of that.

Testing the Full CRUD Flow

Integration tests shine when you exercise a whole flow: create, then read the thing you created, then confirm it shows up in the list. Each assertion leans on a real DB round trip.

tests/test_users.py

def test_create_then_read_round_trip(client):
    created = client.post(
        "/users",
        json={"email": "[email protected]", "full_name": "Grace Hopper", "password": "pw"},
    ).json()

    # read it back by id — proves it was really persisted
    response = client.get(f"/users/{created['id']}")
    assert response.status_code == 200
    assert response.json()["id"] == created["id"]
    assert response.json()["email"] == "[email protected]"


def test_list_users(client):
    client.post("/users", json={"email": "[email protected]", "full_name": "A", "password": "pw"})
    client.post("/users", json={"email": "[email protected]", "full_name": "B", "password": "pw"})

    response = client.get("/users")
    assert response.status_code == 200
    assert len(response.json()) == 2     # isolation: only THIS test's rows exist

💡

Notice test_list_users can safely assert == 2. Thanks to per-test isolation (fresh tables), no rows from other tests are hanging around. That predictability is what makes integration tests maintainable.

Testing Error Paths (404, 422, 409)

The happy path is the easy half. Real confidence comes from testing what happens when things go wrong — and these are exactly the cases mocks fumble.

tests/test_users.py

def test_get_missing_user_returns_404(client):
    response = client.get("/users/999999")
    assert response.status_code == 404


def test_invalid_email_returns_422(client):
    # Pydantic validation rejects a bad email before it hits the DB
    response = client.post(
        "/users",
        json={"email": "not-an-email", "full_name": "X", "password": "pw"},
    )
    assert response.status_code == 422


def test_duplicate_email_returns_409(client):
    payload = {"email": "[email protected]", "full_name": "First", "password": "pw"}
    assert client.post("/users", json=payload).status_code == 201

    # second insert hits the UNIQUE constraint in the REAL database
    response = client.post("/users", json=payload)
    assert response.status_code == 409

That last test only works because the app catches the database's integrity error and turns it into a clean 409 — the kind of code a mocked test would never exercise:

app/main.py (the handler being tested)

from sqlalchemy.exc import IntegrityError


@app.post("/users", response_model=UserRead, status_code=status.HTTP_201_CREATED)
def create_user(payload: UserCreate, session: SessionDep):
    user = User(
        email=payload.email,
        full_name=payload.full_name,
        hashed_password=hash_password(payload.password),
    )
    session.add(user)
    try:
        session.commit()
    except IntegrityError:
        session.rollback()
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Email already exists")
    session.refresh(user)
    return user

✅

This is the payoff: the duplicate-email test caught a real database constraint firing and verified your error handling around it. A unit test with a mocked session would sail right past this bug.

Faster Isolation: Transaction Rollback per Test

Dropping and recreating tables per test is correct but gets slow with hundreds of tests. The pro move: wrap each test in a transaction and roll it back at the end. Setup runs once; each test starts clean in milliseconds.

tests/conftest.py (faster variant)

import pytest
from sqlalchemy import event
from sqlmodel import Session, SQLModel, create_engine

engine = create_engine(TEST_DATABASE_URL)
SQLModel.metadata.create_all(engine)   # once for the whole run


@pytest.fixture(name="session")
def session_fixture():
    connection = engine.connect()
    transaction = connection.begin()               # outer transaction
    session = Session(bind=connection)

    # restart a SAVEPOINT after each inner commit so app-level
    # commit() doesn't actually persist past the test
    nested = connection.begin_nested()

    @event.listens_for(session, "after_transaction_end")
    def restart_savepoint(sess, trans):
        nonlocal nested
        if not nested.is_active:
            nested = connection.begin_nested()

    yield session

    session.close()
    transaction.rollback()     # undo EVERYTHING this test did
    connection.close()

💡

The begin_nested() + after_transaction_end trick means even endpoints that call session.commit() (like our transfer/create code) are still fully rolled back after the test. You get speed and perfect isolation. (Same begin_nested savepoints from fastapi-database-transactions, reused for testing.)

⚠️

Start with the simple create_all/drop_all version. Only reach for the rollback variant when suite speed actually hurts — it's more moving parts, and premature optimization here just adds confusion.

Run It

requirements.txt

fastapi==0.116.1
uvicorn[standard]==0.30.6
sqlmodel==0.0.22
psycopg[binary]==3.2.1
pydantic[email]==2.9.2
pydantic-settings==2.5.2
pytest==8.3.2
httpx==0.27.2                # required by TestClient

1Spin up the test database

run.sh

# start postgres and create the test DB
docker compose up -d
docker compose exec db createdb -U app app_test || true

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

2Run pytest

$ pytest -v
tests/test_users.py::test_create_user PASSED                       [ 16%]
tests/test_users.py::test_create_then_read_round_trip PASSED       [ 33%]
tests/test_users.py::test_list_users PASSED                        [ 50%]
tests/test_users.py::test_get_missing_user_returns_404 PASSED      [ 66%]
tests/test_users.py::test_invalid_email_returns_422 PASSED         [ 83%]
tests/test_users.py::test_duplicate_email_returns_409 PASSED       [100%]

======================= 6 passed in 0.42s =======================

✅

You did it: six tests exercising your real endpoints against a real Postgres — CRUD round trips and every error path — all isolated and repeatable. This is the suite you actually trust before a deploy.

Common Mistakes (and Fixes)

Mistake	Symptom	Fix
Testing against SQLite when prod is Postgres	Tests pass; Postgres-only bugs ship	Use a real Postgres test DB
Forgetting `httpx`	`TestClient` import/runtime error	`pip install httpx`
Not clearing `dependency_overrides`	Overrides leak into other tests	`app.dependency_overrides.clear()` in the fixture
No isolation between tests	Order-dependent, flaky assertions on counts	Fresh tables (or rollback) per test
Sharing one session for setup and the client	Data written in-test not visible to the endpoint (or vice versa)	Use the SAME session in the override and arrange steps
Pointing tests at the dev/prod DB	`drop_all()` wipes real data	Dedicated `_test` DB + a URL guard

Production Notes

Run migrations in CI, don't just create_all. For real parity, apply your Alembic migrations against the test DB (fastapi-alembic-migrations) so you also test that migrations work — not just the models.
Wire it into CI. GitHub Actions can spin up a Postgres service container and run pytest on every push. The same docker-compose.yml works locally and in the pipeline.
Keep the pyramid. Many fast unit tests, a solid layer of integration tests for DB-touching paths, a few end-to-end tests. Don't integration-test pure functions.
Seed with factories. As the suite grows, helper functions or a factory library keep test data setup readable instead of copy-pasted JSON.

What's Next

✅

🚀 Recommended next reads:

fastapi-unit-testing — the fast, mocked half of the testing pyramid
fastapi-database-transactions — the savepoints powering the rollback isolation trick
fastapi-crud-patterns — the endpoints these tests exercise
fastapi-docker — the compose setup that runs your test Postgres locally and in CI

Recap: mocks test your logic; integration tests test your logic against the database. Override get_session to point TestClient at a real Postgres test DB, keep each test isolated (fresh tables, or transaction rollback for speed), and cover the error paths — 404, 422, 409 — where the real bugs hide. That's the suite worth trusting before you ship.

fastapi-integration/                # ✅ finished
├── requirements.txt                # + pytest, httpx
├── docker-compose.yml              # postgres (local + CI)
├── app/                            # the app under test (unchanged)
└── tests/
    ├── conftest.py                 # engine + session + client fixtures
    └── test_users.py               # CRUD round trips + 404/422/409

FastAPI Integration Testing (Test Against a Real Database)

🎯 What You Will Learn

Why a Mocked Database Lies to You (See It First)

Choosing a Test Database

The Key Trick: dependency_overrides

pytest Fixtures: engine, session, client

Your First Integration Test

Testing the Full CRUD Flow

Testing Error Paths (404, 422, 409)

Faster Isolation: Transaction Rollback per Test

Run It

Common Mistakes (and Fixes)

Production Notes

What's Next

About the Author

Thirdy Gayares

🎯 What You Will Learn

1Why a Mocked Database Lies to You (See It First)

2Choosing a Test Database

3The Key Trick: dependency_overrides

4pytest Fixtures: engine, session, client

5Your First Integration Test

6Testing the Full CRUD Flow

7Testing Error Paths (404, 422, 409)

8Faster Isolation: Transaction Rollback per Test

9Run It

10Common Mistakes (and Fixes)

11Production Notes

12What's Next

About the Author

Thirdy Gayares

Why a Mocked Database Lies to You (See It First)

Choosing a Test Database

The Key Trick: dependency_overrides

pytest Fixtures: engine, session, client

Your First Integration Test

Testing the Full CRUD Flow

Testing Error Paths (404, 422, 409)

Faster Isolation: Transaction Rollback per Test

Run It

Common Mistakes (and Fixes)

Production Notes

What's Next