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
.
* If you use a cloud-hosted database for development, you need to create the shadow database manually.
Important: The shadow database is not required in production, and is not used by production-focused commands such as
prisma migrate resolve
andprisma 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
Detecting schema drift
To detect drift in development, Prisma Migrate:
- Creates a fresh copy of the shadow database (or performs a soft reset if the shadow database is cloud-hosted)
- Reruns the current migration history in the shadow database.
- Introspects the shadow database to generate the 'current state' of your Prisma schema.
- Compares the end state of the current migration history to the development database.
- Reports schema drift if the end state of the current migration history does not match the development database (for example, due to a manual change)
If Prisma Migrate does not detect schema drift, it moves on to generating new migrations.
Note: The shadow database is not responsible for checking if a migration file has been edited or deleted. This is done using the
checksum
field in the_prisma_migrations
table.
In 2.25.0 and later, Prisma Migrate outputs detailed information about which parts of the database have drifted. In the following example output, the schema that would be generated from the target database differs from the expected schema. For example, the Color
enum in the target database is missing the expected variant RED
and include the unexpected variant TRANSPARENT
:
[*] 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)
Generating new migrations
Assuming Prisma Migrate did not detect schema drift, it moves on to generating new migrations from schema changes. To generate new migrations, Prisma Migrate:
- Calculates the target database schema as a function of the current Prisma schema.
- Compares the end state of the existing migration history and the target schema, and generates steps to get from one to the other.
- Renders these steps to a SQL string and saves it in the new migration file.
- Applies the generated migration to the development database (assuming you have not specified the
--create-only
flag) - Drops the shadow database (cloud-hosted databases cannot be dropped, but are reset at the start of the
migrate dev
command)
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:
- Create a dedicated cloud-hosted shadow database
- 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
andshadowDatabaseUrl
.
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.
Database | Database user requirements |
---|---|
SQLite | No special requirements. |
MySQL | Database user must have CREATE, ALTER, DROP, REFERENCES ON *.* privileges |
PostgreSQL | The user must be a super user or have CREATEDB privilege. See CREATE ROLE (PostgreSQL official documentation) |
Microsoft SQL Server | The user must be a site admin or have the SERVER securable. See the official documentation. |
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 databaseDatabase 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 theprisma 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.