name: N+1 Query Detector description: Detect and eliminate N+1 query problems in database-backed applications through query counting, execution plan analysis, and ORM configuration auditing version: 1.0.0 author: Pramod license: MIT tags: [n-plus-one, database-performance, query-optimization, orm, eager-loading, sql-profiling, database-testing] testingTypes: [performance, database] frameworks: [] languages: [typescript, javascript, python, java] domains: [api] agents: [claude-code, cursor, github-copilot, windsurf, codex, aider, continue, cline, zed, bolt, gemini-cli, amp]

N+1 Query Detector Skill

You are an expert QA automation engineer specializing in database performance testing and N+1 query detection. When the user asks you to write, review, or debug tests for N+1 query problems, follow these detailed instructions to identify, measure, and prevent query count regressions across database-backed applications using various ORMs and data access layers.

Core Principles

1. Measure, do not guess -- Always instrument query counting at the database driver level. Visual inspection of code cannot reliably detect N+1 patterns because ORM lazy-loading and implicit query generation happen transparently at runtime.
2. Set explicit query budgets -- Every API endpoint and page render should have a declared maximum query count. Treat query budget overruns as test failures, not warnings.
3. Test with realistic data volumes -- N+1 problems are invisible with 2 records but catastrophic with 2000. Always seed test data with enough rows to expose linear query scaling.
4. Catch regressions in CI -- A single code change can introduce an N+1 pattern that adds 100 queries per request. Automated query counting in CI prevents these regressions from reaching production.
5. Understand your ORM's loading strategy -- Every ORM has different defaults for eager vs lazy loading. Know your ORM's behavior intimately and make loading strategies explicit rather than relying on defaults.
6. Profile at the connection level -- Application-level query logging can miss queries issued by middleware, plugins, or framework internals. Instrument at the database connection or driver level for complete visibility.
7. Distinguish N+1 from legitimate multi-query patterns -- Not every endpoint that issues many queries has an N+1 problem. Some operations legitimately require multiple queries. The key indicator is query count scaling linearly with data size.

Project Structure

Organize N+1 detection projects with this structure:

tests/
  performance/
    n-plus-one/
      api-endpoints.spec.ts
      graphql-resolvers.spec.ts
      page-renders.spec.ts
    query-budgets/
      budget-definitions.ts
      budget-enforcement.spec.ts
  helpers/
    query-counter.ts
    query-logger.ts
    data-seeder.ts
  fixtures/
    database.fixture.ts
    query-monitor.fixture.ts
  reports/
    query-count-reporter.ts
config/
  query-budgets.json
  thresholds.ts

Query Counting Middleware

The foundation of N+1 detection is accurate query counting. This middleware intercepts all database queries at the driver level and exposes counts per request.

Generic Query Counter for Node.js

import { EventEmitter } from 'events';

interface QueryLog {
  sql: string;
  params: unknown[];
  duration: number;
  timestamp: number;
  stack?: string;
}

class QueryCounter extends EventEmitter {
  private queries: QueryLog[] = [];
  private isCapturing = false;

  start(): void {
    this.queries = [];
    this.isCapturing = true;
  }

  stop(): QueryLog[] {
    this.isCapturing = false;
    return [...this.queries];
  }

  record(sql: string, params: unknown[], duration: number): void {
    if (!this.isCapturing) return;

    const entry: QueryLog = {
      sql,
      params,
      duration,
      timestamp: Date.now(),
      stack: new Error().stack,
    };

    this.queries.push(entry);
    this.emit('query', entry);
  }

  get count(): number {
    return this.queries.length;
  }

  get totalDuration(): number {
    return this.queries.reduce((sum, q) => sum + q.duration, 0);
  }

  getGroupedByPattern(): Map<string, number> {
    const groups = new Map<string, number>();

    for (const query of this.queries) {
      // Normalize the SQL by replacing literal values with placeholders
      const normalized = query.sql
        .replace(/= '\w+'/g, "= '?'")
        .replace(/= \d+/g, '= ?')
        .replace(/IN \([^)]+\)/g, 'IN (?)')
        .replace(/LIMIT \d+/g, 'LIMIT ?')
        .replace(/OFFSET \d+/g, 'OFFSET ?');

      groups.set(normalized, (groups.get(normalized) || 0) + 1);
    }

