# No N+1 query patterns

- **Pattern:** `ab-000903` (`database-design-operations.query-patterns.no-n-plus-one`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000903
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000903)

## Why it matters

N+1 queries are the most common performance failure in AI-generated API code. A posts listing endpoint that fetches 50 posts and then runs 50 individual user lookups executes 51 database round-trips instead of 1. At low traffic this is invisible; at 100 concurrent users, each triggering a 51-query path, the connection pool saturates and requests queue. The pattern emerges because AI coding tools write `posts.map(p => getUser(p.userId))` idiomatically in JavaScript without recognizing the database implications. ISO 25010 time-behaviour captures the direct impact: response latency scales linearly with list size rather than being constant.

## Severity rationale

Critical because N+1 patterns multiply database round-trips with list size, saturating connection pools under moderate load and causing cascading timeouts.

## Remediation

Replace per-record queries inside loops with Prisma `include`, SQL `JOIN`, or batch `WHERE id IN (...)` lookups.

```ts
// BEFORE — N+1: one user query per post
const posts = await prisma.post.findMany()
const withAuthors = await Promise.all(
  posts.map(p => prisma.user.findUnique({ where: { id: p.userId } }))
)

// AFTER — eager load: 2 queries total
const posts = await prisma.post.findMany({
  select: {
    id: true, title: true, createdAt: true,
    author: { select: { id: true, name: true } }
  }
})
```

```sql
-- AFTER — single JOIN instead of N lookups
SELECT p.id, p.title, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 50;
```

## Detection

- **ID:** `no-n-plus-one`
- **Severity:** `critical`
- **What to look for:** Read the application's data fetching code — API route handlers, server components, service layer files, and repository classes. Look for: (1) A `findMany`/`SELECT *` call that retrieves a list of records, followed by a loop (`.map`, `.forEach`, `for...of`) that runs a `findUnique`/`findFirst`/`SELECT` per record. This is the classic N+1 pattern — 1 query for the list plus N queries for each related item. (2) In Prisma, look for `include` or `select` to confirm related data is loaded eagerly. A `findMany` with no `include` on a model that has relations, followed by accessing those relations in a loop, is an N+1. (3) In raw SQL, look for `SELECT` statements inside loops. (4) In GraphQL resolvers, look for resolvers that query the database without a DataLoader. (5) In ORMs with lazy-loading (TypeORM, Sequelize), accessing relation properties triggers a query — check whether relations are eagerly loaded for bulk operations.
- **Pass criteria:** Related data is fetched using eager loading — Prisma `include`/`select` with nested relations, SQL `JOIN`, Drizzle `with`, or batch queries (`WHERE id IN (...)`). No database query executes inside a loop over a list of records. Count every data-fetching endpoint and verify at least 90% use eager loading or batch queries.
- **Cross-reference:** For connection pooling configuration that mitigates N+1 impact, see the `connection-pooling` check in the Query Patterns category.
- **Fail criteria:** A query for a list of records is followed by per-record queries in a loop. `findUnique` or `SELECT` called N times for N records. TypeORM/Sequelize relation accessed in a loop without eager loading.
- **Skip (N/A) when:** Application has no data listing or collection endpoints — only single-record lookups (extremely rare).
- **Detail on fail:** Specify where the N+1 pattern is found. Example: `"In src/app/api/posts/route.ts: posts fetched with findMany, then for each post, author fetched with findUnique(user.id) — N+1 pattern."` or `"In components/PostList.tsx: posts.map(post => getCommentCount(post.id)) executes one DB query per post."`.
- **Remediation:** Use eager loading to fetch related data in one query:

  ```ts
  // BEFORE — N+1 pattern (one query per post for the author)
  const posts = await prisma.post.findMany()
  const postsWithAuthors = await Promise.all(
    posts.map(post => prisma.user.findUnique({ where: { id: post.userId } }))
  )

  // AFTER — eager loading with include (2 queries total: posts + batch of users)
  const posts = await prisma.post.findMany({
    include: { author: true }
  })

  // AFTER — with select for performance (only get needed fields)
  const posts = await prisma.post.findMany({
    select: {
      id: true,
      title: true,
      createdAt: true,
      author: {
        select: { id: true, name: true, avatarUrl: true }
      }
    }
  })
  ```

  ```sql
  -- BEFORE — N+1 in raw SQL (separate query per post)
  -- for each postId: SELECT * FROM users WHERE id = $1

  -- AFTER — JOIN to get everything in one query
  SELECT
    p.id, p.title, p.created_at,
    u.id as author_id, u.name as author_name
  FROM posts p
  JOIN users u ON u.id = p.user_id
  WHERE p.status = 'published'
  ORDER BY p.created_at DESC
  LIMIT 20;
  ```

  For cases where a join is impractical (e.g., fetching counts for many items), use batch queries:
  ```ts
  // Batch lookup — 1 query total
  const userIds = posts.map(p => p.userId)
  const users = await prisma.user.findMany({
    where: { id: { in: userIds } }
  })
  const userMap = new Map(users.map(u => [u.id, u]))
  ```

---

## External references

- iso-25010 performance-efficiency.time-behaviour

Taxons: performance

HTML version: https://auditbuffet.com/patterns/ab-000903