How to run database migrations on every deploy

Automating migrations is the easy half — one command in the right place and it runs on every deploy. The hard half is that a zero-downtime deploy runs the old application code against the new schema for a window of seconds to minutes, and almost every migration outage traces back to someone forgetting that.

So: where the migrate step goes, why the overlap window dictates what you're allowed to change, and the specific statements that will take a lock and stall your API.

Where the migrate step goes

Three places are plausible, and two of them are wrong often enough to be worth naming.

In the app's start command is the worst option. If you run more than one instance they race; if the migration fails the app crash-loops; and if you ever scale to two replicas you'll apply the same migration twice concurrently. It works right up until it doesn't, and it fails at the exact moment you were scaling up.

In CI, before the deploy, is defensible but has a nasty failure mode: CI applies the schema change, the deploy then fails for an unrelated reason, and now production is running old code against a new schema with nothing to roll it back.

As part of the build step of the deploy that needs it is the one I'd default to. It runs once, in the same context that's about to ship the code, with the same environment, and a failure fails the deploy before any traffic moves. On PandaStack, that means putting it in the build command:

{
  "type": "node",
  "installCommand": "npm ci",
  "buildCommand": "npm run build && npx prisma migrate deploy",
  "startCommand": "node dist/server.js"
}

Or set it on the app directly, which is the same thing without committing a manifest:

curl -X PATCH https://api.pandastack.ai/v1/apps/$APP_ID \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"build_command":"npm run build && npx prisma migrate deploy"}'

# App runtime: pooled, port 6432
DATABASE_URL=postgres://pandastack:<pw>@<id>.db.pandastack.ai:6432/pandastack?sslmode=require
# Migrations: direct, port 5432
DIRECT_URL=postgres://pandastack:<pw>@<id>.db.pandastack.ai:5432/pandastack?sslmode=require

The overlap window is the whole problem

A blue-green deploy provisions the new version alongside the old one, builds it, health-checks it, and only then flips traffic. That's what makes the deploy zero-downtime — and it's also why the old code and the new schema coexist. If your migration runs during the build, the old version is still serving every request against a database that has already changed.

Which gives you exactly one rule, from which everything else follows: every migration must be backwards-compatible with the code currently running.

Not backwards-compatible with the code you're deploying. With the code already deployed. That's what makes the following, entirely reasonable-looking migration an outage:

-- Deploy 47: rename the column, update the code to match.
ALTER TABLE users RENAME COLUMN email TO email_address;

The instant that commits, every SELECT email FROM users from the still-running old version throws. You get a burst of 500s that lasts until traffic flips, and if the new version fails its health check you get 500s until someone notices.

Expand and contract, in three deploys

The fix is to split any breaking change into additive steps, each safe on its own, spread across deploys. It feels bureaucratic for a column rename. It is also the only version that doesn't page anyone.

Deploy 1 — expand. Add the new thing. Nothing reads it yet, and the old code doesn't know it exists.

ALTER TABLE users ADD COLUMN email_address text;

-- Backfill in batches, outside the migration, so you never hold a long
-- transaction over a large table.
UPDATE users SET email_address = email
WHERE id IN (
  SELECT id FROM users WHERE email_address IS NULL LIMIT 5000
);

Deploy 2 — migrate the code. The application writes both columns and reads the new one. Old rows are already backfilled; new rows are consistent either way. Nothing about the schema changes in this deploy, which means it can be rolled back freely.

await db.user.update({
  where: { id },
  data: { email: next, email_address: next },   // dual-write during the transition
});

Deploy 3 — contract. Once no running code touches the old column, drop it. This is the deploy people postpone for six months, which is fine — an unused column costs almost nothing, and a premature drop costs an outage.

ALTER TABLE users DROP COLUMN email;

The same three-step shape covers every breaking change: adding a NOT NULL column (add nullable, backfill, add the constraint), changing a type (new column, dual-write, swap, drop), splitting a table, tightening an enum. Additive, then code, then destructive.

The four statements that will lock your table

