Relationships

relationship() declares Python-side links between mapped classes. Under the hood it’s a JOIN or a second SELECT (depending on loading strategy). It does NOT create the foreign key — you still need ForeignKey(...) on the column.

The four shapes

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

The FK lives on the “many” side (posts.author_id). User.posts is the collection; Post.author is the scalar reference.

Many-to-one

The same setup viewed from the other side. Post.author is many-to-one.

One-to-one

Same as one-to-many but with uselist=False:

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    profile: Mapped["Profile"] = relationship(back_populates="user", uselist=False)

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

unique=True on user_id enforces the “one” at the DB level. Without it the schema allows many-to-one even if Python expects one-to-one.

Many-to-many

Requires an association table:

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):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    tags: Mapped[list["Tag"]] = relationship(secondary=post_tags, back_populates="posts")

class Tag(Base):
    __tablename__ = "tags"
    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[list["Post"]] = relationship(secondary=post_tags, back_populates="tags")

The composite primary key on the association table prevents duplicate (post, tag) pairs. SQLAlchemy auto-manages inserts/deletes when you post.tags.append(tag) / post.tags.remove(tag).

Association object (M:N with extra columns)

When the link itself has data (created_at, role, weight), use an explicit class instead of a Table:

class PostTag(Base):
    __tablename__ = "post_tags"
    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"), primary_key=True)
    tag_id: Mapped[int] = mapped_column(ForeignKey("tags.id"), primary_key=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

    post: Mapped["Post"] = relationship(back_populates="post_tags")
    tag: Mapped["Tag"] = relationship(back_populates="post_tags")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    post_tags: Mapped[list["PostTag"]] = relationship(back_populates="post")

Now post.post_tags[0].created_at works. The trade-off: you manipulate PostTag instances rather than tag objects directly. For ergonomics, layer association_proxy:

from sqlalchemy.ext.associationproxy import association_proxy

class Post(Base):
    post_tags: Mapped[list["PostTag"]] = relationship(back_populates="post")
    tags: AssociationProxy[list["Tag"]] = association_proxy("post_tags", "tag")

Now post.tags lets you read/write Tag instances directly; the proxy maintains PostTag rows behind the scenes.

back_populates vs backref

# back_populates — both sides declared (explicit; recommended)
class User(Base):
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    author: Mapped["User"] = relationship(back_populates="posts")

# backref — only one side declared (the other auto-generated)
class User(Base):
    posts: Mapped[list["Post"]] = relationship(backref="author")

class Post(Base):
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    # `author` attribute auto-created on Post

backref is concise but less type-checkable. back_populates (each side explicit) is the recommended 2.0 style.

What relationship() does at access time

By default, lazy loading. The first time you touch user.posts, SQLAlchemy issues:

SELECT * FROM posts WHERE author_id = :user_id

Subsequent accesses use the loaded collection. Loading strategies (joined, selectin, etc.) change this — see 06_loading_strategies_n_plus_1.md.

The relationship attribute returns:

• The collection (for one-to-many / many-to-many) — usually a list.
• The related object or None (for many-to-one / one-to-one).
• A Query-like object if lazy="dynamic" (legacy; produces a query you can filter further).

Cascades

What happens to related objects when the parent is added, deleted, or removed.

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

Common cascade values:

The most common cascade for “parent owns child” relationships:

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

Now session.delete(user) deletes all their posts; user.posts.remove(post) deletes that post.

Cascade is SQLAlchemy-side, distinct from DB ON DELETE CASCADE. Both can be set; they’re not redundant — Python cascade handles loaded session state, DB cascade handles raw SQL deletes. For “all parent.children get deleted when parent is deleted,” set both:

author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
# and on the parent's relationship:
posts: Mapped[list["Post"]] = relationship(
    back_populates="author",
    cascade="all, delete-orphan",
    passive_deletes=True,    # let the DB handle deletion; don't issue per-row DELETE from Python
)

passive_deletes=True is important — without it, SQLAlchemy issues a DELETE for every loaded child even though the DB will cascade. Big perf win for many children.

Self-referential relationships

For trees / hierarchies:

class Category(Base):
    __tablename__ = "categories"
    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")

remote_side tells SQLAlchemy which side of the relationship is “remote” (the FK target). Required for self-references; without it SQLAlchemy can’t figure out which way is up.

Complex join conditions

Sometimes the relationship isn’t a simple a.id = b.a_id:

class User(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    # only "published" posts, not drafts
    published_posts: Mapped[list["Post"]] = relationship(
        primaryjoin="and_(User.id == Post.author_id, Post.status == 'published')",
        viewonly=True,
    )

primaryjoin overrides the inferred join. viewonly=True is critical here: SQLAlchemy can’t auto-maintain a filtered relationship through cascades, so make it read-only.

viewonly=True

Tells SQLAlchemy “don’t try to manage this relationship; it’s purely for reads.” Useful for:

• Filtered relationships (published_posts above).
• Relationships across complex expressions.
• Derived “view” attributes.

viewonly=True skips all writes/cascades. Mutating the collection has no effect on the DB.

Common pitfalls

• N+1 queries from accessing parent.children in a loop. The default lazy loading is fine for one record, terrible for lists. See 06_loading_strategies_n_plus_1.md.
• Forgetting back_populates on both sides. SQLAlchemy can’t coordinate Python-side state if only one side knows about the relationship.
• DB ON DELETE CASCADE without passive_deletes=True. SQLAlchemy issues redundant per-row DELETEs.
• cascade="all" includes delete but NOT delete-orphan. Need to add delete-orphan explicitly when “removing from collection = delete the row.”
• Accessing user.posts after the session is closed. Lazy load triggers a query on a detached instance → DetachedInstanceError. See 14_common_pitfalls.md.
• Self-referential without remote_side. Cryptic error: “Could not determine direction for relationship.”

Common interview confusions

• “relationship() creates a foreign key.” — no, it creates a Python-side accessor. The ForeignKey(...) on the column creates the DB-level FK.
• “backref is the modern way.” — back_populates is the modern recommendation (more explicit, better type checking). backref is the older shortcut.
• “cascade='all' deletes children.” — all is shorthand for save-update, merge, refresh-expire, delete. To auto-delete orphaned children (removed from collection), add delete-orphan.

Interview angle

• “How do you set up a one-to-many in SQLAlchemy 2.0?” — ForeignKey on the child’s column, relationship(back_populates=...) on both sides. Parent declares Mapped[list["Child"]]; child declares Mapped["Parent"].
• “back_populates vs backref?” — back_populates is explicit on both sides (recommended). backref declares both from one side (concise but less type-checkable).
• “What’s a cascade and when does delete-orphan matter?” — controls how operations on the parent propagate to children. delete-orphan means a child removed from the parent’s collection (or whose parent is unset) is deleted from the DB. Use for “owned” children.
• “DB-level ON DELETE CASCADE vs SQLAlchemy cascade — both?” — both, with passive_deletes=True on the relationship to avoid redundant per-row DELETE from Python. DB cascade handles raw SQL; SQLAlchemy cascade handles loaded session state.
• “How do you model many-to-many with extra columns on the link?” — define the association as a full mapped class (with PK across both FKs and the extra columns); use association_proxy for ergonomic access if needed.
• “What does viewonly=True do?” — makes the relationship read-only; SQLAlchemy won’t try to maintain it through cascades or writes. Required for filtered/computed relationships.