Skip to content

20 · Testing the Request Lifecycle with a Real Test Database

The previous lesson made browser retries safe:

Browser creates an idempotency key
→ POST starts a review
→ SQLite stores the key with the job
→ response may disappear
→ browser retries with the same key
→ FastAPI returns the existing job

Today we’ll verify that behavior automatically.

Our main test will send the same logical request twice and prove three things:

First request
→ 202 Accepted
→ new job created
Second request with same key
→ 200 OK
→ existing job returned
SQLite
→ contains exactly one review job

This test sits here in the architecture:

TestClient
→ middleware
→ router
→ Pydantic validation
→ dependencies
→ authorization query
→ service
→ SQLAlchemy
→ isolated SQLite database
→ response model
→ TestClient

There is one important difference from the real lifecycle:

Production
Browser + JavaScript → real network → FastAPI
Today’s test
Python TestClient → ASGI application directly

TestClient exercises FastAPI without opening a real HTTP socket. It is based on HTTPX and works naturally with ordinary pytest functions. FastAPI

It does not execute Jinja HTML, construct a DOM, or run our JavaScript. We will keep that boundary honest throughout the lesson.


In the previous route, CSRF verification appeared as a direct function call:

verify_csrf_token(request)

That works, but expressing it as a dependency makes the route’s requirements more visible and independently testable:

app/security_dependencies.py
import secrets
from typing import Annotated
from fastapi import Depends, HTTPException, Request
def verify_csrf_token(request: Request) -> None:
session_token = request.session.get("csrf_token")
submitted_token = request.headers.get("X-CSRF-Token")
if (
session_token is None
or submitted_token is None
or not secrets.compare_digest(
submitted_token.encode("utf-8"),
session_token.encode("utf-8"),
)
):
raise HTTPException(
status_code=403,
detail="Invalid CSRF token",
)
CsrfProtected = Annotated[
None,
Depends(verify_csrf_token),
]

The route can now declare:

@router.post(
"/{document_id}/reviews",
response_model=ReviewAccepted,
status_code=202,
)
def start_document_review(
request: Request,
response: Response,
payload: ReviewStart,
idempotency_key: IdempotencyKey,
document: AuthorizedDocument,
operator: ReviewOperator,
db: DbSession,
_csrf: CsrfProtected,
):
try:
job, replayed = queue_document_review(
db=db,
document_id=document.id,
firm_id=document.firm_id,
requested_by_user_id=operator["id"],
idempotency_key=idempotency_key,
depth=payload.depth,
)
except (IdempotencyConflictError, ReviewAlreadyRunningError) as exc:
raise HTTPException(409, str(exc)) from exc
if replayed:
response.status_code = 200
return ReviewAccepted(
job_id=job.id,
document_id=job.document_id,
status=job.status,
progress=job.progress,
replayed=replayed,
events_url=str(
request.url_for(
"document_review_events",
document_id=job.document_id,
).include_query_params(job_id=job.id)
),
result_summary=job.result_summary,
error_message=job.error_message,
)

The underscore in:

_csrf

communicates that the route does not use a returned value.

It needs the dependency’s behavior:

Run CSRF verification
→ continue if valid
→ stop with 403 if invalid

This is still real security behavior in production. Making it a dependency does not weaken it.

It merely gives tests a clear seam where they can choose whether to exercise the real CSRF machinery or concentrate on another part of the lifecycle.


Our tests must never use:

review.db

That contains application data and persists between runs.

Instead, every test receives a temporary SQLite file:

/tmp/.../review-test.db

Each test gets a separate temporary path. Pytest may retain recent temporary directories for debugging; isolation comes from distinct database files, not a promise that each file disappears immediately.

Here is the fixture:

This is a focused integration-test excerpt. create_app() is an assumed application factory that returns a fresh FastAPI application with its middleware and routes, without opening the live database or starting a worker. seed_review_scenario() is an assumed fixture helper that inserts valid user and document rows. Their application-specific implementations are omitted.

We keep the flat app/dependencies.py module from earlier lessons. The new CSRF dependency lives in app/security_dependencies.py; creating a dependencies/ package with the same name would require an explicit import migration.

