codejump

AcademyFastAPI

Use a database in FastAPI

Create a request-scoped database dependency, use parameterized queries, and keep transactions explicit in FastAPI routes.

2 min readUpdated

3 exercises

Waiting at the end of this page.

Key takeaways

  • Give each request its own database session or connection through a dependency and close it reliably.
  • Pass user values as query parameters rather than building SQL with string interpolation.
  • Commit successful writes, roll back failures, and translate expected database errors into HTTP responses.

Make the connection a dependency

An in-memory list disappears when the process restarts. For durable data, open a database resource for the request and release it afterwards. This small SQLite example uses a synchronous driver, so its route is an ordinary def:

python
import sqlite3
from collections.abc import Iterator
from typing import Annotated
from fastapi import Depends, FastAPI, HTTPException

app = FastAPI()

def get_db() -> Iterator[sqlite3.Connection]:
    db = sqlite3.connect("books.db")
    db.row_factory = sqlite3.Row
    try:
        yield db
    finally:
        db.close()

@app.get("/books/{book_id}")
def read_book(book_id: int, db: Annotated[sqlite3.Connection, Depends(get_db)]):
    row = db.execute("SELECT id, title FROM books WHERE id = ?", (book_id,)).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="Book not found")
    return dict(row)

This assumes a books table already exists. Create schema through a migration or application setup, not during every request. A production database often uses an ORM session instead of a raw connection, but the request ownership rule is the same.

Bind values; decide when to commit

The ? placeholder tells SQLite to bind book_id as data. Never assemble user input into SQL text with an f-string. For a write, call commit() only after all related statements succeed; call rollback() if an error occurs. If two changes must happen together, keep them in one transaction so a failure cannot leave half the work saved.

Map expected conditions to useful HTTP responses: a missing row to 404, a known uniqueness conflict to 409. Let unexpected database failures reach monitoring instead of presenting every error as a normal client mistake.

Match the driver to the route

FastAPI runs a plain def route in a worker thread in a normal server, so it can call a blocking database driver. An async def route should await an async database client; calling a blocking driver directly inside it stalls the event loop. For SQLite, also consider transaction contention under concurrent writes and use a server database when the workload outgrows it.

Now practice it

3 exercises

Reading this page is the cheap half. These are the exercises that make you use it.

Read next