Modeling Relationships, Cascades and Association Objects

A relationship() is both a read path and a persistence rule: link every pair with back_populates, state ownership with cascade and passive_deletes where a child cannot exist without its parent, map links that carry data as association objects, and choose a loading strategy per query — because under async an unplanned lazy load raises instead of querying. This topic belongs to mastering SQLAlchemy 2.0 Core and ORM architecture.

Concept & Execution Model

A relationship() in SQLAlchemy does two jobs, and almost every relationship problem comes from remembering one and forgetting the other. It is a read path: an attribute that produces related objects. It is also a persistence rule: an instruction to the unit of work about which foreign key column it maintains, and what should happen to related rows when the parent is saved or deleted.

Four shapes, four annotations Four tiles. Many-to-one: a foreign key on this table, annotated Mapped of the target class. One-to-many: the same foreign key seen from the other end, annotated Mapped list of the child. Many-to-many with no extra data: a secondary link table, annotated Mapped list. Many-to-many with data on the link: an association object class mapped in its own right, reached through two one-to-many relationships. many-to-one FK on this table Mapped["Customer"] one-to-many the same FK, other end Mapped[list["Order"]] many-to-many (plain) secondary=link_table Mapped[list["Tag"]] many-to-many (+ data) association object class Mapped[list["OrderItem"]] Every shape is one or two foreign keys. The relationship() is how the ORM reads and maintains them.

The four shapes a schema needs are all built from foreign keys:

from sqlalchemy import ForeignKey, Table, Column
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


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):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id", ondelete="RESTRICT"))

    # many-to-one: the foreign key lives on this table
    author: Mapped["Author"] = relationship(back_populates="posts")
    # one-to-many: composition — comments belong to this post and nothing else
    comments: Mapped[list["Comment"]] = relationship(
        back_populates="post", cascade="all, delete-orphan", passive_deletes=True
    )
    # many-to-many with nothing on the link
    tags: Mapped[list["Tag"]] = relationship(secondary=post_tags, back_populates="posts")

Three decisions are visible in that mapping, and they are the three this topic is about. back_populates on every relationship says which pairs are two views of one link, rather than two rules competing for one column — the failure mode described in fixing "relationship will copy column" conflict warnings. cascade="all, delete-orphan" with passive_deletes=True says comments are owned by their post, while ondelete="RESTRICT" on author_id says an author is not owned by anything; both are covered in configuring cascade delete and delete-orphan correctly. And secondary says the post-to-tag link carries no data of its own — the moment it does, it becomes an association object.

The fourth shape, a table related to itself, is the same one-to-many with both ends on one table, and needs remote_side to say which end is which: modeling self-referential relationships for trees.

This topic sits inside mastering SQLAlchemy 2.0 Core and ORM architecture, and it depends on two neighbours: the typed annotations that declare these attributes, and the loading strategies that decide what a relationship costs to read.

Query Construction & Async Execution Patterns

Relationships are declared identically for synchronous and asynchronous code. What differs is that under async, how a relationship is loaded stops being a performance question and becomes a correctness one: a lazy load raises MissingGreenlet instead of quietly issuing a query.

Mappings are the same; loading is not Left, synchronous: a relationship with the default lazy select loads on first access, issuing a query whenever an attribute is touched. Right, asynchronous: the identical mapping needs the collection loaded before it is read, with a loader option on the query or an eager default on the relationship, because a lazy load raises MissingGreenlet. sync — lazy="select" (default) order.lines triggers a SELECT works anywhere, any time N+1 is a performance bug loader options optional async — plan the load selectinload(Order.lines) on the query or lazy="selectin" on the mapping N+1 is an exception, not a slowdown lazy="raise" makes it explicit Under async, loading strategy stops being an optimisation and becomes part of correctness.
# Sync — lazy loading works, and is a performance question
from sqlalchemy import select
from sqlalchemy.orm import Session

from blog.models import Post


def recent_titles_with_tags(session: Session) -> list[tuple[str, list[str]]]:
    posts = session.scalars(select(Post).order_by(Post.id.desc()).limit(20)).all()
    return [(p.title, [t.name for t in p.tags]) for p in posts]   # 20 extra queries

# Async — the same code raises; the collection must be loaded up front
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from blog.models import Post


async def recent_titles_with_tags(session: AsyncSession) -> list[tuple[str, list[str]]]:
    posts = (await session.scalars(
        select(Post).order_by(Post.id.desc()).limit(20).options(selectinload(Post.tags))
    )).all()
    return [(p.title, [t.name for t in p.tags]) for p in posts]   # 2 queries total

