SQLModel / SQLAlchemy

Q: SQLModel or SQLAlchemy alone?

SQLModel for FastAPI greenfield CRUD; drop to SQLAlchemy patterns when queries outgrow ORM ergonomics.

Q: How do migrations work?

Use Alembic autogenerate from metadata - see Alembic.

Q: Async sessions?

SQLAlchemy 2.0 AsyncSession with asyncpg - FastAPI async routes should not block on sync sessions.

Q: How do I paginate?

select(Book).offset(skip).limit(limit) - add stable sort key; consider keyset pagination for large tables.

Q: Relationship loading?

Define Relationship() fields; use selectinload in query to eager load.

Q: How do I test DB code?

Transaction rollbacks per test fixture or SQLite :memory: with create_all per test module.

Q: Connection string from env?

Load DATABASE_URL via pydantic-settings.

Q: Raw SQL escape hatch?

session.exec(text("SELECT ...")) for reporting - still bind parameters, never f-string SQL.

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=True registers 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: int

Gotchas

create_all in production - No migration history. Fix: Alembic revisions.
N+1 queries - Lazy loading in loops. Fix: selectinload or 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 | None matches nullable columns.
Giant SQLModel god table - API leaks DB columns. Fix: separate Read/Create schemas.

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.

Databases Basics - deep data layer coverage
Alembic - schema migrations
FastAPI Async Routes and Databases - AsyncSession patterns
Django Models and the ORM - framework alternative

Python Package Index (PyPI) - Official repository of Python packages.
Read the Docs - Hosting for thousands of Python project documentations.
Real Python - In-depth tutorials, courses, and practical guides.
Fluent Python (2nd Edition) - Intermediate to advanced Python idioms (Luciano Ramalho).
Python Crash Course (Penguin Random House) - Same title via publisher.

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+.

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=True registers 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: int

Gotchas

create_all in production - No migration history. Fix: Alembic revisions.
N+1 queries - Lazy loading in loops. Fix: selectinload or 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 | None matches nullable columns.
Giant SQLModel god table - API leaks DB columns. Fix: separate Read/Create schemas.

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.

Databases Basics - deep data layer coverage
Alembic - schema migrations
FastAPI Async Routes and Databases - AsyncSession patterns
Django Models and the ORM - framework alternative

Python Package Index (PyPI) - Official repository of Python packages.
Read the Docs - Hosting for thousands of Python project documentations.
Real Python - In-depth tutorials, courses, and practical guides.
Fluent Python (2nd Edition) - Intermediate to advanced Python idioms (Luciano Ramalho).
Python Crash Course (Penguin Random House) - Same title via publisher.

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+.

SQLModel / SQLAlchemy

Search Documentation

SQLModel / SQLAlchemy