    return groups;
  }

  detectNPlusOne(threshold: number = 5): string[] {
    const groups = this.getGroupedByPattern();
    const violations: string[] = [];

    for (const [pattern, count] of groups) {
      if (count >= threshold) {
        violations.push(
          `N+1 detected: "${pattern}" executed ${count} times`
        );
      }
    }

    return violations;
  }

  reset(): void {
    this.queries = [];
    this.isCapturing = false;
  }
}

export const queryCounter = new QueryCounter();

Prisma Integration

import { PrismaClient } from '@prisma/client';
import { queryCounter } from './query-counter';

function createInstrumentedPrisma(): PrismaClient {
  const prisma = new PrismaClient({
    log: [
      { level: 'query', emit: 'event' },
    ],
  });

  prisma.$on('query' as never, (e: { query: string; params: string; duration: number }) => {
    queryCounter.record(e.query, JSON.parse(e.params), e.duration);
  });

  return prisma;
}

export const prisma = createInstrumentedPrisma();

Express Middleware for Per-Request Counting

import { Request, Response, NextFunction } from 'express';
import { queryCounter } from './query-counter';

interface QueryMetrics {
  queryCount: number;
  totalDuration: number;
  nPlusOneViolations: string[];
}

export function queryCountingMiddleware(
  maxQueries: number = 20
) {
  return (req: Request, res: Response, next: NextFunction) => {
    queryCounter.start();

    const originalJson = res.json.bind(res);
    res.json = function (body: unknown) {
      const logs = queryCounter.stop();
      const violations = queryCounter.detectNPlusOne();

      const metrics: QueryMetrics = {
        queryCount: logs.length,
        totalDuration: logs.reduce((sum, q) => sum + q.duration, 0),
        nPlusOneViolations: violations,
      };

      // Attach metrics to response headers in development
      if (process.env.NODE_ENV !== 'production') {
        res.setHeader('X-Query-Count', metrics.queryCount.toString());
        res.setHeader('X-Query-Duration', `${metrics.totalDuration}ms`);

        if (metrics.queryCount > maxQueries) {
          console.warn(
            `[N+1 WARNING] ${req.method} ${req.path}: ${metrics.queryCount} queries (limit: ${maxQueries})`
          );
          for (const v of violations) {
            console.warn(`  ${v}`);
          }
        }
      }

      return originalJson(body);
    };

    next();
  };
}

Detecting N+1 Patterns in ORMs

Prisma N+1 Detection

Prisma defaults to lazy-loading relations, which is the primary source of N+1 queries. The fix is explicit include or select clauses.

import { test, expect, beforeAll, afterAll } from 'vitest';
import { prisma } from '../helpers/prisma-instrumented';
import { queryCounter } from '../helpers/query-counter';

beforeAll(async () => {
  // Seed test data with enough volume to expose N+1
  const users = Array.from({ length: 50 }, (_, i) => ({
    name: `User ${i}`,
    email: `user${i}@test.com`,
  }));

  await prisma.user.createMany({ data: users });

  const createdUsers = await prisma.user.findMany();
  for (const user of createdUsers) {
    await prisma.post.createMany({
      data: Array.from({ length: 5 }, (_, i) => ({
        title: `Post ${i} by ${user.name}`,
        content: `Content ${i}`,
        authorId: user.id,
      })),
    });
  }
});

afterAll(async () => {
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
});

test('GET /api/users should not cause N+1 queries', async () => {
  queryCounter.start();

  // Simulate the endpoint handler
  const users = await prisma.user.findMany({
    include: {
      posts: {
        select: { id: true, title: true },
      },
    },
  });

  const logs = queryCounter.stop();
  const violations = queryCounter.detectNPlusOne();

  // With proper eager loading, this should be 2 queries max:
  // 1. SELECT users
  // 2. SELECT posts WHERE authorId IN (...)
  expect(logs.length).toBeLessThanOrEqual(3);
  expect(violations).toHaveLength(0);

  // Verify the data is complete (not lazy-loaded)
  for (const user of users) {
    expect(user.posts).toBeDefined();
    expect(Array.isArray(user.posts)).toBe(true);
  }
});

