sqlmodel

What it is

sqlmodel is a Python ORM that fuses Pydantic models with SQLAlchemy tables into a single class. Authored by Sebastián Ramírez (the author of FastAPI and Typer), it is designed so the same model definition serves as the request/response schema and the database row mapping.

Reach for sqlmodel when you are building a FastAPI service and want one canonical model per resource. Drop to plain SQLAlchemy when you need features SQLModel doesn't expose (composite relationships, complex query construction, custom dialects), or when your domain model and API schema legitimately differ.

Install

pip install sqlmodel

Output: (none — exits 0 on success). Pulls in sqlalchemy and pydantic as required dependencies.

uv add sqlmodel

Output: dependency resolved + added to pyproject.toml

poetry add sqlmodel

Output: updated lockfile + virtualenv install

pip install sqlmodel "sqlalchemy[asyncio,postgresql_asyncpg]"

Output: installs SQLModel plus the async Postgres driver stack. SQLModel itself has no Postgres extra — pull the driver via SQLAlchemy's extras.

Versioning & Python support

• Current stable line is the 0.x series. Despite the 0. prefix, SQLModel is used in production. Sebastián Ramírez has historically held releases below 1.0 until the underlying dependencies (Pydantic, SQLAlchemy) stabilised.
• Supports Python 3.7+. Older SQLModel releases support 3.6.
• Tracks the major Pydantic + SQLAlchemy versions tightly — the Pydantic v1 → v2 migration and the SQLAlchemy 1.4 → 2.0 migration both forced coordinated SQLModel updates.
• Pin to sqlmodel>=0.0.16,<1 (or whichever recent minor) when stability matters; minor bumps can shift behaviour on edge cases like default-value coercion.

Package metadata

• Maintainer: Sebastián Ramírez (tiangolo on GitHub)
• Project home: github.com/fastapi/sqlmodel
• Docs: sqlmodel.tiangolo.com
• PyPI: pypi.org/project/sqlmodel
• License: MIT
• Governance: maintained under the FastAPI org on GitHub
• First released: 2021
• Downloads: millions per month — growing alongside FastAPI adoption.

Optional dependencies & extras

sqlmodel itself does not declare published extras. It pulls in two required dependencies:

• sqlalchemy (≥ 2.0 on recent SQLModel releases) — the underlying ORM
• pydantic (≥ 2.0 on recent SQLModel releases) — validation, serialization

For database drivers, install them via SQLAlchemy's extras instead:

• pip install sqlmodel "sqlalchemy[postgresql_asyncpg]" — async Postgres
• pip install sqlmodel "sqlalchemy[asyncio,aiosqlite]" — async SQLite
• pip install sqlmodel "sqlalchemy[postgresql]" — sync Postgres via psycopg2
• pip install sqlmodel "sqlalchemy[mysql]" — sync MySQL via mysqlclient

For migrations, you'll also want:

• alembic — schema migrations. Not pulled in automatically by SQLModel; you must add it yourself if you need migration support.

Alternatives

Common gotchas

1. Alembic templates are not directly compatible. Alembic's stock env.py reflects from SQLAlchemy metadata; SQLModel models are SQLAlchemy models, but you must import them so the metadata is populated. The typical pattern is from myapp.models import * # noqa inside env.py. Without it, autogenerated migrations are empty.
2. Multiple imports double-register tables. Because SQLModel models are declared at import time and registered in SQLModel.metadata, importing the same module under two different paths (e.g. app.models and myapp.app.models) registers two copies and raises Table already defined on create_all().
3. table=True is the magic switch. A class without table=True is a pure Pydantic model (API schema only). With it, the class becomes both a Pydantic model and a SQLAlchemy mapped table. Forgetting it produces a model that validates but never persists.
4. Pydantic v1 → v2 transition was painful. Earlier SQLModel releases pinned Pydantic v1; once both ecosystems moved to v2, validators changed (@validator → @field_validator), serializers changed, and some Config settings were renamed. Mixed-version stacks (v1 SQLModel + v2 Pydantic) silently misbehave.
5. Async sessions need explicit AsyncSession import from SQLAlchemy, not SQLModel. The sqlmodel.ext.asyncio.session.AsyncSession shim exists but lags upstream features. For new code, use SQLAlchemy's AsyncSession directly with SQLModel models.
6. Field(default_factory=...) for mutable defaults. A bare dict or list as a field default is captured by reference — every row gets the same mutable. Use Field(default_factory=dict) exactly as you would in Pydantic / dataclasses.
7. Relationship typing is verbose. SQLAlchemy 2.0 introduced Mapped[] typing; SQLModel layered its own conventions on top. Mixing Optional[], Mapped[], and SQLModel's Relationship(...) can confuse mypy. Lean on the SQLModel docs' relationship examples verbatim until the patterns are familiar.

