Set a Query Budget per Request
Count SQL statements per request, fail tests that exceed the budget, and expose the count so N+1 regressions surface before users do.
Task
Bound the number of database queries a single request may issue and enforce that bound in automated tests.
When to use this
A serializer adds one query per order line. The endpoint works in staging with ten rows, then a customer with 500 rows triggers 501 queries and the connection pool collapses.
Prerequisites
- Node.js 22 with pg 8.x
- node:test or an equivalent test runner
- A seed script that can create a large, deterministic dataset
Steps
1. Wrap the pool so a test can count every statement issued through it.
export function countedPool(pool) {
let executed = 0;
const query = pool.query.bind(pool);
pool.query = (...args) => {
executed += 1;
return query(...args);
};
return { pool, reset: () => { executed = 0; }, count: () => executed };
}
Expected output: Each call through the wrapped pool increments one counter, independent of rows returned.
2. Measure the endpoint with a small and a large dataset before choosing the budget.
const counted = countedPool(pool);
counted.reset();
await request('/api/orders?limit=10');
const small = counted.count();
counted.reset();
await request('/api/orders?limit=50');
const large = counted.count();
Expected output: Small requests stay near 3 statements; the 50-row request reports about 53 before the fix, exposing one query per row.
3. Assert the budget in the test and print the actual count on failure.
assert.ok(
counted.count() <= 5,
`query budget exceeded: ${counted.count()} statements for 50 orders`,
);
Expected output: The test passes at 3 statements and fails with a readable message when a lazy relation adds one query per row.
4. Record the same count per request in production so the budget is visible outside tests.
import { AsyncLocalStorage } from 'node:async_hooks';
const queryContext = new AsyncLocalStorage();
const query = pool.query.bind(pool);
pool.query = (...args) => {
const ctx = queryContext.getStore();
if (ctx) ctx.queries += 1;
return query(...args);
};
app.use((req, res, next) => {
queryContext.run({ queries: 0 }, () => {
res.on('finish', () => {
logger.info({ route: req.route?.path, dbQueries: queryContext.getStore()?.queries }, 'request completed');
});
next();
});
});
Expected output: Each request logs dbQueries, isolated from concurrent requests by AsyncLocalStorage, with no timing problem from writing headers late.
Troubleshooting
Problem
The count varies between runs
Solution
Lazy loading, background refreshes, or cache misses are issuing extra statements. Disable lazy relations in the test and seed a fixed dataset.
Problem
The budget passes locally but fails in CI
Solution
CI seeds more rows or runs with a different ORM setting. Seed deterministically and assert the dataset size before measuring.
Problem
The budget is exceeded by queries the test never sees
Solution
Something opens its own Client instead of using the pool. Route all queries through the pool and check for direct constructors.
Limitations
- Statements are not cost: ten cheap lookups can be better than one expensive join, so pair the budget with latency and plan checks.
- Only queries through the instrumented pool are counted; direct Client usage and external caches are invisible.
- A tight budget can block legitimate features, so set it per endpoint and review changes deliberately.
Row count hides query count
An endpoint can be fast for ten rows and catastrophic for five hundred, because the number of queries grows with the payload rather than staying constant. This is the N+1 pattern, and it is invisible in unit tests that use one or two fixtures. A query budget turns it into a hard, testable property: one request may issue at most N statements, regardless of row count.
Measure first, then set the budget
Instrument the pool with a counter, run the endpoint with a small and a large dataset, and compare. A healthy list endpoint with eager loading holds steady at two or three statements. A serializing endpoint that adds one query per row grows linearly. Set the budget slightly above the healthy number so legitimate additions do not break CI, but low enough that an N+1 cannot hide.
counted.reset();
await request('/api/orders?limit=50');
assert.ok(counted.count() <= 5, `query budget exceeded: ${counted.count()}`);Make the failure readable
The assertion message should include the actual count and the dataset size, because the next engineer needs to know whether the budget or the query pattern changed. Log the statement text at debug level during the test run so the extra queries are immediately identifiable. For ORMs, enable query logging once, fix the missing include or join, then disable it again.
Do not stop at the test
The same counter belongs in request logs. A dbQueries field per request makes regressions visible in production within minutes, long before a customer with a large account arrives. Combine it with a p95 latency panel: a rising query count and rising latency usually have the same cause.
Keep the budget honest
Review budgets when an endpoint genuinely grows, and never raise one just to make CI green without understanding the new statements. For endpoints with intentionally variable work, such as exports, use a budget per page or per batch instead of a single global number, so the test still catches per-row queries.