Fixing Prisma Shadow Database Errors on Hosted Postgres

Monday morning, the agent adds a column to the intake app's schema � droppedOffAt, one field � and runs prisma migrate dev like it has a hundred times. This time:

Error: You are trying to create a shadow database, but you do not have
the permission to do so. Please check the docs.

Everything about this error is annoying: it worked locally all week, it only appears against the hosted Postgres, and the docs that explain it are spread across three pages. Here's the one walkthrough I wish existed when the shop's database moved to a hosted instance � both failure modes, in the order you'll meet them.

What the shadow database even is

prisma migrate dev doesn't run your migrations against your dev database blind. First it spins up a throwaway shadow database, replays your entire migration history into it from scratch, compares the result to your schema, and only then generates the new migration. It's how Prisma detects drift and catches migrations that would fail before they touch anything real.

Creating that throwaway database requires the CREATEDB privilege. Local Docker Postgres: you're superuser, so it just works � which is exactly why the prisma migrate dev shadow database problem only shows up the day you point DATABASE_URL at Supabase, Neon, Heroku, RDS, or any managed Postgres where your user can create tables but not databases. Two failure modes follow.

Failure mode 1: the shadow database cannot be created

The error above (or its sibling, a P1003 "database does not exist" aimed at a _shadow suffix). Your user lacks CREATEDB, and on most managed platforms no support ticket will grant it. Two real fixes:

Fix A � create the shadow database yourself and point Prisma at it:

-- connect to the hosted server as a user who can, or use the panel:
CREATE DATABASE shop_shadow;
GRANT ALL PRIVILEGES ON DATABASE shop_shadow TO app_user;

datasource db {
  provider             = "postgresql"
  url                  = env("DATABASE_URL")
  shadowDatabaseUrl    = env("SHADOW_DATABASE_URL")
}

The shadow database must be a database your user fully owns � Prisma drops and recreates its contents on every run � but it can be tiny and it can live anywhere reachable, including a second container on your own box. SHADOW_DATABASE_URL gets the full connection string to it.

Fix B � develop locally, deploy only: point dev at a local Docker Postgres (superuser again, shadow works), and against the hosted database run only:

npx prisma migrate deploy

migrate deploy applies migration files without generating anything, which means it never touches a shadow database at all. That's also the only command production should ever see.

Failure mode 2: migrations "failed to apply cleanly" to the shadow database

Error: P3006: Migration `20260914_add_customer_notes` failed to apply
cleanly to the shadow database.

This one feels like gaslighting: your dev database is fine, the migration ran months ago, and now Prisma says it's broken. The explanation: the shadow database replays your whole history from zero, so it executes every migration file as if for the first time. A migration that was hand-edited after it shipped, generated against a different provider, mangled by a merge conflict, or references a table that a later manual change already removed � the dev database forgave all of that when it happened. The replay from scratch does not.

The error names the migration, so open it. Then:

• Trivially broken SQL (a leftover conflict marker, a duplicated index name)? Fix the file � the shadow replay validates your history for free.
• History that's long or genuinely tangled? Squash it. This is the point where squashing is the real fix, not procrastination:

npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql

Archive the old folder, commit 0_init as the new baseline, and mark it as applied on databases that already have the schema:

npx prisma migrate resolve --applied 0_init

A history that replays cleanly is worth more than a history that's archaeologically accurate.

The escape hatch, and why it's dev-only

prisma migrate dev --skip-shadow-database exists and does exactly what it says � no shadow database, no creation needed. The cost is right there in the docs' fine print: without the replay there's no drift detection, and Prisma will also reset your dev database to apply this migration cleanly. Fine for a scratch database on a Tuesday; not fine as a habit, because drift detection failing silently is how failure mode 2 gets worse for months before anyone notices.

For the record, the whole map:

One sentence to keep: the shadow database is Prisma replaying your whole history from zero to check it still works � give it a database it's allowed to create, keep that history replayable, and squash it when it stops being.