N+1 Queries skill for Claude Code

Done well means: the request’s query count is measured before and after, it no longer grows with the number of rows returned, the chosen loading strategy does not explode row counts or memory, and a test asserts the query count so the regression cannot return.

Ground rules

• Count queries per request before changing anything. “Feels slow” is not a diagnosis; “page renders 1 + 2×N queries for N=50 orders” is.
• The fix is usually in the data-access layer (eager loading, batching), not in caching. Caching an N+1 hides it until the cache is cold.
• Eager-load only what the code path uses. Loading every association everywhere trades N+1 for giant joins and memory blowups.
• Turn lazy loading into an error in development and tests where the ORM supports it.
• Keep the query count independent of page size: the same endpoint with 10 or 100 rows must issue the same number of queries.

Workflow

- [ ] 1. Reproduce with realistic data (at least 20–50 parent rows)
- [ ] 2. Turn on query logging; count queries for one request
- [ ] 3. Find repeated statements differing only by an ID parameter
- [ ] 4. Locate the code that triggers each (template loop, serializer, resolver)
- [ ] 5. Pick the strategy (table below) and apply it
- [ ] 6. Re-count; check EXPLAIN and row counts of the new queries
- [ ] 7. Add a query-count assertion test

2–3. Detection by stack

Stack	Detect
Django	django-debug-toolbar SQL panel; connection.queries with DEBUG=True; nplusone package
Rails	Log shows repeated SELECT ... WHERE id = ?; Bullet gem; strict_loading raises
SQLAlchemy	create_engine(..., echo=True); lazy="raise" / raiseload("*")
Prisma	new PrismaClient({ log: ["query"] })
Drizzle	drizzle(client, { logger: true })
Hibernate / Spring Data JPA	spring.jpa.show-sql=true, Hibernate statistics (hibernate.generate_statistics=true)
EF Core	optionsBuilder.LogTo(Console.WriteLine); .ToQueryString()
Laravel	Model::preventLazyLoading(); Debugbar / Telescope query counts
Ecto	Logger shows queries; Ecto never lazy-loads, so N+1 appears as explicit Repo calls in loops
Any (production)	APM traces with span counts; pg_stat_statements with huge calls for a simple lookup

Signature in logs: the same SELECT ... FROM line_items WHERE order_id = $1 50 times in one request.

5. Choose the strategy

Situation	Strategy
To-one relation (order -> customer)	JOIN eager load (select_related, joinedload, eager_load, Include)
To-many relation (order -> line items)	Separate WHERE id IN (...) query (prefetch_related, selectinload, preload) — avoids row multiplication
Several to-many relations at once	Separate queries per relation (AsSplitQuery in EF Core); never a single join across multiple collections (cartesian product)
Only a count/sum per parent	Aggregate in SQL (annotate(Count), withCount, GROUP BY) or counter cache column
Only a few columns of the child	Projection/DTO query selecting just those columns
GraphQL / resolvers / any per-item function	DataLoader-style batching per request
Deep, repeated reads of rarely-changing data	Then consider caching, after batching

Fixes by ORM

Django:

orders = (Order.objects
          .select_related("customer")                                   # FK: JOIN
          .prefetch_related(Prefetch("items", queryset=LineItem.objects.select_related("product")))
          .annotate(item_count=Count("items"))
          .filter(status="open")[:50])

Rails:

orders = Order.where(status: "open").includes(:customer, items: :product).limit(50)
# preload: always separate queries; eager_load: always LEFT JOIN; includes: Rails decides
Order.strict_loading.find(id)                    # raises on lazy load; or config.active_record.strict_loading_by_default = true
customers = Customer.left_joins(:orders).group(:id).select("customers.*, COUNT(orders.id) AS orders_count")

SQLAlchemy 2.0:

from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload, raiseload

stmt = (select(Order)
        .options(joinedload(Order.customer), selectinload(Order.items).joinedload(LineItem.product), raiseload("*"))
        .where(Order.status == "open").limit(50))
orders = session.scalars(stmt).unique().all()

For async SQLAlchemy, lazy loading raises MissingGreenlet; eager loading is mandatory.

Prisma:

const orders = await prisma.order.findMany({
  where: { status: "open" },
  take: 50,
  include: { customer: true, items: { include: { product: true } } },
  // or select only needed fields: select: { id: true, customer: { select: { name: true } } }
});