Three habits make this manageable across a large model.

Default to lazy="raise" on collections while a codebase is being written or ported. Every place that forgot a loader option then fails immediately, with a message naming the attribute, instead of failing later in a serialiser. lazy="raise_on_sql" is the softer variant: already-loaded collections are fine, only a query would raise. Using raiseload to catch unexpected lazy loads covers both.

Choose the strategy per query, not per model. selectinload() issues one extra query per relationship and keeps the row count flat, which suits collections. joinedload() issues none but multiplies rows, which suits many-to-one. The comparison is in using selectinload vs joinedload for N+1 prevention.

Chain loaders for depth. Two relationships deep needs two options: selectinload(Order.items).selectinload(OrderItem.product). Missing the second is the single most common cause of MissingGreenlet in code that already uses eager loading.

For filtering by a relationship rather than loading it, any() and has() render EXISTS subqueries and leave the row count alone:

from sqlalchemy import select

from blog.models import Post, Tag

published_with_tag = select(Post).where(Post.tags.any(Tag.name == "sqlalchemy"))
by_staff_author = select(Post).where(Post.author.has(is_staff=True))

State Management & Session Boundaries

Relationship attributes are session state, and they behave like any other loaded attribute: they belong to the session that loaded them, they expire on commit unless told otherwise, and they cannot be refreshed once the session is gone.

A relationship at flush time Five steps. The flush sorts pending objects so that a row is written after anything it depends on. Parents are inserted first and their generated primary keys read back. Each relationship then copies the parent key into the child foreign key — this is the persistence rule two relationships must not share. Child inserts and updates follow. Deletes run in the reverse order, children before parents, unless the database is cascading them. dependency sort parents before children per relationship INSERT parents primary keys returned RETURNING on PostgreSQL copy keys into children order_lines.order_id = orders.id the persistence rule INSERT / UPDATE children in dependency order DELETE in reverse children first Two relationships claiming step three is exactly what the "will copy column" warning reports.

Both sides synchronise in memory, before any SQL. With back_populates, post.comments.append(comment) also sets comment.post, immediately, with no round trip. That is a property of the relationship, not of the database, and it is why two unlinked relationships over the same column produce objects that disagree with each other.

Collections expire on commit. With the default expire_on_commit=True, every loaded attribute — including collections — is expired when the session commits, so the next access reloads. Under async that access raises rather than reloading, which is why most async applications set expire_on_commit=False on the session factory, as using expire_on_commit=False in FastAPI dependencies explains.

Detached objects cannot load anything. An object returned from a closed session keeps the attributes that were loaded and raises DetachedInstanceError for the rest. So the decision about which relationships to load has to be made where the query is written, not where the data is used — a serialiser that walks an object graph is the wrong place to discover a missing loader option.

A relationship is not a query. post.comments is the whole collection. Filtering it in Python loads every row first; filtering it in SQL means a query against Comment, or a contains_eager() load with an explicit join, which changes what the collection contains:

from sqlalchemy import select
from sqlalchemy.orm import contains_eager

from blog.models import Comment, Post

# post.comments will contain ONLY the approved comments.
stmt = (
    select(Post)
    .join(Post.comments)
    .where(Comment.approved.is_(True))
    .options(contains_eager(Post.comments))
)

That is useful for reading and dangerous for writing: the in-memory collection no longer represents every row, so delete-orphan would treat the filtered-out comments as removed. Load filtered collections in read-only paths, or with viewonly=True relationships built for the purpose — the technique is covered in using contains_eager with filtered joins.

Advanced Relationship Configuration

Beyond the four shapes, a handful of options come up repeatedly in production models.

How strongly does the parent own the child? Four bands. Composition, where the child cannot exist alone: cascade all with delete-orphan, passive deletes, and ON DELETE CASCADE. Reference, where both sides live independently: no cascade, and ON DELETE RESTRICT so the database refuses to orphan rows. Shared many-to-many: a secondary table, with no delete cascade at all. Recoverable data: soft deletion instead of any cascade. composition — order lines, invoice rows, attachments cascade="all, delete-orphan", passive_deletes=True, ondelete="CASCADE" reference — an order's customer, a post's author no delete cascade; ondelete="RESTRICT" so the database refuses to orphan rows shared many-to-many — tags, roles, categories secondary table; deleting one side never deletes the other recoverable — anything a user might want back soft deletion and a filter, rather than a cascade of any kind

