SQLModel Cheat Sheet

1Install & imports0.0.27

pip install sqlmodel★
Pulls in SQLAlchemy 2.0 and Pydantic v2 automatically — you don't install them separately.
from sqlmodel import (SQLModel, Field, Relationship, Session, create_engine, select)★
The whole everyday surface. SQLModel re-exports most of what you need; drop to raw sqlalchemy only for advanced column types.
col: str = Field(sa_column=Column(JSON)) # escape hatch
Anything SQLAlchemy can do, you can reach via sa_column= / sa_type= — SQLModel is a thin, typed layer, not a walled garden.

2Define a modeltable=True is the switch

class Hero(SQLModel, table=True): id: int | None = Field(default=None, primary_key=True) name: str age: int | None = None★
table=True makes it a real DB table. Annotations become columns; | None = nullable; a default makes it optional. The class is also a valid Pydantic model.
class HeroBase(SQLModel): # NO table=True name: str★
Without table=True it's a plain Pydantic schema (request/response body, shared base) — no table, full validation. This distinction drives the whole design.
__tablename__ = "heroes" # override table name
Default table name is the lowercased class name. Set __tablename__ to override.

3Field & columnsconstraints & indexes

id: int | None = Field(default=None, primary_key=True)★
The canonical PK: optional int, None until the DB assigns it on commit.
name: str = Field(index=True) email: str = Field(unique=True, index=True)★
index=True builds a DB index; unique=True a unique constraint. Combine freely.
team_id: int | None = Field(default=None, foreign_key="team.id")★
Foreign key as a string "table.column". This is the column; the Relationship (card 7) is the navigation attribute.
name: str = Field(max_length=255, nullable=False) created: datetime = Field(default_factory=datetime.utcnow)
max_length sets the VARCHAR limit (Pydantic v2); default_factory for per-row computed defaults.

4Engine, tables & Sessionsetup

engine = create_engine("sqlite:///database.db", echo=True)★
One engine per app (a connection pool). echo=True logs SQL — great while learning. Use a postgresql:// / mysql:// URL for real DBs.
SQLModel.metadata.create_all(engine)★
Creates every table from all imported table=True models. Import the models before calling it. Only CREATEs missing tables — it never migrates existing ones (use Alembic).
with Session(engine) as session: ... # one unit of work; commits/rolls back★
A Session wraps a transaction. Always scope it with with; short-lived, one per request in web apps.

5Create & readadd / select / exec

hero = Hero(name="Deadpond", age=48) session.add(hero); session.commit(); session.refresh(hero)★
Insert. add stages, commit writes, refresh reloads DB-generated values (like the new id) back onto the object.
hero = session.get(Hero, 1) # fetch by primary key★
The fastest read for a known PK — returns the object or None.
stmt = select(Hero).where(Hero.age > 40) heroes = session.exec(stmt).all()★
Query with select() + session.exec(). .all() / .first() / .one(). exec returns model objects directly — that's SQLModel's ergonomics over raw SQLAlchemy.
select(Hero).where(Hero.age > 40).order_by(Hero.name).offset(10).limit(5)
Chain where (multiple = AND), order_by, offset/limit. Use col(Hero.age) if the editor complains about operators on a column.

6Update & deletemutate rows

hero = session.get(Hero, 1) hero.age = 49 session.add(hero); session.commit(); session.refresh(hero)★
Update = read, mutate, add, commit. Re-add the tracked object, then commit.
session.delete(hero); session.commit()★
Delete the object and commit the transaction.
results = session.exec(select(Hero).where(Hero.name == "X")) hero = results.one() # raises if 0 or >1 rows
.one() enforces exactly one row (great for updates by unique key); .first() tolerates zero.

7Relationshipsnavigate between tables

class Team(SQLModel, table=True): id: int | None = Field(default=None, primary_key=True) heroes: list["Hero"] = Relationship(back_populates="team")★
Parent side (one-to-many): a list of children. Relationship is NOT a column — it's a lazy-loaded navigation attribute.
class Hero(SQLModel, table=True): team_id: int | None = Field(default=None, foreign_key="team.id") team: Team | None = Relationship(back_populates="heroes")★
Child side: the FK column plus a single-object relationship. back_populates names the attribute on the other class — they must match.
hero.team = team # assign by object, not id team.heroes.append(hero) # either side works★
Set relationships with objects; SQLModel fills the FK on commit. Access hero.team / team.heroes to load related rows.
Relationship(sa_relationship_kwargs={"cascade": "all, delete"})
Pass through SQLAlchemy relationship options (cascades, ordering, lazy strategy). Many-to-many uses a link_model= table.

8FastAPI & asyncmulti-model + AsyncSession

class HeroBase(SQLModel): name: str; age: int | None = None class Hero(HeroBase, table=True): id: int | None = Field(default=None, primary_key=True) class HeroCreate(HeroBase): pass class HeroPublic(HeroBase): id: int★
The recommended FastAPI pattern: a shared Base, one table=True model, and separate Create/Public/Update schemas. Never accept or return the table model directly.
@app.post("/heroes/", response_model=HeroPublic) def create(hero: HeroCreate, session: Session = Depends(get_session)): db = Hero.model_validate(hero); session.add(db); session.commit()★
Hero.model_validate(hero) (Pydantic v2) builds the table row from the input schema; response_model filters the output.
from sqlmodel.ext.asyncio.session import AsyncSession from sqlalchemy.ext.asyncio import create_async_engine engine = create_async_engine("sqlite+aiosqlite:///db.sqlite")async
Official async support. Use an async driver (aiosqlite, asyncpg). SQLModel's AsyncSession keeps the .exec() ergonomics.
async with AsyncSession(engine) as session: res = await session.exec(select(Hero)) await session.commit()
await the exec/commit/refresh. Create tables via async with engine.begin() as c: await c.run_sync(SQLModel.metadata.create_all).

!Common gotchasread before shipping

table=True vs not
The core concept: only table=True classes are DB tables. Others are plain Pydantic schemas. Mixing them up is the #1 confusion.
session.exec(), not session.execute()
Use SQLModel's exec() to get model objects back. execute() is raw SQLAlchemy and returns Row tuples — different result shape.
id is None until commit + refresh
The PK is assigned by the DB. Read hero.id only after commit() then refresh() (or accessing it re-triggers a load).
DetachedInstanceError on relationships
Lazy relationships need a live session. Accessing hero.team after the with block closes raises. Access inside the session, eager-load, or set expire_on_commit=False.
don't reuse the table model as a request body
Accept HeroCreate, return HeroPublic — exposing the table model risks mass-assignment (e.g. client sets id) and leaks columns.
table models skip validation
With table=True, Pydantic validation is off by default (so ORM loads are cheap). Validate on the non-table Create schema, then model_validate into the table model.
create_all ≠ migrations
metadata.create_all only CREATEs missing tables; it never ALTERs. Use Alembic for schema changes on a live DB.

SQLModel cheat sheet