Sqlmodel

SQLModel Patterns

Use SQLModel's API first. Do not default to raw SQLAlchemy patterns unless the task explicitly needs a SQLAlchemy-only feature.

Imports

Prefer imports from sqlmodel:

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select

Do not use SQLAlchemy declarative defaults such as declarative_base(), Mapped[...], mapped_column(), relationship(), or sessionmaker() for normal SQLModel code.

Models

Define table models with SQLModel, table=True and Field():

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")

Use Field(default_factory=...) for generated values:

id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)

Use non-table SQLModel classes for create/update/public API schemas instead of mixing request/response-only fields into table models.

Sessions and Queries

Open sessions directly with the engine:

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

Do not create a sessionmaker() for typical SQLModel examples.

Use session.exec(select(...)), not session.execute(...) and not session.query(...). SQLModel's exec() handles scalar results so agents should not add .scalars() after selects of models.

Use session.get(Model, id) for primary-key lookups.

Use normal result methods after session.exec(...): .all() for lists, .first() for optional first rows, .one() when exactly one row must exist, and .one_or_none() when zero or one row is valid.

After creating or mutating objects, commit and refresh when returned data needs DB defaults or generated IDs:

session.add(hero)
session.commit()
session.refresh(hero)

Relationships

Use SQLModel relationship attributes, not ad-hoc SQLAlchemy tables or secondary=.

One-to-many:

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


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

Many-to-many:

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


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


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

If the link table has extra fields, model it as a full SQLModel table with relationships to both sides, and interact with the link objects directly.

When starting a new app, prefer keeping related SQLModel table models in one file to simplify relationship annotations and metadata ordering. If models must be split across files, use TYPE_CHECKING imports plus string annotations:

if TYPE_CHECKING:
    from .team_model import Team

team: Optional["Team"] = Relationship(back_populates="heroes")

For SQLAlchemy relationship options not exposed as first-class SQLModel parameters, prefer Relationship(sa_relationship_kwargs={...}) or Relationship(sa_relationship_args=[...]). Use sa_relationship=relationship(...) only as an escape hatch when SQLModel's relationship wrapper cannot express the mapping.

Creates and Updates

Use direct construction for trusted internal values:

hero = Hero(name="Deadpond", secret_name="Dive Wilson")

Build table objects from data models with Model.model_validate(...):

db_hero = Hero.model_validate(hero_create)

For FastAPI, use a split model pattern: HeroBase for shared fields, Hero(HeroBase, table=True) for the table, HeroCreate for input, HeroUpdate with all-optional fields for PATCH, HeroPublic for output, and relationship-specific public models such as HeroPublicWithTeam only where needed.

For partial updates, dump only provided fields and update in place:

hero_data = hero_update.model_dump(exclude_unset=True)
db_hero.sqlmodel_update(hero_data)
session.add(db_hero)
session.commit()
session.refresh(db_hero)

Metadata and App Setup

Call SQLModel.metadata.create_all(engine) only after all table model classes have been imported. In FastAPI examples, use a dependency that yields Session(engine).

For tests and small examples, SQLite is a common option; when using it, create metadata explicitly and use direct sessions:

engine = create_engine("sqlite:///:memory:")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
    ...

When using SQLite with FastAPI, include:

connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, connect_args=connect_args)

Deletes

Use SQLModel's relationship helpers for cascades:

heroes: list["Hero"] = Relationship(back_populates="team", cascade_delete=True)
team_id: int | None = Field(default=None, foreign_key="team.id",

For database-level delete behavior such as SET NULL, pair ondelete with a nullable foreign key and use passive_deletes on the relationship when appropriate.

ondelete="SET NULL" requires a nullable foreign key:

team_id: int | None = Field(default=None, foreign_key="team.id", NULL")

SQLAlchemy Escape Hatches

Use SQLModel's Field(sa_type=...), Field(sa_column=...), Field(sa_column_args=...), or Field(sa_column_kwargs=...) only when normal Field() parameters do not cover a column or type requirement. Do not switch the whole model to SQLAlchemy declarative style for one custom column.