Real-world recipes

Patterns that exercise SQLModel's "one class for API + DB" pitch. SQLModel's killer feature is the dual-role class — these recipes lean into that, while staying realistic about its limits.

Full CRUD with FastAPI and dependency-injected sessions

The canonical SQLModel app: FastAPI uses the same SQLModel class for request validation, response serialization, and table mapping. The Session is yielded from a dependency so each request gets a fresh transaction.

from typing import Annotated
from fastapi import Depends, FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select

class HeroBase(SQLModel):
    name: str = Field(index=True)
    age: int | None = None

class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

class HeroCreate(HeroBase): pass

class HeroRead(HeroBase):
    id: int

engine = create_engine("sqlite:///./heroes.db")
SQLModel.metadata.create_all(engine)

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

SessionDep = Annotated[Session, Depends(get_session)]
app = FastAPI()

@app.post("/heroes", response_model=HeroRead)
def create(hero: HeroCreate, session: SessionDep):
    db_hero = Hero.model_validate(hero)
    session.add(db_hero); session.commit(); session.refresh(db_hero)
    return db_hero

@app.get("/heroes/{hero_id}", response_model=HeroRead)
def read(hero_id: int, session: SessionDep):
    hero = session.get(Hero, hero_id)
    if not hero: raise HTTPException(404)
    return hero

Output: POST /heroes accepts HeroCreate (no id), persists a Hero row, returns a HeroRead (with id). The shared HeroBase keeps the field declarations in one place.

Relationships with Relationship

SQLModel's Relationship is a thin wrapper over SQLAlchemy's relationship(). The pattern below sets up a one-to-many between Team and Hero with bidirectional navigation.

from sqlmodel import Field, Relationship, SQLModel

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    heroes: list["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")

Output: hero.team follows the FK lazily; team.heroes returns a list of related heroes. The back_populates= keeps both sides synchronised when you mutate one.

Joined queries with select + where

SQLModel re-exports select from SQLAlchemy. The query API is identical — same .where(...), .join(...), .options(selectinload(...)) — so all SA query patterns carry over.

from sqlmodel import Session, select
from sqlalchemy.orm import selectinload

with Session(engine) as session:
    # Eager-load each Team's heroes in two queries
    teams = session.exec(
        select(Team).options(selectinload(Team.heroes))
    ).all()

    # Inner join across both tables, filtered
    statement = (
        select(Hero, Team)
        .join(Team)
        .where(Team.name == "Avengers")
    )
    for hero, team in session.exec(statement):
        print(hero.name, "->", team.name)

Output: the joined query returns tuples of (Hero, Team); the eager-loaded version avoids N+1 fetches when iterating team.heroes.

Async session with SQLAlchemy's AsyncSession

SQLModel's sqlmodel.ext.asyncio.session.AsyncSession exists but lags upstream. The recommended pattern for new code is to use SQLAlchemy's AsyncSession directly with SQLModel models.

import asyncio
from sqlmodel import SQLModel
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlmodel import select

engine = create_async_engine("sqlite+aiosqlite:///./heroes.db")
async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def main():
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)
    async with async_session() as session:
        session.add(Hero(name="alice"))
        await session.commit()
        result = await session.exec(select(Hero))
        for h in result:
            print(h.id, h.name)

asyncio.run(main())

Output: the SQLModel class round-trips through the async engine without any SQLModel-specific async wiring; the async_sessionmaker(..., class_=AsyncSession) is the only fixture needed.

Pydantic v2 validators on a table=True model

SQLModel inherits Pydantic validation — @field_validator and @model_validator work on both pure SQLModel classes and table=True ones. The validation runs on Model.model_validate(...) and on FastAPI request parsing.

