N+1 Query Prevention

Overview

Never query in a loop. Fetch related data in a single query.

N+1 is when you fetch N items, then make N more queries to get related data. It's the most common database performance killer.

When to Use

• Fetching a list with related data
• Loop that contains a database query
• Slow list/index endpoints
• Multiple queries for one API response

The Iron Rule

NEVER put a database query inside a loop.

No exceptions:

• Not for "it's only a few items"
• Not for "the query is fast"
• Not for "we'll cache it"
• Not for "it's simpler"

Detection: N+1 Pattern

If you query inside a loop, STOP:

// ❌ VIOLATION: N+1 queries
const orders = await Order.findAll();  // 1 query

const ordersWithCustomers = await Promise.all(
  orders.map(async (order) => {
    // N queries (one per order)
    const customer = await Customer.findByPk(order.customerId);
    return { ...order, customerName: customer.name };
  })
);
// Total: 1 + N queries

For 100 orders = 101 database queries!

The Correct Pattern: Eager Loading

// ✅ CORRECT: Single query with JOIN

// Sequelize
const orders = await Order.findAll({
  include: [{ model: Customer, attributes: ['name'] }]
});
// 1 query with JOIN

// Prisma
const orders = await prisma.order.findMany({
  include: { customer: { select: { name: true } } }
});

// TypeORM
const orders = await orderRepository.find({
  relations: ['customer']
});

// Raw SQL
const orders = await db.query(`
  SELECT o.*, c.name as customer_name 
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
`);

Common N+1 Scenarios

1. Related Entity

// ❌ N+1
posts.map(post => await User.findById(post.authorId));

// ✅ Eager load
Post.findAll({ include: [User] });

2. Aggregates

// ❌ N+1
users.map(user => await Order.count({ where: { userId: user.id } }));

// ✅ Subquery or GROUP BY
User.findAll({
  attributes: {
    include: [[sequelize.fn('COUNT', sequelize.col('orders.id')), 'orderCount']]
  },
  include: [{ model: Order, attributes: [] }],
  group: ['User.id']
});

3. Multiple Relations

// ❌ N+1 (multiple)
orders.map(order => {
  await Customer.findByPk(order.customerId);
  await Product.findAll({ where: { orderId: order.id } });
});

// ✅ Eager load all
Order.findAll({
  include: [Customer, Product]
});

Detection Tools

// Log query count per request
let queryCount = 0;
db.on('query', () => queryCount++);

app.use((req, res, next) => {
  queryCount = 0;
  res.on('finish', () => {
    if (queryCount > 10) {
      console.warn(`N+1 alert: ${req.path} made ${queryCount} queries`);
    }
  });
  next();
});

Pressure Resistance Protocol

1. "It's Only a Few Items"

Pressure: "We only have 10 orders"

Response: 10 becomes 100 becomes 10,000. Fix it now.

Action: Always use eager loading regardless of current data size.

2. "The Query Is Fast"

Pressure: "Each query takes 1ms"

Response: 1ms × 1000 = 1 second. Network overhead adds more.

Action: One 5ms query beats 1000 × 1ms queries.

3. "We'll Cache It"

Pressure: "Redis will cache the results"

Response: Cache misses still hit the DB. First requests are slow. Cache adds complexity.

Action: Fix the query. Cache if still needed.

4. "It's Simpler"

Pressure: "Looping is easier to understand"

Response: Simple code that's 100x slower isn't simple.

Action: Learn your ORM's eager loading syntax.

Red Flags - STOP and Reconsider

• await inside .map() or .forEach()
• Query count grows with result size
• List endpoints slower than detail endpoints
• "Loading..." takes forever on lists
• ORM lazy loading by default

All of these mean: Refactor to eager loading.

Quick Reference

Common Rationalizations (All Invalid)

The Bottom Line

One query for the list. One query for related data. Never query in a loop.

Use eager loading (include/join) to fetch related data. Watch query counts. Any query inside a loop is a bug waiting to scale.