KAHIBARO
Discord Login Register ☰
☰

12.9. Relationships

Table of Contents

Understanding Relationships with an ORM

When you connect your backend to a relational database, you rarely work with isolated tables. Users have posts, orders have items, products belong to categories. In SQL you represent these links with foreign keys. In an ORM you represent them with relationships between models.

This chapter focuses on how to model and use relationships in an ORM, using SQLAlchemy-style examples in Python. We will not re-explain what primary keys and foreign keys are, since that is covered in database chapters. Here the focus is how to express and work with them from your code.

Throughout, assume a typical SQLAlchemy 2.x style setup with:

python 
from sqlalchemy.orm import DeclarativeBase, relationship, Mapped, mapped_column
from sqlalchemy import ForeignKey, String, Integer
class Base(DeclarativeBase):
    pass

Why Relationships Matter in an ORM

Without relationships, your ORM models are just separate tables that you must manually glue together with raw queries. Relationships help you:

A relationship in ORM has two parts:

  1. A foreign key column on the child or linking table.
  2. A relationship attribute that maps Python objects to each other.

Key rule:
Every ORM relationship depends on a correct foreign key in the database.
If the foreign key is wrong, the relationship will not work correctly.

A simple mental model:

One-to-One Relationships

A one-to-one relationship means that one row in table A is linked to at most one row in table B, and vice versa.

Common examples:

Basic One-to-One Example

Imagine each User has exactly one UserProfile.

Database side

ORM models

python 
from sqlalchemy import Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relationship, Mapped, mapped_column
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True)
    # One-to-one: User -> UserProfile
    profile: Mapped["UserProfile"] = relationship(
        back_populates="user",
        uselist=False,
    )
class UserProfile(Base):
    __tablename__ = "user_profiles"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id"),
        unique=True  # ensures one-to-one at DB level
    )
    full_name: Mapped[str] = mapped_column(String)
    # Back reference to User
    user: Mapped[User] = relationship(back_populates="profile")

Important details:

Using a One-to-One Relationship

python 
# create a user and its profile
user = User(email="alice@example.com")
profile = UserProfile(full_name="Alice Example")
user.profile = profile
session.add(user)
session.commit()
# later, load and use
db_user = session.get(User, user.id)
print(db_user.profile.full_name)  # "Alice Example"
# you can also access the user from the profile
db_profile = session.get(UserProfile, profile.id)
print(db_profile.user.email)  # "alice@example.com"

Key idea: you work with Python object attributes, not foreign key integers directly. The ORM handles user_id behind the scenes.

When Should You Use One-to-One?

Use a one-to-one relationship when:

If you are not sure, starting with a one-to-many is often safer, then later enforce uniqueness if necessary.

One-to-Many Relationships

One-to-many is the most common relationship type. It means one row in table A can be linked to many rows in table B, while each row in B belongs to exactly one row in A.

Examples:

Basic One-to-Many Example

Let's model users and their blog posts.

Database side

ORM models

python 
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True)
    # One-to-many: User -> Post
    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)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    title: Mapped[str] = mapped_column(String)
    content: Mapped[str] = mapped_column(String)
    # Many-to-one: Post -> User
    author: Mapped[User] = relationship(back_populates="posts")

Notes:

Important rule:
In a one-to-many relationship, the "many" side holds the foreign key.
The ORM relationship exists on both sides, but the database foreign key is only on one table.

Working with One-to-Many

Creating related objects

python 
# create a user
user = User(email="bob@example.com")
# add posts through the relationship
user.posts.append(Post(title="First post", content="Hello"))
user.posts.append(Post(title="Second post", content="More text"))
session.add(user)
session.commit()
print(user.posts[0].title)  # "First post"

You can also assign the user explicitly on the post:

python 
post = Post(
    title="Third post",
    content="Another one",
    author=user,  # sets user_id automatically
)
session.add(post)
session.commit()

Querying and navigating

python 
# fetch a user and see their posts
db_user = session.query(User).filter_by(email="bob@example.com").one()
for post in db_user.posts:
    print(post.title)
