ORM Relationships — Deep Dive

Implement complex relationship patterns in SQLAlchemy 2.0: self-referential, polymorphic, and association proxies with loading strategies.

Relationship fundamentals in SQLAlchemy 2.0

SQLAlchemy 2.0 uses Mapped[] type annotations and mapped_column() for modern, type-safe relationship definitions.

One-to-Many

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, 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))
    books: Mapped[list["Book"]] = relationship(back_populates="author")

class Book(Base):
    __tablename__ = "books"

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

The Mapped[list["Book"]] type annotation tells both SQLAlchemy and type checkers that author.books returns a list of Book objects. The Mapped["Author"] on the other side returns a single object.

One-to-One

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)
    bio: Mapped[str | None] = mapped_column(Text)
    user: Mapped["User"] = relationship(back_populates="profile")

The unique=True on user_id enforces the one-to-one constraint at the database level. uselist=False tells SQLAlchemy to return a single object instead of a list.

Many-to-Many with association table

from sqlalchemy import Table, Column, ForeignKey

# Association table — no ORM model needed for simple cases
student_course = Table(
    "student_course",
    Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)

class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    courses: Mapped[list["Course"]] = relationship(
        secondary=student_course, back_populates="students"
    )

class Course(Base):
    __tablename__ = "courses"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    students: Mapped[list["Student"]] = relationship(
        secondary=student_course, back_populates="courses"
    )

Advanced relationship patterns

Association Object (many-to-many with extra data)

When the relationship itself carries data — enrollment date, grade, role — use a full ORM model instead of a bare table:

class Enrollment(Base):
    __tablename__ = "enrollments"

    student_id: Mapped[int] = mapped_column(
        ForeignKey("students.id"), primary_key=True
    )
    course_id: Mapped[int] = mapped_column(
        ForeignKey("courses.id"), primary_key=True
    )
    enrolled_at: Mapped[datetime] = mapped_column(default=func.now())
    grade: Mapped[str | None] = mapped_column(String(2))

    student: Mapped["Student"] = relationship(back_populates="enrollments")
    course: Mapped["Course"] = relationship(back_populates="enrollments")

class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    enrollments: Mapped[list["Enrollment"]] = relationship(back_populates="student")

class Course(Base):
    __tablename__ = "courses"

    id: Mapped[int] = mapped_column(primary_key=True)
    enrollments: Mapped[list["Enrollment"]] = relationship(back_populates="course")

Now you access student.enrollments[0].grade and student.enrollments[0].course.title.

Association Proxy for cleaner access

The association object pattern requires navigating through the intermediate model. association_proxy shortcuts this:

from sqlalchemy.ext.associationproxy import association_proxy

class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    enrollments: Mapped[list["Enrollment"]] = relationship(back_populates="student")

    # Access courses directly: student.courses
    courses = association_proxy("enrollments", "course")

Self-referential relationships

For tree structures like categories, org charts, or comment threads:

class Category(Base):
    __tablename__ = "categories"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"))

    children: Mapped[list["Category"]] = relationship(
        back_populates="parent",
        cascade="all, delete-orphan",
    )
    parent: Mapped["Category | None"] = relationship(
        back_populates="children",
        remote_side=[id],
    )

remote_side=[id] tells SQLAlchemy which side of the self-join is the “one” side.

Self-referential many-to-many (social follows)

follows = Table(
    "follows",
    Base.metadata,
    Column("follower_id", ForeignKey("users.id"), primary_key=True),
    Column("followed_id", ForeignKey("users.id"), primary_key=True),
)

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)

    following: Mapped[list["User"]] = relationship(
        secondary=follows,
        primaryjoin=id == follows.c.follower_id,
        secondaryjoin=id == follows.c.followed_id,
        backref="followers",
    )

Loading strategies

The choice of loading strategy has a massive impact on query count and performance.

Lazy loading (default)

# Executes 1 query
authors = session.scalars(select(Author)).all()

# Each access triggers another query — N+1 problem
for author in authors:
    print(author.books)  # 1 query per author

Eager loading with joinedload

