Chapter 6: relationships. The feature that makes the ORM an ORM. We cover one-to-many, many-to-many, the loading strategies, association tables, and how to avoid N+1.

One-to-many

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

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

back_populates keeps both sides in sync.

user.posts.append(post)
# post.author == user automatically

Many-to-many

from sqlalchemy import Table, Column

post_tags = Table(
    "post_tags",
    Base.metadata,
    Column("post_id", ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), 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 many-to-many with extra columns (e.g., assigned_at): use an association object pattern (a 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"), unique=True)
    user: Mapped[User] = relationship(back_populates="profile")

Unique FK on the child side; uselist=False on the parent.

Loading strategies

The N+1 problem:

users = (await session.execute(select(User))).scalars().all()
for u in users:
    print(u.email, u.posts)  # one query per user

If you have 100 users, that’s 101 queries. Catastrophic.

Solutions:

selectinload

from sqlalchemy.orm import selectinload

stmt = select(User).options(selectinload(User.posts))
users = (await session.execute(stmt)).scalars().all()

for u in users:
    print(u.email, u.posts)  # all preloaded

Two queries: SELECT users then SELECT posts WHERE author_id IN (...). Default-best for one-to-many.

joinedload

from sqlalchemy.orm import joinedload

stmt = select(User).options(joinedload(User.profile))

Joins in one query. Best for one-to-one or many-to-one (no row multiplication).

For one-to-many with joinedload, rows multiply (a user with 5 posts → 5 rows duplicated). SQLAlchemy de-duplicates but it’s wasteful.

subqueryload

from sqlalchemy.orm import subqueryload

stmt = select(User).options(subqueryload(User.posts))

Older alternative to selectinload. Selectinload is usually preferred.

lazy=‘dynamic’

class User(Base):
    posts: Mapped["DynamicLoader[Post]"] = relationship(lazy="dynamic")

user.posts returns a query object, not a list. You then add filters / pagination and execute.

recent = await session.execute(user.posts.filter(Post.created_at > yesterday))

For one-to-many that can be huge.

In async, lazy="dynamic" has caveats. Often easier: explicit query.

Per-relationship default

class User(Base):
    posts: Mapped[list[Post]] = relationship(lazy="selectin")

Default load when accessed. For very common access patterns. But: hidden cost on every load, even when you don’t need posts.

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

Nested loading

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

Loads users, posts (via IN), and comments (via IN). Three queries; no N+1.

stmt = select(User).join(Post).where(Post.title.contains("python"))

Don’t confuse selectinload(...) (loads relationship) with join(...) (filters parent by child).

To filter loaded children, use a custom relationship or filter in Python (rarely):

from sqlalchemy.orm import selectinload, with_loader_criteria

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

with_loader_criteria filters the loaded children.

Backref vs back_populates

# back_populates (recommended; explicit)
class User: posts = relationship(back_populates="author")
class Post: author = relationship(back_populates="posts")

# backref (auto-creates the reverse; magic)
class User: posts = relationship("Post", backref="author")
# Post.author created automatically

Prefer back_populates. Explicit; type-checks better.

Cascade

class User(Base):
    posts: Mapped[list[Post]] = relationship(back_populates="author", cascade="all, delete-orphan")

all, delete-orphan means: when a Post is removed from user.posts, delete it.

Common cascades:

  • all = save-update + merge + refresh-expire + expunge + delete.
  • delete-orphan = delete when removed from parent.
  • save-update = default; cascades adds.

For most apps: all, delete-orphan for owned children; default otherwise.

Cascade vs FK ondelete

ORM cascade (Python-side) is different from ON DELETE CASCADE (DB-side). They can complement:

author_id = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))  # DB-side

posts = relationship(cascade="all, delete-orphan", passive_deletes=True)

passive_deletes=True tells the ORM not to load and individually delete posts; rely on the DB cascade.

Polymorphism (single-table inheritance)

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"}

class CD(Item):
    artist: Mapped[str | None]
    __mapper_args__ = {"polymorphic_identity": "cd"}

One table; type column distinguishes. select(Book) filters automatically.

Joined-table and concrete inheritance also exist; rarely worth it.

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

For trees. Use recursive CTEs for traversal.

Relationship arguments to know

  • back_populates: explicit two-way.
  • cascade: which ops propagate.
  • passive_deletes: rely on DB cascade.
  • lazy: default load strategy.
  • order_by: order children.
  • viewonly=True: read-only relationship; not part of unit-of-work.
  • secondary: association table for many-to-many.
  • primaryjoin / secondaryjoin: explicit join conditions for non-trivial cases.

Eager loading defaults

Setting lazy="selectin" on a relationship loads it eagerly always. For per-query control, use options(selectinload(...)).

For consistent read paths, eager-default can simplify code. Watch for over-fetching.

Common mistakes

1. N+1

The classic. Fix with selectinload.

2. joinedload for one-to-many

Row multiplication. Use selectinload for collections.

3. Missing back_populates

Updates to one side don’t reflect on the other in memory.

4. Forgetting cascade

Delete user; orphan posts in DB. Add cascade or DB-level FK ON DELETE.

5. lazy=‘dynamic’ in async

Some operations don’t work directly; usually clearer to write explicit queries.

Performance patterns

For a list endpoint with relationships:

async def list_users_with_posts(session: AsyncSession):
    stmt = (
        select(User)
        .options(selectinload(User.posts).load_only(Post.id, Post.title))
        .limit(20)
    )
    return (await session.execute(stmt)).scalars().all()

load_only restricts loaded columns on the related table. Smaller payload.

For listing posts with author info:

stmt = select(Post).options(joinedload(Post.author))

joinedload for many-to-one (one author per post; no multiplication).

What’s next

Chapter 7: Transactions and concurrency.

Read this next

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 .