Connecting a Database

Your app runs on your laptop, you paste postgresql://app:secret@localhost:5432/shop into a file, and everything works. Three weeks later a teammate commits that file, GitHub's secret scanner emails you, and the database vendor rotates your credentials out from under a demo. The failure is not exotic: Next.js has two classes of environment variables, and only one of them ever stays on the server.

Next.js inlines every variable whose name starts with NEXT_PUBLIC_ into the JavaScript bundle that ships to the browser. Anything else is server-only: readable in Route Handlers, Server Actions, and Server Components, absent from the client bundle. The rule is mechanical, so memorize the boundary:

# .env.local — gitignored by default, loaded by `next dev` and `next build`
DATABASE_URL="postgresql://app:secret@db.internal:5432/shop?pgbouncer=true&connection_limit=1"
DIRECT_URL="postgresql://app:secret@db.internal:5432/shop"

.env.local is read at build and run time on your machine; on Vercel or in CI the same names come from the project's environment settings screen. Production secrets in a committed .env.production are just as leaked as secrets in code — the file's name changes nothing about git history. If a credential has ever been committed, rotating it at the vendor is the only fix; git rm removes the file from the branch, not from every fork, cache, or clone.

Fail loudly at boot rather than at query time. A two-line guard turns a deployment mistake into a readable message:

// lib/env.ts
const url = process.env.DATABASE_URL;
if (!url) throw new Error('DATABASE_URL is not set — check .env.local (dev) or project env vars (prod)');
export const DATABASE_URL = url;

Without it, a missing variable surfaces later as PrismaClientInitializationError: Environment variable not found: DATABASE_URL, wrapped in a 500 on some deep page during a stakeholder demo. With it, the first request after boot names the exact file or settings screen to fix.

One subtlety interviewers probe: process.env.DATABASE_URL in client code compiles without complaint and resolves to undefined at runtime, because only NEXT_PUBLIC_-prefixed names are substituted into the bundle. That is why "it works in dev, it's undefined in the client" happens — the compiler never warns you. This is the same trust boundary you enforced in Role-Based Access Control: whatever the browser can read, an attacker can read. Credentials are simply the most expensive instance of that rule.

Two timing details separate the two classes. NEXT_PUBLIC_* values are inlined when next build runs, so changing one means a new bundle — updating a dashboard field without redeploying changes nothing in the shipped JavaScript. Unprefixed variables are read from process.env when server code executes, so a platform-level edit plus a function restart is enough. Precedence matters too: .env.local overrides .env, and files for the current stage (NODE_ENV=production → .env.production.local) win over both, which is how a stale local default can quietly survive in someone's shell.

Rehearse the leak response while nothing is on fire, in this order: revoke the credential at the database vendor, push a replacement secret, redeploy, and only then scrub git history. Scrubbing first is the classic mistake — the old key keeps working until the vendor revokes it.

Make the failure loud at boot with a schema check rather than a comment in the README:

import { z } from 'zod';
const Env = z.object({ DATABASE_URL: z.string().url() });
export const env = Env.parse(process.env);   // throws with the missing key named

The error now says DATABASE_URL: Required on the first request after a bad deploy, instead of surfacing as a driver-level message three layers down.

A fresh app on a laptop opens a few connections per request and never notices connection management. Then you deploy: several serverless instances, each opening a socket per query, and Postgres replies FATAL: too many connections for role "app" — or, against the instance-wide ceiling, FATAL: sorry, too many clients already. The default max_connections = 100 is smaller than the product of instances, queries per request, and concurrent requests. Do that multiplication before you design anything else.

A pool sits between your code and the server, holding a small set of open sockets and lending them out per query:

request A ─┐
request B ─┼──►  Pool (max 10) ────► PostgreSQL (max_connections 100)
request C ─┘        ▲
          checkout / checkin

The pool, not the query, owns the connection. A query that arrives while all ten are busy waits in a queue for milliseconds instead of opening socket number 101.

Where the pool is created decides how large it can silently grow. Prisma's PrismaClient is a pool — its connection_limit defaults to num_cpus * 2 + 1 — while pg makes you size it yourself:

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                     // per process — multiply by instances when budgeting
  idleTimeoutMillis: 30_000,   // return idle sockets to the server
  connectionTimeoutMillis: 5_000,
});

Budget arithmetic worth doing out loud in an interview:

