Rahul Patel

Posted on Mar 31 • Originally published at apilens.rest

How to Detect and Fix N+1 Queries in Node.js The Complete Guide

#node #opensource #javascript #typescript

N+1 queries silently kill your API performance. Learn what causes them, how to detect them manually, and how to auto-detect them with one line of middleware.

What is the N+1 Query Problem?

The N+1 query problem is one of the most common performance killers in web APIs. It happens when your code executes 1 query to fetch a list of items, then N additional queries to fetch related data for each item.

Example: You want to show 50 orders with their product names.

// ❌ BAD: N+1 — 51 queries total
app.get('/api/orders', async (req, res) => {
  const orders = await db.query('SELECT * FROM orders LIMIT 50');  // 1 query

  for (const order of orders) {
    order.product = await db.query(                                 // 50 queries
      'SELECT name FROM products WHERE id = $1',
      [order.product_id]
    );
  }

  res.json(orders);
});

This makes 51 database queries instead of 2. At 50 orders it's slow. At 500 orders, your API is dead.

Why N+1 Queries Are So Dangerous

The scary part? You won't notice them during development. With 5 test records, 6 queries take 10ms. In production with 500 records, those 501 queries take 5+ seconds.

Records	Queries	Approx Time
5	6	~10ms
50	51	~200ms
500	501	~3-5 seconds
5000	5001	~30+ seconds (timeout)

N+1 queries are the #1 reason APIs slow down as data grows.

How N+1 Happens in Common ORMs

Prisma

// ❌ N+1: Prisma executes separate queries for each user's posts
const users = await prisma.user.findMany();
for (const user of users) {
  user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// ✅ Fixed: Use include (eager loading)
const users = await prisma.user.findMany({
  include: { posts: true }
});

Sequelize

// ❌ N+1: Lazy loading each order's items
const orders = await Order.findAll();
for (const order of orders) {
  order.items = await order.getItems();
}

// ✅ Fixed: Eager loading with include
const orders = await Order.findAll({
  include: [{ model: Item }]
});

Mongoose

// ❌ N+1: Populating one by one
const orders = await Order.find();
for (const order of orders) {
  await order.populate('product');
}

// ✅ Fixed: Populate in the query
const orders = await Order.find().populate('product');

Raw SQL (pg / mysql2)

// ❌ N+1: Loop queries
const orders = await db.query('SELECT * FROM orders');
for (const order of orders) {
  const [product] = await db.query(
    'SELECT * FROM products WHERE id = $1',
    [order.product_id]
  );
  order.product = product;
}

// ✅ Fixed: JOIN or IN clause
const orders = await db.query(`
  SELECT o.*, p.name as product_name
  FROM orders o
  JOIN products p ON p.id = o.product_id
`);

How to Detect N+1 Queries - The Manual Way

Method 1: Count queries with a wrapper

let queryCount = 0;
const originalQuery = db.query.bind(db);
db.query = async (...args) => {
  queryCount++;
  return originalQuery(...args);
};

// After the request
console.log('Total queries:', queryCount);  // If this is 51 for 50 records... N+1!

Problems with this approach:

You have to add/remove this code manually
It doesn't tell you WHICH route has the problem
No per-request isolation (concurrent requests mix counts)
Doesn't work across async boundaries

Method 2: Database query logging

Enable query logging in your ORM:

// Prisma
const prisma = new PrismaClient({ log: ['query'] });

// Sequelize
const sequelize = new Sequelize({ logging: console.log });

// Mongoose
mongoose.set('debug', true);

Problems:

Floods your console with every query
No grouping by request
No automatic "this looks like N+1" detection
You have to manually count repeated patterns

The Better Way: Auto-Detect N+1 with One Line

What if your middleware could automatically track every database query per request, count them, and flag routes with suspiciously high query counts?

That's exactly what auto-api-observe does.

Install

npm install auto-api-observe

Add one line

const express = require('express');
const observe = require('auto-api-observe');

const app = express();
app.use(observe());  // That's it

What happens automatically

The middleware patches your database libraries (pg, mysql2, mongoose, prisma, knex, sequelize, ioredis, better-sqlite3) at the prototype level. For every request, you get:

{
  "method": "GET",
  "route": "/api/orders",
  "status": 200,
  "latency": 1850,
  "dbCalls": {
    "calls": 51,
    "totalTime": 1720,
    "slowestQuery": 80,
    "queries": [
      { "query": "SELECT * FROM orders", "source": "pg", "queryTime": 30 },
      { "query": "SELECT * FROM products WHERE id = ?", "source": "pg", "queryTime": 35 },
      { "query": "SELECT * FROM products WHERE id = ?", "source": "pg", "queryTime": 28 }
    ]
  }
}

When you see "calls": 51 on a GET request - that's your N+1.

Cloud Dashboard Detection

Add your API key to see it in the dashboard:

app.use(observe({ apiKey: process.env.APILENS_KEY }));

The Database page in the APILens dashboard automatically flags routes with high DB calls per request as potential N+1 issues.

The N+1 Query Detector table shows:

Route name
Average DB calls per request
Total queries
Flag for suspicious patterns

No manual counting. No query log parsing. It just works.

How Auto-Detection Works Under the Hood

AsyncLocalStorage creates a per-request context that follows the entire async chain
Prototype monkey-patching intercepts queries from 8 DB libraries at the driver level
Query masking replaces literal values with ? for security
Per-request aggregation counts calls, sums time, tracks the slowest query
~50ns overhead per DB call (vs 1-50ms actual query time)

The key insight: by patching at the driver level (not the ORM level), it works with ANY ORM - Prisma, Sequelize, TypeORM, Drizzle, Knex, or raw queries. You don't need to configure anything per-ORM.

Quick Comparison: N+1 Detection Methods

Method	Setup	Per-Request	Auto-Detect	Cross-ORM
Manual counter	~30 lines	No	No	No
ORM query logging	1 line per ORM	No	No	No
Datadog APM	30+ min	Yes	Partial	Yes
auto-api-observe	1 line	Yes	Yes	Yes

Summary

N+1 queries happen when you fetch N related records with N separate queries instead of 1
They're invisible during development but kill production performance
Every ORM has this problem - Prisma, Sequelize, Mongoose, raw SQL
Fix them with eager loading (include), JOINs, or IN clauses
Detect them automatically with auto-api-observe - one line of middleware, 8 DB libraries, zero config

Try it

npm install auto-api-observe

Dashboard: apilens.rest
GitHub: github.com/rahhuul/auto-api-observe
npm: auto-api-observe

Free during beta. Zero dependencies. 44 tests.

What tools do you use to detect N+1 queries in your Node.js APIs? I'd love to hear your approach.

DEV Community