order_by makes a collection's order deterministic. Without it, the order is whatever the database returns, which changes with the plan. order_by="Comment.created_at.desc()" is evaluated lazily as a string, which avoids import ordering problems between modules.

primaryjoin and foreign_keys are needed whenever the join is ambiguous: two foreign keys to the same table, a self-referential mapping, or a join on something other than a foreign key. Two foreign keys to one table is the common case, and forgetting to disambiguate produces AmbiguousForeignKeysError:

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


class Transfer(Base):
    __tablename__ = "transfers"

    id: Mapped[int] = mapped_column(primary_key=True)
    from_account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"))
    to_account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"))

    from_account: Mapped["Account"] = relationship(foreign_keys=[from_account_id])
    to_account: Mapped["Account"] = relationship(foreign_keys=[to_account_id])

viewonly=True declares a relationship that reads but never persists. It is the right answer for any derived or filtered view of data another relationship owns, and SQLAlchemy 2.0 warns when a viewonly collection is mutated, so the mistake is visible during development.

lazy="write_only" is the 2.0 option for collections too large to load at all. The attribute offers add(), remove() and a select() builder, and refuses to materialise the collection — exactly right for a parent with a million children, where the usual collection semantics are a trap rather than a convenience.

single_parent=True asserts that a child has at most one parent, which is what makes delete-orphan legal on a many-to-many or many-to-one relationship.

innerjoin=True on a joinedload of a non-nullable many-to-one turns the LEFT OUTER JOIN into an inner join, which the planner can often execute better. It is only correct when the foreign key is NOT NULL.

Two deliberate omissions are worth stating. Association proxies and secondary relationships are covered in the association object guide, because they are two answers to the same modelling question. And hybrid properties — attributes computed from a relationship in both Python and SQL — belong to hybrid properties, column properties and SQL expressions, where the SQL-expression side gets the space it needs.

Hybrid Architectures & Migration Strategies

Relationships are the part of a 1.x model that changes most on the way to 2.0, and the changes are mechanical enough to do incrementally.

Legacy relationship style, and 2.0 Left, legacy: Column and relationship with a string class name, a backref that creates the other side invisibly, and implicit lazy loading everywhere. Right, 2.0: Mapped annotations with mapped_column, back_populates naming both ends explicitly, cascades stated where ownership exists, and loader options or lazy raise so nothing loads by accident. legacy 1.x lines = relationship("OrderLine", backref="order") implicit other side lazy loading everywhere 2.0 lines: Mapped[list["OrderLine"]] = relationship(back_populates="order") both ends visible and typed loading declared per query back_populates and typed annotations are what let a type checker see relationships at all.

Column to Mapped with mapped_column. Annotations are what give relationships their types, and what let a type checker see that post.comments is a list[Comment]. The mechanics are in using mapped_column instead of Column.

backref to back_populates. A backref creates the other side of the relationship implicitly, at mapper configuration time, so it appears in no source file and no type stub. Converting means writing that side out explicitly on the other class — the subject of replacing backref with back_populates. Do it one relationship at a time; both styles coexist.

Implicit lazy loading to explicit loader options. This is the real work in an async port, and the order that makes it tractable is: add lazy="raise" to collections, run the test suite, and fix each failure by adding a loader option to the query that needs it. The failures are precise, and the work is bounded by the number of query sites rather than by the number of attribute accesses.

Cascades, stated rather than inherited. A 1.x model often relies on database-level ON DELETE rules that the ORM knows nothing about, or on the ORM's default null-out behaviour that nobody chose. Go through each relationship once and answer the ownership question from the decision ladder above.

Mixing synchronous and asynchronous use of the same models is fine and common: a web service reads through AsyncSession while a reporting job or a legacy admin uses a synchronous Session against the same classes. The mappings are shared; only the loading discipline differs. What does not work is sharing a session or an engine between the two worlds — that boundary is covered in calling async SQLAlchemy from synchronous code.

Measuring What a Relationship Costs

Loading strategy is the one relationship decision that has to be re-checked as data grows, because the right answer depends on numbers that change: rows per parent, bytes per row, and how many parents a page shows. Three measurements keep it honest.

Three numbers to re-check as data grows Three bands. Queries per request, counted with an event listener, catches a forgotten loader option turning into an N plus one. Rows returned versus rows needed shows when a joinedload of a collection is multiplying parent columns. And the size of the largest collection, not the average, decides whether any load-the-whole-collection strategy is still safe. queries per request a counter in a before_cursor_execute listener, asserted in tests rows returned vs rows needed joinedload of a collection repeats the parent columns once per child the largest collection, not the average one parent with 200,000 children breaks every in-memory strategy — use lazy="write_only"