from pydantic import field_validator
from sqlmodel import Field, SQLModel

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    @field_validator("name")
    @classmethod
    def normalize_name(cls, v: str) -> str:
        v = v.strip()
        if not v:
            raise ValueError("name cannot be empty")
        return v.title()

Output: Hero(name=" alice ") produces Hero(name="Alice"); FastAPI returns 422 for empty names automatically.

Production deployment

A SQLModel app deploys like any FastAPI/SQLAlchemy app — see pip-sqlalchemy for pool sizing, transactional patterns, and observability. The SQLModel-specific extras are below.

Engine creation and lifespan

Create one engine per process. With FastAPI, the canonical pattern is to wire it into the lifespan context manager so dispose() runs on shutdown:

from contextlib import asynccontextmanager
from fastapi import FastAPI
from sqlmodel import SQLModel, create_engine

engine = create_engine(
    "postgresql+psycopg://app@db.internal/app",
    pool_size=16, max_overflow=8, pool_recycle=1800, pool_pre_ping=True,
)

@asynccontextmanager
async def lifespan(app: FastAPI):
    SQLModel.metadata.create_all(engine)   # dev-only; replace with Alembic in prod
    yield
    engine.dispose()

app = FastAPI(lifespan=lifespan)

Output: the engine is initialised once at process startup; connections are returned to the pool cleanly on shutdown.

expire_on_commit=False for API handlers

SQLAlchemy's default expires every loaded object after commit(), triggering a re-fetch on the next attribute access. For FastAPI handlers that return the freshly-saved object to the client, this manifests as an extra SELECT for every POST/PUT.

from sqlmodel import Session

def get_session():
    with Session(engine, expire_on_commit=False) as session:
        yield session

Output: session.add(hero); session.commit(); return hero runs one INSERT, not INSERT + SELECT.

Containerisation

SQLModel pulls in SQLAlchemy + Pydantic; neither is huge but both have C extensions that benefit from prebuilt wheels. The slim image pattern:

FROM python:3.12-slim
WORKDIR /app
COPY pyproject.toml uv.lock ./
RUN pip install --no-cache-dir uv && uv sync --frozen
COPY . .
CMD ["fastapi", "run", "app/main.py", "--host", "0.0.0.0", "--port", "8000"]

Output: uv sync --frozen installs from the lockfile in seconds; fastapi run wraps uvicorn with sensible defaults.

Database migration strategies

SQLModel works with Alembic exactly like SQLAlchemy, but Alembic's stock env.py template doesn't know about SQLModel and won't autodetect the SQLModel.metadata registry unless you wire it explicitly.

The pattern — import models so metadata fills

pip install alembic
alembic init alembic

Output: an alembic/ directory with env.py, script.py.mako, and versions/.

Then edit alembic/env.py to point at SQLModel.metadata and import every model module so it registers:

# alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context
from sqlmodel import SQLModel

# CRITICAL: import every model module so SQLModel.metadata is populated.
# Without this, autogenerate produces empty migrations.
from myapp import models  # noqa: F401

config = context.config
fileConfig(config.config_file_name)
target_metadata = SQLModel.metadata

