Warming up the neural circuits...
By the end of this chapter you will:
Most backend bugs are data bugs: wrong joins, unexpected nulls, stale reads, duplicate writes, and mistakes.
Raw gives precision but can become repetitive. ORMs can speed development but become dangerous if used like magic.
SQLAlchemy 2.0 is most powerful when treated as a SQL toolkit with explicit models and explicit query shapes.
The goal of this chapter is not "hide SQL." The goal is:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "postgresql+psycopg://app_user:password@localhost:5432/app_db"
engine = create_engine(
DATABASE_URL,
pool_pre_ping=True,
future=True,
)
SessionLocal = sessionmaker(
bind=engine,
autoflush=False,
autocommit=False,
expire_on_commit=False,
)Engine manages DB connections. Session is your unit-of-work boundary per request.
from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id:
from sqlalchemy import select
from sqlalchemy.orm import Session
def get_user_by_email(db: Session, email: str):
stmt = select(User).where(User.email == email)
return db.execute
from sqlalchemy import select
from sqlalchemy.orm import selectinload
def list_users_with_posts(db: Session):
stmt = select(User).options(selectinload(User.posts))
return db.execute(stmt).scalars().from sqlalchemy.exc import IntegrityError
def create_user(db: Session, email: str, name: str) -> User:
user = User(email=email, name=name)
from collections.abc import Generator
from fastapi import Depends, FastAPI
app = FastAPI()
def get_db() -> Generator[Session, None, None]:
db = SessionLocal()
try:
yield db
finally:
db
If you cannot mentally predict the SQL generated by your query, your abstraction is too opaque for production debugging.
| Situation | Better default | Why |
|---|---|---|
| Service startup | Single engine, pooled connections | Efficient and observable |
| Request handling | One session per request | Clean transaction boundaries |
| API output | Map to response schema, not raw ORM object | Prevent accidental field leakage |
| Lists with child collections | Eager loading (selectinload) | Avoid N+1 query storms |
| Complex reporting SQL | Use explicit SQL/text or optimized statement | Clarity over ORM gymnastics |
| Mistake | Why it hurts | Better move |
|---|---|---|
| Global long-lived Session reuse | Transaction contamination and stale | Request-scoped session lifecycle |
| Returning lazy-loaded objects directly | Surprise queries during | Preload relationships intentionally |
| Blind autogenerate trust | Schema drift and missing constraints | Review SQL intent before migrate |
| Catching write errors without rollback | Session left in failed transaction state | Always rollback on write failure |
| Ignoring indexes on filter columns | Slow queries at scale | fields used in where/join/order |
PostgreSQL DATABASE_URLReusable engine plus request-scoped session factoryusers and posts tablesMapped models with relationship and ForeignKeyemail stringUser object or NoneEndpoint that loops users and accesses postsStable query count regardless of list sizeUnique email constraintRollback and meaningful API-level conflict responseBeginner:
"What is the difference between Engine and Session in SQLAlchemy?"
Engine manages database connections and dialect behavior. Session manages unit-of-work state and transaction flow for your operations.
"Why is one session per request a common API pattern?"
It gives a clean transaction boundary and avoids cross-request state contamination.
Senior:
"How do you systematically detect and fix N+1 issues?"
Instrument query count and latency, identify hot endpoints, then apply loading strategy changes and verify query count reduction.
"When would you bypass ORM convenience and write explicit SQL?"
For complex reports or performance-critical paths where explicit SQL shape and planner behavior matter more than abstraction convenience.
Engine = connections and dialect
Session = unit of work per request
Mapped[] = typed ORM contract
select() + explicit loading strategy = predictable SQL
rollback after failed writes before continuingWhat is the most common safe session lifecycle pattern in FastAPI APIs?
Typed mappings improve editor support and reduce model drift.
Prefer explicit statement objects over hidden query builder chains.
Lazy loading can silently trigger extra queries in loops. Choose loading strategy intentionally.
Always rollback after failed writes before reusing session in the same request.
One session per request is a strong default for API services.