The second category of migration outage isn't compatibility — it's locking. Postgres takes an ACCESS EXCLUSIVE lock for many DDL statements, and while that lock is held, every query on the table queues behind it. On a busy table it reads as a total outage even though nothing failed.

1. CREATE INDEX without CONCURRENTLY. Blocks writes for the whole build. Always use CREATE INDEX CONCURRENTLY on a live table — and note it cannot run inside a transaction, so most migration tools need an explicit escape hatch for it.
2. ADD COLUMN ... NOT NULL DEFAULT ... — safe and instant on Postgres 11 and later for a constant default, but a full table rewrite with a volatile default like now(). Know which one you wrote.
3. ALTER COLUMN TYPE. Rewrites the table and every index on it. Use the expand-and-contract shape with a new column instead.
4. ADD FOREIGN KEY. Takes locks on both tables while it validates every existing row. Add it NOT VALID, then VALIDATE CONSTRAINT as a separate statement — validation takes a much weaker lock.

-- Two-step foreign key: the second step doesn't block writes
ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id)
  REFERENCES users(id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

And the one setting that converts a lock-related outage into a failed deploy, which is a far better outcome:

SET lock_timeout = '3s';
SET statement_timeout = '60s';

Without lock_timeout, a DDL statement that can't get its lock waits — and because it is queued for an ACCESS EXCLUSIVE lock, every subsequent query on that table queues behind the waiter. One long-running SELECT can therefore convert your ALTER TABLE into a site-wide stall. With a three-second timeout the migration fails, the deploy fails, and you retry once the long query is gone.

Rehearse it against real data

A migration that takes 40ms against your seed database can take 40 minutes against production, because the cost is a function of row count and index size and your seed script made fifty rows. Staging with synthetic data does not tell you this.

The cheap way to find out is to branch the production database and run the migration against the branch. A branch is a real, writable copy that diverges copy-on-write from its parent, so you can apply the migration, time it, and throw the branch away — production never notices.

# Branch production, then migrate the branch and time it
curl -X POST https://api.pandastack.ai/v1/databases/$PROD_DB_ID/branch \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label":"migration-rehearsal-047"}'
# 202 -> poll GET /v1/databases/<new-id> until "running", then:

time psql "$BRANCH_URL" -f migrations/047_add_email_address.sql

Two things to look for beyond the wall-clock number: whether the statement took a lock that would have blocked writes, and whether the query plans your application depends on changed. The second one bites when a migration adds an index the planner then prefers, on a table where it's the wrong choice.

When a migration fails mid-deploy

Because the migration ran during the build, a failure means the deploy never flipped and the old version is still serving. That's the good case, and it's the reason to put the step there. But the database may be in a partial state, and how partial depends on your tool.

Postgres supports transactional DDL, so a migration wrapped in a single transaction either fully applies or fully rolls back, and most tools do this per migration file. The exceptions are the statements that can't run in a transaction — CREATE INDEX CONCURRENTLY, most notably — which is precisely why those need their own file with nothing else in it.

• One logical change per migration file, so a failure has an obvious boundary.
• Never edit a migration that has been applied anywhere. Add a new one. Editing history is how two environments quietly diverge.
• Keep migrations forward-only in production. A down migration you wrote six months ago and never ran is not a rollback plan.

That last one deserves saying plainly. Rolling code back is trivial and instant. Rolling a schema back is neither, and the reason expand-and-contract is worth the ceremony is that it makes code rollback safe on its own — deploy 2 can be reverted to deploy 1 at any moment, because the schema at that point is compatible with both.

The rules, condensed

1. Run migrations in the build step of the deploy that needs them, never in the start command.
2. Use the direct connection string, not the pooled one.
3. Every migration must be compatible with the code that is already running, not just the code you're shipping.
4. Split breaking changes into expand, code, contract — three deploys, each safe alone.
5. CREATE INDEX CONCURRENTLY, foreign keys as NOT VALID then VALIDATE, and never ALTER COLUMN TYPE on a big table.
6. Set lock_timeout so a blocked migration fails fast instead of stalling the site.
7. Rehearse against a branch of production, and time it.