sqlalchemy-code-review

name	sqlalchemy-code-review
description	Reviews SQLAlchemy code for session management, relationships, N+1 queries, and migration patterns. Use when reviewing SQLAlchemy 2.0 code, checking session lifecycle, relationship() usage, or Alembic migrations.

SQLAlchemy Code Review

Quick Reference

Issue Type	Reference
Session lifecycle, context managers, async sessions	references/sessions.md
relationship(), lazy loading, N+1, joinedload	references/relationships.md
select() vs query(), ORM overhead, bulk ops	references/queries.md
Alembic patterns, reversible migrations, data migrations	references/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

Step	Action	Pass condition
1a	Open 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.
1b	If 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

Step	Action	Pass condition
2a	Identify the loop or repeated call site (ORM attribute access, execute in a loop).	file:line for the loop or hot path.
2b	If 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)

Step	Action	Pass condition
3a	Open the revision file (e.g. under versions/, or the project’s Alembic layout).	Repo-relative path + file:line for revision / upgrade / downgrade.
3b	If 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?