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`: ```python 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()`: ```python 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: ```python 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. ## 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: ```python from datetime import UTC, datetime from sqlmodel import Field, SQLModel class Event(SQLModel, table=True): id: int | None = Field(default=None, primary_key=True) created_at: datetime = Field(default_factory=lambda: datetime.now(UTC)) ``` 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](https://sqlmodel.tiangolo.com/advanced/datetime/#upgrade-existing-applications). Changing the model does not alter existing columns or convert historical data. ## Sessions and Queries Open sessions directly with the engine: ```python 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: ```python session.add(hero) session.commit() session.refresh(hero) ``` ## Relationships Use SQLModel relationship attributes, not ad-hoc SQLAlchemy tables or `secondary=`. One-to-many: ```python 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: ```python 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: ```python 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: ```python hero = Hero(name="Deadpond", secret_name="Dive Wilson") ``` Build table objects from data models with `Model.model_validate(...)`: ```python 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: ```python 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: ```python engine = create_engine("sqlite:///:memory:") SQLModel.metadata.create_all(engine) with Session(engine) as session: ... ``` When using SQLite with FastAPI, include: ```python connect_args = {"check_same_thread": False} engine = create_engine(sqlite_url, connect_args=connect_args) ``` ## Deletes Use SQLModel's relationship helpers for cascades: ```python heroes: list["Hero"] = Relationship(back_populates="team", cascade_delete=True) team_id: int | None = Field(default=None, foreign_key="team.id", ondelete="CASCADE") ``` 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: ```python team_id: int | None = Field(default=None, foreign_key="team.id", ondelete="SET 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.