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 jobToday 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 jobThis test sits here in the architecture:
TestClient→ middleware→ router→ Pydantic validation→ dependencies→ authorization query→ service→ SQLAlchemy→ isolated SQLite database→ response model→ TestClientThere is one important difference from the real lifecycle:
ProductionBrowser + JavaScript → real network → FastAPI
Today’s testPython TestClient → ASGI application directlyTestClient 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.
Making CSRF verification replaceable
Section titled “Making CSRF verification replaceable”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:
import secretsfrom 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:
_csrfcommunicates 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 invalidThis 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.
The isolated test database
Section titled “The isolated test database”Our tests must never use:
review.dbThat contains application data and persists between runs.
Instead, every test receives a temporary SQLite file:
/tmp/.../review-test.dbEach 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.
import pytest
from fastapi.testclient import TestClientfrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker
from app.database import Base, get_dbfrom app.dependencies import get_current_userfrom app.security_dependencies import verify_csrf_tokenfrom app.main import create_app
# Importing these registers every mapped table with Base.metadata.from app.models.documents import Documentfrom app.models.review_jobs import ReviewJobfrom app.models.users import User
from tests.factories import seed_review_scenario
@pytest.fixturedef 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.
Why use a temporary file?
Section titled “Why use a temporary file?”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 fileThe 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.
Creating the test engine
Section titled “Creating the test engine”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.
Creating the schema
Section titled “Creating the schema”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 databaseWe’ll return to that distinction shortly.
Seeding known starting data
Section titled “Seeding known starting data”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 noneTests should deliberately establish their starting state.
Depending on records left by an earlier test creates order-dependent failures:
Test A passes aloneTest B passes aloneTest B fails after Test AA fresh temporary database prevents that kind of leakage.
Overriding the database dependency
Section titled “Overriding the database dependency”The production dependency creates sessions using the production session factory:
def get_db(): with SessionLocal() as db: yield dbOur test supplies:
def override_get_db(): with TestSessionLocal() as db: yield dbThen it registers the replacement:
test_app.dependency_overrides[get_db] = override_get_dbFastAPI’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: DbSessionFastAPI therefore creates a test database session.
The route and service do not know that anything changed:
Route receives a SessionService receives a SessionSQLAlchemy executes SQLOnly 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.
Overriding authentication
Section titled “Overriding authentication”The fixture also replaces:
get_current_userwith:
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→ responseIt 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"]Overriding CSRF verification
Section titled “Overriding CSRF verification”The fixture replaces the CSRF dependency:
def allow_test_csrf(): return NoneThis 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 tokenA 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.
The idempotent route test
Section titled “The idempotent route test”Here is the central test:
from uuid import UUID
from sqlalchemy import select
from app.models.documents import Documentfrom 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.
Constructing the first test request
Section titled “Constructing the first test request”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.
What client.post() does
Section titled “What client.post() does”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/reviewsContent-Type: application/jsonIdempotency-Key: 8c043429-e90e-4351-ad69-1aa0dd938a4d
{"depth":"standard"}FastAPI validates the request
Section titled “FastAPI validates the request”FastAPI matches:
POST /documents/{document_id}/reviewsIt converts the URL value:
101into 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.
Dependencies run
Section titled “Dependencies run”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 documentsWHERE 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 queryThe CSRF override succeeds, so FastAPI finally calls the route.
The first service call
Section titled “The first service call”The service searches for an existing job containing:
user_id: 27idempotency_key: 8c043429-...None exists.
It then performs the guarded document transition:
not_started → queuedand constructs a new ReviewJob:
document_id: 101requested_by_user_id: 27idempotency_key: 8c043429-...depth: standardstatus: queuedprogress: 0SQLAlchemy sends the UPDATE and INSERT to the temporary SQLite database.
SQLite commits them.
The service returns:
job, FalseThe route interprets False as:
This request created the job.FastAPI validates the returned ReviewAccepted model and creates:
HTTP/1.1 202 AcceptedContent-Type: application/jsonThe response reaches TestClient.
The second request
Section titled “The second request”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 userSame documentSame review depthSame idempotency keyIt returns:
existing_job, TrueNo new ReviewJob is added.
The route changes the default response status:
if replayed: response.status_code = 200The second response therefore means:
HTTP/1.1 200 OKThis operation was already accepted.Here is its existing durable job.Parsing and comparing the responses
Section titled “Parsing and comparing the responses”The test checks:
assert first_response.status_code == 202assert second_response.status_code == 200Then:
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.
Inspecting SQLite directly
Section titled “Inspecting SQLite directly”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) == 1This is the persistence-level proof.
The complete evidence is:
HTTP response 1→ new job
HTTP response 2→ same job replayed
SQLite→ exactly one rowFinally:
assert document.review_status == "queued"confirms that the related document state also matches the accepted operation.
Testing validation before the service
Section titled “Testing validation before the service”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 responseIt never reaches:
service→ INSERT→ COMMITThe empty database assertion proves that invalid HTTP input caused no persistent side effect.
Testing authorization before the service
Section titled “Testing authorization before the service”We should also seed a document belonging to another firm:
Document 202firm_id: 20Our test user belongs to:
firm_id: 10The request:
response = client.post( "/documents/202/reviews", headers={ "Idempotency-Key": "a9402842-64aa-4df7-8cc2-8d969565f64b", }, json={ "depth": "standard", },)
assert response.status_code == 404The authorization query includes:
WHERE id = 202 AND firm_id = 10It 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.
What this test does not prove
Section titled “What this test does not prove”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.
Testing the Alembic history separately
Section titled “Testing the Alembic history separately”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:
from alembic import commandfrom alembic.config import Configfrom 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_columnsAlembic 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 schemaIt 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 structureWhere a real browser test enters
Section titled “Where a real browser test enters”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 changesA 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/databasebefore investigating:
browser/DOM/JavaScriptOne 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 beginsThat 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 jobRequest B checks → no jobRequest A inserts → succeedsRequest B inserts → uniqueness failureRequest B reloads A’s jobA 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 racesBoth matter.
The complete test lifecycle
Section titled “The complete test lifecycle”For the first request:
- Pytest creates a temporary directory.
- SQLAlchemy creates a test engine pointing to a temporary SQLite file.
Base.metadata.create_all()constructs the current tables.- The fixture inserts a known user and document.
create_app()creates an isolated FastAPI application.- Dependency overrides install the test database session and known user.
- TestClient constructs an ASGI request from the Python dictionaries.
- FastAPI matches the POST route.
- FastAPI validates the document ID and UUID header.
- Pydantic validates
{"depth": "standard"}. - The authentication override returns user
27. - The real authorization dependency queries document
101within firm10. - The database dependency yields a test SQLAlchemy session.
- The CSRF test dependency succeeds.
- The route calls the real service.
- The service finds no matching idempotency key.
- SQLAlchemy updates the document and inserts the job.
- SQLite commits.
- The service returns the new job with
replayed=False. - FastAPI validates and serializes the response.
- TestClient receives
202 Accepted. - The request-scoped database session closes.
For the replay:
- TestClient sends another independent request.
- FastAPI resolves and validates everything again.
- A new test database session is created.
- The service finds the stored idempotency key.
- It verifies that the document and depth match.
- No second insert occurs.
- The service returns the same job with
replayed=True. - FastAPI returns
200 OK. - The second request-scoped session closes.
- The test opens a separate inspection session.
- SQLAlchemy queries all review jobs.
- SQLite returns exactly one row.
- The assertions pass.
- The fixture disposes the test engine.
- Pytest manages retention and later cleanup of the temporary directory.
The central idea
Section titled “The central idea”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 assertionsDependency 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 JavaScriptThat 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.