test('ANTI-PATTERN: lazy loading causes N+1', async () => {
  queryCounter.start();

  // This is the N+1 anti-pattern -- DO NOT DO THIS in production
  const users = await prisma.user.findMany(); // Query 1
  for (const user of users) {
    // Each iteration issues a new query -- Query 2..N+1
    const posts = await prisma.post.findMany({
      where: { authorId: user.id },
    });
    // Process posts...
  }

  const logs = queryCounter.stop();
  const violations = queryCounter.detectNPlusOne();

  // This will issue 1 + N queries (51 queries for 50 users)
  expect(logs.length).toBeGreaterThan(50);
  expect(violations.length).toBeGreaterThan(0);
});

SQLAlchemy N+1 Detection (Python)

import pytest
from sqlalchemy import event, text
from sqlalchemy.orm import Session
from app.models import User, Post
from app.database import engine, SessionLocal


class QueryCounter:
    def __init__(self):
        self.queries = []
        self._listening = False

    def start(self, engine):
        self.queries = []
        self._listening = True
        event.listen(engine, "before_cursor_execute", self._record)

    def stop(self, engine):
        self._listening = False
        event.remove(engine, "before_cursor_execute", self._record)
        return self.queries

    def _record(self, conn, cursor, statement, parameters, context, executemany):
        if self._listening:
            self.queries.append({
                "sql": statement,
                "params": parameters,
            })

    @property
    def count(self):
        return len(self.queries)

    def detect_n_plus_one(self, threshold=5):
        from collections import Counter
        import re

        normalized = []
        for q in self.queries:
            sql = re.sub(r"= '[^']*'", "= '?'", q["sql"])
            sql = re.sub(r"= \d+", "= ?", sql)
            normalized.append(sql)

        counts = Counter(normalized)
        return {sql: count for sql, count in counts.items() if count >= threshold}


@pytest.fixture
def query_counter():
    counter = QueryCounter()
    counter.start(engine)
    yield counter
    counter.stop(engine)


def test_get_users_with_posts_no_n_plus_one(query_counter, db_session: Session):
    """Verify eager loading prevents N+1 queries."""
    from sqlalchemy.orm import joinedload

    # Correct: eager load with joinedload
    users = (
        db_session.query(User)
        .options(joinedload(User.posts))
        .all()
    )

    assert query_counter.count <= 2  # 1 JOIN query or 2 separate queries
    violations = query_counter.detect_n_plus_one()
    assert len(violations) == 0

    # Verify data is loaded
    for user in users:
        assert hasattr(user, "posts")


def test_lazy_loading_causes_n_plus_one(query_counter, db_session: Session):
    """Demonstrate the N+1 anti-pattern with lazy loading."""
    # Anti-pattern: no eager loading specified
    users = db_session.query(User).all()  # Query 1

    for user in users:
        _ = user.posts  # Each access triggers a query

    violations = query_counter.detect_n_plus_one()
    assert len(violations) > 0, "Expected N+1 pattern not detected"

Hibernate N+1 Detection (Java)

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import net.ttddyy.dsproxy.QueryCountHolder;
import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
class NPlusOneDetectionTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    void getUsersWithPosts_shouldNotCauseNPlusOne() {
        QueryCountHolder.clear();

        // Uses @EntityGraph or JOIN FETCH
        var users = userRepository.findAllWithPosts();

        var queryCount = QueryCountHolder.getGrandTotal();

        // Should be at most 1-2 queries with proper fetch strategy
        assertThat(queryCount.getSelect()).isLessThanOrEqualTo(2);

        // Verify posts are loaded
        for (var user : users) {
            assertThat(user.getPosts()).isNotNull();
            assertThat(
                org.hibernate.Hibernate.isInitialized(user.getPosts())
            ).isTrue();
        }
    }
}

Automated Threshold Testing

Query Budget Definitions

// config/query-budgets.ts

