by Pramod
Detect and eliminate N+1 query problems in database-backed applications through query counting, execution plan analysis, and ORM configuration auditing
npx @qaskills/cli add n-plus-one-query-detectorAuto-detects your AI agent and installs the skill. Works with Claude Code, Cursor, Copilot, and more.
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.
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
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.
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();
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();
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();
};
}
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);
});
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"
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();
}
}
}
// 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',
},
];
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);
});
}
});
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));
});
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();
};
}
// 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);
}
});
}
});
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);
});
});
// 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',
};
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.
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.
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.
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.
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.
Prefer batch operations over loops -- Replace for loops that issue individual INSERT, UPDATE, or DELETE statements with batch operations (createMany, updateMany, bulk operations).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
- name: Install QA Skills
run: npx @qaskills/cli add n-plus-one-query-detector12 of 29 agents supported