ORM Relationships — One-to-Many, Many-to-Many and Back-Populates

Relationships in SQLAlchemy ORM link Python objects across tables — a Post object can navigate to its author via post.author, and a User object can access all their posts via user.posts. This bidirectional navigation is defined with relationship() and back_populates. Getting relationships right — with correct foreign keys, cascade rules, and loading strategies — is one of the most impactful parts of SQLAlchemy design for FastAPI applications. Poor loading strategies (lazy loading in endpoints) cause the N+1 problem; correct ones make complex data fetching efficient.

One-to-Many Relationship #

# app/models/user.py
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base

class User(Base):
    __tablename__ = "users"

    id:    Mapped[int] = mapped_column(primary_key=True)
    name:  Mapped[str]
    email: Mapped[str] = mapped_column(unique=True)

    # One user → many posts
    # back_populates="author" matches the 'author' relationship on Post
    posts: Mapped[list["Post"]] = relationship(
        back_populates = "author",
        cascade        = "all, delete-orphan",  # delete posts when user deleted
        lazy           = "select",              # lazy load (default)
    )

# app/models/post.py
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship

class Post(Base):
    __tablename__ = "posts"

    id:        Mapped[int] = mapped_column(primary_key=True)
    title:     Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

    # Many posts → one user
    author: Mapped["User"] = relationship(back_populates="posts")

    # One post → many comments
    comments: Mapped[list["Comment"]] = relationship(
        back_populates = "post",
        cascade        = "all, delete-orphan",
    )

Many-to-Many Relationship #

# app/models/post.py — many-to-many: posts ↔ tags
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base

# ── Association table (pure SQL — no ORM class needed) ───────────────────────
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)
    title: Mapped[str]

    # Many posts ↔ many tags (via post_tags association table)
    tags: Mapped[list["Tag"]] = relationship(
        secondary      = post_tags,
        back_populates = "posts",
        lazy           = "select",
    )

class Tag(Base):
    __tablename__ = "tags"
    id:   Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)
    slug: Mapped[str] = mapped_column(unique=True)

    posts: Mapped[list["Post"]] = relationship(
        secondary      = post_tags,
        back_populates = "tags",
    )

Loading Strategies #

from sqlalchemy import select
from sqlalchemy.orm import Session, joinedload, selectinload, noload

# ── joinedload: single query with JOIN ────────────────────────────────────────
# Best for: many-to-one (loading the single related object — author of a post)
stmt = (
    select(Post)
    .options(joinedload(Post.author))         # JOIN users — one query
    .where(Post.status == "published")
    .limit(20)
)
posts = session.scalars(stmt).all()
# post.author is available — no extra queries

# ── selectinload: separate IN query ──────────────────────────────────────────
# Best for: one-to-many (loading many related objects — comments of a post)
# Also good for: many-to-many (tags of multiple posts)
stmt = (
    select(Post)
    .options(
        joinedload(Post.author),              # author: JOIN
        selectinload(Post.tags),              # tags: SELECT WHERE id IN (...)
        selectinload(Post.comments)
            .joinedload(Comment.author),      # comments + their authors: 2 queries
    )
    .where(Post.id == post_id)
)
post = session.scalars(stmt).first()

# ── noload: explicitly prevent loading ───────────────────────────────────────
stmt = select(Post).options(noload(Post.comments))  # never load comments
# Accessing post.comments raises DetachedInstanceError

Cascades and Deletion #

# ── Cascade delete-orphan: children deleted when parent is deleted ────────────
class Post(Base):
    comments: Mapped[list["Comment"]] = relationship(
        cascade = "all, delete-orphan",   # deleting post → deletes all comments
    )

# ── Manual cascade (database-level ON DELETE CASCADE) ────────────────────────
class Comment(Base):
    post_id: Mapped[int] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"),  # DB-level cascade
        index=True
    )
    # post_id becomes NULL or row deleted when post deleted (depending on ondelete=)

# ── Deleting a post and all its related data ──────────────────────────────────
post = session.get(Post, post_id)
session.delete(post)   # cascade delete-orphan removes comments automatically
session.commit()

Common Mistakes #

Mistake 1 — Mismatched back_populates names #

❌ Wrong — names don’t match:

class User(Base):
    posts: Mapped[list["Post"]] = relationship(back_populates="writer")  # "writer"

class Post(Base):
    author: Mapped["User"] = relationship(back_populates="posts")  # "posts"
# SQLAlchemy raises: Could not determine join condition

✅ Correct — back_populates must reference the exact attribute name:

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

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

Mistake 2 — Using joinedload for one-to-many (cartesian product) #

❌ Wrong — joinedload on a list relationship multiplies rows:

stmt = select(Post).options(joinedload(Post.comments))
# If a post has 100 comments: returns 100 duplicate Post rows, then deduplicates
# Very inefficient for large comment lists

✅ Correct — use selectinload for one-to-many:

stmt = select(Post).options(selectinload(Post.comments))   # ✓ second query

Mistake 3 — Accessing unloaded relationship outside session (DetachedInstanceError) #

❌ Wrong — accessing lazy relationship after session is closed:

post = db.query(Post).get(1)
db.close()
print(post.author.name)   # DetachedInstanceError — session is closed!

✅ Correct — load eagerly before closing session, or access within session:

post = db.query(Post).options(joinedload(Post.author)).get(1)   # ✓ author loaded

Quick Reference #