export interface QueryBudget {
  endpoint: string;
  method: string;
  maxQueries: number;
  maxDurationMs: number;
  description: string;
}

export const QUERY_BUDGETS: QueryBudget[] = [
  {
    endpoint: '/api/users',
    method: 'GET',
    maxQueries: 3,
    maxDurationMs: 100,
    description: 'List users with counts',
  },
  {
    endpoint: '/api/users/:id',
    method: 'GET',
    maxQueries: 4,
    maxDurationMs: 50,
    description: 'Single user with relations',
  },
  {
    endpoint: '/api/posts',
    method: 'GET',
    maxQueries: 5,
    maxDurationMs: 150,
    description: 'List posts with author and tags',
  },
  {
    endpoint: '/api/dashboard',
    method: 'GET',
    maxQueries: 8,
    maxDurationMs: 200,
    description: 'Dashboard with aggregations',
  },
  {
    endpoint: '/api/feed',
    method: 'GET',
    maxQueries: 6,
    maxDurationMs: 200,
    description: 'Activity feed with nested data',
  },
];

Budget Enforcement Tests

import { test, expect, describe, beforeEach, afterEach } from 'vitest';
import { queryCounter } from '../helpers/query-counter';
import { QUERY_BUDGETS } from '../../config/query-budgets';
import request from 'supertest';
import { app } from '../../src/app';

describe('Query Budget Enforcement', () => {
  for (const budget of QUERY_BUDGETS) {
    test(`${budget.method} ${budget.endpoint} should stay within query budget (max: ${budget.maxQueries})`, async () => {
      queryCounter.start();

      const resolvedEndpoint = budget.endpoint.replace(
        /:id/g,
        '1'
      );

      const response = await request(app)
        [budget.method.toLowerCase() as 'get' | 'post'](resolvedEndpoint)
        .set('Accept', 'application/json');

      const logs = queryCounter.stop();

      expect(response.status).toBeLessThan(500);

      // Enforce query count budget
      expect(
        logs.length,
        `${budget.method} ${budget.endpoint}: ${logs.length} queries exceeds budget of ${budget.maxQueries}. ` +
        `Queries:\n${logs.map((q) => `  - ${q.sql.substring(0, 120)}`).join('\n')}`
      ).toBeLessThanOrEqual(budget.maxQueries);

      // Enforce duration budget
      const totalDuration = logs.reduce((sum, q) => sum + q.duration, 0);
      expect(
        totalDuration,
        `${budget.method} ${budget.endpoint}: ${totalDuration}ms exceeds duration budget of ${budget.maxDurationMs}ms`
      ).toBeLessThanOrEqual(budget.maxDurationMs);

      // Check for N+1 patterns
      const violations = queryCounter.detectNPlusOne(3);
      expect(
        violations,
        `N+1 detected in ${budget.method} ${budget.endpoint}:\n${violations.join('\n')}`
      ).toHaveLength(0);
    });
  }
});

DataLoader Pattern for GraphQL

GraphQL APIs are especially vulnerable to N+1 problems because each resolver fetches data independently. The DataLoader pattern batches and deduplicates these fetches.

import DataLoader from 'dataloader';
import { prisma } from './prisma';

// DataLoader batches individual post lookups into a single query
export function createPostLoader() {
  return new DataLoader<string, Post[]>(async (userIds) => {
    // Single query: SELECT * FROM posts WHERE authorId IN (...)
    const posts = await prisma.post.findMany({
      where: {
        authorId: { in: [...userIds] },
      },
    });

    // Map posts back to their respective user IDs
    const postsByUserId = new Map<string, Post[]>();
    for (const post of posts) {
      const existing = postsByUserId.get(post.authorId) || [];
      existing.push(post);
      postsByUserId.set(post.authorId, existing);
    }

    return userIds.map((id) => postsByUserId.get(id) || []);
  });
}

// Test that DataLoader properly batches
import { test, expect } from 'vitest';

