review-orm-usage

Skill: Review ORM Usage

Purpose

Only atlibrary levelreviewORM usage patterns. Do not define scope (diff vs codebase) or perform security/architecture analysis; these are handled by scope and cognitive skills. Emit in standard formatresult listfor aggregation. Focus on N+1 query detection, connection management, migration safety, transaction handling, query efficiency, and model design across ORM libraries (Prisma, Entity Framework, SQLAlchemy, Sequelize, TypeORM, Hibernate, Django ORM, ActiveRecord, etc.).

Core Objective

Primary objective: Generate an ORM usage result list covering N+1 queries, connection management, migration safety, transaction handling, query efficiency, and model design for the given code scope.

Success criteria(all requirements must be met):

✅ ORM library-specific scope: Review only ORM usage patterns; no scope selection, security, or architecture analysis performed
✅ Covers all six ORM dimensions: N+1, connections, migrations, transactions, query efficiency, and model design (if relevant) are evaluated
✅ Result format compatible: Each result includes location, category (library-orm), severity, title, description, and optional recommendation
✅ File/model references: All findings reference a specific file:line or model/entity name
✅ ORM agnostic: Findings apply across ORM libraries; specific libraries are referenced only in context

AcceptanceTest: Does the output contain an ORM-centric result list with file/model references covering all relevant library dimensions, without performing security, architecture, or scope analysis?

Scope Boundaries

This skill is responsible for：

N+1 query detection (eager vs lazy loading, include/join patterns, batch loading, data loader patterns)
Connection management (pool configuration, connection leaks, timeout handling, connection reuse)
Migration safety (backward-compatible migrations, zero-downtime deployment, data vs schema migrations, rollback strategy)
Transaction handling (transaction scope, isolation levels, nested transactions, deadlock prevention)
Query efficiency (unnecessary SELECT *, missing indexes implied by query patterns, raw query fallbacks, query complexity)
Model design (appropriate relationships, cascade behavior, soft delete patterns, audit columns, index declarations)

This skill is not responsible for：

Scope selection — scope is provided by the caller
Security analysis (SQL injection, sensitive data exposure) — use "review-security"
Architecture analysis (module boundaries, coupling) — use "review-architecture"
Raw SQL quality (syntax, portability, parameterization) — use review-sql
General performance analysis (algorithmic complexity, I/O cost) — use review-performance
Full orchestrated review — use "review code"

Handoff point: After all ORM results are emitted, hand them to "orchestrate-code-review" for aggregation. For SQL injection risks (unsanitized raw queries), note them and recommend "review-security". For complex raw SQL quality, note it and recommend "review-sql".

Use Cases

Orchestrated review: when orchestrate-code-review runs the scope → language → framework → library → cognitive for a project using an ORM, use as a library step.
ORM-only review: when the user only wants to inspect ORM usage patterns in their data layer.
Pre-PR ORM checklist: ensure N+1 queries, transaction handling, and migration safety are correct before merging.
Migration review: focus on checking migration files for backward compatibility and rollback safety.

When to use: when the code under review uses an ORM library and the task includes library-level quality. The scope is determined by the caller or the user.

Behavior (Behavior)

Scope of this skill

Analyze：the given code scope(files or diff provided by the caller) for ORM usage patterns. Do not decide the scope; accept the code scope as input.
Do not: perform scope selection, security review, or architecture review; unless within scope, do not check ORM rules in non-ORM files.

Review Checklist (ORM Libraries Only)

N+1 query detection: identify lazy loading patterns that trigger N+1 queries; check eager loading (includes/joins), batch loading, or data loader patterns; flag loops that issue separate queries on each iteration.
Connection management: verify pool configuration (min/max, idle timeout); detect potential connection leaks (connections not returned, missing disposal/close); check timeout and retry settings; confirm connection reuse within request-scoped contexts.
Migration safety: assess backward compatibility (additive-only changes vs. breaking changes); check zero-downtime deployment readiness (no table locks on large tables, no NOT NULL without a default); verify data migrations are separate from schema migrations; confirm a rollback strategy exists.
Transaction handling: assess transaction scope (too broad or too narrow); check isolation level correctness; detect nested transaction abuse (savepoints vs. flat); flag potential deadlock patterns (inconsistent lock ordering, long-held locks).
Query efficiency: flag unnecessarySELECT *or over-fetched columns; identify query patterns that suggest missing indexes (unindexed WHERE/ORDER BY columns); assess the appropriateness of raw query fallbacks; evaluate query complexity (deep joins, subqueries in loops).
Model design: verify correct relationship declarations (one-to-many, many-to-many, polymorphic); check cascade behavior (unintended cascade deletes); review soft delete implementation; confirm expected audit columns (createdAt, updatedAt); check index declarations for frequently queried fields.

