04-database-sqlmodel.md

04 — Database with SQLModel

Goal: store and query data in a SQL database using SQLModel.

Why SQLModel

SQLModel (by FastAPI's author) combines two tools into one class:

• SQLAlchemy — the mature Python library that talks to SQL databases.
• Pydantic — the validation layer you already met.

So one SQLModel class can be both a database table and a validated schema. Less duplication, fewer bugs.

Defining tables

Open app/models.py. A class with table=True becomes a database table:

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str = Field(index=True, unique=True)
    hashed_password: str
    is_active: bool = True
    created_at: datetime = Field(default_factory=utcnow)

    items: list["Item"] = Relationship(back_populates="owner")

• primary_key=True — the unique row id, auto-assigned by the database.
• index=True — makes lookups on that column fast.
• unique=True — the database rejects duplicate emails.
• Relationship(...) — links User to its Items. Item has the matching owner_id foreign key.

The engine and sessions

Open app/database.py.

• The engine is the connection to the database, created once from settings.database_url.
• A session is a short-lived workspace for a unit of work (a request). You add/query/commit through it.

engine = create_engine(settings.database_url, connect_args=connect_args)

def get_session():
    with Session(engine) as session:
        yield session

get_session is a dependency: FastAPI calls it per request, hands your endpoint a session, and closes it afterward automatically.

Using the session in a route

Open app/routers/items.py. Creating a row:

@router.post("", response_model=ItemRead, status_code=201)
def create_item(data: ItemCreate, session: SessionDep, current_user: CurrentUser):
    item = Item(**data.model_dump(), owner_id=current_user.id)
    session.add(item)      # stage the new row
    session.commit()       # write it to the database
    session.refresh(item)  # reload it (to get the generated id)
    return item

Querying rows uses select:

from sqlmodel import select

statement = select(Item).where(Item.owner_id == current_user.id)
items = session.exec(statement).all()

SessionDep and CurrentUser are shorthand dependencies defined in app/deps.py — we'll cover CurrentUser in the auth lesson.

CRUD at a glance

The items router implements the full set:

Operation	HTTP	Session calls
Create	POST /items	add → commit → refresh
Read list	GET /items	exec(select(...))
Read one	GET /items/{id}	session.get(Item, id)
Delete	DELETE /items/{id}	delete → commit

Creating the tables

For local development, the app creates tables on startup via create_db_and_tables() (called from the lifespan in main.py). That's convenient but doesn't handle changes to an existing schema — for that you need migrations, which is the next lesson.

SQLite now, Postgres later

The default DATABASE_URL is a local SQLite file — zero setup, perfect for learning. To use PostgreSQL, install a driver (pip install "psycopg[binary]") and set:

DATABASE_URL=postgresql+psycopg://user:pass@localhost:5432/app

Nothing else in the code changes. That's the payoff of SQLAlchemy/SQLModel: the same code runs on different databases.

Try it

Register a user and create a couple of items through /docs, then call GET /items to read them back. Delete one and confirm it's gone.

---

Next: 05 — Migrations with Alembic