tests/conftest.py
import pytest
from fastapi.testclient import TestClient
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.database import Base, get_db
from app.dependencies import get_current_user
from app.security_dependencies import verify_csrf_token
from app.main import create_app
# Importing these registers every mapped table with Base.metadata.
from app.models.documents import Document
from app.models.review_jobs import ReviewJob
from app.models.users import User
from tests.factories import seed_review_scenario
@pytest.fixture
def review_client(tmp_path):
database_path = tmp_path / "review-test.db"
engine = create_engine(
f"sqlite:///{database_path}",
connect_args={
"check_same_thread": False,
},
)
TestSessionLocal = sessionmaker(
bind=engine,
autoflush=False,
expire_on_commit=False,
)
Base.metadata.create_all(bind=engine)
with TestSessionLocal.begin() as db:
seed_review_scenario(
db,
user_id=27,
firm_id=10,
document_id=101,
review_status="not_started",
)
test_app = create_app()
def override_get_db():
with TestSessionLocal() as db:
yield db
def override_current_user():
return {
"id": 27,
"firm_id": 10,
"role": "reviewer",
}
def allow_test_csrf():
return None
test_app.dependency_overrides[get_db] = override_get_db
test_app.dependency_overrides[
get_current_user
] = override_current_user
test_app.dependency_overrides[
verify_csrf_token
] = allow_test_csrf
try:
with TestClient(test_app) as client:
yield client, TestSessionLocal
finally:
test_app.dependency_overrides.clear()
engine.dispose()

Let’s unpack this before looking at the test.


We could write:

sqlite:///:memory:

That creates an in-memory SQLite database.

The trouble is that an in-memory database is associated with its database connection. If different threads or connections are involved, code can unexpectedly encounter a different empty database.

TestClient, synchronous FastAPI routes, and SQLAlchemy connection handling make that detail easy to trip over. SQLAlchemy documents special pooling requirements for sharing an in-memory SQLite database across threads. SQLAlchemy 2.0 Documentation

A temporary file is boring and predictable:

All test sessions
→ connect to the same temporary database file

The file remains isolated from production. Pytest manages its temporary directory and may retain it briefly for debugging.

For a small SQLite application, that clarity is usually worth the tiny amount of disk activity.


This line creates a separate SQLAlchemy engine:

engine = create_engine(
f"sqlite:///{database_path}",
connect_args={
"check_same_thread": False,
},
)

It does not alter the production engine.

We now have two completely separate connection systems:

Environment Engine points to
Application review.db
Current test Temporary review-test.db

The test session factory is then bound to the test engine:

TestSessionLocal = sessionmaker(
bind=engine,
autoflush=False,
expire_on_commit=False,
)

Whenever the test creates:

TestSessionLocal()

that session communicates with the temporary database.


The fixture calls:

Base.metadata.create_all(bind=engine)

Earlier, we removed create_all() from normal application startup because Alembic owns production schema evolution.

Using it here does not reverse that decision.

This particular test asks:

Does the current route, service, and model code work together?

Creating the current model schema is a quick way to prepare that test.

But it does not prove:

Can every historical Alembic migration transform an empty or older database into this schema?

Those are separate claims and should have separate tests.

Route integration test
→ current application code against current model schema
Migration test
→ Alembic revision history against a fresh database

We’ll return to that distinction shortly.


The fixture calls:

seed_review_scenario(
db,
user_id=27,
firm_id=10,
document_id=101,
review_status="not_started",
)

This helper creates a valid user and document using the ordinary SQLAlchemy models.

Conceptually, SQLite begins each test with:

User
id: 27
firm_id: 10
role: reviewer
Document
id: 101
firm_id: 10
review_status: not_started
Review jobs
none

Tests should deliberately establish their starting state.

Depending on records left by an earlier test creates order-dependent failures:

Test A passes alone
Test B passes alone
Test B fails after Test A

A fresh temporary database prevents that kind of leakage.


The production dependency creates sessions using the production session factory:

def get_db():
with SessionLocal() as db:
yield db

Our test supplies:

def override_get_db():
with TestSessionLocal() as db:
yield db

Then it registers the replacement:

test_app.dependency_overrides[get_db] = override_get_db

FastAPI’s dependency_overrides dictionary maps an original dependency function to the function that should replace it during the test. FastAPI

When the route asks for:

db: DbSession

FastAPI therefore creates a test database session.

The route and service do not know that anything changed:

Route receives a Session
Service receives a Session
SQLAlchemy executes SQL

Only the session’s destination changed.

That is one practical benefit of dependency injection: infrastructure can be replaced at the application boundary without adding test conditions to business code.

We do not write:

if testing:
use_test_database()

inside the route.


The fixture also replaces:

get_current_user

with:

def override_current_user():
return {
"id": 27,
"firm_id": 10,
"role": "reviewer",
}

This lets the test concentrate on:

Authorized user
→ authorized document lookup
→ service
→ database
→ response

It deliberately does not test:

  • Password verification
  • Session-cookie signing
  • Login redirects
  • Session expiration
  • The real authentication dependency

Those require their own tests.

This distinction matters. A dependency override is not a magical fake that somehow proves the original dependency works.

It means:

For this test, assume authentication already produced this user.

The authorization query still runs. The user is synthetic, but the document must still match:

document.firm_id == current_user["firm_id"]

The fixture replaces the CSRF dependency:

def allow_test_csrf():
return None

This means today’s test does not need to manufacture a signed browser session containing a matching token.

Again, we should describe the coverage accurately:

Covered
→ the installed test dependency resolves
→ the downstream lifecycle works with CSRF assumed valid
Not covered here
→ token comparison
→ session-token storage
→ rejection of an invalid token

A separate focused test should exercise the real dependency with matching and mismatching tokens.

The replay test alone would still pass if someone accidentally removed the CSRF requirement from the route. A rejection test with the override removed is what checks that missing protection path.

Tests become easier to trust when each one makes a precise claim instead of pretending to prove the entire application at once.


Here is the central test:

tests/test_review_routes.py
from uuid import UUID
from sqlalchemy import select
from app.models.documents import Document
from app.models.review_jobs import ReviewJob
def test_same_operation_creates_only_one_job(
review_client,
):
client, TestSessionLocal = review_client
operation_key = (
"8c043429-e90e-4351-ad69-1aa0dd938a4d"
)
request_headers = {
"Accept": "application/json",
"Content-Type": "application/json",
"X-CSRF-Token": "test-token",
"Idempotency-Key": operation_key,
}
request_body = {
"depth": "standard",
}
first_response = client.post(
"/documents/101/reviews",
headers=request_headers,
json=request_body,
)
second_response = client.post(
"/documents/101/reviews",
headers=request_headers,
json=request_body,
)
assert first_response.status_code == 202
assert second_response.status_code == 200
first_data = first_response.json()
second_data = second_response.json()
assert first_data["replayed"] is False
assert second_data["replayed"] is True
assert second_data["job_id"] == first_data["job_id"]
assert second_data["document_id"] == 101
with TestSessionLocal() as db:
jobs = db.scalars(
select(ReviewJob)
).all()
document = db.get(Document, 101)
assert len(jobs) == 1
assert jobs[0].idempotency_key == operation_key
assert jobs[0].depth == "standard"
assert document.review_status == "queued"

Now we can trace the execution.


The test starts with a fixed UUID string:

operation_key = (
"8c043429-e90e-4351-ad69-1aa0dd938a4d"
)

In the browser, JavaScript creates this value using:

crypto.randomUUID()

In the test, a fixed value is more useful.

If the test fails, its input is repeatable and obvious.

The test then creates Python dictionaries:

request_headers = {...}
request_body = {
"depth": "standard",
}

These are not JavaScript objects.

The production and test representations compare like this:

Stage Browser interaction Test interaction
Caller’s value JavaScript object Python dictionary
JSON encoding JSON.stringify() HTTPX encodes json=
Request body JSON bytes JSON bytes
FastAPI input Pydantic object Pydantic object
Response body JSON bytes JSON bytes
Parsed result JavaScript object Python dictionary

