FastAPI + aiosqlite Architecture

~14 min · fastapi, architecture, real-world

Level 0Scout

0 XP0/80 lessons0/10 achievements

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

The end-to-end shape that scales to a real product

Putting the pieces together: a real FastAPI service with aiosqlite ends up with five clear layers.

Lifespan — open store at startup, close at shutdown.
Store — CRUD class wrapping aiosqlite. The only place SQL lives.
Routes — FastAPI endpoints. Inject the store via Depends, call methods, return Pydantic models.
Models — Pydantic schemas for request/response shapes.
Background tasks — anything long-running (embedding generation, indexing) goes through asyncio.create_task or a job queue, not in the request/response path.

Self-reference: Pippa's backend exactly follows this shape: main.py is the lifespan, store/conversations.py is the store, routes/chat.py is the routes, routes/models.py-style files hold Pydantic schemas, and the heartbeat scheduler runs as a long-lived background task.

Code

Routes that lean on the store, not on SQL·python

from fastapi import FastAPI, Depends, HTTPException, Request
from pydantic import BaseModel

class MessageIn(BaseModel):
    role: str
    content: str

class MessageOut(BaseModel):
    id: int
    created_at: str

async def store(request: Request) -> 'ConversationStore':
    return request.app.state.store

@app.post('/conversations/{cid}/messages', response_model=MessageOut)
async def post_message(
    cid: int,
    body: MessageIn,
    store: 'ConversationStore' = Depends(store),
):
    return await store.add_message(cid, body.role, body.content)

@app.get('/conversations/{cid}/messages')
async def list_messages(
    cid: int, store: 'ConversationStore' = Depends(store),
):
    return await store.messages_for(cid)

External links

FastAPI — SQL (Relational) Databases tutorial

Exercise

Build a complete mini-Pippa: FastAPI + aiosqlite + ConversationStore + 4 endpoints (create conversation, list conversations, post message, list messages). Wire up lifespan, dependencies, Pydantic models. Then run a load test (e.g., hey -n 1000 -c 50) hitting both reads and writes. Confirm the latency holds.

Progress

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

← PreviousA Real ConversationStore — CRUD End-to-End Next →JSON Support — json_extract, json_each, JSONB

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

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.