How to use Drizzle ORM with a managed Postgres database

Drizzle's pitch is that it is a thin typed layer over SQL rather than an abstraction that hides it. In practice that means the ORM itself is rarely where you get stuck. You get stuck on TLS, on connection pooling, and on the ten seconds during a deploy when the migration has run but the old code is still serving. This is a walkthrough of all three against a managed Postgres.

I'm Ajay; I build PandaStack, which runs managed Postgres in dedicated microVMs. The specifics below use our connection format, but everything except the branching section applies to any managed Postgres that terminates TLS.

Connecting, and the certificate error you will hit

Create a database and you get a connection URL. TLS is required, and routing is by SNI, which means the hostname in the URL is not decoration — it is how the connection is routed to your database at all.

curl -X POST https://api.pandastack.ai/v1/databases \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label":"acme-prod","size":"4g"}'

# create is synchronous but slow (30-90s) — it returns when Postgres is ready
# postgres://pandastack:<pw>@<id>.db.pandastack.ai:5432/pandastack

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

const client = postgres(process.env.DATABASE_URL!, {
  ssl: "require",
  max: 10,
  idle_timeout: 20,
  connect_timeout: 10,
});

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

The `max: 10` is the other setting worth thinking about rather than copying. Postgres connections are not free — each one is a backend process with its own memory — and a 1 GiB instance does not want a hundred of them. If you run several application instances, multiply: four instances at `max: 10` is forty connections, and that is what the server sees.

When you need a pooler and when you do not

The classic advice is "always use a pooler", and it comes from a serverless world where every request might open its own connection. If your application is a long-lived process holding a client-side pool, you are already pooling and a second layer buys you little.

It becomes necessary when connection count is unbounded by the application's shape: a per-request execution model, a fleet that scales out under load, or a lot of short-lived jobs. If you add a transaction-mode pooler, know what it takes away — session-scoped features stop working. Prepared statements, `LISTEN`/`NOTIFY`, session-level advisory locks, temporary tables and `SET` that is expected to persist beyond a transaction all break in ways that are confusing because they work fine in development against a direct connection.

// through a transaction-mode pooler, turn off prepared statements
const client = postgres(process.env.DATABASE_URL!, {
  ssl: "require",
  prepare: false,
});

Migrations on deploy: the part that bites

Drizzle Kit generates SQL from your schema file and applies it. The generation step is uncontroversial. Where deploys go wrong is the applying step, and specifically its interaction with a zero-downtime rollout.

npx drizzle-kit generate   # writes SQL into ./drizzle
git add drizzle/ && git commit -m "migration: add orders.status"
npx drizzle-kit migrate    # applies pending migrations

Our deploys are blue-green: a new instance is built and health-checked while the old one keeps serving, and traffic flips atomically at the end. That means there is a window — from when the migration runs to when the flip happens — where the old code is talking to the new schema. Any migration that is not backwards compatible produces errors in that window, on live traffic, from code that was working perfectly a second ago.

The fix is the expand-and-contract discipline, and it costs you one extra deploy per destructive change.

1. Expand. Add the new column as nullable, or add the new table. Deploy. Old code ignores it; new code can write it.
2. Backfill and dual-write. New code writes both old and new shapes. Backfill the historical rows in batches, not in one statement.
3. Migrate reads. A deploy where the application reads the new column. The old one is still there, still populated.
4. Contract. Once nothing reads the old column, a separate deploy drops it.

Renaming a column in one migration is the single most common way to take a zero-downtime deploy and give it thirty seconds of 500s. Drizzle will happily generate that migration for you. It is not the ORM's job to know your rollout strategy.

Where to run the migration

Run it as a build step, not at application start. Starting N instances that each run migrations at boot is a race: they contend on the migrations table, and the loser either blocks for the duration or fails its health check and gets restarted into the same race.

{
  "type": "node",
  "install": "npm ci",
  "build": "npm run build && npx drizzle-kit migrate",
  "start": "node dist/server.js"
}

Putting it in the build phase gives you one more thing: a migration failure fails the deploy before any traffic moves. The old instance keeps serving, you read the build log, you fix the migration. That is a much better failure than a half-migrated database and a restart loop.

Testing against a real database instead of a mock

The best thing about a managed Postgres that supports branching is that "test against production's schema" stops being an aspiration. A clone gives you a new, independent database from the source's archive — including a point-in-time clone, if you want the state from before something went wrong.

# branch prod into a throwaway database for this test run
NEW=$(curl -sS -X POST \
  "https://api.pandastack.ai/v1/databases/$PROD_DB_ID/clone" \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label":"ci-'"$CI_RUN_ID"'","size":"1g"}' | jq -r '.id')

# clone is async: 202 now, poll until running
until [ "$(curl -sS -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  "https://api.pandastack.ai/v1/databases/$NEW" | jq -r .status)" = running ]; do
  sleep 5
done

This is also how I would test a scary migration. Clone production, run the migration against the clone, time it, look at what it locked, throw the clone away. The source database is untouched — a clone reads from the archive, not from the live instance — so there is no risk to production in finding out that your index build takes eleven minutes and takes an `ACCESS EXCLUSIVE` lock while it does.

One behaviour to know about: idle suspend

Databases suspend when idle and wake on the next connection. For an application this is invisible and it is why an idle environment costs almost nothing. For a test suite it means the very first connection of a run can be slower than the rest.

If your suite has an aggressive connection timeout, raise it for the first connection or add a warm-up query in global setup. This is a one-line fix that I have watched people spend an afternoon on, because "the first test in the file is flaky" does not sound like a connection-timeout problem.

// global-setup.ts — wake the database before the suite starts timing things
import postgres from "postgres";

export default async function () {
  const sql = postgres(process.env.DATABASE_URL!, {
    ssl: "require",
    connect_timeout: 30,
  });
  await sql`select 1`;
  await sql.end();
}