The two callers differ, but they meet FastAPI at the same HTTP contract.


The first request is:

first_response = client.post(
"/documents/101/reviews",
headers=request_headers,
json=request_body,
)

client.post() is synchronous from the test’s perspective.

Python waits until the FastAPI application produces a response.

Internally, TestClient converts the arguments into an ASGI HTTP request and passes that request directly into the application. There is no browser, DNS lookup, TCP connection, or external web server.

The application still sees ordinary request information:

POST /documents/101/reviews
Content-Type: application/json
Idempotency-Key: 8c043429-e90e-4351-ad69-1aa0dd938a4d
{"depth":"standard"}

FastAPI matches:

POST /documents/{document_id}/reviews

It converts the URL value:

101

into a Python integer.

It parses the idempotency header into:

UUID("8c043429-e90e-4351-ad69-1aa0dd938a4d")

It parses the JSON body as:

ReviewStart(
depth="standard",
)

The route does not receive the original JSON text.

It receives validated Python values.


FastAPI now resolves the dependency graph.

The overridden authentication dependency returns:

{
"id": 27,
"firm_id": 10,
"role": "reviewer",
}

The overridden database dependency creates:

TestSessionLocal()

The real authorized-document dependency uses those values to query:

SELECT ...
FROM documents
WHERE id = 101
AND firm_id = 10;

SQLite returns the seeded document.

This is an important middle ground:

Authentication
→ replaced with a known user
Authorization
→ exercised using a real query

The CSRF override succeeds, so FastAPI finally calls the route.


The service searches for an existing job containing:

user_id: 27
idempotency_key: 8c043429-...

None exists.

It then performs the guarded document transition:

not_started → queued

and constructs a new ReviewJob:

document_id: 101
requested_by_user_id: 27
idempotency_key: 8c043429-...
depth: standard
status: queued
progress: 0

SQLAlchemy sends the UPDATE and INSERT to the temporary SQLite database.

SQLite commits them.

The service returns:

job, False

The route interprets False as:

This request created the job.

FastAPI validates the returned ReviewAccepted model and creates:

HTTP/1.1 202 Accepted
Content-Type: application/json

The response reaches TestClient.


The test sends the exact same request again:

second_response = client.post(
"/documents/101/reviews",
headers=request_headers,
json=request_body,
)

This is a completely new HTTP request.

FastAPI repeats:

  • Route matching
  • Header validation
  • Pydantic body validation
  • Authentication dependency resolution
  • Authorization query
  • Database-session creation
  • CSRF dependency resolution

A previous successful request does not permanently authorize later requests.

The service now finds the stored key.

It verifies that the existing job belongs to the same operation:

Same user
Same document
Same review depth
Same idempotency key

It returns:

existing_job, True

No new ReviewJob is added.

The route changes the default response status:

if replayed:
response.status_code = 200

The second response therefore means:

HTTP/1.1 200 OK
This operation was already accepted.
Here is its existing durable job.

The test checks:

assert first_response.status_code == 202
assert second_response.status_code == 200

Then:

first_data = first_response.json()
second_data = second_response.json()

These calls parse the JSON response bodies into Python dictionaries.

The result may resemble:

first_data == {
"job_id": 1,
"document_id": 101,
"status": "queued",
"progress": 0,
"replayed": False,
"events_url":
"http://testserver/documents/101/review-events?job_id=1",
"result_summary": None,
"error_message": None,
}

and:

second_data == {
"job_id": 1,
"document_id": 101,
"status": "queued",
"progress": 0,
"replayed": True,
"events_url":
"http://testserver/documents/101/review-events?job_id=1",
"result_summary": None,
"error_message": None,
}

The test verifies:

assert second_data["job_id"] == first_data["job_id"]

That is the HTTP-level proof that both attempts refer to the same durable job.


An HTTP response alone is not enough to prove that no duplicate row exists.

The test opens a new database session:

with TestSessionLocal() as db:

This is not the session used by either request. Those request-scoped sessions have already closed.

