Relationships in SQLAlchemy

One-to-Many Relationship

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(50))

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

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

    # Each post belongs to one user
    author: Mapped["User"] = relationship(back_populates="posts")

Many-to-Many Relationship

from sqlalchemy import Table, Column

# Association table
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)
    title: Mapped[str] = mapped_column(String(200))

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

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

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

Eager Loading (N+1 Prevention)

from sqlalchemy.orm import selectinload, joinedload

# selectinload - separate SELECT with IN clause
stmt = select(User).options(selectinload(User.posts))
users = session.scalars(stmt).all()

# joinedload - single JOIN query
stmt = select(User).options(joinedload(User.posts))
users = session.scalars(stmt).unique().all()

# Nested eager loading
stmt = select(User).options(
    selectinload(User.posts).selectinload(Post.comments)
)

Explicit Joins

# Inner join
stmt = select(User, Post).join(Post, User.id == Post.user_id)

# Left outer join
stmt = select(User, Post).outerjoin(Post, User.id == Post.user_id)

# Join with relationship
stmt = select(User).join(User.posts).where(Post.title.like("%python%"))

# Join with filter
stmt = (
    select(User.username, func.count(Post.id).label("post_count"))
    .join(Post)
    .group_by(User.id)
)

Relationships and Joins

Relationships and Joins

Relationships in SQLAlchemy

One-to-Many Relationship

Many-to-Many Relationship

Eager Loading (N+1 Prevention)

Explicit Joins