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.
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:
priceusesNumeric(10, 2)so Postgres enforces precision, and Python getsDecimal, neverfloat.created_atusesDateTime(timezone=True), which istimestamptz. The baredatetimedefault istimestampwithout 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()rendersGENERATED BY DEFAULT AS IDENTITY. Without it, an integer primary key on Postgres becomesSERIAL, the older mechanism. Both work; identity is the SQL-standard one.default=0is a Python-side default applied when the ORM inserts.server_default="0"putsDEFAULT 0in the table, so raw SQL inserts and other applications get it too. I set both forquantity. Note thatItem().quantityisNoneuntil the object is flushed: defaults apply at insert time, not at construction.server_default=func.now()makes Postgres stampcreated_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_populateslinks the two sides, so appending tolondon.itemsalso setsitem.warehousein 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 becauseItemis 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 fromwarehouse.itemsdeletes that item (it became an orphan).ondelete="CASCADE"on theForeignKeyis 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 givesVARCHARwith no limit. That is legal in Postgres, but addString(n)where a real limit exists. Mapped[datetime]is naive by default. Always addDateTime(timezone=True)and store aware datetimes.- Python
defaultdoes not reach the table. Onlyserver_defaultappears in the DDL and in migrations. relationship()needs aForeignKey. Without one, SQLAlchemy cannot work out the join and raises an error at mapper configuration time.- Avoid
MappedAsDataclassuntil 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
- What does
Mapped[str | None]change in the generated table compared withMapped[str]? - Why does
priceneedNumeric(10, 2)andcreated_atneedDateTime(timezone=True)when the annotations alone would work? - What is the difference between
default=0andserver_default="0"? - Explain the difference between
cascade="all, delete-orphan"andondelete="CASCADE". - After
session.add(item)but before a flush, what state is the item in, and does it have anid?