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

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

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

Datetimes

In SQLModel 0.0.45 and newer, use datetime for timestamp fields. SQLModel automatically selects UTCDateTime, which normalizes aware values to UTC when writing and returns aware UTC datetimes when reading, including with SQLite and MySQL/MariaDB.

Do not add sa_type=DateTime(timezone=True) or sa_column=Column(DateTime(timezone=True)) just to enable timezone support. These overrides bypass SQLModel's UTC processing. If an explicit column type is needed, import UTCDateTime from sqlmodel.

For generated timestamps on Python 3.11 and newer:

On Python 3.10, import timezone instead of UTC and use lambda: datetime.now(timezone.utc).

• Supply aware datetimes for writes, updates, and query filters. Avoid datetime.now() without a timezone and datetime.utcnow() for UTC timestamps. Naive database parameters are rejected when the statement executes.
• Use Pydantic's AwareDatetime when input validation must reject naive values. It uses the same UTC storage. Validate table-model input with Model.model_validate(data). Direct table-model construction currently skips Pydantic validation, and plain datetime validation accepts naive values.
• Use Pydantic's NaiveDatetime for intentional naive storage. It selects DateTime(timezone=False) and requires naive values during Pydantic validation.

When upgrading existing applications, follow the datetime migration guide. Changing the model does not alter existing columns or convert historical data.

Sessions and Queries

Open sessions directly with the engine:

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:

Relationships

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

One-to-many:

Many-to-many:

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:

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:

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

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:

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:

When using SQLite with FastAPI, include:

Deletes

Use SQLModel's relationship helpers for cascades:

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:

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.