SQLAlchemy 2.0 #5 Relationships: One-to-Many, Many-to-Many, and the N+1 Problem

No real application deals with just one table. A user has many orders; a post has many tags. In the ORM this wiring is the job of relationship() — and exactly as convenient as it is, it is also the source of the ORM’s biggest performance trap, the N+1 problem. This part covers the declarations and the trap together.

One-to-many: how ForeignKey and relationship divide the work #

Declaring a user with many addresses:

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))

    addresses: Mapped[list["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )


class Address(Base):
    __tablename__ = "address"

    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str] = mapped_column(String(100))
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))

    user: Mapped["User"] = relationship(back_populates="addresses")

The roles split cleanly.

• ForeignKey belongs to the database. It creates the foreign key constraint on the address.user_id column. Without it there is no relationship.
• relationship() belongs to Python. It creates no column at all; it creates the object navigation paths user.addresses and address.user.
• back_populates: tells SQLAlchemy the two paths are two faces of the same relationship. Assign address.user = user on one side and it appears in user.addresses on the other automatically.

Usage feels like ordinary collection manipulation.

with SessionLocal.begin() as session:
    user = User(name="Alice")
    user.addresses.append(Address(email_address="alice@example.com"))
    user.addresses.append(Address(email_address="alice@work.com"))
    session.add(user)
# the session INSERTs the user, then both addresses with the issued id

Note that we never set user_id by hand. At flush time the session fills the parent’s primary key into the children’s foreign keys.

cascade: when the parent goes, what happens to the children? #

The cascade="all, delete-orphan" in the declaration above is the most common combination in practice.

• all: propagates the major operations — session.add on the parent includes the children, deleting the parent deletes the children, and so on.
• delete-orphan: a child removed from the collection (user.addresses.remove(addr)) is considered orphaned and gets DELETEd.

If “an address is a possession of its user,” this combination is right. Conversely, for relationships where the child must live independently — the author of a post — do not put delete in the cascade. Decide first whether the relationship is ownership or reference, then choose the cascade.

Many-to-many: the secondary table #

When both sides are many — posts and tags — you put a link table in between.

from sqlalchemy import Column, ForeignKey, Table

post_tag = Table(
    "post_tag",
    Base.metadata,
    Column("post_id", ForeignKey("post.id"), primary_key=True),
    Column("tag_id", ForeignKey("tag.id"), primary_key=True),
)


class Post(Base):
    __tablename__ = "post"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    tags: Mapped[list["Tag"]] = relationship(secondary=post_tag, back_populates="posts")


class Tag(Base):
    __tablename__ = "tag"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30), unique=True)
    posts: Mapped[list["Post"]] = relationship(secondary=post_tag, back_populates="tags")

The link table stays a plain Table wired in via secondary= — that is the basic form. post.tags.append(tag) is all you write; the session handles the link-table INSERT. But the moment the link table needs extra columns (when the tag was applied, by whom), promote it to a full model class and decompose into two one-to-many relationships. That is the association object pattern.

The N+1 problem: 80% of ORM performance issues #

By default, relationship attributes are lazy loading: the first access to user.addresses is the moment a SELECT goes out. Combine that with a loop and you have an incident.

with SessionLocal() as session:
    users = session.scalars(select(User)).all()   # 1 query
    for user in users:
        print(user.name, len(user.addresses))     # 1 query per user!

With 100 users, that is 1 + 100 = 101 queries. That is N+1. The classic pattern: unnoticeable in development with little data, then the list page suddenly crawls in production as data accumulates. Turn on echo=True, run the listing code, and the same-shaped SELECT repeating over and over confirms it instantly.

The fix is declaring up front that you want things loaded together. Two main strategies:

from sqlalchemy.orm import joinedload, selectinload

# selectinload: a second query with an IN clause loads all children at once
stmt = select(User).options(selectinload(User.addresses))

# joinedload: one LEFT JOIN loads everything
stmt = select(User).options(joinedload(User.addresses))
users = session.scalars(stmt).unique().all()

The default choice is simple: selectinload for collections, joinedload for single references. Either way, the goal is making the query count independent of the data count. You can also bake lazy="selectin" into the relationship declaration to change the default, but children are not needed on every query — per-query options() as the baseline avoids the waste.

One more thing, combined with part 4’s detached state: touching a lazy-loading attribute after the session closes raises DetachedInstanceError. That is the true identity of most errors met while touching relationship attributes right before an API response — and the answer is to eager-load at query time.

Summary #

• A relationship is two parts: ForeignKey makes the database constraint, relationship() makes the Python navigation paths, and back_populates ties the two directions together.
• Choose cascade by first deciding ownership (all, delete-orphan) versus reference (no delete propagation).
• Many-to-many is Table + secondary in its basic form; the moment the link table grows columns, promote to the association object pattern.
• Lazy loading + loop = N+1. Load together at query time: selectinload for collections, joinedload for single references.
• Next part: advanced queries — joins, aggregation, subqueries, pagination, and bulk operations, all in 2.0 style.