Two settings prevent most production incidents. connectionTimeoutMillis converts a saturated database into a fast, retryable error instead of hanging a request until the platform kills it at 30 seconds. And serverless platforms freeze functions between invocations, so a pool built once can hold sockets the network already dropped — the next call returns Error: Connection terminated unexpectedly or ECONNRESET. Short idle timeouts, TCP keep-alive, or an external pooler are the cures; the extreme version of this problem needs a different transport entirely, which is what HTTP Drivers at the Edge addresses.

Note the ?pgbouncer=true&connection_limit=1 suffix on the URL above. That flag tells Prisma it is already behind a transaction-mode pooler, so it must not assume a dedicated connection per transaction — get this wrong and prepared statements break in ways that look like query bugs.

Before tuning anything, confirm what the database actually sees:

SELECT state, count(*), now() - min(xact_start) AS longest
FROM pg_stat_activity
WHERE datname = 'shop'
GROUP BY state;

idle in transaction lingering for seconds is its own bug class — an interactive $transaction that awaited an external HTTP call while holding a row lock — and it starves the pool faster than query volume does. Prisma exposes the same sizing knob through the URL (?connection_limit=5) rather than code, so a per-environment override needs no redeploy.

Transient failures deserve a bounded retry with jitter rather than a 500: reconnection attempts after a pool blip usually succeed on the second try, but a retry loop with no ceiling turns a database outage into a request storm. Cap attempts, back off exponentially, and let the fast timeout from connectionTimeoutMillis end the loop quickly.

Save a file in dev and the terminal answers ✓ Compiled /dashboard in 342ms. That message means the Next.js dev server re-evaluated the affected module graph. If lib/prisma.ts constructs its client at module scope, every save constructs another one:

// lib/prisma.ts — wrong in dev
import { PrismaClient } from '@prisma/client';
export const prisma = new PrismaClient();   // runs again on every hot reload

Each instance opens its own pool. Reload fifteen times while debugging one query and you have fifteen pools pointed at a database with max_connections = 100. The symptom arrives disguised: P1017: The server has closed the connection, or the screenshot everyone posts — PrismaClientKnownRequestError: Can't reach database server at "localhost:5432" when the server is in fact running and merely out of slots.

The fix attaches one instance to the process, not to the module:

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

globalThis survives module re-evaluation, so the reload path reuses the existing client. In production each process is long-lived and evaluates once, which is why the branch is gated to development — harmless there, and it documents why the code looks unusual.

Three habits keep the invariant intact:

1. Import the singleton, never instantiate. A stray new PrismaClient() in a route file reintroduces the bug invisibly; grep -rn "new PrismaClient" src in code review catches it.
2. Reuse it across requests. Creating and $disconnect()-ing a client per request throws away warm pooled sockets every time — the client is designed to live for the process lifetime.
3. Never query during module evaluation. export const users = await prisma.user.findMany() at the top of a file runs at build time and in any environment without the database, not per request.

One boundary remains: middleware.ts executes in the edge runtime, where a TCP-backed client cannot start at all — new PrismaClient() there fails before its first query. That constraint motivates the schema-and-client workflow in Modeling Data with Prisma, where the declared schema becomes both your SQL and your TypeScript types.

Two environments refine the pattern instead of breaking it. On serverless, "one per process" means one per instance — twenty concurrent functions hold twenty pools, which is exactly the budget you planned earlier, not a leak. And a cold instance pays for its first connection on the first request, so you can move that cost to startup with Next.js instrumentation:

// instrumentation.ts — runs once when the server process boots
export async function register() {
  if (process.env.NEXT_RUNTIME === 'nodejs') {
    const { prisma } = await import('./lib/prisma');
    await prisma.$connect();   // build the pool before traffic arrives
  }
}

Test suites deserve the same thought: runners like Jest or Vitest spawn one worker per test file, so eight parallel workers each holding a full-size pool exhaust a local database just as effectively as hot reload does. Point test environments at a URL with ?connection_limit=1.

Verify the invariant instead of trusting it — run this, reload a dozen times, and run it again:

SELECT count(*) FROM pg_stat_activity WHERE datname = 'shop' AND application_name = 'prisma';

If the number climbed with your reloads, something outside lib/prisma.ts is still constructing clients. The same singleton discipline applies to a hand-rolled pg Pool, which is the object whose sizing rules you just worked through.