The test queries every review job:

jobs = db.scalars(
select(ReviewJob)
).all()

Then it asserts:

assert len(jobs) == 1

This is the persistence-level proof.

The complete evidence is:

HTTP response 1
→ new job
HTTP response 2
→ same job replayed
SQLite
→ exactly one row

Finally:

assert document.review_status == "queued"

confirms that the related document state also matches the accepted operation.


A second small test verifies the Pydantic boundary:

def test_invalid_review_depth_creates_no_job(
review_client,
):
client, TestSessionLocal = review_client
response = client.post(
"/documents/101/reviews",
headers={
"Idempotency-Key":
"73fce83e-c281-4da4-8771-5400ea8f2fd4",
},
json={
"depth": "instant",
},
)
assert response.status_code == 422
with TestSessionLocal() as db:
jobs = db.scalars(
select(ReviewJob)
).all()
assert jobs == []

Suppose the schema allows only:

Literal[
"quick",
"standard",
"thorough",
]

Then:

{"depth":"instant"}

fails before the route executes normally.

The lifecycle stops here:

Test request
→ router match
→ Pydantic validation fails
→ 422 response

It never reaches:

service
→ INSERT
→ COMMIT

The empty database assertion proves that invalid HTTP input caused no persistent side effect.


We should also seed a document belonging to another firm:

Document 202
firm_id: 20

Our test user belongs to:

firm_id: 10

The request:

response = client.post(
"/documents/202/reviews",
headers={
"Idempotency-Key":
"a9402842-64aa-4df7-8cc2-8d969565f64b",
},
json={
"depth": "standard",
},
)
assert response.status_code == 404

The authorization query includes:

WHERE id = 202
AND firm_id = 10

It finds nothing.

The service never receives document 202, and no job is inserted.

The test user exists only because of an authentication override, but the server-side authorization rule remains authoritative.


Our central test is valuable, but it is not a browser test.

It does not prove that:

  • Jinja renders the correct data-start-url
  • JavaScript registers the submit event
  • crypto.randomUUID() is called once
  • The key survives a simulated network failure
  • The depth selector remains disabled during uncertainty
  • The DOM displays “Existing review recovered”
  • EventSource reconnects and shows progress
  • A real cookie passes authentication
  • A real CSRF token is accepted

Those behaviors need different test boundaries.

Test type Primary question
Service test Does business logic produce correct database state?
FastAPI route test Does the HTTP contract reach the service correctly?
Authentication test Do cookies and sessions identify the user correctly?
Migration test Can Alembic construct the required schema?
Browser test Does HTML and JavaScript produce the correct visible behavior?

Trying to test everything through one enormous browser scenario usually produces a slow test that is difficult to diagnose.

A useful suite has several layers.


create_all() prepared today’s route test, but it bypassed the migration files.

A migration smoke test can create another empty SQLite database and run:

tests/test_migrations.py
from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine, inspect
def test_alembic_upgrades_empty_database_to_head(
tmp_path,
):
database_path = tmp_path / "migration-test.db"
database_url = f"sqlite:///{database_path}"
config = Config("alembic.ini")
config.set_main_option(
"sqlalchemy.url",
database_url,
)
command.upgrade(config, "head")
engine = create_engine(database_url)
inspector = inspect(engine)
assert "documents" in inspector.get_table_names()
assert "review_jobs" in inspector.get_table_names()
review_job_columns = {
column["name"]
for column in inspector.get_columns(
"review_jobs"
)
}
assert "idempotency_key" in review_job_columns

Alembic officially supports invoking upgrade() programmatically with a Config object, just as shown here. Alembic 1.20.0 documentation

This test follows a different lifecycle:

Empty SQLite database
→ Alembic reads current revision
→ runs every upgrade in order
→ records head revision
→ test inspects resulting schema

It does not send an HTTP request.

The route test and migration test complement each other:

Migration test
→ proves the database can reach the expected structure
Route test
→ proves application code can use that structure

A browser-level test would start at the outermost layer:

