Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 36 ยท Performance and Patterns

Connection Pooling and Logging

Configure connection pools with Prisma driver adapters, avoid too-many-connections errors in serverless apps, reuse one PrismaClient, and log queries with durations.

Opening a database connection is expensive (network round trips, authentication, memory on the server). Databases also cap how many connections they accept: a small PostgreSQL plan may allow only 20 to 100. A connection pool keeps a few connections open and lends them to queries, so you pay the opening cost once. This lesson shows how pooling works with Prisma, the classic mistakes, and how to log queries so you can see what your app is doing.

Prisma code is read-only. The TryIts are labelled stand-ins: a pool written in plain Node, and a logger imitating Prisma's query events.

One client, one pool

A PrismaClient owns a pool. Create one client per process and share it:

TypeScript
// src/db.ts
import { PrismaClient } from './generated/client';
import { PrismaPg } from '@prisma/adapter-pg';

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // pool size, passed to the pg driver
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

export const prisma = new PrismaClient({ adapter });

In Prisma 7, the query engine is no longer a separate Rust binary: Prisma talks to the database through a driver adapter (@prisma/adapter-pg, adapter-mariadb, adapter-better-sqlite3, adapter-neon and others), and the pool settings are those of the underlying driver. (Older versions put connection_limit and pool_timeout in the connection URL; check the docs for the version you use.)

The classic bug is creating a new PrismaClient per request, or per hot-reload in development. Every instance opens its own pool until the database refuses connections. In frameworks with hot reload, cache the instance on globalThis:

TypeScript
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter });
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

How big should the pool be?

A pool is shared by concurrent queries. More connections is not more speed: past a small number the database spends time switching between them. A decent start is roughly (CPU cores * 2) + 1 for the database, divided among your app instances. Ten app instances with max: 10 each need 100 server connections. Add up all instances, workers and migration tools before choosing numbers.

Node.js Stand-in: a connection pool with a waiting queue

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Twelve queries shared three connections. Extra requests waited instead of opening a fourth connection. When the wait is longer than maxWait (interactive transactions) or the driver's connection timeout, you get a timeout error: that is your signal that the pool is too small, or queries are too slow and hold connections too long.

Serverless and edge

Each serverless function instance has its own client and pool. A traffic spike can start hundreds of instances at once, each opening connections, and the database runs out. Options:

  • Use a pooler in front of the database (PgBouncer, Supabase Supavisor, RDS Proxy) and point DATABASE_URL at it. With transaction-mode PgBouncer, avoid features that need session state.
  • Use an HTTP or serverless-aware driver with the matching adapter (Neon, PlanetScale, D1, Turso).
  • Keep the per-instance pool tiny (max: 1 to 3).
  • Use Prisma Accelerate, a managed pooler and cache, via withAccelerate().

Also keep a separate direct connection for migrations, since poolers often cannot run DDL safely: in Prisma 7 give the CLI its own URL in prisma.config.ts, while the app uses the pooled one.

Logging queries

TypeScript
const prisma = new PrismaClient({
  adapter,
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'warn' },
    { emit: 'stdout', level: 'error' },
  ],
});

prisma.$on('query', (e) => {
  if (e.duration > 200) console.warn(`SLOW ${e.duration}ms: ${e.query} -- ${e.params}`);
});

Levels are query, info, warn and error. emit: 'stdout' prints directly; emit: 'event' gives you objects with query, params, duration (milliseconds) and target. Query logs contain parameter values, which may be personal data: do not ship them to a log service unredacted. Use this to find N+1 patterns and slow statements to index.

Node.js Stand-in: collect query events and flag N+1

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Disconnecting

A long-running server never needs to call $disconnect(). Scripts, seed files and tests should call it when finished, or the process stays open because the pool holds sockets. Prisma also connects lazily on the first query; $connect() just makes it happen earlier, which helps surface a bad URL at startup.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many PrismaClient instances should a typical server process create?

    Each client owns a pool; many clients multiply connections and exhaust the database.
  2. 2. What is a good first response to 'too many connections' in a serverless deployment?

    Many short-lived instances each open connections; a pooler multiplexes them onto few real ones.
  3. 3. Which log setting delivers query objects including duration?

    Event emission gives you objects with query, params and duration in milliseconds.
  4. 4. Why use a separate direct URL for migrations?

    Schema changes should run on a real direct connection.

Summary

  • Each PrismaClient owns a connection pool: create one and reuse it (cache on globalThis in dev).
  • Pool size is set through the driver adapter; total connections across all instances must fit the database.
  • Serverless needs a pooler, a serverless driver or Accelerate, and small per-instance pools.
  • Log with emit: 'event' and $on('query') to find slow and repeated queries; protect the parameters.
  • Disconnect scripts and tests; leave servers connected.

Next: Testing Prisma Code