VENNStart
Guide

Where prisma migrate deploy should run in CI/CD

My Prisma migrate deploy step was committed and green, and production never ran it. Where migrations belong in CI/CD, and how to spot a lagging schema.

Lê Anh Tuấn4 min read

The API behind my post scheduler (NestJS and Next.js in a pnpm monorepo) answered every request with "The database rejected that request". The deploy was green, but the schema hadn't moved, because the pipeline's migrate step kept skipping itself.

The migrate job that skipped itself

There was no prisma/migrations folder yet. The schema went out with prisma db push from a GitHub Actions job that printed a notice and exited 0 when the database secret was missing. The repo had no secrets, and Render (the API) and Vercel (the frontends) deployed on their own anyway.

For a while the step would have failed even with the secret, because Prisma 7 removed the --skip-generate flag it still used. When a step only runs if a secret exists, "never ran" and "broken" look the same. In the end production was behind by 9 tables, 11 columns and one enum value.

A partly stale schema doesn't turn anything red

A nearly empty database crashes the API at boot with P2021 and the deploy goes red. Partial drift is quieter. Prisma selects every scalar field by default, so one missing column on User breaks every User query, login included, with P2022. Our exception filter turned unknown Prisma codes into that "rejected" sentence.

To check, ask the database. prisma migrate status exits 1 when anything is pending or failed, but only compares history. migrate diff with --exit-code compares the real tables and returns 2 on drift.

Only migrate deploy belongs in a deploy

prisma migrate dev is for your machine. It uses a shadow database, creates new migrations and may offer to reset, and Prisma's docs say never to run it against production. prisma migrate deploy applies pending committed migrations in order and never resets. prisma db push writes the difference straight in, and once its data-loss warning is accepted it drops any column the current branch doesn't know about. With three git worktrees on one dev database, that risk was real for me.

Moving off db push, 0_init describes a schema the database mostly has already, and migrate deploy refuses with P3005. You have to baseline:

# generate the baseline from the current schema
pnpm exec prisma migrate diff --from-empty \
  --to-schema prisma/schema.prisma --script \
  -o prisma/migrations/0_init/migration.sql

# once per existing database; this runs no SQL against your tables
pnpm exec prisma migrate resolve --applied 0_init

# history check: exit 1 if anything is pending or failed
pnpm exec prisma migrate status

# drift check: real tables vs schema, exit 2 if they differ
pnpm exec prisma migrate diff --from-config-datasource \
  --to-schema prisma/schema.prisma --exit-code

resolve --applied only writes a row into _prisma_migrations. If the database is really missing parts of 0_init, migrate status still reports from that row. The drift check catches it. Diffing 0_init on a scratch database left one harmless mismatch, a default Postgres stores as (gen_random_uuid())::text with brackets.

Migrate in the API package's build script

My first fix put db:deploy in the build command in render.yaml, and production never ran it. A Render service keeps the build command it was created with, and a blueprint change only gets to it when the blueprint is synced. My fix for a silently skipped step was silently skipped too, which I still find funny.

Now it runs from the API package's build script. The current command, the stale one stored on the service and the root pnpm build all end in pnpm --filter @app/api build.

// scripts/migrate-on-build.mjs (trimmed; the real one also retries, see below)
import { spawnSync } from 'node:child_process';

if (process.env.SKIP_DB_MIGRATE === '1') {
  console.log('[migrate] SKIP_DB_MIGRATE=1, skipped on purpose.');
  process.exit(0);
}

const url = process.env.DATABASE_URL?.trim();
if (!url) {
  // CI or a fresh clone: no database, nothing to migrate. Say it out loud.
  console.log('[migrate] No DATABASE_URL, skipping. This is not a deploy.');
  process.exit(0);
}

const result = spawnSync('prisma', ['migrate', 'deploy'], {
  env: { ...process.env, DATABASE_URL: directUrl(url) }, // drops "-pooler", see below
  stdio: 'inherit',
});

if (result.status !== 0) {
  console.error('[migrate] FAILED. Stopping before new code meets an old schema.');
  process.exit(result.status ?? 1);
}

A failed migration fails the build before nest build. I'd take a red deploy over a green one serving P2022. Without a DATABASE_URL the script says so out loud. CI has no database, and the API build I added there exercises the skip path.

