[FastAPI Series 9] First steps with SQLModel and databases

With dependency injection under your belt, it is time to attach real storage. SQLModel lets you describe tables as Python classes without writing raw SQL. SQLite stores data inside a single file, making it perfect for laptop practice. FastAPI supports the entire SQLAlchemy ecosystem, but SQLModel is the most approachable starting point.

Warm-up: databases in plain language

Think of a database (DB) as “a table that survives reboots.”
SQLModel is the tool that writes “this is what the table looks like” in Python.
SQLite is “a thin notebook that holds the table,” packaged as one file with no server setup.

Keep these analogies in mind while following the exercises.

Key terms

SQLModel: Declares Pydantic models and SQLAlchemy tables simultaneously. We use it to define tables and the JSON schema in one go.
ORM (Object Relational Mapper): Bridges Python objects and DB tables so you can do CRUD without hand-written SQL.
SQLite: A lightweight, file-based DB (todo.db) that enables persistence without installing a server.
Session: The SQLModel/SQLAlchemy object that represents a DB connection and handles transactions via commit().
create_engine: Builds a connection pool from a DB URL such as sqlite:///./todo.db, connecting FastAPI to the database.

Think of this trio working together: SQLModel defines the schema, the engine opens the notebook file, and each Session is a temporary library card that lets a single request read and write.

Practice card

Estimated time: 60 minutes

Prereqs: Part 8 dependency code, Python 3.12, bundled SQLite

Goal: Create tables with SQLModel and connect CRUD endpoints to the DB

Core ideas

Confirm why SQLModel + SQLite is a friendly combo
Write init_db and get_session to bridge FastAPI and the DB
Route CRUD endpoints through the session so data persists on disk

Why SQLModel

SQLModel allows you to reuse Pydantic models as database tables. You do not learn a separate ORM DSL, and type hints flow straight into the docs. SQLite ships with most laptops, so you can persist data without maintaining a server. This duo empowers even high school students to learn FastAPI and DB flows on a single machine.

Install and bootstrap

uv add sqlmodel sqlalchemy sqlite-utils

sqlite-utils is handy for inspecting tables later, so we install it alongside SQLModel and SQLAlchemy. Use SQLite as the default file database and keep the URL configurable via environment variables.

from sqlmodel import SQLModel, Session, create_engine
from os import getenv

DATABASE_URL = getenv("DATABASE_URL", "sqlite:///./todo.db")
engine = create_engine(DATABASE_URL, echo=False)

def init_db():
    SQLModel.metadata.create_all(engine)

def get_session():
    session = Session(engine)
    try:
        yield session
    finally:
        session.close()

Call init_db() once when the app starts—@app.on_event("startup") is a good hook. From now on, the same JSON-focused APIs write to an actual file instead of an in-memory list. Set echo=True only when you want to see raw SQL for debugging; otherwise it can flood logs with sensitive data.

from fastapi import FastAPI
from app.db import init_db

app = FastAPI()

@app.on_event("startup")
def on_startup():
    init_db()

Define a model

from typing import Optional
from sqlmodel import SQLModel, Field

class Task(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    done: bool = False

Setting table=True produces both a SQLAlchemy table and a Pydantic model. Routers can reuse the same class for requests and responses, keeping API and DB shapes aligned. In larger projects you will often create TaskCreate/TaskRead variants to control which fields appear in requests, but a single model keeps this exercise focused on persistence.

Connect CRUD endpoints

from fastapi import APIRouter, Depends, HTTPException
from sqlmodel import Session, select

router = APIRouter(prefix="/tasks", tags=["tasks"])

@router.post("", response_model=Task, status_code=201)
def create_task(task: Task, session: Session = Depends(get_session)):
    session.add(task)
    session.commit()
    session.refresh(task)
    return task

@router.get("", response_model=list[Task])
def list_tasks(session: Session = Depends(get_session)):
    return session.exec(select(Task)).all()

@router.patch("/{task_id}")
def toggle_task(task_id: int, session: Session = Depends(get_session)):
    task = session.get(Task, task_id)
    if not task:
        raise HTTPException(status_code=404)
    task.done = not task.done
    session.add(task)
    session.commit()
    session.refresh(task)
    return task

select(Task) builds a SQL expression, and session.exec(...).all() runs it through SQLAlchemy 2.0’s API. session.refresh(task) reloads the record so auto-generated columns such as the primary key flow back into the response. Dependency injection means declaring “this function needs a DB session” is all it takes to access the database. Focus on business logic while FastAPI prepares the plumbing.

(Optional) Think ahead about migrations

SQLite shines for development or single-instance deployments. Move to PostgreSQL or another server DB for multi-instance setups.
SQLModel rides on SQLAlchemy Core, so Alembic-based migration tools plug in seamlessly.
As models grow, split files such as schemas.py, models.py, or repositories.py to keep responsibilities clear and tests simple.

Why this matters

List-backed APIs lose data on every restart. Adding SQLModel + SQLite makes the same FastAPI code persist to disk and handle concurrent requests for a single FastAPI worker. SQLite allows one writer at a time, so if you need multiple processes or heavy concurrent writes you should migrate to PostgreSQL or another server DB. Once you master this flow, moving to a server-grade database mostly involves swapping the connection URL and driver while keeping the SQLModel code intact. For student labs, the “Add → refresh → data is still there” experience is the best motivation.

Practice

Follow along: Implement init_db and get_session, then connect /tasks CRUD to the SQLModel table.
Extend: Build extra PATCH/filter routes and try writing SQL queries manually.
Debug: Run sqlite3 todo.db .tables to confirm creation, and inspect transaction flow when data fails to persist.
Done when: After a POST, todo.db stores the data and GET/PATCH touch the same record.

Run and verify

uv run fastapi dev app/main.py

Submit a POST request inside /docs and a todo.db file appears with stored data. Part 10 connects this database to authentication and authorization so each user’s data stays protected.

Wrap-up

SQLModel plus SQLite delivers durable storage without heavyweight setup. Expand the current structure and you can migrate to server databases like PostgreSQL with minimal code changes.