Skip to content

skillDatabases

ORM Query Review

Reviews ORM code for N+1 queries, lazy loading, over-fetching and missing transactions, and proves each finding by counting queries.

npx workspacetemplates add orm-query-review --tool claude-code

Writes 1 file to .claude/skills/orm-query-review/

.claude/skills/orm-query-review/SKILL.md28 lines · 1.7 KBFile
---
name: orm-query-review
description: "Reviews ORM code for N+1 queries, lazy loading, over-fetching and missing transactions, and proves each finding by counting queries."
---

# ORM query review

Show the SQL the ORM really sends. Judge the code by statements per request, not by how it reads.

## Steps

1. Turn on query logging for the framework in use (Django `connection.queries`, Rails log, Hibernate `show_sql`, SQLAlchemy `echo`, Prisma `log: ['query']`). Run the target request or test and count statements.
2. Find N+1 patterns: a loop that touches a relation, a serializer or template that reads a related field, a property that queries on access. Confirm by seeing one parent query followed by N near-identical queries.
3. Fix with the ORM's eager loading: join or prefetch the relation in the one query that loads the parents. Use batch loading when a join would multiply rows.
4. Find over-fetching: whole rows loaded to read two fields, or entire tables loaded to count or check existence. Select only needed columns; use `count`, `exists` and aggregates in the database, not in memory.
5. Find missing transactions: two or more writes that must succeed together, read-then-write sequences, counters updated in memory. Wrap them in a transaction, and lock the row (`select for update`) or use an atomic update expression when two requests could collide.
6. Look for unbounded queries without limit or pagination, and filters on columns with no index.
7. Re-run and show the statement count before and after.

## Rules

- Never fix a query you have not seen the SQL for.
- Keep fixes local; do not rewrite the data layer.
- Keep transactions short and free of network calls.

## Report

Per finding: file and line, statements before and after, and the change.

What it does

ORMs hide the SQL, which is how a list page ends up running 400 queries. This skill makes the agent surface the real statements, count them and trace each repeated one back to a line of code.

Fixes stay small: eager loading in the right place, selecting only needed columns, and wrapping related writes in one transaction.

Example

you › The /projects page takes 3 seconds, check the queries

agent › The page ran 214 queries. ProjectSerializer read owner.name per row (N+1): fixed with a select_related on owner, now 3 queries. Also replaced len(project.tasks.all()) with a COUNT, and wrapped archive-and-notify in one transaction. 214 queries down to 5, 3.0 s to 160 ms.

Questions

Is eager loading always better?
No. Joining a one-to-many relation multiplies rows and can be slower than a second batched query. Measure both.
Should I drop the ORM for raw SQL?
Rarely. Keep the ORM for ordinary work and write raw SQL for the few reports where the generated query cannot be shaped properly.
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free
  • Database Schema Design

    SkillDatabases

    Free
  • MongoDB Schema Modeling

    Skillmongodb

    Free