The advisory lock doesn't survive PgBouncer

The first real build failed with P1002 after 10 seconds, the fixed timeout on migrate deploy's advisory lock. That lock belongs to a session, and our DATABASE_URL pointed at Neon's -pooler endpoint, PgBouncer in transaction mode, where statements can hit different connections. The script now migrates through the direct endpoint (DIRECT_DATABASE_URL, or the host without -pooler).

The next build used the direct host and still hit P1002. I guess the attempt through PgBouncer left a session-level lock behind. The script now makes three attempts, 20 seconds apart, when the output says P1002 or mentions the lock, then prints a query to find the holder:

SELECT a.pid, a.usename, a.state, a.query_start, left(a.query, 60)
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'advisory';

-- once you're sure it's a dead build's session:
SELECT pg_terminate_backend(<pid>);

Where migrate deploy should run

A separate CI job only works if code can't get to production without it, which means failing red on a missing secret and turning off the platform's auto-deploy. I migrate inside the build on Render instead, and no deploy route gets around it, at the cost of database credentials in the build. A second service building the same package gets SKIP_DB_MIGRATE=1 to stay off the lock.

Running it on container start, as I did in getting my Express API ready for production, makes replicas that boot together queue on the lock and crash after 10 seconds.

On EC2, nobody migrates for you

Production later moved to EC2 and RDS with Docker Compose. The image is built in GitHub Actions without a DATABASE_URL, and the script skips the migration as written. The runbook runs it on the server with the pulled image, matching the Prisma version to the code:

cd /srv/app

# what's pending? (exits 1 when something is, which isn't an error)
docker compose -f deploy/docker-compose.yml --env-file deploy/.env \
  run --rm --no-deps api pnpm --filter @app/api exec prisma migrate status

# apply
docker compose -f deploy/docker-compose.yml --env-file deploy/.env \
  run --rm --no-deps api pnpm --filter @app/api db:deploy

That's a manual step, the very thing I complained about, and health checks return 200 with the database degraded. I haven't added a migrate step to the deploy job yet.

Migrations have to work with the old code

Wherever the step goes, the new schema and the old code overlap for a while. The EC2 runbook even deploys before it migrates, and I haven't moved it to pull, migrate, then up -d yet.

The pattern is expand and contract. Add the column as nullable and backfill it, deploy code that uses it, and drop the old one in a later release. To old code, a renamed column is a dropped column. Here, the post-level firstComment moved to each publishing target:

-- expand: nullable, so the old code can keep inserting targets
ALTER TABLE "PostTarget" ADD COLUMN "firstComment" TEXT;

-- backfill: every target inherits its post's comment
UPDATE "PostTarget"
SET "firstComment" = "Post"."firstComment"
FROM "Post"
WHERE "Post"."id" = "PostTarget"."postId"
  AND "Post"."firstComment" IS NOT NULL;

Post.firstComment stays as the fallback for targets from older clients until the contract step, which hasn't happened yet. For SQL Prisma won't write for you, like that backfill, run migrate dev with --create-only and edit the file before applying it.

  • Prisma
  • PostgreSQL
  • CI/CD
  • Database migrations
  • NestJS
  • DevOps

I'm Tuấn (Venn), a web developer in Ho Chi Minh City. I mostly build Next.js sites and web apps, and I take freelance work.

Work with me

More posts

  1. 01Guide4 min read

    Deploying Next.js and NestJS to AWS EC2 with Docker Compose

    How I deploy a pnpm monorepo to one EC2 box. Images get built in GitHub Actions and Docker Compose pulls them from GHCR. The server has no code.

  2. 02Guide4 min read

    How I store OAuth tokens for social media APIs in NestJS

    OAuth for Facebook, TikTok and YouTube in a NestJS API. State and PKCE, encrypted tokens, refreshing before each publish, and when to ask for a reconnect.

  3. 03vennee.dev3 min read

    llms.txt on my Next.js site, and now Lighthouse grades it

    vennee.dev has a hand-written llms.txt and an llms-full.txt built from the blog. Notes on the spec, .txt routes in Next.js, and Lighthouse 13's new audit.