Knowledge base

SQLAlchemy ORM models and relationships

Declaring the inventory models with DeclarativeBase, Mapped and mapped_column, linking them with relationship(), and understanding object states and cascades.

  • Data modelling
  • Fundamentals

Updated

The ORM maps Python classes to tables. In SQLAlchemy 2.x the mapping is driven by type annotations: Mapped[int] tells SQLAlchemy (and my type checker) what the attribute holds, and mapped_column() adds database details. The goal for the inventory example is models that produce exactly the schema I would write by hand in SQL.

The complete models

# src/inventory_api/models.py
from datetime import datetime
from decimal import Decimal

from sqlalchemy import CheckConstraint, DateTime, ForeignKey, Identity, MetaData, Numeric, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=convention)


class Warehouse(Base):
    __tablename__ = "warehouse"

    id: Mapped[int] = mapped_column(Identity(), primary_key=True)
    name: Mapped[str] = mapped_column(String(100), unique=True)

    items: Mapped[list["Item"]] = relationship(
        back_populates="warehouse", cascade="all, delete-orphan"
    )


class Item(Base):
    __tablename__ = "item"
    __table_args__ = (CheckConstraint("quantity >= 0", name="quantity_non_negative"),)

    id: Mapped[int] = mapped_column(Identity(), primary_key=True)
    warehouse_id: Mapped[int] = mapped_column(
        ForeignKey("warehouse.id", ondelete="CASCADE"), index=True
    )
    sku: Mapped[str] = mapped_column(String(32), unique=True)
    name: Mapped[str] = mapped_column(String(200))
    quantity: Mapped[int] = mapped_column(default=0, server_default="0")
    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    description: Mapped[str | None]
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    warehouse: Mapped[Warehouse] = relationship(back_populates="items")

The naming convention gives every constraint and index a predictable name (pk_item, uq_item_sku, fk_item_warehouse_id_warehouse), which Alembic needs to drop or alter them later. The migrations note explains why.

How annotations become columns

Annotation Column type by default Nullable
Mapped[int] INTEGER NOT NULL
Mapped[str] VARCHAR (no length) NOT NULL
Mapped[Decimal] NUMERIC NOT NULL
Mapped[datetime] TIMESTAMP WITHOUT TIME ZONE NOT NULL
Mapped[str | None] VARCHAR nullable

So nullability follows the type: str | None is nullable, str is not. Anything beyond the default (a length, precision, time zone) goes in mapped_column(). Two of these defaults are wrong for the inventory schema, so they are overridden:

  • price uses Numeric(10, 2) so Postgres enforces precision, and Python gets Decimal, never float.
  • created_at uses DateTime(timezone=True), which is timestamptz. The bare datetime default is timestamp without time zone, which I do not want.

A column that needs nothing beyond its annotation, like description, needs no mapped_column() call at all.

Keys and defaults

  • Identity() renders GENERATED BY DEFAULT AS IDENTITY. Without it, an integer primary key on Postgres becomes SERIAL, the older mechanism. Both work; identity is the SQL-standard one.
  • default=0 is a Python-side default applied when the ORM inserts. server_default="0" puts DEFAULT 0 in the table, so raw SQL inserts and other applications get it too. I set both for quantity. Note that Item().quantity is None until the object is flushed: defaults apply at insert time, not at construction.
  • server_default=func.now() makes Postgres stamp created_at, using the database clock rather than each app server’s.

Relationships

relationship() adds an object-level attribute on top of the foreign key column. It does not create a column.

london = Warehouse(name="London")
london.items.append(Item(sku="BOLT-M6", name="M6 bolt", quantity=500, price=Decimal("0.12")))

with SessionLocal.begin() as session:
    session.add(london)  # the item is added too, through the "save-update" cascade
  • back_populates links the two sides, so appending to london.items also sets item.warehouse in memory, before anything is flushed.
  • On flush, SQLAlchemy inserts the warehouse first, gets its id back, and fills in item.warehouse_id. I never set foreign key ids by hand when I have the objects.
  • Mapped[list["Item"]] uses a string because Item is defined later in the file.

Cascades versus ON DELETE CASCADE

There are two separate mechanisms with confusingly similar names:

  • cascade="all, delete-orphan" is ORM behaviour. session.delete(warehouse) loads its items and deletes them as well, and removing an item from warehouse.items deletes that item (it became an orphan).
  • ondelete="CASCADE" on the ForeignKey is database behaviour. Postgres deletes child rows itself, even for deletes that do not go through the ORM.

I use both: the database rule protects the data, and the ORM rule keeps the session’s view consistent. For large collections, adding passive_deletes=True to the relationship tells the ORM to trust the database instead of loading every child before deleting.

Object states and the identity map

Every ORM object is in one of a few states, which explains most “why didn’t that save?” moments:

State Meaning
transient created in Python, not in a session
pending session.add() called, not yet flushed
persistent flushed, has a row and an identity in this session
detached was persistent, but its session closed
deleted session.delete() called and flushed

sqlalchemy.inspect(obj) reports the state (inspect(item).pending, .persistent and so on).

Within one session, the identity map guarantees one Python object per database row: loading the same item twice gives the same object, so session.get(Item, 1) is session.get(Item, 1) is True, and the second call does not even hit the database.

Gotchas

  • No length means unbounded. Mapped[str] alone gives VARCHAR with no limit. That is legal in Postgres, but add String(n) where a real limit exists.
  • Mapped[datetime] is naive by default. Always add DateTime(timezone=True) and store aware datetimes.
  • Python default does not reach the table. Only server_default appears in the DDL and in migrations.
  • relationship() needs a ForeignKey. Without one, SQLAlchemy cannot work out the join and raises an error at mapper configuration time.
  • Avoid MappedAsDataclass until the basics are solid. It is a good feature, but 2.1 changed how its defaults behave, and old examples will mislead me.

Check yourself

  1. What does Mapped[str | None] change in the generated table compared with Mapped[str]?
  2. Why does price need Numeric(10, 2) and created_at need DateTime(timezone=True) when the annotations alone would work?
  3. What is the difference between default=0 and server_default="0"?
  4. Explain the difference between cascade="all, delete-orphan" and ondelete="CASCADE".
  5. After session.add(item) but before a flush, what state is the item in, and does it have an id?

Further reading