SQLModel

SQLModel is a Python library for SQL databases built on top of SQLAlchemy and Pydantic. It uses Python type annotations to define both database table schemas and API data schemas in a single class hierarchy.

Installation

pip install sqlmodel

For PostgreSQL or MySQL, install the appropriate driver alongside SQLModel:

pip install sqlmodel psycopg2-binary   # PostgreSQL
pip install sqlmodel pymysql           # MySQL

Core Concepts

Table Models vs Data Models

SQLModel has two distinct model kinds:

• Table models — SQLModel subclasses with table=True. Map to real database tables. Are also SQLAlchemy models and Pydantic models.
• Data models — SQLModel subclasses without table=True. Pydantic models only. Used for API schemas, input validation, and response shaping. Never create tables.

from sqlmodel import Field, SQLModel

# Data model only — no table created
class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

# Table model — creates the `hero` table
class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

Nullable Fields and Primary Keys

• Declare nullable fields with int | None (Python 3.10+) or Optional[int].
• Primary keys must be int | None = Field(default=None, primary_key=True) — the database generates the value; code holds None until the row is saved.

class Article(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    body: str
    views: int | None = None  # nullable column, default NULL

Engine — One Per Application

Create a single engine for the whole application and reuse it everywhere. Do not recreate it per request.

from sqlmodel import SQLModel, create_engine

DATABASE_URL = "sqlite:///database.db"
engine = create_engine(DATABASE_URL)

# PostgreSQL example:
# engine = create_engine("postgresql+psycopg2://user:pass@host/db")

Use echo=True during development to log generated SQL, but remove it in production.

Table Creation

Call SQLModel.metadata.create_all(engine) after all table model classes have been imported. The order matters — classes must be registered in SQLModel.metadata first.

# db.py
from sqlmodel import SQLModel, create_engine
from . import models  # import models before create_all

engine = create_engine("sqlite:///database.db")

def create_db_and_tables() -> None:
    SQLModel.metadata.create_all(engine)

For production, use Alembic for schema migrations instead of create_all.

Session Management

Use with Blocks — Never Manual .close()

Always open sessions in a with block. This guarantees cleanup even on exceptions.

from sqlmodel import Session

with Session(engine) as session:
    hero = Hero(name="Deadpond", secret_name="Dive Wilson")
    session.add(hero)
    session.commit()
    session.refresh(hero)  # populates auto-generated fields like id
    print(hero.id)         # now has the DB-assigned value

One Session Per Request (FastAPI)

Create a new session per request using a FastAPI dependency with yield. The engine is shared; sessions are per-request.

from fastapi import Depends
from sqlmodel import Session

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

@app.post("/heroes/", response_model=HeroPublic)
def create_hero(
    *,
    session: Session = Depends(get_session),
    hero: HeroCreate,
) -> HeroPublic:
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

The yield dependency ensures the session's with block finalizes after the response is sent.

Multiple Models Pattern (Best Practice)

Avoid exposing the table model directly in API routes. Use a model hierarchy:

Model	Purpose	table=True
HeroBase	Shared fields (base data model)	No
Hero	DB table model	Yes
HeroCreate	API input — no id	No
HeroPublic	API output — required id: int	No
HeroUpdate	Partial update — all fields optional	No

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

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

class HeroCreate(HeroBase):
    pass  # same fields as HeroBase, named explicitly for clarity

class HeroPublic(HeroBase):
    id: int  # required in responses (always present after DB save)

class HeroUpdate(SQLModel):
    name: str | None = None
    secret_name: str | None = None
    age: int | None = None

Never inherit data models from table models. Only inherit from other data models to avoid confusion and accidental table creation.

Creating from Input Model

Use Hero.model_validate(hero_create_instance) to convert a data model to a table model:

db_hero = Hero.model_validate(hero)  # hero is HeroCreate
session.add(db_hero)
session.commit()
session.refresh(db_hero)
return db_hero  # FastAPI serializes via HeroPublic response_model

Partial Update Pattern

Use model_dump(exclude_unset=True) and sqlmodel_update() for PATCH operations:

@app.patch("/heroes/{hero_id}", response_model=HeroPublic)
def update_hero(
    *,
    session: Session = Depends(get_session),
    hero_id: int,
    hero: HeroUpdate,
) -> HeroPublic:
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    hero_data = hero.model_dump(exclude_unset=True)
    db_hero.sqlmodel_update(hero_data)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Querying

SELECT with select()

from sqlmodel import select

with Session(engine) as session:
    # All rows
    heroes = session.exec(select(Hero)).all()

    # Single result
    hero = session.exec(select(Hero).where(Hero.name == "Deadpond")).first()

    # By primary key (preferred for single-row lookup)
    hero = session.get(Hero, hero_id)

    # Filtering, ordering, pagination
    statement = (
        select(Hero)
        .where(Hero.age >= 18)
        .order_by(Hero.name)
        .offset(offset)
        .limit(limit)
    )
    heroes = session.exec(statement).all()

Indexes

Declare indexes with Field(index=True) on frequently queried columns. Indexes speed up WHERE, ORDER BY, and JOIN operations at the cost of slightly slower writes.

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)           # indexed
    age: int | None = Field(default=None, index=True)  # indexed
    secret_name: str                         # not indexed

Relationships

Foreign Keys and Relationship

Define foreign keys with Field(foreign_key="table.column") and use Relationship for ORM-level access to related objects.

from sqlmodel import Relationship

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
    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")

• back_populates keeps both sides of the relationship in sync in memory.
• Use string forward references ("Hero") when the referenced class is defined after the current class.
• Relationship attributes are not columns — they do not appear in the table schema.

Many-to-Many

Use an explicit link model with table=True as the association table:

class HeroTeamLink(SQLModel, table=True):
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)

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

Code Structure

Organize multi-model projects across files to avoid circular imports and enforce create_all ordering:

app/
├── models.py        # all SQLModel table models
├── schemas.py       # data models (HeroCreate, HeroPublic, etc.)
├── database.py      # engine, get_session dependency
└── routers/
    └── heroes.py    # FastAPI router

Import models in database.py before calling create_all, or import them explicitly in the startup handler:

# main.py
from app import models  # ensures registration before create_all
from app.database import engine, create_db_and_tables

@app.on_event("startup")
def on_startup() -> None:
    create_db_and_tables()

Quick Reference

Operation	Code
Create engine	create_engine(url)
Create tables	SQLModel.metadata.create_all(engine)
Open session	with Session(engine) as session:
Insert row	session.add(obj); session.commit()
Fetch by PK	session.get(Model, pk)
Query rows	session.exec(select(Model).where(...)).all()
Update row	session.add(obj); session.commit()
Delete row	session.delete(obj); session.commit()
Refresh from DB	session.refresh(obj)
Convert input→table	Model.model_validate(input_obj)
Partial update dict	obj.model_dump(exclude_unset=True)

Additional Resources

• references/relationships-and-queries.md — Relationship patterns, lazy loading, many-to-many with extra fields, advanced query techniques
• references/fastapi-patterns.md — Complete FastAPI integration: dependencies, lifespan, testing, response model patterns