Relationships, Loading Strategies & Migrations

Avoiding N+1 Queries

from sqlalchemy.orm import joinedload, selectinload

# N+1 problem: 1 query for authors, then N more — one per author —
# when .posts is lazily accessed inside the loop
for author in session.query(Author).all():
    print(author.posts)  # separate query EVERY iteration

# joinedload — one query, via a JOIN — good for one-to-one/many-to-one
authors = session.query(Author).options(joinedload(Author.posts)).all()

# selectinload — one extra query total (not per-row) — often better for
# one-to-many with many related rows, avoids a huge JOIN result set
authors = session.query(Author).options(selectinload(Author.posts)).all()

Identity Map & Detached Objects

Within one session, querying the same row twice returns the SAME Python object instance — mutating it in one place is visible everywhere that object is referenced in that session. Accessing a lazy relationship attribute AFTER the originating session has closed raises DetachedInstanceError, since SQLAlchemy has no active connection left to run the lazy-load query. Fix: eagerly load what you'll need before the session closes, or keep the session open while accessing lazy attributes.

Alembic Migrations

alembic init migrations
alembic revision --autogenerate -m "add email column"   # diffs models vs DB
alembic upgrade head                                     # apply pending migrations
alembic downgrade -1                                     # roll back one revision

# create_all() is fine for quick scripts/tests, but real production schema
# changes over time should go through Alembic, not repeated create_all() calls.

Connection Pooling

engine = create_engine(
    'postgresql://user:pass@localhost/mydb',
    pool_size=10,       # base number of persistent connections
    max_overflow=20,    # extra connections allowed under load
)
# Too small a pool bottlenecks concurrent throughput; too large can
# overwhelm the database server's own connection limit.