skip to content

Related Objects & Cascades

relationship() links mapped classes through foreign keys, back_populates keeps both sides in step and cascades decide what a delete takes along. Interviewers ask about delete-orphan.

on this pageshow

explore

questions

5

In SQLAlchemy 2.1, how do you map an Author with many Posts using relationship() and back_populates, and what does back_populates do?

level: juniorimportance: must knowfreq 70%

answer

  1. two relationship() calls, one per class
  2. the foreign key sits with the many
  3. list annotation picks the collection
  4. both attributes agree before any flush

basics

~10 s

Put ForeignKey("authors.id") on Post.author_id, declare Author.posts: Mapped[list["Post"]] and Post.author: Mapped["Author"], each with relationship(back_populates=...) naming the other attribute. back_populates keeps both Python sides in step before any flush.

solid answer

~40 s

The foreign key goes on the many side: `author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))` on `Post`. Then `Author.posts: Mapped[list["Post"]] = relationship(back_populates="author")` gives a list collection and `Post.author: Mapped["Author"] = relationship(back_populates="posts")` gives a many-to-one scalar; SQLAlchemy reads the target class and collection type from the annotation and the join from the foreign key. `back_populates` links the two relationship objects, so `ada.posts.append(post)` also sets `post.author` in memory, and the flush copies `ada.id` into `post.author_id`. Without it the two sides are independent, the other side stays stale until a reload, and SQLAlchemy warns that both relationships write the same column. `backref=` is the legacy one-sided shortcut, and SQLAlchemy 2.1 also accepts the other attribute or a lambda in place of the string.

code

python · 38 lines
python
from __future__ import annotations

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


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    posts: Mapped[list[Post]] = relationship(back_populates="author")


class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped[Author] = relationship(back_populates="posts")


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    ada = Author(name="Ada")
    post = Post(title="Hello, relationships")
    ada.posts.append(post)
    print(post.author is ada)  # True: back_populates set the other side in memory
    session.add(ada)  # save-update cascade adds the post too

with Session(engine) as session:
    post = session.scalars(select(Post)).one()
    print(post.author_id, post.author.name)

go deeper

for a junior

Recall the three declarations: the ForeignKey column on the many side, a list-typed relationship on the one side, a scalar relationship on the many side, both naming each other through back_populates.

for a middle

Explain what back_populates does in memory before a flush, how the annotation sets the collection type, and why two unlinked relationships over one column trigger a conflict warning.

for a senior

Show you know the one-way save-update cascade since 2.0: assigning post.author does not add the post to the Session, so code that builds objects from the child side must add them.

for a principal

Have a view on house style: back_populates over backref so both attributes are visible to readers and type checkers, and the 2.1 attribute or lambda form where tooling matters.

