About the shadow database

Some development-focused commands for relational databases of Prisma Migrate use a second, temporary database:

• prisma migrate dev
• prisma migrate reset

The shadow database is created and deleted automatically* each time you run a development-focused command and is primarily used to detect problems such as schema drift.

migrate diff command also requires a shadow database when diffing against a local migrations directory with --from-migrations or --to-migrations.

Important: The shadow database is not required in production, and is not used by production-focused commands such as prisma migrate resolve and prisma migrate deploy.

Note: A shadow database is never used for MongoDB as these commands are not used there.

How the shadow database works

When you run prisma migrate dev to create a new migration, Prisma Migrate uses the shadow database to:

• Detect schema drift, which means checking that no manual changes have been made to the development database
• Generate new migrations

[*] Changed the `Color` enum
  [+] Added variant `TRANSPARENT`
  [-] Removed variant `RED`

[*] Changed the `Cat` table
  [-] Removed column `color`
  [+] Added column `vaccinated`

[*] Changed the `Dog` table
  [-] Dropped the primary key on columns (id)
  [-] Removed column `name`
  [+] Added column `weight`
  [*] Altered column `isGoodDog` (arity changed from Nullable to Required, default changed from `None` to `Some(Value(Boolean(true)))`)
  [+] Added unique index on columns (weight)

Cloud-hosted shadow databases must be created manually

Some cloud providers do not allow you to drop and create databases with SQL. Some require to create or drop the database via an online interface, and some really limit you to 1 database. If you develop in such a cloud-hosted environment, you must:

1. Create a dedicated cloud-hosted shadow database
2. Add the URL to the shadowDatabaseUrl field:

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

Important: Do not use the same values for url and shadowDatabaseUrl.

Shadow database user permissions

In order to create and delete the shadow database when using development commands such as migrate dev and migrate reset, Prisma Migrate currently requires that the database user defined in your datasource has permission to create databases.

If you use a cloud-hosted database for development and can not use these permissions, see: Cloud-hosted shadow databases

Note: The automatic creation of shadow databases is disabled on Azure SQL.

Prisma Migrate throws the following error if it cannot create the shadow database with the credentials your connection URL supplied:

Error: A migration failed when applied to the shadow database
Database error: Error querying the database: db error: ERROR: permission denied to create database

To resolve this error:

• If you are working locally, we recommend that you update the database user's privileges.
• If you are developing against a cloud-based database (for example, on Heroku, Digital Ocean, or Vercel Postgres) see: Cloud-hosted shadow databases.
• If you are developing against a cloud-based database (for example, on Heroku, Digital Ocean, or Vercel Postgres) and are currently prototyping such that you don't care about generated migration files and only need to apply your Prisma data model to the database schema, you can run prisma db push instead of the prisma migrate dev command.

Important: The shadow database is only required in a development environment (specifically for the prisma migrate dev command) - you do not need to make any changes to your production environment.