Tone and References

Professional and technical: reference specific locations (file:line or model/entity name). Emit findings containing location, category, severity, title, description, recommendation.

Input & Output (Input & Output)

Input (Input)

Code scope: A file or directory (or diff) containing ORM code (models, migrations, repositories, queries). Provided by the user or the scope skill.

Output

Emit zero or moreAppendix: Output Contractin the format defined inresults。
The category of this skill is library-orm。

Limits

Hard Boundaries

Do notperform scope selection, security, or architecture review. Maintain the ORM library usage patterns.
Do notgive conclusions without a concrete location or actionable recommendation.
Do notreview ORM-specific rules for non-ORM code unless explicitly in scope.
Do notduplicate raw SQL analysis that belongs to "review-sql"; only flag ORM-generated query issues.

Skill Boundaries

Do not do these(other skills can handle them):

Do not select or define code scope - scope is determined by the caller or "review-code"
Do not perform security analysis (SQL injection, data exposure) - use "review-security"
Do not perform architecture analysis (module boundaries, coupling) - use "review-architecture"
Do not perform raw SQL syntax or portability review - use review-sql
Do not perform general algorithm performance analysis - use "review-performance"

When to Stop and Hand Off：

After all ORM results are emitted, hand them off to "orchestrate-code-review" for aggregation
When SQL injection risks are found (e.g., unsanitized interpolation in raw queries), note them and recommend "review-security"
When raw SQL quality issues are found (syntax, portability), note them and recommend "review-sql"
When the user needs a comprehensive review (scope + language + cognitive), redirect to "review-code"

Self-Check

Core Success Criteria

ORM library scope only: Only ORM usage patterns are reviewed; scope selection, security, or architecture analysis is not performed
All six ORM dimensions covered: N+1, joins, migrations, transactions, query efficiency, and model design are evaluated (if relevant)
Result format compatible: Each result includes location, category (library-orm), severity, title, description, and optional recommendation
File/model references: All results reference a specific file:line or model/entity name
ORM-agnostic: Findings apply to ORM libraries in general; specific libraries are referenced only in context

Process Quality Checks

Were only ORM library dimensions reviewed (no scope/security/architecture)?
Were the relevant N+1, joins, migrations, transactions, query efficiency, and model design covered?
Does each published finding include location, category=library-orm, severity, title, description, and optional recommendation?
Are issues referenced by file:line or model/entity name?

Acceptance Tests

Does the output contain an ORM-centric findings list with file/model references covering all relevant library dimensions, without performing security, architecture, or scope analysis?

Examples (illustrative)

Example 1: N+1 queries in a loop

Input: A controller or service that fetches a list of orders, then iterates accessing "order.customer" without eager loading.
Expected: Emit a finding (major) of the N+1 query pattern; recommend eager loading via include/join (e.g., Prisma include、EF Include、SQLAlchemy joinedload、Hibernate @EntityGraph). Category=library-orm.

Example 2: Destructive migration without rollback

Input: A migration that drops a column or renames a table, without a corresponding down/rollback migration and without a data preservation step.
Expected: Emit a finding (critical) of a destructive migration without a rollback strategy; recommend the additive migration pattern (add new column → backfill → switch reads → drop old column). Category=library-orm.

Edge case: Raw query fallback in an ORM context

Input: A repository method that queries using raw SQL (prisma.$queryRaw、DbContext.Database.ExecuteSqlRaw、session.execute(text(...))), which could be expressed using the ORM query builder.
Expected: Emit a finding (suggestion) noting that the raw query bypasses ORM type safety and migration tracking; if the query is expressible, recommend using the ORM query builder. If the raw query is justified (performance, unsupported feature), accept it, but flag missing parameterization (if present) and recommend "review security" for injection risk. Category=library-orm.