from sqlalchemy.orm import joinedload

# 1 query with LEFT JOIN
stmt = select(Author).options(joinedload(Author.books))
authors = session.scalars(stmt).unique().all()

joinedload appends a JOIN to the original query. Good for one-to-one and one-to-many with small result sets. Can produce large cartesian products with multiple joinedload calls.

Subquery loading

from sqlalchemy.orm import subqueryload

# 2 queries: one for authors, one for all their books
stmt = select(Author).options(subqueryload(Author.books))
authors = session.scalars(stmt).all()

Better than joinedload for large collections — avoids duplicate parent rows in the result set.

Select-in loading

from sqlalchemy.orm import selectinload

# 2 queries: one for authors, one IN query for books
stmt = select(Author).options(selectinload(Author.books))
authors = session.scalars(stmt).all()

The most efficient strategy for most cases. Uses SELECT ... WHERE author_id IN (1, 2, 3, ...) to batch-fetch related objects. Works well up to thousands of parent IDs.

Nested eager loading

# Load authors → books → reviews in 3 queries
stmt = select(Author).options(
    selectinload(Author.books).selectinload(Book.reviews)
)

Cascade configuration

class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    books: Mapped[list["Book"]] = relationship(
        back_populates="author",
        cascade="all, delete-orphan",  # delete books when author is deleted
        passive_deletes=True,          # let the database handle cascades
    )

Cascade options:

  • save-update (default): Adding a parent also adds its children to the session
  • delete: Deleting a parent deletes children via ORM
  • delete-orphan: Removing a child from the collection deletes it
  • merge: Merging a parent also merges children
  • all: Shorthand for save-update, merge, delete, refresh-expire, expunge

passive_deletes=True paired with ON DELETE CASCADE in the database lets the database handle deletions efficiently, especially for bulk operations.

Performance patterns

Write-through relationship modification

# Adding to a relationship
book = Book(title="New Book")
author.books.append(book)  # triggers SQL INSERT on flush

# Bulk association — use direct INSERT for large batches
from sqlalchemy import insert
session.execute(
    insert(student_course),
    [
        {"student_id": 1, "course_id": c_id}
        for c_id in range(1, 1001)
    ]
)

Avoiding lazy loads in serialization

When converting ORM objects to JSON (for APIs), lazy loading can trigger dozens of queries:

# Dangerous: each .to_dict() may trigger lazy loads
return [author.to_dict() for author in authors]

# Safe: eagerly load everything needed for serialization
stmt = select(Author).options(
    selectinload(Author.books).selectinload(Book.publisher)
)
authors = session.scalars(stmt).all()
return [author.to_dict() for author in authors]

Configure lazy="raise" on relationships to make accidental lazy loading throw an error instead of silently executing queries:

books: Mapped[list["Book"]] = relationship(
    back_populates="author",
    lazy="raise",  # forces explicit eager loading
)

One thing to remember: Every relationship access is potentially a database query. Choose your loading strategy deliberately — use selectinload as a default, joinedload for single objects with small related sets, and lazy="raise" to catch accidental N+1 queries during development.

pythondatabasesorm

See Also

  • Python Aioredis Understand Aioredis through a practical analogy so your Python decisions become faster and clearer.
  • Python Alembic Understand Alembic through a practical analogy so your Python decisions become faster and clearer.
  • Python Asyncpg Database asyncpg is the fastest way for Python to talk to PostgreSQL without making your program sit around waiting.
  • Python Asyncpg Understand Asyncpg through a practical analogy so your Python decisions become faster and clearer.
  • Python Cassandra Python Understand Cassandra Python through a practical analogy so your Python decisions become faster and clearer.

Related Topics

ab-testing How tech companies run thousands of experiments at once to improve their products — the scientific method applied to everything from button colors to recommendation algorithms. 3 levels → activation-functions Why neural networks need these tiny mathematical functions — and how ReLU's simplicity accidentally made deep learning possible. 3 levels → ai-agents-architecture How AI systems go from answering questions to actually doing things — the design patterns that turn language models into autonomous agents that browse, code, and plan. 3 levels →