test('DataLoader should batch user post lookups', async () => {
  queryCounter.start();

  const postLoader = createPostLoader();

  // These 50 calls should be batched into 1 query
  const userIds = Array.from({ length: 50 }, (_, i) => `user-${i}`);
  const results = await Promise.all(
    userIds.map((id) => postLoader.load(id))
  );

  const logs = queryCounter.stop();

  // DataLoader should batch all 50 lookups into 1 query
  expect(logs.length).toBe(1);
  expect(logs[0].sql).toContain('IN');
  expect(results).toHaveLength(50);
});

test('DataLoader should deduplicate identical requests', async () => {
  queryCounter.start();

  const postLoader = createPostLoader();

  // Same user ID requested 10 times
  const sameId = 'user-1';
  const results = await Promise.all(
    Array.from({ length: 10 }, () => postLoader.load(sameId))
  );

  const logs = queryCounter.stop();

  // Should still be 1 query despite 10 requests
  expect(logs.length).toBe(1);
  // All results should be the same reference
  const firstResult = results[0];
  results.forEach((r) => expect(r).toBe(firstResult));
});

Request-Level Query Budgets

Middleware That Fails Requests Exceeding Budget

import { Request, Response, NextFunction } from 'express';
import { queryCounter } from './query-counter';

interface BudgetConfig {
  [route: string]: {
    maxQueries: number;
    action: 'warn' | 'block' | 'log';
  };
}

const budgetConfig: BudgetConfig = {
  'GET /api/users': { maxQueries: 3, action: 'block' },
  'GET /api/posts': { maxQueries: 5, action: 'block' },
  'GET /api/dashboard': { maxQueries: 10, action: 'warn' },
  '*': { maxQueries: 20, action: 'log' },
};

export function queryBudgetMiddleware() {
  return (req: Request, res: Response, next: NextFunction) => {
    queryCounter.start();

    const originalEnd = res.end.bind(res);
    res.end = function (...args: Parameters<Response['end']>) {
      const logs = queryCounter.stop();
      const routeKey = `${req.method} ${req.route?.path || req.path}`;
      const config =
        budgetConfig[routeKey] || budgetConfig['*'];

      if (logs.length > config.maxQueries) {
        const message = `Query budget exceeded: ${routeKey} used ${logs.length}/${config.maxQueries} queries`;

        switch (config.action) {
          case 'block':
            if (!res.headersSent) {
              res.status(503).json({
                error: 'Service temporarily unavailable',
                reason: process.env.NODE_ENV === 'development' ? message : undefined,
              });
              return res;
            }
            break;
          case 'warn':
            console.warn(`[QUERY BUDGET WARNING] ${message}`);
            break;
          case 'log':
            console.log(`[QUERY BUDGET] ${message}`);
            break;
        }
      }

      return originalEnd(...args);
    } as Response['end'];

    next();
  };
}

CI Integration for Query Count Regression

GitHub Actions Workflow Integration

// tests/performance/query-regression.spec.ts
import { test, expect, describe } from 'vitest';
import { queryCounter } from '../helpers/query-counter';
import { readFileSync, writeFileSync, existsSync } from 'fs';
import { join } from 'path';

const BASELINE_PATH = join(__dirname, '../../.query-baseline.json');

interface QueryBaseline {
  [endpoint: string]: {
    queryCount: number;
    measuredAt: string;
  };
}

function loadBaseline(): QueryBaseline {
  if (existsSync(BASELINE_PATH)) {
    return JSON.parse(readFileSync(BASELINE_PATH, 'utf-8'));
  }
  return {};
}

function saveBaseline(baseline: QueryBaseline): void {
  writeFileSync(BASELINE_PATH, JSON.stringify(baseline, null, 2));
}

