FastAPI Table Relationships

Real data rarely lives in one table. An author writes many books. A student joins many classes, and each class holds many students. Relationships link tables so the database can follow those connections. SQLAlchemy lets you describe the links in Python and read related records as simple attributes. Picture a school register: each student card carries a class number that points to the class sheet.

Three Relationship Types

One-to-Many     One author  ───▶  many books
Many-to-Many    many students  ◀──▶  many classes
One-to-One      one user  ───▶  one profile

One-to-many appears most often in everyday applications.

Foreign Keys

A foreign key is a column that stores the ID of a record in another table.

authors                      books
┌────┬──────────┐            ┌────┬─────────────┬───────────┐
│ id │ name     │            │ id │ title       │ author_id │
├────┼──────────┤            ├────┼─────────────┼───────────┤
│ 1  │ Meera    │◀───────────│ 1  │ River Tales │ 1        │
│ 2  │ Dev      │◀──┐        │ 2  │ Night Sky   │ 1        │
└────┴──────────┘   └────────│ 3  │ Iron Road   │ 2         │
                             └────┴─────────────┴───────────┘

The author_id column in the books table points back to an author row.

One-to-Many in SQLAlchemy

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]
    books: Mapped[list["Book"]] = relationship(back_populates="author")

class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped["Author"] = relationship(back_populates="books")

The back_populates setting connects both sides. Reading author.books returns every book. Reading book.author returns the writer.

Using the Relationship

meera = Author(name="Meera")
meera.books.append(Book(title="River Tales"))
meera.books.append(Book(title="Night Sky"))

db.add(meera)
db.commit()

print([b.title for b in meera.books])   # ['River Tales', 'Night Sky']

Adding books to the list sets the author_id value automatically.

Many-to-Many with an Association Table

Students and classes need a middle table that pairs their IDs.

from sqlalchemy import Column, Table

enrollment = Table(
    "enrollment",
    Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("class_id", ForeignKey("classes.id"), primary_key=True),
)

class Student(Base):
    __tablename__ = "students"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    classes: Mapped[list["SchoolClass"]] = relationship(
        secondary=enrollment, back_populates="students"
    )

class SchoolClass(Base):
    __tablename__ = "classes"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    students: Mapped[list["Student"]] = relationship(
        secondary=enrollment, back_populates="classes"
    )

students      enrollment                classes
┌────┬─────┐  ┌────────────┬──────────┐  ┌────┬─────────┐
│ 1  │ Ana │  │ student_id │ class_id │  │ 10 │ Math    │
│ 2  │ Raj │  ├────────────┼──────────┤  │ 20 │ Science │
└────┴─────┘  │ 1          │ 10       │  └────┴─────────┘
              │ 1          │ 20       │
              │ 2          │ 10       │
              └────────────┴──────────┘

Ana joins Math and Science. Raj joins Math only.

One-to-One

Add uselist=False to the relationship, and place a unique foreign key on the child table.

profile: Mapped["Profile"] = relationship(back_populates="user", uselist=False)

Returning Related Data from Routes

Response models read from database objects when you turn on from_attributes.

from pydantic import BaseModel, ConfigDict

class BookOut(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    title: str

class AuthorOut(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str
    books: list[BookOut] = []

@app.get("/authors/{author_id}", response_model=AuthorOut)
def read_author(author_id: int, db: Session = Depends(get_db)):
    return db.get(Author, author_id)

The N+1 Query Problem

Loading ten authors and each author's books fires eleven queries: one for the authors and ten more for the books. Eager loading fixes this by fetching related rows together.

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = select(Author).options(selectinload(Author.books))
authors = db.scalars(stmt).all()   # 2 queries in total

A Complete Example Walkthrough

Picture a small library system built with the models above. A librarian adds an author named Meera and two books by her. The code creates one author row and two book rows. SQLAlchemy fills the author_id column in both book rows with the new author's ID.

A visitor requests the author's page. The route loads the author with selectinload, so the database answers with two queries: one for the author and one for all books linked to that author. The response model converts the objects into JSON that contains the author name and a list of book titles.

Deleting an author raises a design question. The books that point to the author would lose their parent. Choose a rule up front. A cascade setting deletes the books together with the author. Another option blocks the deletion until the books move to a different author. Write the rule into the relationship so the database never holds orphaned rows.

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

The cascade setting removes the books together with their author. Pick the behavior that matches your business rules, and test it with a few sample records before real data arrives.

Naming matters for readability. Use plural names for tables, singular names for models, and clear names for relationship attributes. A teammate who reads author.books understands the link immediately, while a vague name such as items forces a trip to the model file.

Key Points

• Foreign keys link rows across tables.
• relationship() exposes linked rows as Python attributes.
• Many-to-many links need an association table.
• from_attributes lets response models read database objects.
• selectinload prevents the N+1 query problem.