PostgreSQL + Prisma in Docker: migrations that are safe to run on every container start

The problem

PostgreSQL is my default database, and I run it in Docker everywhere. I also like the app container to apply migrations by itself when it starts:

• no "did someone run the migration on the server?" question,
• deploys are just "rebuild and restart",
• every environment ends up on the same schema.

With Prisma, that's prisma migrate deploy before starting the app. In practice, three problems got in the way:

1. Production had been migrated by hand. Early on, migration SQL files were run directly with psql. The schema was correct, but Prisma's history table (_prisma_migrations) didn't exist. Prisma saw "database has tables, no migration history" and stopped with error P3005, refusing to do anything.
2. The full migration history didn't belong on the server. Locally, I had a long prisma/migrations folder full of experiments. Production only needed the migrations it hadn't run yet.
3. The Docker build needed a database URL just to generate the Prisma client, even though generating code never connects to a database.

(For a service that uses prisma db push instead of migrations, the safety story is different. It's covered in "Deploys that can't hang and can't lose data".)

The details

What migrate deploy does

prisma migrate deploy:

• reads the migration folders on disk,
• compares them with the rows in _prisma_migrations,
• applies, in order, every migration not yet recorded,
• never resets the database, never asks interactive questions.

That makes it the right command for automation. (migrate dev, by contrast, can ask "you may lose all your data, continue?", which would block a container forever.)

Why P3005 happens

When _prisma_migrations doesn't exist but the database has tables, Prisma can't know which migrations already ran. Applying them all would fail or duplicate objects. So it stops and asks you to baseline the database first.

The challenges

• Baseline once, automatically, and never break a healthy database. The fix must be safe on every start, forever.
• Ship only what's needed. Production should receive the migrations it hasn't run, not my whole local history.
• Custom SQL (functions, deferrable constraints, triggers) that Prisma can't express must also run after migrations.
• Any failure must stop the app. An app running on a half-migrated schema is worse than an app that's down.
• Builds must not need infrastructure. The image build shouldn't require a running database.

The solution

1. A startup script with numbered steps

The container runs one script, step by step, and stops at the first failure:

#!/bin/sh
# If any step fails, `set -e` stops here and the app does NOT start.
# Better a container that does not boot than an app running against
# a half-migrated database.
set -e

PROD_CONFIG="prisma.prod.config.ts"

echo "[entrypoint] 1/5 checking Prisma migration history..."
node prisma/baseline.js

echo "[entrypoint] 2/5 applying database migrations..."
yarn prisma migrate deploy --config "$PROD_CONFIG"

echo "[entrypoint] 3/5 generating Prisma client..."
yarn prisma generate

echo "[entrypoint] 4/5 applying SQL functions..."
node prisma/apply-functions.js

echo "[entrypoint] 5/5 starting the app..."
exec yarn start:prod

Details that matter:

• set -e: any failing command stops the script, so the app never starts on a broken schema.
• Numbered log lines: docker logs shows exactly which step failed (3/5 …).
• exec for the last step: the app replaces the shell, so Docker's stop and restart signals reach Node.js directly and shutdowns are clean.
• Run through sh in the Dockerfile (CMD ["sh", "docker-entrypoint.sh"]), so the script doesn't need the executable bit. That avoids a classic failure when the repository is cloned on Windows.

2. The baseline: an empty history table

Step 1 fixes P3005 with the smallest possible change:

Create _prisma_migrations as an empty table. That's all.

Empty means "no migration has been applied through Prisma yet", which is true for a hand-migrated database. Prisma stops complaining and applies only what's in the production migrations folder (next section).

The script is "smart" about when to do nothing:

It never touches application data, and it's safe to run on every container start, forever. The table is created with exactly the shape Prisma itself uses, copied from a database Prisma created, so Prisma accepts it as its own.

Why not mark old migrations as "applied" instead (the usual baselining approach)? Because their folders aren't shipped to production, so Prisma never needs to know about them.

3. A production-only migrations folder

Locally, prisma/migrations holds the full development history, and it's git-ignored. What's committed and shipped is a separate folder, prisma/prod-migrations, containing only migrations that still have to run in production. A dedicated config file points Prisma at it:

// prisma.prod.config.ts
export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: { path: 'prisma/prod-migrations' },
});

