N+1 Query Problem — The Silent Performance Killer in Every ORM

Request: "Get 100 posts with their authors"

Query 1:  SELECT * FROM posts LIMIT 100;         ← 1 query
Query 2:  SELECT * FROM users WHERE id = 1;      ← +1
Query 3:  SELECT * FROM users WHERE id = 2;      ← +1
Query 4:  SELECT * FROM users WHERE id = 3;      ← +1
...
Query 101: SELECT * FROM users WHERE id = 100;   ← +1

Total: 101 queries instead of 1 or 2.

// Prisma — the N+1 trap
const posts = await prisma.post.findMany({ take: 100 })

// This looks innocent. But then:
for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId }  // 1 query PER post!
  })
  console.log(post.title, author.name)
}
// Result: 1 + 100 = 101 queries

// TypeORM — equally sneaky
const posts = await postRepository.find({ take: 100 })

for (const post of posts) {
  // If 'author' is a lazy relation, this triggers a query
  const author = await post.author  // 1 query per post!
}

// ✅ Prisma — include relation in the same query
const posts = await prisma.post.findMany({
  take: 100,
  include: {
    author: true,  // JOIN in a single query
    comments: {
      include: { author: true }  // Nested include
    }
  }
})
// Result: 1 query with a JOIN — not 101

// ✅ TypeORM — eager loading
const posts = await postRepository.find({
  relations: ['author', 'comments', 'comments.author'],
  take: 100,
})

// ✅ Sequelize — include
const posts = await Post.findAll({
  include: [{ model: User, as: 'author' }],
  limit: 100,
})

npm install dataloader

import DataLoader from 'dataloader'

// The batch function: receives array of IDs, returns array of results
const userLoader = new DataLoader<number, User>(async (userIds) => {
  console.log(`Batch loading ${userIds.length} users`)
  // ONE query for ALL IDs
  const users = await prisma.user.findMany({
    where: { id: { in: [...userIds] } }
  })

  // IMPORTANT: Return in same order as input IDs
  const userMap = new Map(users.map(u => [u.id, u]))
  return userIds.map(id => userMap.get(id) ?? new Error(`User ${id} not found`))
})

// Now, even with a loop, only ONE DB call is made
const posts = await prisma.post.findMany({ take: 100 })

const postsWithAuthors = await Promise.all(
  posts.map(async post => ({
    ...post,
    author: await userLoader.load(post.authorId),  // Batched!
  }))
)

// Result: 2 queries total (1 for posts, 1 batched user query)
// Instead of 101 queries

const posts = await pool.query(`
  SELECT
    p.id,
    p.title,
    p.content,
    p.created_at,
    u.id AS author_id,
    u.name AS author_name,
    u.avatar_url AS author_avatar
  FROM posts p
  JOIN users u ON p.author_id = u.id
  ORDER BY p.created_at DESC
  LIMIT 100
`)

// Result: 1 perfectly optimized query

// ❌ N+1 pattern
const orderIds = orders.map(o => o.id)
const details = []
for (const id of orderIds) {
  details.push(await getOrderDetail(id))  // 1 query per ID!
}

// ✅ Single query with IN
const details = await prisma.orderDetail.findMany({
  where: { orderId: { in: orderIds } }
})

// Map results back to orders
const detailsByOrderId = new Map(details.map(d => [d.orderId, d]))
const ordersWithDetails = orders.map(order => ({
  ...order,
  detail: detailsByOrderId.get(order.id),
}))

// ❌ Classic GraphQL N+1
const resolvers = {
  Post: {
    author: async (post) => {
      return await prisma.user.findUnique({ where: { id: post.authorId } })
      // Called once per post — N queries!
    }
  }
}

// ✅ With DataLoader in context
const resolvers = {
  Post: {
    author: async (post, args, { loaders }) => {
      return loaders.user.load(post.authorId)  // Batched!
    }
  }
}

// Setup loaders in context
function createLoaders() {
  return {
    user: new DataLoader<number, User>(async (ids) => {
      const users = await prisma.user.findMany({
        where: { id: { in: [...ids] } }
      })
      const map = new Map(users.map(u => [u.id, u]))
      return ids.map(id => map.get(id)!)
    }),
  }
}

// Log all queries in development (Prisma)
const prisma = new PrismaClient({
  log: ['query'],
})

// Count queries per request
let queryCount = 0
prisma.$on('query', () => queryCount++)

app.use((req, res, next) => {
  queryCount = 0
  res.on('finish', () => {
    if (queryCount > 10) {
      console.warn(`⚠️  ${req.path} made ${queryCount} DB queries!`)
    }
  })
  next()
})

# Use pg_stat_statements to find expensive query patterns
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;