# fetch a post and see its author
db_post = session.query(Post).first()
print(db_post.author.email)

The ORM will issue the necessary SQL to fetch the right records and set up the object graph.

Controlling Cascades

Cascade rules are important to avoid orphaned or unexpected data. Common options:

Cascade optionMeaning (simplified)
save-updatePropagate changes to child objects
deleteDelete children when parent is deleted
delete-orphanDelete children that are no longer attached to parent
allIncludes most operations (save-update, merge, delete)

Practical combinations:

python 
# Typical "delete children when parent is deleted"
relationship(..., cascade="all, delete-orphan")
# No cascade delete, children must be deleted separately
relationship(..., cascade="save-update, merge")

For example, you might want to:

Many-to-Many Relationships

Many-to-many means each row in table A can be linked to many rows in table B, and each row in B can be linked to many rows in A.

Examples:

In a relational database, many-to-many is implemented with a link table (also called association table or join table).

Basic Many-to-Many Example

Suppose:

Database side

ORM models with an association table

python 
from sqlalchemy import Table, Column
post_tag_table = 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)
    tags: Mapped[list["Tag"]] = relationship(
        secondary=post_tag_table,
        back_populates="posts",
    )
class Tag(Base):
    __tablename__ = "tags"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True)
    posts: Mapped[list[Post]] = relationship(
        secondary=post_tag_table,
        back_populates="tags",
    )

Key pieces:

Rule for many-to-many:
You always need a separate association table.
Do not try to store arrays or comma separated IDs in a single column instead.

Using a Many-to-Many Relationship

Creating and linking

python 
post = Post(title="ORM relationships")
tag_sql = Tag(name="sql")
tag_python = Tag(name="python")
# Link tags to post
post.tags.append(tag_sql)
post.tags.append(tag_python)
session.add(post)
session.commit()
# Behind the scenes, post_tags rows are inserted:
# (post_id=post.id, tag_id=tag_sql.id)
# (post_id=post.id, tag_id=tag_python.id)

You can also add posts to tags:

python 
another_post = Post(title="Another post")
tag_python.posts.append(another_post)
session.add(another_post)
session.commit()

Querying

Find all posts with a given tag:

python 
python_tag = session.query(Tag).filter_by(name="python").one()
for post in python_tag.posts:
    print(post.title)

Find all tags for a given post:

python 
some_post = session.query(Post).first()
for tag in some_post.tags:
    print(tag.name)

The ORM generates a JOIN between posts, post_tags, and tags as needed.

Association Object Pattern

Sometimes the link itself has extra data. For example:

In that case, you map the association table as a full model class.

Example: students, courses, and enrollments with a grade.

python 
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
    )
    grade: Mapped[str] = mapped_column(String)
    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)
    name: Mapped[str] = mapped_column(String)
    enrollments: Mapped[list[Enrollment]] = relationship(
        back_populates="student"
    )
    courses: Mapped[list["Course"]] = relationship(
        secondary="enrollments",
        back_populates="students",
        viewonly=True,  # derived from enrollments
    )
class Course(Base):
    __tablename__ = "courses"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String)
    enrollments: Mapped[list[Enrollment]] = relationship(
        back_populates="course"
    )
    students: Mapped[list[Student]] = relationship(
        secondary="enrollments",
        back_populates="courses",
        viewonly=True,
    )

Now you can access enrollment.grade and still navigate between students and courses.

Relationship Loading Strategies

Relationships control how objects are connected. Loading strategies control when related data is loaded from the database.

There are two main strategies:

Lazy Loading

This is the default in most ORMs. Example:

python 
user = session.query(User).first()
# At this point, no posts have been loaded yet.
print(user.posts)  # SQL query is executed here

Pros:

Cons:

python 
users = session.query(User).all()  # 1 query
for user in users:
    print(len(user.posts))         # 1 query per user, can be many!

Eager Loading

Eager loading fetches related data in the same database round trip or with a limited number of queries.

Common SQLAlchemy options:

Example: load users and their posts in one go.