## The three pieces of a one-to-many mapping A **one-to-many** link in SQLAlchemy is built from three declarations, and each has its own job: - **The foreign key column** sits on the *many* side: `author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))` on `Post`. It is the only piece that exists in the database. Because it is annotated `Mapped[int]` and not `Mapped[int | None]`, the column is created `NOT NULL`. - **The collection** sits on the *one* side: `posts: Mapped[list["Post"]] = relationship(back_populates="author")` on `Author`. The `list[...]` inside `Mapped[]` tells SQLAlchemy the target class and the Python collection type at once; `Mapped[set["Post"]]` would give a set instead. - **The scalar reference** sits on the many side: `author: Mapped["Author"] = relationship(back_populates="posts")` on `Post`. An annotation without a collection type makes it a **many-to-one** attribute that holds a single object. If the foreign key column is nullable (`Mapped[int | None]`), annotate the relationship `Mapped[Author | None]` or `Mapped[Optional["Author"]]` to match. `relationship()` creates no column of its own. It works out the join condition by finding the `ForeignKey` between the two tables, which is why the foreign key has to be declared before the relationship can be configured. With `from __future__ import annotations` at the top of the module the quotes around forward-referenced class names can be dropped. ```python class Author(Base): __tablename__ = "authors" 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("authors.id")) author: Mapped[Author] = relationship(back_populates="posts") ``` ## What back_populates actually does `back_populates` joins two separate `relationship()` objects into a **bidirectional** pair. Each side names the *attribute* on the other class, not the class itself. Once the pair is linked: 1. `ada.posts.append(post)` sets `post.author` to `ada` in Python straight away. No SQL runs and no flush is needed. 2. Assigning `post.author = ada` is mirrored into `ada.posts` by the same in-memory event. 3. At flush, the unit of work copies `ada.id` into `post.author_id`, so application code never sets the foreign key by hand. The pair still describes **one** foreign key. Either side can be changed, and SQLAlchemy writes the same column. ## What goes wrong without it Two relationships over the same foreign key without `back_populates` are two independent attributes. Appending to `ada.posts` leaves `post.author` as `None` until the object is expired and reloaded, so code that reads the other side in the same unit of work sees stale state. SQLAlchemy also notices that both relationships would write `posts.author_id` and warns when the mappers are configured: ```plaintext SAWarning: relationship 'Post.author' will copy column authors.id to column posts.author_id, which conflicts with relationship(s): 'Author.posts' (copies authors.id to posts.author_id). If this is not the intention, consider if these relationships should be linked with back_populates, ... ``` The warning itself suggests the remedies: link the pair with `back_populates`, mark one side `viewonly=True` if it is read-only, or pass `overlaps=` when the overlap is deliberate. For an ordinary parent and child, `back_populates` is the answer. ## The Session cascade runs in one direction The default `cascade` of every `relationship()` is `"save-update, merge"`. The **save-update** part means that adding a parent to a `Session` adds whatever hangs off it: - `session.add(ada)` also adds every post already in `ada.posts`. - Appending a new post to `ada.posts` while `ada` is in a `Session` adds that post as well. - Assigning `post.author = ada` does **not** add `post` to `ada`'s `Session`, even though the attributes now agree. Call `session.add(post)` yourself. That last rule has held since SQLAlchemy 2.0, which removed the old `cascade_backrefs` option. Assigning an attribute is not meant to change an object's persistence state, because a half-built object could otherwise be flushed early by autoflush. ## backref and the 2.1 callable form | Form | Where it is written | Status in 2.1 | |---|---|---| | `back_populates="author"` | on both relationships | the standard form | | `backref="author"` | on one relationship only | legacy: it generates the reverse relationship at configuration time, so type checkers and readers never see it in the class body | | `back_populates=lambda: Post.author` or `back_populates=Author.posts` | on both relationships | new in 2.1: a callable or a class-bound attribute instead of a string | The 2.1 forms exist for tooling. A string is opaque to a type checker and to an IDE rename, while `Author.posts` or a lambda returning `Post.author` is an ordinary attribute reference. Use the direct reference when the other class is already defined, and a lambda when it is defined further down the module. Behaviour at runtime is the same as with the string.

  • A new Post gets post.author = ada while ada is already in a SQLAlchemy Session; is the post saved at commit?
    No, not unless it is added as well. Since SQLAlchemy 2.0 save-update cascade flows only from the object whose attribute you change: `ada.posts.append(post)` changes ada, which is in the Session, so the post is pulled in; `post.author = ada` changes the post, which is not, so nothing is added. The `cascade_backrefs` option was removed in 2.0, so call `session.add(post)` explicitly.
  • What does the 2.1 callable form of back_populates buy a team?
    Type checkers and IDE refactorings can follow an attribute reference but not a string. In SQLAlchemy 2.1, `back_populates` accepts the attribute itself, such as `Author.posts` when that class already exists, or a lambda such as `lambda: Post.author` when the other class is defined later. Runtime behaviour is unchanged; the string form still works.

  • Without back_populates SQLAlchemy cannot write the foreign key at all.
  • The ForeignKey column belongs on the Author side, next to the collection.
  • back_populates takes the name of the other class, not of its attribute.
  • backref= is the modern replacement for back_populates.
  • relationship() adds the author_id column if you leave it out.

Assumes SQLAlchemy 2.1; the attribute and lambda forms of back_populates are new in 2.1, while the string form works in every 2.x release.

open as a page

With SQLAlchemy's default relationship() cascade, what happens to a Post's Comments when the post is deleted, and what does "all, delete-orphan" change?

level: middleimportance: must knowfreq 48%

basics

~20 s

The default cascade is "save-update, merge", so deleting a Post sets each Comment's post_id to NULL, failing on a NOT NULL key. "all, delete-orphan" deletes the comments with the post and deletes any comment removed from post.comments.

open as a page

In SQLAlchemy, why does a Post with author_id and editor_id both referencing authors raise AmbiguousForeignKeysError, and how do you fix it?

level: middleimportance: should knowfreq 35%

basics

~20 s

relationship() infers its join from the foreign keys between two tables, and two keys to authors give two candidate paths. Pass foreign_keys= on every relationship involved, Post.author and Post.editor and their reverse collections, naming its own column.

open as a page

In SQLAlchemy, when do you map Post-to-Tag through relationship(secondary=post_tags), and when through an association-object class instead?

level: middleimportance: should knowfreq 42%

basics

~20 s

Use secondary= when post_tags holds only the two foreign keys: SQLAlchemy inserts and deletes link rows as tags are added or removed. Once the link row carries data, map post_tags to its own class and reach tags through it.

open as a page

Deleting a SQLAlchemy Post with 200,000 comments through Session.delete() takes minutes and huge memory; why, and how do passive_deletes and WriteOnlyMapped fix it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

The ORM SELECTs every unloaded comment so it can delete or null each one. Add ondelete="CASCADE" to the foreign key and passive_deletes=True to the relationship, and declare it WriteOnlyMapped so the collection is never loaded.

open as a page