Our whole database client is twenty lines, and three of its four options exist because of the connection pooler

The Notifio website is a Next.js app on Vercel with a Postgres database behind it. The database does very little: it stores licences, and that is all. The client that talks to it is twenty lines long, and almost every line is a concession to the thing sitting between the app and Postgres.

The whole file

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

declare global {
  var _pgClient: postgres.Sql | undefined;
}

// Reuse connection in dev hot-reload, create fresh in production
const client =
  global._pgClient ??
  postgres(process.env.DATABASE_URL!, {
    prepare: false,
    max: 1,
    idle_timeout: 30,
    connect_timeout: 10,
  });

if (process.env.NODE_ENV !== "production") {
  global._pgClient = client;
}

export const db = drizzle(client, { schema });

Four options and a global. Each one has a reason, and two of them are not the reason you would guess.

prepare: false, or the error that only happens under load

postgres-js creates prepared statements automatically. Its own options table lists prepare: true as the default, described as "automatic creation of prepared statements", and the README is explicit that you turn it off "when using PGBouncer in transaction mode".

Supabase's transaction-mode pooler listens on port 6543 and hands your connection back to the pool after every transaction. Its connection guide states the consequence plainly: "Transaction mode does not support prepared statements. To avoid errors, turn them off in your connection library."

The failure mode is what makes this worth writing down. A prepared statement is named and lives on a backend connection. Your client prepares stmt_1, the pooler returns that connection to the pool, your next request gets a different backend that has never heard of stmt_1, or worse, gets one where another request has already created a stmt_1 of its own. You get prepared statement "stmt_1" already exists. On a quiet afternoon with one request at a time you may never see it. It arrives the first time two requests overlap, which is to say the first time it matters.

max: 1, and the transactions this app does not have

The obvious reading of max: 1 is cost control: serverless means many short-lived instances, each with its own pool, and the pooler is already doing the multiplexing. Supabase makes the same point, that your library "keeps its own pool of connections, separate from the poolers Supabase runs, and its defaults assume a persistent backend". postgres-js defaults to max: 10. Ten per instance across however many instances Vercel decides to run is a pool size nobody chose.

The postgres-js README adds a second effect worth knowing about:

There are no guarantees about queries executing in order unless using a transaction with sql.begin() or setting max: 1.

That caveat is about queries issued concurrently without awaiting; a sequence of awaited queries behaves as you expect either way. It is still the right pin here, because of something I only noticed while writing this post: there is not a single transaction in this codebase. The Stripe webhook, which is the one handler where two requests can genuinely collide, does its own thing instead.

Its job is to turn a completed Checkout Session into a licence row, exactly once, under redelivery. It reads by session id, then by email, then inserts, and the insert is wrapped in a catch that does not assume the error is fatal:

try {
  await db.insert(licenses).values({ id: createId(), email, token, stripeSessionId: session.id, active: true });
} catch (err) {
  // Concurrent webhook won the race and inserted first. Fall back to
  // the row that won so we still deliver a valid token.
  const [row] = await db.select().from(licenses).where(eq(licenses.email, email)).limit(1);
  if (!row) throw err; // genuine DB error, so 500 and Stripe retries
  token = row.token;
}

UNIQUE(email) is what makes the race safe, and the re-select is what makes losing it harmless: the loser delivers the winner's token, so the customer gets a working activation either way. The if (!row) throw err matters as much as the fallback, because swallowing a real database error here would hand somebody a success response and no licence.

That is the shape transaction-mode pooling likes. Correctness carried by constraints and idempotent retries needs no session state, which is precisely what the pooler refuses to keep.

The global is only for the dev server

In development, Next.js reloads modules on every edit. Without the global, every save would construct another postgres() client, each holding its connection, and after an hour of work you have exhausted your own connection limit from a laptop. Hence the global._pgClient cache, guarded so production never takes that branch: in production the module is evaluated once per instance anyway, and a global would just be a slower way of spelling a module-level constant.

The declare global block above it is there purely so TypeScript tolerates the property. It is a lint suppression wearing a type declaration's clothes, and I would rather have it visible at the top of the file than scattered as casts.

Two more things transaction mode takes away

The Supabase docs list four casualties of transaction mode, not one, and the other three are the kind of thing you discover in production:

• Query pipelining is not supported, because the pooler releases the connection as soon as it sees one reply finish, even if the client has already queued more work on it.
• Session-level state is lost between transactions: SET, session-level advisory locks, LISTEN / NOTIFY, and temporary tables.
• Prepared statements, covered above.

None of those appear in this codebase, and that is not luck, it is the schema. The entire database is one table:

export const licenses = pgTable("licenses", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  token: text("token").notNull().unique(),
  stripeSessionId: text("stripe_session_id").notNull().unique(),
  active: boolean("active").notNull().default(true),

  /** Has this licence been upgraded to include auto-reply? Permanent once true. */
  autoReply: boolean("auto_reply").notNull().default(false),
  autoReplySessionId: text("auto_reply_session_id").unique(),
  autoReplyPurchasedAt: timestamp("auto_reply_purchased_at", { withTimezone: true }),

  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

There is no recurring billing, so there is no billing state to track over time. Everything the pricing page sells is a one-time purchase, which means "is this unlocked?" is a column read rather than a date comparison against a period end. A schema with nothing to coordinate is a schema that does not miss LISTEN.

Four of those columns are unique, and that is the same bet as above: auto_reply_session_id is unique so a redelivered webhook for the upgrade cannot be recorded as a second purchase. The constraint is the idempotency, not the code around it.

And when the database is simply not there

The last two options, idle_timeout: 30 and connect_timeout: 10, are the ones that decide how a bad day looks. A ten second connect timeout on a serverless function means a database that is down produces a refused request in ten seconds rather than a function that runs to its own limit.

Below that, there is a switch for the case where the database is deliberately unavailable, because we have moved it once. A proxy (what used to be called middleware in Next.js) answers every API route that needs the database with a 503 and a Retry-After, and allows through the three that do not:

const NO_DATABASE_ROUTES = ["/api/download", "/api/contact", "/api/webhooks/resend"];

function needsDatabase(pathname: string): boolean {
  return !NO_DATABASE_ROUTES.some(
    (route) => pathname === route || pathname.startsWith(`${route}/`)
  );
}

The allowlist points the right way round. A route added next month is refused by default rather than quietly trying to reach a database that is not answering. The three exceptions earn their place: downloads are presigned R2 links and never touch Postgres, so the download page keeps working through the whole window, and the contact form on the help page keeps working too, which matters most precisely when something is broken.

A 503 with Retry-After rather than a 500 is the other half. The desktop app reads it as "try later" instead of "your licence is invalid", and Stripe redelivers its webhooks once the switch is off again, so no purchase is lost to a maintenance window.

Takeaways

• If your connection string ends in 6543, set prepare: false before you ship, not after you see prepared statement already exists.
• max: 1 in serverless is about your own pool size, not the pooler's. Your driver's default was chosen for a long-lived server.
• Check all four things transaction mode removes, not just prepared statements. If your code uses LISTEN, advisory locks or temp tables, the pooler is the wrong one.
• Carry correctness in unique constraints and idempotent retries rather than session state, and transaction-mode pooling stops being a constraint you work around.
• Decide what your app does with no database at all before you need to find out, and make the list of routes that survive an allowlist, not a denylist.