FastAPI · Lesson 12 of 15
SQLAlchemy Relationships and Pagination
One-to-many models, eager loading and page/limit query params.
- Advanced
- 16 min read
- 3 objectives
Before this lessonLesson 11: Background Tasks and WebSockets
What you will learn
- Map a relationship
- Avoid N+1
- Return a page
Your Progress
0 of 15 lessons 0%
- Lessons0 / 15
- Completed0
- Est. time left~ 4 hours
Create a free account to keep your progress on every device.
Real schemas have relationships. Load them on purpose, and paginate anything that can grow.
One-to-many
from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import Mapped, mapped_column, relationship
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(120), unique=True)
posts: Mapped[list["Post"]] = relationship(back_populates="author")
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(200))
author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
author: Mapped[User] = relationship(back_populates="posts")Eager loading
from sqlalchemy.orm import selectinload
posts = db.scalars(
select(Post).options(selectinload(Post.author)).limit(20)
).all()Pagination
from fastapi import Query
@app.get("/posts")
def list_posts(page: int = Query(1, ge=1), size: int = Query(20, ge=1, le=100),
db: Session = Depends(get_db)):
q = select(Post).order_by(Post.id.desc())
total = db.scalar(select(func.count()).select_from(Post))
items = db.scalars(q.offset((page - 1) * size).limit(size)).all()
return {"total": total, "page": page, "size": size, "items": items}