python 
from sqlalchemy.orm import selectinload
users = (
    session.query(User)
    .options(selectinload(User.posts))
    .all()
)
for user in users:
    print(user.email, len(user.posts))  # no extra queries

selectinload pattern:

joinedload pattern:

python 
from sqlalchemy.orm import joinedload
users = (
    session.query(User)
    .options(joinedload(User.posts))
    .all()
)

joinedload uses a LEFT OUTER JOIN to load users and posts together.

Performance rule:
Use lazy loading for simple, one off access.
Use eager loading when you know you will access relationships in bulk to avoid the N+1 query problem.

Setting Loading Strategy in the Relationship

You can also set default loading on the relationship itself:

python 
posts: Mapped[list["Post"]] = relationship(
    back_populates="author",
    lazy="selectin",  # or "joined", "select", "raise", etc.
)

This way, every query benefiting from that relationship uses the same strategy unless overridden.

Bidirectional Relationships and `back_populates` vs `backref`

Relationships can be:

Bidirectional relationships are very common and convenient.

`back_populates`

back_populates is explicit. You define both relationship attributes and tell them which one is the other side.

python 
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)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped[User] = relationship(back_populates="posts")

Advantages:

`backref`

backref is shorthand. You define a relationship on one side and ORM automatically creates the reverse side.

python 
from sqlalchemy.orm import backref
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[list["Post"]] = relationship(
        backref=backref("author")  # creates Post.author automatically
    )
class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

This is shorter, but less explicit. In new projects, prefer back_populates since it plays better with modern type annotations.

Consistency rule:
Choose either back_populates or backref as your standard and use it consistently across your codebase.

Relationship Configuration Tips and Common Pitfalls

Relationships look simple, but a few small mistakes can cause confusing bugs. Here are some practical tips.

1. Always Align Foreign Keys and Relationships

If your relationship is Post.author, the foreign key must match:

Common error: misspelling table or column name in ForeignKey("users.id"). This breaks the link even if code compiles.

Check:

2. Decide Ownership and Cascades

Ask, for each relationship:

Examples:

RelationshipOwnership decisionCascade
User → UserProfileUser owns profile, delete profile with userall, delete-orphan
User → PostDepends on product, often delete posts or notConfigurable
Order → OrderItemOrder owns items, delete items with orderall, delete-orphan
Product ↔ TagNeither owns the other, only delete associationNo delete cascade

If you are unsure, start with no delete cascade and handle deletions in code until the behavior is clear.

3. Be Careful with Lazy Loading in APIs

In a web API, a common pattern is:

If serialization code accesses relationships, and those are lazy loaded, you can accidentally trigger N+1 queries.

Consider:

python 
users = session.query(User).all()
result = []
for user in users:
    result.append({
        "id": user.id,
        "email": user.email,
        "posts": [p.title for p in user.posts],  # triggers query per user
    })

Better:

python 
from sqlalchemy.orm import selectinload
users = (
    session.query(User)
    .options(selectinload(User.posts))
    .all()
)

Then serialize, and no extra queries are created inside the loop.

4. Use Relationship Properties Instead of Foreign Keys in Code

Avoid code like:

python 
post = Post(user_id=user.id, title="Hi")

Prefer:

python 
post = Post(author=user, title="Hi")

Benefits:

5. Bidirectional Consistency

When you modify a relationship, the ORM keeps both sides in sync.

python 
user = User(email="c@example.com")
post = Post(title="Hello")
post.author = user
# Now user.posts contains post automatically
user.posts.remove(post)
# Now post.author is None

If you see inconsistencies between the two sides, something is wrong with how the relationship is configured.

6. Use Type Hints to Clarify Relationships

In SQLAlchemy 2.x you can use generics with Mapped and standard Python types:

python 
from typing import List
posts: Mapped[List["Post"]]
author: Mapped["User"]

This makes it clear which relationships return lists and which return single objects, and helps static analysis tools catch mistakes.

Summary

In this chapter you learned how to represent database relationships in an ORM:

Next chapters will build on this foundation and show how to perform queries, transactions, and more advanced patterns using these relationships.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3703 / 24h
Impressum / Imprint
Datenschutz / Data Policy