SQLAlchemy ORM 2.0
Declarative models, sessions, and relationships.
Recipe
Quick-reference recipe card - copy-paste ready.
class Author(Base):
__tablename__ = "authors"
id: Mapped[int] = mapped_column(primary_key=True)
books: Mapped[list["Book"]] = relationship(back_populates="author")When to reach for this:
- Application models
- Relationship navigation
- Unit of work pattern
Working Example
session.scalars(select(Author).options(selectinload(Author.books))).unique().all()What this demonstrates:
- relationship()
- selectinload
- 2.0 query style
Deep Dive
How It Works
- Session is unit of work for ORM objects.
- Lazy loads can cause N+1; eager load intentionally.
- Use typed Mapped annotations.
Gotchas
- Boundary validation skipped - Invalid data reaches persistence layers.. Fix: Validate with Pydantic or framework forms at the edge..
- Leaking stack traces - Clients see internal errors.. Fix: Map exceptions to stable HTTP responses..
- Blocking async event loops - Workers stall under concurrent load.. Fix: Use async drivers or threadpool wrappers..
- Secrets in source control - Credentials leak via git history.. Fix: Load secrets from env or a vault at runtime..
- Missing observability - Incidents are hard to debug.. Fix: Add structured logs, metrics, and request IDs..
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| Alternate framework in this cookbook | Team standard or existing monolith | Greenfield API with different constraints |
| Managed BaaS | CRUD-only MVP | Custom auth, workflows, or compliance needs |
| gRPC | Internal high-performance RPC | Public HTTP clients and browser access |
FAQs
When should I adopt SQLAlchemy ORM 2.0?
Use it when the patterns and trade-offs on this page match your API or data boundary.
What is the top production mistake with SQLAlchemy ORM 2.0?
Skipping validation, timeouts, or explicit error contracts at the HTTP edge.
How do I test SQLAlchemy ORM 2.0?
Use the framework test client, override dependencies, and assert status plus JSON shape.
Does SQLAlchemy ORM 2.0 work with Python 3.14?
Yes - examples target Python 3.14 with pinned framework versions from the stack footer.
How does SQLAlchemy ORM 2.0 relate to Pydantic 2?
Validate and serialize at boundaries; keep services working with typed domain objects.
Sync or async?
Prefer async routes when I/O dominates; keep CPU work small or offload to workers.
Where should business logic live?
Thin handlers; services own rules; repositories own queries.
How do I document APIs?
Publish OpenAPI or schema docs that match response models in code.
How do I handle versioning?
Explicit URL or header versioning with deprecation windows - avoid silent breaks.
What should I read next?
Follow the Related links for the next layer of depth in this section.
How do I stay secure?
Authenticate callers, authorize per resource, rate-limit, and never log secrets.
Performance first step?
Measure DB and upstream latency before swapping frameworks.
Related
- Databases Basics - Connections and transactions
- SQLAlchemy ORM 2.0 - ORM patterns
- Migrations with Alembic - Schema changes
- Async Database Access - asyncpg
Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.