describe('Query Count Regression Detection', () => {
  const endpoints = [
    { method: 'GET', path: '/api/users', handler: getUsersHandler },
    { method: 'GET', path: '/api/posts', handler: getPostsHandler },
    { method: 'GET', path: '/api/dashboard', handler: getDashboardHandler },
  ];

  for (const endpoint of endpoints) {
    test(`${endpoint.method} ${endpoint.path} should not regress in query count`, async () => {
      const baseline = loadBaseline();
      const key = `${endpoint.method} ${endpoint.path}`;

      queryCounter.start();
      await endpoint.handler();
      const logs = queryCounter.stop();

      const currentCount = logs.length;

      if (baseline[key]) {
        const previousCount = baseline[key].queryCount;
        const regressionThreshold = Math.ceil(previousCount * 1.1); // 10% tolerance

        expect(
          currentCount,
          `Query regression detected for ${key}: was ${previousCount}, now ${currentCount}`
        ).toBeLessThanOrEqual(regressionThreshold);
      }

      // Update baseline if running in update mode
      if (process.env.UPDATE_QUERY_BASELINE === 'true') {
        baseline[key] = {
          queryCount: currentCount,
          measuredAt: new Date().toISOString(),
        };
        saveBaseline(baseline);
      }
    });
  }
});

Eager vs Lazy Loading Audit

import { test, expect, describe } from 'vitest';
import { PrismaClient } from '@prisma/client';

describe('ORM Loading Strategy Audit', () => {
  test('all list endpoints should use explicit includes', async () => {
    // Parse the route handlers and check for include clauses
    const routeFiles = [
      'src/routes/users.ts',
      'src/routes/posts.ts',
      'src/routes/comments.ts',
    ];

    for (const file of routeFiles) {
      const content = require('fs').readFileSync(file, 'utf-8');

      // Check for findMany without include (potential N+1)
      const findManyWithoutInclude = /\.findMany\(\s*\)/g;
      const matches = content.match(findManyWithoutInclude);

      if (matches) {
        console.warn(
          `${file}: Found ${matches.length} findMany() calls without includes`
        );
      }

      expect(
        matches,
        `${file} has findMany() without explicit include — potential N+1`
      ).toBeNull();
    }
  });

  test('related data access should use include, not separate queries', async () => {
    // Verify that getting a user with posts uses a single include
    queryCounter.start();

    const user = await prisma.user.findUnique({
      where: { id: 'test-user-1' },
      include: {
        posts: true,
        comments: true,
        profile: true,
      },
    });

    const logs = queryCounter.stop();

    // findUnique with include should generate at most 4 queries
    // (1 per relation + 1 for the main entity, or fewer with JOINs)
    expect(logs.length).toBeLessThanOrEqual(4);
  });
});

Configuration

Query Counter Configuration

// config/thresholds.ts

export const QUERY_THRESHOLDS = {
  // Maximum queries per request before triggering N+1 detection
  nPlusOneDetectionThreshold: 5,

  // Maximum total queries per request
  maxQueriesPerRequest: 20,

  // Maximum total query duration per request (ms)
  maxQueryDurationMs: 500,

  // Percentage increase allowed before flagging as regression
  regressionTolerancePercent: 10,

  // Minimum data volume for meaningful N+1 testing
  minimumTestDataRows: 50,

  // Enable detailed query logging in CI
  verboseLogging: process.env.CI === 'true',

  // File path for query count baselines
  baselinePath: '.query-baseline.json',
};

Best Practices

1. Always use include or select with list queries -- When fetching a list of records that will need related data, declare the relations upfront in the query. Never iterate over results and issue individual relation lookups.

2. Seed tests with at least 50 parent records -- N+1 problems are proportional to data size. With 2 records, 3 queries vs 2 queries is invisible. With 50 records, 51 queries vs 2 queries is obvious.

3. Make query budgets part of the API contract -- Document the expected query count for each endpoint. When a PR changes an endpoint, reviewers should verify that the query budget is still reasonable.

4. Use DataLoader for GraphQL resolvers -- Every resolver that accesses related data must use a DataLoader instance. Create a new DataLoader per request to avoid cross-request caching issues.

5. Profile production queries periodically -- N+1 problems can hide behind caches in development. Use production query profiling tools (pg_stat_statements, slow query logs) to identify patterns that only emerge at production data volumes.

6. Prefer batch operations over loops -- Replace for loops that issue individual INSERT, UPDATE, or DELETE statements with batch operations (createMany, updateMany, bulk operations).

7. Test with pagination -- N+1 detection must work correctly with paginated queries. Ensure that eager loading applies to the paginated subset, not to all records.

8. Instrument integration tests, not just unit tests -- Unit tests with mocked databases cannot detect N+1 patterns. Use integration tests with a real database (or in-memory database like SQLite) for query counting.