Queries per request. A ContextVar counter incremented from a before_cursor_execute listener turns "this endpoint feels slow" into a number, and an assertion in tests turns a new N+1 into a failing build. The instrument is in counting queries per request to catch N+1 regressions, and relationships are what it catches most often: one forgotten loader option in a serialiser turns twenty rows into twenty-one queries.

Rows returned versus rows needed. This is what distinguishes a bad joinedload from a good one. A joinedload of a collection multiplies the parent row by its children, so twenty orders with fifty lines each is one query returning a thousand rows, each repeating the order's columns. The fix is not always selectinload: sometimes it is not loading the collection at all, and selecting an aggregate instead — func.count() through a column_property or a correlated subquery — when the page only shows "12 items".

The size of the largest collection. Every collection strategy assumes the collection fits in memory. A relationship that is fine at ten children per parent behaves very differently at fifty thousand, and the failure is a slow endpoint and a large resident set rather than an error. lazy="write_only" exists for exactly this case: it refuses to materialise the collection and offers add(), remove() and a select() builder instead, so the code that writes to it keeps working while nothing can accidentally load it all.

A quick way to find the candidates is one query against the real data:

from sqlalchemy import func, select

from shop.models import OrderLine

widest = (
    select(OrderLine.order_id, func.count().label("lines"))
    .group_by(OrderLine.order_id)
    .order_by(func.count().desc())
    .limit(10)
)

Run it for each child table and look at the top of the distribution rather than the average. Averages hide the parent with two hundred thousand children, and that parent is the one that will page someone.

Production Pitfalls & Anti-Patterns

  • MissingGreenlet: greenlet_spawn has not been called — a lazy load under async. Add the loader option, and chain one per level of depth.
  • SAWarning: relationship 'X' will copy column ... conflicts with relationship(s) 'Y' — two relationships maintaining one foreign key. Link them with back_populates, or mark the read-only one viewonly=True.
  • IntegrityError: null value in column "post_id" violates not-null constraint on delete — the default cascade blanking a child's foreign key. The relationship is composition: cascade="all, delete-orphan".
  • AmbiguousForeignKeysError — two foreign keys to the same table. Name foreign_keys= on each relationship.
  • DetachedInstanceError in a serialiser — the session closed before the attribute was read. Load it inside the session, or return plain data.
  • SAWarning: Object of type <Comment> not in session, add operation along backref will not proceed — an object was appended to a collection whose parent is not in a session.
  • A collection that used to be ordered is not any more — no order_by on the relationship; the plan changed.
Loading strategy decides the query count Bar chart for 200 orders with lines. Lazy loading issues 201 queries, and under async raises on the first collection access. joinedload issues one query whose row count is orders times lines. selectinload issues two queries regardless of the number of orders. A raiseload default issues one query and fails loudly if any collection is touched unexpectedly. lazy="select" (default) 201 queries — or MissingGreenlet under async joinedload 1 query, rows multiplied by lines per order selectinload 2 queries, flat row count lazy="raise" + explicit options 2 queries, and an error if anything is missed Illustrative. The point is the shape: per-row growth against a constant.

The quiet one is a viewonly or filtered collection used as if it were the whole thing. Nothing errors: the objects are real, the reads are correct, and a delete-orphan cascade or a remove() then acts on a collection that never contained every row. Keep filtered loads in read paths, and make the write path go through the owning relationship.

Frequently Asked Questions

back_populates or backref in SQLAlchemy 2.0?

back_populates. It declares both ends where they are defined, so type checkers and readers can see them, and it avoids the "will copy column" conflict that two independently declared relationships produce.

When should a relationship cascade deletes?

When the child cannot meaningfully exist without the parent — order lines, invoice rows, attachments. Use cascade="all, delete-orphan" with passive_deletes=True and ON DELETE CASCADE. For references such as an order's customer, use no cascade and ON DELETE RESTRICT.

Why does reading a relationship raise MissingGreenlet?

Because it was not loaded and lazy loading cannot run under AsyncSession. Add a loader option such as selectinload() to the query, one per level of relationship depth.

When do I need an association object instead of secondary?

As soon as the link itself has data — a quantity, a role, a timestamp. A secondary table can only hold the two foreign keys.

How do I map a table related to itself?

A nullable foreign key to its own primary key, with remote_side=[id] on the parent relationship so SQLAlchemy knows which end is the "one".