SQLModel / SQLAlchemy
SQLAlchemy is Python's foundational SQL toolkit and ORM. SQLModel (by the FastAPI author) layers Pydantic validation on SQLAlchemy models for API-friendly schemas. This page orients the essentials - the databases section goes deeper.
Recipe
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str
secret_name: str
engine = create_engine("sqlite:///heroes.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
session.add(Hero(name="Deadpond", secret_name="Dive"))
session.commit()
heroes = session.exec(select(Hero)).all()When to reach for this:
- FastAPI services persisting to Postgres/MySQL/SQLite
- Sharing types between DB rows and API response models
- Complex queries needing SQLAlchemy expression power
- Django-adjacent scripts that still need raw SQL control
Working Example
from __future__ import annotations
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Book(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
title: str
author: str
published_year: int
def seed(engine) -> None:
with Session(engine) as session:
session.add(Book(title="Neuromancer", author="Gibson", published_year=1984))
session.commit()
def list_recent(engine, min_year: int) -> list[Book]:
with Session(engine) as session:
statement = select(Book).where(Book.published_year >= min_year).order_by(Book.title)
return list(session.exec(statement))
if __name__ == "__main__":
engine = create_engine("sqlite:///:memory:")
SQLModel.metadata.create_all(engine)
seed(engine)
print(list_recent(engine, 1980))What this demonstrates:
- Single class serves ORM table and validated model
- SQLAlchemy 2.0
select()+session.exec - Session per unit of work with explicit commit
- In-memory SQLite for runnable example
Deep Dive
How It Works
- Engine - Connection pool to database URL.
- Session - Unit of work tracking dirty objects;
commit/rollback. - SQLModel -
table=Trueregisters ORM mapping; without it, class is Pydantic-only DTO. - SQLAlchemy Core -
Table,insert(),update()when ORM is too heavy.
SQLModel vs Raw SQLAlchemy
| Approach | Best For |
|---|---|
| SQLModel | FastAPI CRUD, shared API/DB types |
| SQLAlchemy declarative | Large domains, mixed Core/ORM |
| Django ORM | Django monoliths |
Python Notes
# Read vs write models in larger apps
class BookCreate(SQLModel):
title: str
author: str
class BookRead(BookCreate):
id: intGotchas
create_allin production - No migration history. Fix: Alembic revisions.- N+1 queries - Lazy loading in loops. Fix:
selectinloador explicit joins. - SQLite in prod assumptions - Differs from Postgres locking/types. Fix: test against prod engine in CI.
- Sharing session across requests - Race conditions. Fix: session per request via FastAPI dependency.
- Nullable without
Optional- Type hints lie. Fix:str | Nonematches nullable columns. - Giant SQLModel god table - API leaks DB columns. Fix: separate
Read/Createschemas.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| Django ORM | Django is the web framework | Standalone FastAPI microservice |
psycopg raw SQL | Report queries, bulk COPY | Rich domain model with relationships |
| Tortoise ORM | Async-first smaller apps | Mature SQLAlchemy ecosystem needed |
| Peewee | Tiny scripts | Connection pooling at scale |
FAQs
SQLModel or SQLAlchemy alone?
SQLModel for FastAPI greenfield CRUD; drop to SQLAlchemy patterns when queries outgrow ORM ergonomics.
How do migrations work?
Use Alembic autogenerate from metadata - see Alembic.
Async sessions?
SQLAlchemy 2.0 AsyncSession with asyncpg - FastAPI async routes should not block on sync sessions.
How do I paginate?
select(Book).offset(skip).limit(limit) - add stable sort key; consider keyset pagination for large tables.
Relationship loading?
Define Relationship() fields; use selectinload in query to eager load.
Does SQLModel work with Pydantic v2?
Yes on current stacks pinned in this cookbook - validate upgrade notes when bumping either library.
How do I test DB code?
Transaction rollbacks per test fixture or SQLite :memory: with create_all per test module.
Connection string from env?
Load DATABASE_URL via pydantic-settings.
Raw SQL escape hatch?
session.exec(text("SELECT ...")) for reporting - still bind parameters, never f-string SQL.
Django and SQLAlchemy together?
Possible in hybrid migrations but awkward - pick one ORM per bounded context.
Related
- Databases Basics - deep data layer coverage
- Alembic - schema migrations
- FastAPI Async Routes and Databases - AsyncSession patterns
- Django Models and the ORM - framework alternative
Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.