1. Declarative models and mapped columns
Start with the contract: make inputs, outputs and failure behavior explicit before adding infrastructure. This keeps the feature easy to reason about and gives tests a stable boundary.
2. Session lifecycle as a dependency
Apply the pattern through a small vertical slice. Keep framework wiring at the edge and business decisions in focused functions or services that can be tested without starting the whole application.
from sqlalchemy import select
from sqlalchemy.orm import Mapped, mapped_column
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
def find_task(session, task_id: int):
return session.scalar(select(Task).where(Task.id == task_id))3. Select, insert, update and transactions
Treat failure paths as part of the design. Add bounded resource usage, meaningful errors and a verification step so the behavior remains dependable under real production conditions.
4. Hands-on Exercise
Build the feature
Connect the tasks API to PostgreSQL, implement a per-request session dependency, and replace every in-memory operation with SQLAlchemy 2.0 queries.
Definition of done
- The happy path works through the real HTTP boundary.
- At least one failure path is handled and tested.
- Configuration and secrets stay outside source code.
- The README explains how to run and verify the result.
5. Knowledge Check
Why should one request usually use one short-lived Session?
Show answer
It gives the request a clear transaction boundary and guarantees connections are returned to the pool.