Connection Pooling & N+1 Problem

Difficulty: Intermediate

Question

What is connection pooling and the N+1 query problem? How do you solve them in a Node.js application?

Answer

Connection pooling maintains a cache of database connections that are reused across requests instead of creating a new connection for each query. Creating a connection involves TCP handshake, authentication, and TLS negotiation - this overhead is significant at scale.

Prisma uses a built-in connection pool (default 5 connections for each Prisma Client instance). The pool manages acquiring, releasing, and recycling connections automatically. You can configure pool size via the connection string or Prisma config.

The N+1 problem occurs when you fetch a list of N items, then make N additional queries to fetch related data for each item. For example, fetching 100 users, then querying posts for each user individually results in 101 queries instead of 2.

Solutions include: eager loading (Prisma's include), batch loading (DataLoader pattern), joins (raw SQL), and selecting only needed fields. Prisma's include/select performs efficient JOINs or batched queries automatically.

Code examples

N+1 Problem and Solution

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient({ log: ['query'] }); // Log queries

// BAD: N+1 problem - 1 query for users + N queries for posts
const users = await prisma.user.findMany();
for (const user of users) {
  // Each iteration = 1 query!
  const posts = await prisma.post.findMany({
    where: { authorId: user.id }
  });
  user.posts = posts;
}
// Result: 101 queries for 100 users!

// GOOD: Eager loading with include - 2 queries total
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});
// Prisma runs: SELECT * FROM User; SELECT * FROM Post WHERE authorId IN (...);

// GOOD: Select only needed fields
const lightweight = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: { select: { posts: true } },
  },
});

include causes Prisma to batch-load related records in a single IN query. select limits the fields returned. Both solve N+1 efficiently.

Connection Pool Configuration

// Via connection string
// postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=10

// Via Prisma Client
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=20'
    }
  }
});

// Singleton pattern - one client for the entire app
// db.js
let prisma;

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  // Prevent multiple instances in development (hot reload)
  if (!global.__prisma) {
    global.__prisma = new PrismaClient({ log: ['query'] });
  }
  prisma = global.__prisma;
}

module.exports = prisma;

// Monitor pool usage
prisma.$on('query', (e) => {
  if (e.duration > 1000) {
    console.warn(`Slow query (${e.duration}ms):`, e.query);
  }
});

Use a singleton Prisma client to share the connection pool. In development, store on global to survive hot reloads without creating new pools.

DataLoader Pattern for GraphQL/Batch Loading

const DataLoader = require('dataloader');
const prisma = require('./db');

// Batch function: receives array of keys, returns array of results
async function batchUsers(ids) {
  const users = await prisma.user.findMany({
    where: { id: { in: ids } },
  });
  // Must return results in same order as input ids
  const userMap = new Map(users.map(u => [u.id, u]));
  return ids.map(id => userMap.get(id) || null);
}

// Create loader (per request!)
const userLoader = new DataLoader(batchUsers);

// Usage: individual loads are batched automatically
const user1 = await userLoader.load(1); // Not executed yet
const user2 = await userLoader.load(2); // Not executed yet
// After current tick: single query WHERE id IN (1, 2)

// Results are cached within the request
const user1Again = await userLoader.load(1); // Returns cached

DataLoader collects individual load calls within a tick and batches them into a single query. Create a new loader per request to avoid cross-request caching.

Key points

• Default Prisma pool size is num_cpus * 2 + 1; tune based on your workload
• N+1: fetching a list then querying each item individually - watch for loops with DB calls
• Use Prisma's include for eager loading and select for field limiting
• DataLoader is essential for GraphQL but useful in REST APIs with batch endpoints too

Concepts covered

Connection Pool, N+1 Query, DataLoader, Query Optimization