No N+1 query patterns

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.

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

// 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 } }
  }
})

-- 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;

• ID: database-design-operations.query-patterns.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:

  // 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 }
      }
    }
  })
  

  -- 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:

  // 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]))