SQLAlchemy Code Review#

Quick Reference#

Issue TypeReferenceSession lifecycle, context managers, async sessionsreferences/sessions.mdrelationship(), lazy loading, N+1, joinedloadreferences/relationships.mdselect() vs query(), ORM overhead, bulk opsreferences/queries.mdAlembic patterns, reversible migrations, data migrationsreferences/migrations.md

Review Checklist#

• Sessions use context managers ( with, async with)
• No session sharing across requests or threads
• Sessions closed/cleaned up properly
• relationship() uses appropriate lazy strategy
• Explicit joinedload/ selectinload to avoid N+1
• No lazy loading in loops (N+1 queries)
• Using SQLAlchemy 2.0 select() syntax, not legacy query()
• Bulk operations use bulk_insert/bulk_update, not ORM loops
• Async sessions use proper async context managers
• Migrations are reversible with downgrade()
• Data migrations use op.execute() not ORM models
• Migration dependencies properly ordered

Gates (SQLAlchemy-specific)#

Run once per SQLAlchemy-related finding, after you can anchor file:line (see review-verification-protocol) and before the finding ships. If a step’s pass condition is not met, do not assert the finding as written—gather evidence, withdraw, downgrade severity, or rephrase as a question.

Gate 1 — Session scope and lifecycle#

StepActionPass condition1aOpen the module where the session is created or injected (not from memory).file:line for Session, sessionmaker, async_session, or the factory/ Depends() that yields a session.1bIf claiming leak, cross-request sharing, or missing cleanup: trace the session’s scope (context manager, try/ finally, middleware).Scoped region cited with a file:line range, or withdraw if scope is correct after the read.

Gate 2 — N+1, lazy loading, eager loads#

StepActionPass condition2aIdentify the loop or repeated call site (ORM attribute access, execute in a loop).file:line for the loop or hot path.2bIf claiming N+1: name the relationship or query pattern emitted per iteration.Relationship or per-iteration SQL pattern with file:line, or rephrase as a question if unclear.

Gate 3 — Migrations (Alembic)#

StepActionPass condition3aOpen the revision file (e.g. under versions/, or the project’s Alembic layout).Repo-relative path + file:line for revision / upgrade / downgrade.3bIf claiming broken downgrade() or risky data migration: point at the op.* / op.execute() involved.Snippet or line range in that file for each claimed op, or withdraw.

When to Load References#

• Reviewing session creation/cleanup → sessions.md
• Reviewing model relationships → relationships.md
• Reviewing database queries → queries.md
• Reviewing Alembic migration files → migrations.md

Review Questions#

1. Are all sessions properly managed with context managers?
2. Are relationships configured to avoid N+1 queries?
3. Are queries using SQLAlchemy 2.0 select() syntax?
4. Are all migrations reversible and properly tested?