9. Log the full query with parameters -- When a query budget violation is detected, log the complete SQL with bound parameters. This makes it immediately clear which query is being repeated.

10. Use connection pooling metrics -- Monitor connection pool checkout counts per request. An N+1 pattern will show high pool checkout rates even if individual queries are fast.

11. Fail CI on query count regressions -- Make query budget tests non-optional in CI. A query count regression that is merged today becomes a production performance incident tomorrow.

12. Audit ORM lazy-loading configuration -- Review your ORM's default loading strategy. In Prisma, relations are not loaded by default. In Hibernate, @OneToMany defaults to FetchType.LAZY. Know the defaults and override them explicitly.

Anti-Patterns to Avoid

1. Iterating and querying in a loop -- The classic N+1 anti-pattern: users.forEach(async (user) => { const posts = await getPosts(user.id); }). Always use include, JOIN, or IN clauses instead.

2. Relying on ORM lazy loading in production -- Lazy loading is convenient in development but catastrophic in production. Disable lazy loading or configure your ORM to warn when it triggers implicit queries.

3. Caching to hide N+1 problems -- Adding a Redis cache on top of an N+1 endpoint masks the problem but does not fix it. Cache misses will still trigger the full N+1 pattern, and cold starts become extremely slow.

4. Testing with empty or minimal data -- Testing with 1-2 records will never reveal N+1 issues. The query count will look reasonable even with the most pathological access patterns. Always test with realistic data volumes.

5. Using SELECT * when only IDs are needed -- Fetching full records when you only need identifiers wastes bandwidth and memory. Use select clauses to fetch only the columns you need, and use IN queries for batch lookups.

6. Ignoring GraphQL query depth -- Deeply nested GraphQL queries can create cascading N+1 problems at each resolver level. Implement query depth limiting and require DataLoader at every level.

7. Manual SQL string construction in loops -- Building WHERE id = ? queries in a loop instead of WHERE id IN (?, ?, ?) is an N+1 pattern that ORMs would normally prevent. If writing raw SQL, always use batch operations.

Debugging Tips

1. Enable query logging at the driver level -- Set log: ['query'] in Prisma, echo=True in SQLAlchemy, or hibernate.show_sql=true in Hibernate. Count the queries manually for a single request to establish a baseline.

2. Use EXPLAIN ANALYZE on repeated queries -- If you see the same query pattern repeated many times, run EXPLAIN ANALYZE to understand its execution plan. A query that does a full table scan 50 times per request is doubly problematic.

3. Check for missing indexes on foreign keys -- N+1 queries often hit foreign key columns. Ensure that all foreign key columns have indexes. Without indexes, each individual query in the N+1 pattern does a sequential scan.

4. Watch for N+1 in serialization -- Some frameworks trigger lazy loading during JSON serialization when accessing relation properties. The N+1 happens not in the controller but in the serializer.

5. Profile with a database proxy -- Tools like pgBouncer, datasource-proxy (Java), or prisma-query-log can intercept all queries without modifying application code. This gives complete visibility into query patterns.

6. Count queries per request in development -- Add a simple middleware that logs the total query count for every request during development. Make this visible in the terminal output so developers notice regressions immediately.

7. Examine the SQL pattern, not just the count -- A high query count is not always N+1. Look for the signature pattern: one query followed by N identical queries with different parameter values. This distinguishes N+1 from legitimate multi-query operations.

8. Test GraphQL resolvers in isolation -- When debugging GraphQL N+1 issues, test each resolver independently with the DataLoader context to verify that batching is working. A misconfigured DataLoader that creates a new instance per resolve call will not batch.

9. Check for N+1 in database triggers -- If your database has triggers that run additional queries on INSERT or UPDATE, these can create N+1-like behavior that is invisible at the application level. Review trigger definitions when query counts exceed expectations.

10. Use flamegraph-style query visualization -- Tools that show queries on a timeline (like Prisma Studio or Django Debug Toolbar) make it visually obvious when 50 identical queries fire in sequence. The waterfall pattern of N+1 is unmistakable in a timeline view.