def run_migrations_offline():
    context.configure(
        url=config.get_main_option("sqlalchemy.url"),
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            render_as_batch=True,  # required for SQLite ALTER TABLE
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Output: alembic revision --autogenerate -m "init" now sees every SQLModel class declared in myapp.models and writes a migration that creates the corresponding tables.

script.py.mako — add the SQLModel import

Alembic-generated migrations use sa.Column(sa.String, ...). SQLModel emits sqlmodel.AutoString for fields with no explicit type — the migration won't import sqlmodel and will fail to apply. Patch the template:

# alembic/script.py.mako — add the line at the top of the import block
import sqlmodel
import sqlalchemy as sa
${imports if imports else ""}

Output: generated migrations import sqlmodel so sqlmodel.AutoString resolves; alembic upgrade head runs cleanly.

The autogenerate workflow

alembic revision --autogenerate -m "add heroes table"
# REVIEW the generated file in alembic/versions/
alembic upgrade head

Output: the migration runs DDL against the configured DB; rolling back uses alembic downgrade -1.

For zero-downtime deploys, follow the expand → migrate → contract pattern from the SQLAlchemy article — SQLModel doesn't change the rules, only the model declaration syntax.

Version migration guide

SQLModel's churn comes primarily from its dependencies — Pydantic v1 → v2 and SQLAlchemy 1.x → 2.x both forced coordinated SQLModel updates. Stay aware of which combination your codebase targets.

Pydantic v1 → v2 (the painful one)

SQLModel pre-0.0.14 ran on Pydantic v1; 0.0.14+ requires Pydantic v2. The migration is the standard Pydantic v1 → v2 dance:

# Before (Pydantic v1)
from sqlmodel import SQLModel

class Hero(SQLModel, table=True):
    name: str

    class Config:
        orm_mode = True

    @validator("name")
    def upper(cls, v): return v.upper()

# After (Pydantic v2)
from pydantic import field_validator, ConfigDict
from sqlmodel import SQLModel

class Hero(SQLModel, table=True):
    name: str

    model_config = ConfigDict(from_attributes=True)  # was orm_mode

    @field_validator("name")
    @classmethod
    def upper(cls, v: str) -> str: return v.upper()

Output: Pydantic v2 enforces v2-style validators and config; the Config inner class and @validator form still work via a compatibility shim but are deprecated.

Other v2 migration calls:

• .dict() → .model_dump()
• .json() → .model_dump_json()
• .parse_obj(d) → .model_validate(d)
• Config.allow_population_by_field_name = True → model_config = ConfigDict(populate_by_name=True)

SQLAlchemy 1.x → 2.x

If you pinned SQLModel to a SQLAlchemy 1.x line, upgrading SQLModel forces SA 2.x. The 2.x changes that affect SQLModel users:

• Session.query(Model) deprecated in favour of session.exec(select(Model)). SQLModel's Session.exec() returns Result objects with .first(), .one(), .all() — close enough that most code ports cleanly.
• Mapped[] typing — SQLModel uses its own Field() and Relationship() rather than mapped_column, so most SA 2.x typing changes don't surface in SQLModel models. Mixing styles in one project (some classes pure SA, some SQLModel) requires care.

Field(default=...) vs Field(default_factory=...)

A mutable default — dict, list, etc. — is shared across instances if declared with default=. Always use default_factory= for mutables:

# Wrong — every Hero shares the same list
class Hero(SQLModel, table=True):
    tags: list[str] = Field(default=[])  # SHARED reference

# Right
class Hero(SQLModel, table=True):
    tags: list[str] = Field(default_factory=list)

Output: the default_factory form constructs a fresh list per instance; the default=[] form leaks state across models.

Testing strategies

SQLModel tests use SQLite (file or in-memory) for speed. The trick is to give each test a fresh database to keep state isolated — a function-scoped fixture creates a new SQLite engine, runs create_all, yields, and disposes.

import pytest
from fastapi.testclient import TestClient
from sqlmodel import Session, SQLModel, create_engine
from sqlmodel.pool import StaticPool

from app.main import app, get_session

@pytest.fixture(name="session")
def session_fixture():
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        yield session

@pytest.fixture(name="client")
def client_fixture(session):
    def override():
        return session
    app.dependency_overrides[get_session] = override
    yield TestClient(app)
    app.dependency_overrides.clear()

def test_create_hero(client):
    r = client.post("/heroes", json={"name": "alice", "age": 30})
    assert r.status_code == 200
    assert r.json()["name"] == "Alice"

def test_validation(client):
    r = client.post("/heroes", json={"name": "   ", "age": 30})
    assert r.status_code == 422

Output: every test gets a fresh in-memory DB; StaticPool ensures the same SQLite connection survives across the FastAPI dependency boundary.

Key patterns:

• sqlite:// in-memory — fast, isolated. For tests that need on-disk persistence (rare), use sqlite:///./test.db and delete the file in a teardown step.
• StaticPool — the default SQLite pool spawns a new connection per checkout, defeating the in-memory shared-state model. StaticPool keeps one connection.
• app.dependency_overrides — FastAPI's standard pattern for swapping in a test session. Always clear in teardown.
• Don't run Alembic in tests. SQLModel.metadata.create_all(engine) is faster and tests should never depend on migration history.

See also

• Python: sqlmodel — API tutorial, FastAPI integration, async sessions
• Packages: pip-sqlalchemy — the underlying ORM