Connection Lifecycle in FastAPI

~14 min · fastapi, lifespan, aiosqlite

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Open once, close once

FastAPI gives you a lifespan context manager that runs at startup and shutdown. The idiomatic pattern: open the database connection there and stash it on app.state, then expose it to routes via dependency injection.

This avoids the two pitfalls people hit:

Opening a new connection per request — wastes file handles and prevents in-memory caching.
Opening at module import time — runs before the event loop exists; aiosqlite needs the loop running.

Self-reference: Pippa's backend/main.py uses exactly this lifespan pattern: open the SQLite store once at startup, run migrations, attach to app.state.store, and close cleanly at shutdown. Routes pull the store via Depends.

Code

FastAPI + aiosqlite lifespan·python

from contextlib import asynccontextmanager
from fastapi import FastAPI, Depends, Request
import aiosqlite

@asynccontextmanager
async def lifespan(app: FastAPI):
    conn = await aiosqlite.connect('myapp.db')
    conn.row_factory = aiosqlite.Row
    await conn.execute('PRAGMA journal_mode = WAL')
    await conn.execute('PRAGMA foreign_keys = ON')
    await conn.execute('PRAGMA busy_timeout = 5000')
    app.state.db = conn
    try:
        yield
    finally:
        await conn.close()

app = FastAPI(lifespan=lifespan)

async def db(request: Request) -> aiosqlite.Connection:
    return request.app.state.db

@app.get('/notes')
async def list_notes(conn: aiosqlite.Connection = Depends(db)):
    async with conn.execute('SELECT id, body FROM notes ORDER BY id DESC LIMIT 50') as cur:
        return [dict(row) async for row in cur]

External links

Exercise

Build a tiny FastAPI app with one CRUD endpoint backed by aiosqlite using the lifespan pattern. Run it under uvicorn and hit it with 1000 concurrent requests via hey or ab. Confirm latency stays sub-millisecond and there are no 'database is locked' errors.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousAsync Cursors, Iteration, and Transactions Next →Single Connection vs Pool

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.