Browser opens Jinja page
→ browser parses HTML
→ JavaScript registers handlers
→ test clicks Start review
→ fetch sends POST
→ FastAPI and SQLite run
→ JavaScript parses response
→ DOM changes

A Playwright-style assertion might conceptually look like:

page.goto("/documents/101")
page.select_option(
"#review-depth",
"standard",
)
page.click("#start-review")
expect(
page.locator("#review-status")
).to_have_text("queued")

That test can prove the visible interaction.

But it is slower and requires more infrastructure:

  • A live application server
  • A browser process
  • Known database state
  • Often a worker process
  • Possibly an SSE connection
  • Cleanup after the scenario

The lower-level route test remains useful because it can identify whether a failure belongs to:

HTTP/service/database

before investigating:

browser/DOM/JavaScript

One final limitation: sequential retries are not a race

Section titled “One final limitation: sequential retries are not a race”

Today’s main test sends:

Request A finishes
→ Request B begins

That proves the ordinary replay path.

It does not fully exercise two simultaneous requests that both initially see no matching key.

The database unique constraint is what protects that race:

Request A checks → no job
Request B checks → no job
Request A inserts → succeeds
Request B inserts → uniqueness failure
Request B reloads A’s job

A dedicated concurrency test can exercise that situation, though SQLite’s locking behavior makes concurrency tests different from tests against PostgreSQL.

The architectural rule remains:

Friendly lookup
→ handles ordinary retries efficiently
Unique constraint
→ protects integrity under races

Both matter.


For the first request:

  1. Pytest creates a temporary directory.
  2. SQLAlchemy creates a test engine pointing to a temporary SQLite file.
  3. Base.metadata.create_all() constructs the current tables.
  4. The fixture inserts a known user and document.
  5. create_app() creates an isolated FastAPI application.
  6. Dependency overrides install the test database session and known user.
  7. TestClient constructs an ASGI request from the Python dictionaries.
  8. FastAPI matches the POST route.
  9. FastAPI validates the document ID and UUID header.
  10. Pydantic validates {"depth": "standard"}.
  11. The authentication override returns user 27.
  12. The real authorization dependency queries document 101 within firm 10.
  13. The database dependency yields a test SQLAlchemy session.
  14. The CSRF test dependency succeeds.
  15. The route calls the real service.
  16. The service finds no matching idempotency key.
  17. SQLAlchemy updates the document and inserts the job.
  18. SQLite commits.
  19. The service returns the new job with replayed=False.
  20. FastAPI validates and serializes the response.
  21. TestClient receives 202 Accepted.
  22. The request-scoped database session closes.

For the replay:

  1. TestClient sends another independent request.
  2. FastAPI resolves and validates everything again.
  3. A new test database session is created.
  4. The service finds the stored idempotency key.
  5. It verifies that the document and depth match.
  6. No second insert occurs.
  7. The service returns the same job with replayed=True.
  8. FastAPI returns 200 OK.
  9. The second request-scoped session closes.
  10. The test opens a separate inspection session.
  11. SQLAlchemy queries all review jobs.
  12. SQLite returns exactly one row.
  13. The assertions pass.
  14. The fixture disposes the test engine.
  15. Pytest manages retention and later cleanup of the temporary directory.

A useful test does more than call a function and receive the expected dictionary.

It establishes a known starting world, crosses a deliberate architectural boundary, and checks the authoritative result:

Known database state
↓
Real HTTP-shaped request
↓
FastAPI validation and dependencies
↓
Real service logic
↓
Real SQLAlchemy transaction
↓
Isolated SQLite database
↓
HTTP response assertions
+
Database assertions

Dependency overrides let us isolate today’s question without adding testing branches to production code.

But every override removes something from the claim the test can make:

Override authentication
→ this test does not prove authentication
Override CSRF
→ this test does not prove CSRF validation
Use TestClient
→ this test does not prove browser JavaScript

That is not a weakness when the boundaries are intentional.

The goal is a collection of understandable tests in which each layer proves what it actually owns—and no test quietly mistakes a Python dictionary, a successful HTTP response, or visible DOM text for the complete truth.