FastAPI / Intermediate: application integration and security / Lesson 12

Database and unit-of-work boundaries

Separate request validation, repository access, transactions, and resource cleanup so an HTTP response reflects a defined persistence outcome.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain the assumptions and boundaries of database and unit-of-work boundaries.
Interpret both complete examples and justify their observed outputs.
Diagnose the listed failures and apply the worked solutions while keeping identity, state, and lifecycle ownership explicit.

A request model is not persistence

Pydantic validates and serializes data; it does not automatically insert a row or enforce a database invariant. A repository expresses storage behavior behind an explicit application contract, while a unit of work coordinates related changes and their commit or rollback. FastAPI can inject these boundaries but does not choose their transaction semantics. The first example uses only a new in-memory SQLite connection owned by the script. It demonstrates rollback after a constraint error without contacting a configured project database or starting a database service. Engine-specific concurrency and durability guarantees remain outside that small example.

python Complete isolated rollback demonstration

import sqlite3
connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)")
    with connection:
        connection.execute("INSERT INTO account VALUES (?, ?)", (1, 10))
    try:
        with connection:
            connection.execute("UPDATE account SET balance = ? WHERE id = ?", (7, 1))
            connection.execute("INSERT INTO account VALUES (?, ?)", (1, 2))
    except sqlite3.IntegrityError:
        pass
    print(connection.execute("SELECT balance FROM account WHERE id = ?", (1,)).fetchone()[0])
finally:
    connection.close()

Expected output

The duplicate primary key aborts the transaction and restores balance ten. SQLite is a new private in-memory fixture, not a project database or a general isolation proof.

Commit a meaningful outcome before reporting it

If an API claims a write completed durably, the relevant commit must have succeeded under the declared contract. Delaying commit until cleanup after a response has started can make an error impossible to report correctly. A yield dependency is a resource-lifetime mechanism, not an automatic correct transaction design. Decide where domain checks, writes, commit, response construction, and cleanup occur. Translate known storage failures at an appropriate boundary and keep unexpected causes available for safe investigation. A failed response or timeout after commit does not prove no write occurred, so retryable commands may need durable idempotency or reconciliation.

Keep connection and session ownership explicit

A database connection or ORM session has driver-specific thread, transaction, and lifetime rules. Do not share one mutable session across concurrent requests or background tasks merely because it can be stored globally. Async endpoints need compatible drivers or deliberate synchronous boundaries, and connection pools need finite acquisition and query budgets. The second example injects an isolated in-memory read repository, not a database session. It verifies route wiring and absence behavior while keeping persistence mechanics outside the HTTP layer. Actual ORM or engine integration requires its own approved fixtures and version-specific tests.

python Complete repository-boundary test

from typing import Annotated
from fastapi import Depends, FastAPI, HTTPException
from fastapi.testclient import TestClient
def repository(): return {1: {"id": 1, "name": "pen"}}
app = FastAPI()
@app.get("/items/{item_id}")
def item(item_id: int, rows: Annotated[dict, Depends(repository)]):
    if item_id not in rows: raise HTTPException(status_code=404, detail="Missing")
    return rows[item_id]
with TestClient(app) as client:
    print(client.get("/items/1").json())
    app.dependency_overrides[repository] = lambda: {}
    try:
        print(client.get("/items/1").status_code)
    finally:
        app.dependency_overrides.clear()

Expected output

{'id': 1, 'name': 'pen'}
404

The route observes a controlled repository contract and then a missing record. The dictionary is a test fixture, provides no durable persistence, and contacts no service.

Protect values, invariants, and tenant scope

Use parameter binding for values and an allowlist for any dynamic identifier or sort choice. Validation does not replace database constraints, authorization, or concurrency control. Two requests can both pass an early balance check before one changes the state, so enforce the relevant invariant close to the write with the database’s documented tools. Keep tenant and ownership conditions attached to repository operations where required. Migrations and old application versions also need a compatibility plan. A simple dictionary fake is useful for interface tests but cannot certify transaction isolation, locking, or crash recovery in another database.

Test success, rollback, and cleanup separately

A useful persistence test checks committed state, a failed mid-operation path, resource release, and the public error contract. Create temporary or in-memory fixtures that the test owns and never reuse configured application storage. For distributed side effects, a database rollback cannot unsend a message or reverse a remote payment; use an explicit protocol such as an outbox and reconciliation when needed. These examples execute only synthetic in-memory data and close the local connection. No project database, external driver service, migration, administrative command, real credential, or network endpoint is used. Report that limited scope rather than claiming production database verification.

Common errors and fixes

Commit fails after success was already sent

Why it happens: Transaction completion was hidden in late cleanup.
How to fix it: Place commit before the response that claims persistence.
Prevent it next time: Test commit failure at the HTTP boundary.

One session is reused across concurrent requests

Why it happens: Global availability was mistaken for safe lifetime.
How to fix it: Use owned driver-compatible sessions and bounded pools.
Prevent it next time: Test concurrency and teardown with the selected driver.

Database rollback is assumed to undo an external effect

Why it happens: Separate systems were treated as one transaction.
How to fix it: Use an explicit durable coordination and reconciliation protocol.
Prevent it next time: Test interruption between commit and publication.

Check your understanding

1. An HTTP response times out after a database commit. Should the caller blindly repeat a payment command?

Show worked solution

No. The write may already be durable. Use a stable idempotency identity or reconcile the operation status under the application protocol. Transport failure does not roll back a database or a remote payment automatically.

2. Why does a dictionary repository test not prove PostgreSQL locking behavior?