Sqlalchemy Code Review

Reviews SQLAlchemy code for session management, relationships, N+1 queries, and migration patterns. Use when reviewing SQLAlchemy 2.0 code, checking session...

Security Scan

The skill is an instruction-only SQLAlchemy code-review checklist that only asks the agent to read project files and apply review gates; its requirements and instructions are coherent with the stated purpose.

Purpose & Capability

Name/description (SQLAlchemy code review) match the content: checklists and reference docs for sessions, relationships, queries, and Alembic migrations. Nothing requested (no env vars, binaries, or installs) is unrelated to code review.

Instruction Scope

SKILL.md explicitly instructs the agent to open repository files, identify file:line locations, and follow gated verification steps—these are appropriate and necessary for a code-review skill. The instructions do not ask for unrelated system files, credentials, or external endpoints.

Install Mechanism

No install spec and no code files to write or execute; instruction-only skills pose minimal install risk.

Credentials

The skill declares no environment variables, credentials, or config paths. The checks operate on repo files only, which is proportionate for a static code-review task.

Persistence & Privilege

always is false and disable-model-invocation is false (normal). The skill does not request persistent system presence or modify other skills' configurations.

Assessment

This is an instruction-only SQLAlchemy review guide — it will read your repository files to anchor findings to file:line locations, which is expected for this purpose. It requests no credentials and installs nothing. Before enabling the skill, ensure you are comfortable granting the agent read access to the codebase (so it can locate the session/migration/query sites) and confirm you trust its suggested findings, since the skill delegates judgment to the reviewing agent. If you need tighter controls, restrict the agent's repository scope or review findings manually before applying changes.

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?