Module 3 · Postgres & Data · Drills
Reading the deep-dive is not the same as building it. Type every model, query, and dependency yourself against a real Postgres before you reveal the solution. Effortful recall is the point.
Mapped / mapped_column / select() — never the 1.x Column(...) form). Attempt it first, run it, then click “Show solution” to compare. If yours works differently but correctly — great, that's fluency. Tick each box as you go; your progress is saved in this browser.
Drill 1 model
Define a Document model with an auto-increment id, a required title (max 200 chars), and a required body. Use 2.0 Mapped / mapped_column style.
from sqlalchemy import String from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column class Base(DeclarativeBase): pass class Document(Base): __tablename__ = "documents" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] = mapped_column(String(200)) body: Mapped[str]
A bare Mapped[str] still becomes a NOT NULL column — the annotation alone is enough. Use Mapped[str | None] when you want it nullable.
Drill 2 relationship
Add a User with many Documents. Give Document an owner_id ForeignKey to users.id, and wire both sides with relationship() + back_populates.
from sqlalchemy import String, ForeignKey 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(255), unique=True) documents: Mapped[list["Document"]] = relationship( back_populates="owner" ) class Document(Base): __tablename__ = "documents" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] = mapped_column(String(200)) owner_id: Mapped[int] = mapped_column(ForeignKey("users.id")) owner: Mapped["User"] = relationship(back_populates="documents")
The ForeignKey creates the column; the relationship() creates the convenient Python attribute. You need both.
Drill 3 select
Write a select() that fetches every Document whose owner_id is 1, ordered by created_at newest first, returned as a clean list of objects.
from sqlalchemy import select stmt = ( select(Document) .where(Document.owner_id == 1) .order_by(Document.created_at.desc()) ) docs = session.scalars(stmt).all() # list[Document]
scalars(...).all() unwraps each row to the single ORM object. Without it you'd get rows of one-element tuples.
Drill 4 insert
Insert a new Document (title="Q2 Report", body="...", owner_id=1), commit it, and print its database-assigned id.
doc = Document(title="Q2 Report", body="...", owner_id=1) session.add(doc) session.commit() session.refresh(doc) # pull back the generated id print(doc.id)
Before commit() the id is None. refresh() re-reads the row so server-generated values land on the object.
Drill 5 FastAPI dep
Write a get_db dependency that yields a session from SessionLocal and always closes it, even if the route raises.
from collections.abc import Generator from sqlalchemy.orm import Session def get_db() -> Generator[Session]: db = SessionLocal() try: yield db finally: db.close()
Use it in a route with db: Session = Depends(get_db). The finally guarantees cleanup — that's why we yield rather than return.
Drill 6 update
Load the document with id == 5, change its title to "Final", and persist the change. Write no UPDATE SQL yourself.
doc = session.scalars(
select(Document).where(Document.id == 5)
).first()
doc.title = "Final" # just set the attribute
session.commit() # dirty tracking issues the UPDATE
The session tracks the changed object and emits the SQL on commit. Mutate-then-commit is the whole pattern.
docs_for_user(session, user_id) that returns all documents for a given user, newest first — using the model relationship and a query. This is the exact shape of the "my documents" endpoint you'll ship in DocChat.
Build · newest-first by user
Two valid approaches — by query, or via the relationship. Try the query version first (it scales and sorts in the database).
from sqlalchemy import select def docs_for_user(session, user_id: int) -> list[Document]: stmt = ( select(Document) .where(Document.owner_id == user_id) .order_by(Document.created_at.desc()) ) return session.scalars(stmt).all() # Eager-load to dodge N+1 when you also need each owner: from sqlalchemy.orm import selectinload user = session.scalars( select(User) .where(User.id == user_id) .options(selectinload(User.documents)) ).first() newest_first = sorted( user.documents, key=lambda d: d.created_at, reverse=True )
Prefer the query version: order_by sorts in Postgres, and selectinload avoids the N+1 storm that plain user.documents can trigger across many users.
Click a card to flip it. Say the answer out loud before you flip — that's the rep that builds storage strength.
id: Mapped[int] = mapped_column(primary_key=True)add, query, and commit through it, one per request.select() vs raw SQL?select(Model) builds a typed, injection-safe query in Python; raw SQL is a hand-written string.relationship() vs ForeignKey?ForeignKey makes the column; relationship() makes the Python navigation attribute.upgrade head) from model changes.Tick each only if you can do it without looking:
Mapped / mapped_column in 2.0 styleForeignKey + relationship + back_populatesselect().where(...).order_by(...) and read it with scalars().all()get_db dependency and run alembic upgrade head