Once a migration has run on every server, its folder can be deleted from prod-migrations. That's safe: migrate deploy doesn't complain about migrations that are in the history but no longer on disk (I tested that before relying on it). The production folder stays small and readable: it's literally "what's pending".

Each production migration is additive whenever possible (a new nullable column, a new table), so it can't break running code or lose data.

4. Custom SQL after migrations

Some database features can't be expressed in the Prisma schema: PL/pgSQL functions, triggers, deferrable constraints. They live as SQL files in prisma/functions/, numbered to run in order, written to be idempotent (CREATE OR REPLACE, DROP … IF EXISTS), and applied by step 4 on every start. A fresh database, a CI database and production all end up identical.

5. Build without a database

Prisma's config file can require DATABASE_URL just to load, even for prisma generate, which only reads the schema and never connects. So the build used to fail without a database. The fix is a placeholder at build time only:

# prisma.config.ts requires DATABASE_URL to be set just to load, even for
# `generate`, which never connects to a database. This placeholder satisfies
# that check at build time; the real value is injected at runtime.
ARG DATABASE_URL=postgresql://build:build@localhost:5432/build
ENV DATABASE_URL=$DATABASE_URL

RUN yarn prisma generate
RUN yarn build

At runtime, Compose provides the real value through env_file: or environment:, which overrides the placeholder. The image never contains real credentials.

6. PostgreSQL details I always set

postgres:
  image: postgres:16-alpine          # pinned major version, small image
  restart: unless-stopped
  environment:
    POSTGRES_USER: ${POSTGRES_USER}
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    POSTGRES_DB: ${POSTGRES_DB}
  volumes:
    - postgres_data:/var/lib/postgresql/data
  healthcheck:
    test: ['CMD-SHELL', 'pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}']
    interval: 5s
    timeout: 5s
    retries: 10

• Pinned major version (16-alpine, not latest). A PostgreSQL major upgrade changes the data format; it must be a deliberate migration, never a surprise on docker compose pull.
• Alpine for a smaller image.
• A named volume for the data (see "Where the data lives").
• A health check, so the app (and its startup script) only starts when PostgreSQL really accepts connections (see "One command, a whole stack").
• Credentials from environment variables, never hard-coded in a committed file for production.

Put together, a deploy is simply:

docker compose up -d --build

PostgreSQL starts and becomes healthy → the app container starts → baseline check → pending migrations → client generation → custom SQL → app running. And if anything in that chain fails, the logs say exactly which step, and the app stays down until it's fixed.

Lessons learned

• Automate migrations on start, but use migrate deploy. It's non-interactive and never resets data.
• Fail closed. set -e and a stopped container beat an app on a half-migrated database.
• Baseline hand-migrated databases with an empty history. Smallest change, safe forever.
• Ship pending migrations, not your history. A production-only folder keeps deploys focused and readable.
• Delete what has run everywhere. migrate deploy tolerates missing old folders.
• Keep custom SQL idempotent and ordered. Run it after migrations on every start.
• Builds shouldn't need infrastructure. A build-time placeholder URL keeps prisma generate happy.
• Pin PostgreSQL's major version. Database upgrades should never happen by accident.
• exec your app process. Signals reach Node.js and shutdowns stay clean.

FAQ

How do I fix Prisma error P3005?

P3005 means the database isn't empty but has no Prisma migration history. Baseline it: either mark existing migrations as applied with prisma migrate resolve, or (if you don't ship the old migrations) create an empty _prisma_migrations table so migrate deploy only applies new migrations.

Should I run prisma migrate deploy when my Docker container starts?

It's a common and practical approach, as long as you use migrate deploy (non-interactive), stop the app if migrations fail, and avoid running several app replicas that migrate at the same moment without coordination.

Why does prisma generate need DATABASE_URL during docker build?

Some Prisma config setups read DATABASE_URL when loading, even though generate never connects. Provide a placeholder build argument; the real URL is supplied at runtime.

Should I use postgres:latest in Docker Compose?

No. Pin at least the major version (for example postgres:16-alpine). Major PostgreSQL upgrades change the on-disk format and need a planned migration, not an automatic image update.