Prisma issues one query per relation level by default; check its relationLoadStrategy option (join vs. query) in your version’s docs. The classic Prisma N+1 is calling findUnique inside Promise.all(orders.map(...)) — replace with one findMany({ where: { id: { in: ids } } }).

Hibernate / JPA:

@Query("select o from Order o join fetch o.customer where o.status = :status")
List<Order> findOpenWithCustomer(@Param("status") String status);

@EntityGraph(attributePaths = {"customer", "items"})
List<Order> findByStatus(String status);

Set hibernate.default_batch_fetch_size (e.g., 50) so remaining lazy collections load in batches of IN queries. join fetch on a collection with pagination triggers in-memory pagination (Hibernate logs a warning that it is applying limits in memory); paginate IDs first, then fetch by IDs.

EF Core: Include(o => o.Customer), ThenInclude, AsSplitQuery() for multiple collections, or Select into a DTO (often best: one query, only needed columns).

Laravel: Order::with(['customer', 'items.product'])->withCount('items')->get();

GraphQL DataLoader (Node):

import DataLoader from "dataloader";

export function createLoaders(db: Db) {           // one set of loaders per request
  return {
    customerById: new DataLoader<string, Customer | null>(async (ids) => {
      const rows = await db.customer.findMany({ where: { id: { in: [...ids] } } });
      const byId = new Map(rows.map((r) => [r.id, r]));
      return ids.map((id) => byId.get(id) ?? null);  // same order and length as ids
    }),
  };
}
// resolver: Order.customer = (order, _args, ctx) => ctx.loaders.customerById.load(order.customerId)

Create loaders per request (never global: they cache and would leak data across users).

6. Check the new queries

• EXPLAIN (ANALYZE, BUFFERS) the IN query: it needs an index on the foreign key (line_items.order_id). Many ORMs do not create FK indexes automatically (Postgres never does).
• Watch row counts on JOIN strategies: a join across two collections of 20 each returns 400 rows per parent.
• Very large IN lists (tens of thousands of IDs) should be chunked or replaced with a join on a subquery.

7. Lock it in with a test

# Django
def test_order_list_query_count(client, django_assert_max_num_queries, make_orders):
    make_orders(30)
    with django_assert_max_num_queries(5):
        client.get("/api/orders/")

# Rails (RSpec), with a small helper around ActiveSupport::Notifications
it "does not N+1" do
  create_list(:order, 30, :with_items)
  expect { get "/orders" }.to make_database_queries(count: 4)   # db-query-matchers gem
end

Generic approach in any stack: subscribe to the ORM’s query event, count during the request, assert a constant bound with two different data sizes (e.g., 5 and 50 rows).

Gotchas

• Serializers and templates trigger lazy loads far from the query code; search the view layer ({{ order.customer.name }}, order.items.length in JSON serializers).
• .count on an association in Rails issues a query each time; use .size after preloading, or withCount/counter caches.
• Calling .filter()/.where() on a prefetched relation in Django or Rails issues a new query and ignores the prefetch; use Prefetch(queryset=...) or filter in memory.
• Eager loading inside a loop (for o in orders: o.items.all().prefetch_related(...)) does nothing useful.
• Promise.all over per-item queries is still N+1; it is just concurrent N+1 that exhausts the connection pool faster.
• Background jobs and exports have N+1s too, often worse (10k rows); batch with find_each/iterator(chunk_size=...) plus eager loading.

Verification

1. Query count for the request is constant across two data sizes and matches the expected number (1 per eager-loaded relation level plus the base query).
2. Response time at realistic N improved; new queries use indexes (EXPLAIN).
3. Query-count test passes and fails if the eager load is removed.
4. Response payload unchanged (snapshot or contract test).

Report template

**Endpoint:** GET /api/orders?status=open (50 orders)
**Before:** 152 queries (1 + 50 customers + 50 items + 51 product lookups), 1.9 s
**Cause:** OrderSerializer reads order.customer and item.product lazily
**Fix:** select_related("customer") + Prefetch items with select_related("product"); index on line_items(order_id) added
**After:** 3 queries, 140 ms; constant for 10 and 200 orders
**Guard:** test_order_list_query_count asserts <= 5 queries