Wiring an app to managed Postgres with Prisma or Drizzle

Provisioning a managed Postgres instance takes one command. Everything between that and a deployed application reading from it is where people lose time, and it's the same small set of decisions each time: how the URL gets to the app, whether TLS is actually verified, where migrations run, and how the pool is sized.

This is the whole path with the ORM specifics filled in, using PandaStack as the concrete platform — the shape transfers to any managed Postgres.

The database and its URL

pandastack db create --label acme-prod --size 4g

# The connection URL is on the database record
pandastack db get db_abc123 --json | jq -r .connection_url
# postgres://pandastack:<password>@db_abc123.db.pandastack.ai:5432/pandastack?sslmode=require

Two details in that hostname worth understanding. The per-database subdomain is how connections get routed — a proxy reads the SNI field of the TLS handshake to decide which database VM to forward to, which is why TLS is required rather than optional. There's no non-TLS port to fall back to, and that's deliberate.

The `?sslmode=require` at the end is doing less than it appears to, which brings us to the first real trap.

sslmode=require does not verify anything

In Postgres client terms, `require` means 'encrypt the connection' and nothing more. It does not check that the certificate is valid, that it was issued by a trusted authority, or that it matches the hostname you dialled. An attacker able to intercept the connection can present any certificate and `require` accepts it.

sslmode=disable      no encryption at all
sslmode=require      encrypted, certificate not checked  <-- most common, weakest useful
sslmode=verify-ca    encrypted, certificate chain checked
sslmode=verify-full  encrypted, chain checked, hostname matched  <-- what you want

Use `verify-full` for anything carrying real data. It's a one-word change and it's the difference between encryption and authenticated encryption. `require` is fine for a scratch database in a test; it is not what you want holding customer records.

Getting the URL into the app

Set it as an app environment variable so both the build and the running process see it. Don't commit it, don't bake it into an image, and don't put it in anything with a `NEXT_PUBLIC_` or `VITE_` prefix — those are compiled into client-side JavaScript and would publish your database credentials to every visitor.

DB_URL=$(pandastack db get db_abc123 --json | jq -r .connection_url)
pandastack apps env set acme-api DATABASE_URL "${DB_URL/require/verify-full}"

# Verify it arrived without printing the value into your logs
pandastack apps env list acme-api

Prisma

Prisma's sharp edge is that it has two migration commands with very different behaviour, and running the wrong one in production is destructive.

# LOCAL ONLY. Compares schema to database, can prompt to reset — i.e. drop
# and recreate — and generates new migration files.
npx prisma migrate dev

# PRODUCTION. Applies existing migration files, never generates, never resets.
npx prisma migrate deploy

{
  "scripts": {
    "build": "prisma generate && next build",
    "release": "prisma migrate deploy",
    "start": "next start"
  }
}

`prisma generate` belongs in the build — it reads your schema file and writes a typed client, no database contact required. `prisma migrate deploy` belongs in a release step that runs once, after the build and before the new version takes traffic. Migrating during a build is a genuine hazard: builds are ephemeral, parallel and automatically retried, which is a bad combination for something that mutates shared state.

// src/db.ts — one client, reused. A new PrismaClient per request opens a new
// pool per request, which exhausts the database's connection limit quickly.
import { PrismaClient } from "@prisma/client";

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

That singleton pattern exists because development hot-reload re-executes modules, and without it you accumulate a new client — and a new pool — on every file save until the database refuses connections. It looks like boilerplate; it's load-bearing.

Drizzle

Drizzle is thinner and gives you the connection directly, which means the pool configuration is explicitly yours to get right.

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                          // per app instance — multiply by instances
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 10_000,  // fail fast rather than hanging
});

export const db = drizzle(pool);

npx drizzle-kit generate   # local: write SQL migration files from your schema
npx drizzle-kit migrate    # release step: apply them

Same division as Prisma: generation is a local, committed activity; application is a release step. Commit the generated SQL and read it before it ships — Drizzle's migrations are plain SQL files, which is a genuine advantage when you want to know exactly what will run against your production data.

One more Drizzle-specific note worth stating, because it catches people moving over from Prisma. Drizzle does not manage the connection for you — there is no equivalent of Prisma's implicit client lifecycle, so the pool you construct is the pool your application uses for its entire life. Construct it once at module scope and export it, exactly as above. Constructing it inside a request handler or a route module that gets re-evaluated creates a new pool per invocation, which reaches the database's connection limit surprisingly quickly under any real traffic.

Pool sizing, briefly

The number that catches people: your pool size multiplies by your instance count. Three app instances with `max: 20` is 60 connections, and a small Postgres instance won't be happy about that. Start around 10 per instance, and size the database's `max_connections` against the realistic total including migration runners, cron jobs and any admin tooling.

One platform-specific note: if you enable idle auto-suspend on a database, a pool holding connections open with keepalives will prevent it ever idling. Set `idleTimeoutMillis` so unused connections actually close, or the feature silently never triggers and the only symptom is a bill that doesn't change.

Verifying it end to end

# From your machine: does the URL work at all?
psql "$DATABASE_URL" -c 'select version();'

# From the app, after deploy: does the app see the same database?
pandastack apps exec acme-api -- node -e \
  "require('pg').Pool && new (require('pg').Pool)().query('select current_database(), now()').then(r=>console.log(r.rows))"

# Watch what the database thinks is connected
psql "$DATABASE_URL" -c \
  "select application_name, state, count(*) from pg_stat_activity group by 1,2;"

Setting `application_name` in your connection string is a small thing that pays off during every future incident — `?application_name=acme-api` makes `pg_stat_activity` immediately legible instead of a wall of anonymous backends.

The order that works

1. Create the database, take the connection URL, switch `sslmode` to `verify-full`.
2. Set it as an app environment variable. Never in a public-prefixed variable, never committed.
3. Put client generation in the build, migrations in a separate release step.
4. Use one shared client instance with an explicit pool size, and an idle timeout.
5. Add `application_name` to the URL so future debugging is easier than it needs to be.
6. Verify from inside the deployed app, not just from your laptop — the laptop test proves the database is reachable, not that your app is configured.