Cheatsheet for relationships. Long-form: Textbook Ch 6 .

One-to-many

from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[list["Post"]] = relationship(back_populates="author", cascade="all, delete-orphan")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    author: Mapped[User] = relationship(back_populates="posts")

back_populates (recommended over backref).

Many-to-many

from sqlalchemy import Table, Column

post_tags = Table(
    "post_tags",
    Base.metadata,
    Column("post_id", ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id", ondelete="CASCADE"), primary_key=True),
)

class Post(Base):
    tags: Mapped[list["Tag"]] = relationship(secondary=post_tags, back_populates="posts")

class Tag(Base):
    posts: Mapped[list[Post]] = relationship(secondary=post_tags, back_populates="tags")

For M:N with extra columns: use an association-object pattern (real model with FKs to both sides).

One-to-one

class User(Base):
    profile: Mapped["Profile | None"] = relationship(back_populates="user", uselist=False)

class Profile(Base):
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), unique=True)
    user: Mapped[User] = relationship(back_populates="profile")

Self-referential

class Category(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"))
    parent: Mapped["Category | None"] = relationship(remote_side="Category.id", back_populates="children")
    children: Mapped[list["Category"]] = relationship(back_populates="parent")

Cascade

relationship(cascade="all, delete-orphan")
# Common values: save-update, merge, refresh-expire, expunge, delete, delete-orphan, all

For “child deleted when parent is”: delete-orphan.

With DB-level ON DELETE CASCADE: add passive_deletes=True to avoid Python-side per-row deletes.

Loading strategies (N+1 fix)

from sqlalchemy.orm import selectinload, joinedload, subqueryload, contains_eager, raiseload

# selectinload — best for one-to-many
stmt = select(User).options(selectinload(User.posts))

# joinedload — best for one-to-one / many-to-one
stmt = select(Post).options(joinedload(Post.author))

# Nested
stmt = select(User).options(
    selectinload(User.posts).selectinload(Post.comments)
)

# load_only restricts columns on loaded relationship
stmt = select(User).options(
    selectinload(User.posts).load_only(Post.id, Post.title)
)

# raiseload to catch N+1 in dev/tests
stmt = select(User).options(raiseload("*"))

Per-relationship default load strategy

class User(Base):
    posts: Mapped[list[Post]] = relationship(lazy="selectin")    # always eager-load
    profile: Mapped[Profile] = relationship(lazy="joined")
    tags: Mapped[list[Tag]] = relationship(lazy="raise")         # forbid lazy

For most apps: explicit options(selectinload(...)) per query is clearer.

Filtering loaded children

from sqlalchemy.orm import with_loader_criteria

stmt = select(User).options(
    selectinload(User.posts),
    with_loader_criteria(Post, Post.published == True),
)

viewonly relationship

class User(Base):
    recent_posts: Mapped[list[Post]] = relationship(
        primaryjoin="and_(Post.author_id == User.id, Post.created_at > func.now() - text(\"'7 days'::interval\"))",
        viewonly=True,
    )

Read-only; not part of unit-of-work.

Polymorphism (single-table)

class Item(Base):
    __tablename__ = "items"
    id: Mapped[int] = mapped_column(primary_key=True)
    type: Mapped[str]
    name: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "item"}

class Book(Item):
    isbn: Mapped[str | None]
    __mapper_args__ = {"polymorphic_identity": "book"}

Async-loading caveats

In async, default lazy-load won’t auto-await:

# Option 1: explicit selectinload upfront
stmt = select(User).options(selectinload(User.posts))

# Option 2: awaitable_attrs
posts = await user.awaitable_attrs.posts

# Option 3: lazy="raise" in dev to force explicitness

Common mistakes

  • N+1 (the classic): fix with selectinload.
  • joinedload for one-to-many: row multiplication; use selectinload.
  • Missing back_populates: in-memory desync.
  • Cascade without DB-level FK ON DELETE: orphans in DB.
  • Lazy-load in async without awaitable_attrs.

Read this next

If you want my repository pattern with eager-loading discipline, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .