Prisma Migrate

MongoDB not supported
Prisma Migrate does not currently support the MongoDB connector.

Prisma Migrate is an imperative database schema migration tool that enables you to:

  • Keep your database schema in sync with your Prisma schema as it evolves and
  • Maintain existing data in your database

Prisma Migrate generates a history of .sql migration files, and plays a role in both development and deployment.

If you are prototyping, consider using the db push command - see Schema prototyping with db push for examples.

Getting started with Prisma Migrate

See Developing with Prisma Migrate for a more in-depth development workflow.

To get started with Prisma Migrate in a development environment:

  1. Create a Prisma schema:

    schema.prisma
    1datasource db {
    2 provider = "postgresql"
    3 url = env("DATABASE_URL")
    4}
    5
    6model User {
    7 id Int @id @default(autoincrement())
    8 name String
    9 posts Post[]
    10}
    11
    12model Post {
    13 id Int @id @default(autoincrement())
    14 title String
    15 published Boolean @default(true)
    16 authorId Int
    17 author User @relation(fields: [authorId], references: [id])
    18}

You can use native type mapping attributes in your schema to decide which exact database type to create (for example, String can map to varchar(100) or text).

  1. Create the first migration:

    $prisma migrate dev --name init
    Show migration SQL

    Your Prisma schema is now in sync with your database schema and you have initialized a migration history:

    migrations/
    โ””โ”€ 20210313140442_init/
    โ””โ”€ migration.sql
  2. Evolve your schema by introducing additional fields:

    model User {
    id Int @id @default(autoincrement())
    jobTitle String
    name String
    posts Post[]
    }
  3. Create the second migration:

    $prisma migrate dev --name added_job_title
    Show migration SQL

    Your Prisma schema is once again in sync with your database schema, and your migration history contains two migrations:

    migrations/
    โ””โ”€ 20210313140442_init/
    โ””โ”€ migration.sql
    โ””โ”€ 20210313140442_added_job_title/
    โ””โ”€ migration.sql

You now have a migration history that you can source control and use to deploy changes to test environments and production.

Migration history

Your migration history is the story of the changes to your data model, and is represented by:

  • A prisma/migrations folder with a sub-folder and migration.sql file for each migration:

    migrations/
    โ””โ”€ 20210313140442_init/
    โ””โ”€ migration.sql
    โ””โ”€ 20210313140442_added_job_title/
    โ””โ”€ migration.sql

    This folder is the source of truth for the history of your data model.

  • A _prisma_migrations table in the database, which is used to check:

    • If a migration has been run against the database
    • If an applied migration has been deleted
    • If an applied migration has been changed

    If you change or delete a migration (not recommended), the next steps depend on whether you are in a development environment (and therefore using migrate dev) or a production / testing environment (and therefore using migrate deploy).

Do not edit or delete migrations that have been applied

In general, you should not edit or delete a migration that has already been applied. Doing so can lead to inconsistencies between development and production environment migration histories, which may have unforeseen consequences - even if the change does not appear to break anything at first.

The following scenario simulates a change that creates a seemingly harmless inconsistency:

  1. Modify an existing migration that has already been applied in a development environment by changing the value of VARCHAR(550) to VARCHAR(560):

    ./prisma/migrations/20210310143435_default_value/migrations.sql
    1 -- AlterTable
    2 ALTER TABLE "Post" ALTER COLUMN "content" SET DATA TYPE VARCHAR(560);

    After making this change, the end state of the migration history no longer matches the Prisma schema, which still has @db.VarChar(550).

  2. Run prisma migrate dev - Prisma Migrate detects that a migration has changed, and asks to reset the database:

    ? The migration `20210310143435_change_type` was modified after it was applied.
    We need to reset the PostgreSQL database "migrate-example" at "localhost:5432".
    Do you want to continue? All data will be lost. ยป (y/N)
  3. If you accept resetting, Prisma Migrate resets the database and replays all migrations, including the migration you edited.

  4. After applying all existing migrations, Prisma Migrate compares the end state of the migration history to the Prisma schema and detects a discrepancy:

    • Prisma schema has @db.VarChar(550)
    • Database schema has VARCHAR(560)
  5. Prisma Migrate generates a new migration to change the value back to 550, because the end state of the migration history should match the Prisma schema.

  6. From now on, when you use prisma migrate deploy to deploy migrations to production and test environments, Prisma Migrate will always warn you that migration histories do not match (and continue to warn you each time you run the command ) - even though the schema end states match:

6 migrations found in prisma/migrations
WARNING The following migrations have been modified since they were applied:
20210310143435_change_type

A change that does not appear to break anything after a migrate reset can hide problems - you may end up with a bug in production that you cannot replicate in development, or the other way around - particularly if the change concerns a highly customized migration.

If Prisma Migrate reports a missing or edited migration that has already been applied, we recommend fixing the root cause (restoring the file or reverting the change) rather than resetting.

Source-controlling the migration history

You must commit the entire prisma/migrations folder to source control. This includes the prisma/migrations/migration_lock.toml file, which is used to detect if you have attempted to change providers.

Source-controlling the schema.prisma file is not enough - you must include your migration history. This is because:

  • As you start to customize migrations, your migration history contains information that cannot be represented in the Prisma schema. For example, you can customize a migration to mitigate data loss that would be caused by a breaking change.
  • The prisma migrate deploy command, which is used to deploy changes to staging, testing, and production environments, only runs migration files. It does not use the Prisma schema file to fetch the models.

Development environments

In a development environment, use the migrate dev command to generate and apply migrations:

$npx prisma migrate dev

Create and apply migrations

migrate dev is a development command and should never be used in a production environment.

This command:

  1. Replays the existing migration history in the shadow database in order to detect schema drift (edited or deleted migration file, or a manual changes to the database schema)
  2. Applies pending migrations to the shadow database (for example, new migrations created by colleagues)
  3. Generates a new migration from any changes you made to the Prisma schema before running migrate dev
  4. Applies all unapplied migrations to the development database and updates the _prisma_migrations table
  5. Triggers the generation of artifacts (for example, the Prisma Client)

The migrate dev command will prompt you to reset the database in the following scenarios:

You can also reset the database yourself to undo manual changes or db push experiments by running:

$npx prisma migrate reset

Reset the development database

migrate reset is a development command and should never be used in a production environment.

This command:

  1. Drops the database if possible, or performs a soft reset if the environment does not allow deleting databases
  2. Creates a new database with the same name if the database was dropped
  3. Applies all migrations
  4. Runs seed scripts

Note: For a simple and integrated way to re-create data in your development database as often as needed, check out our seeding guide.

Customizing migrations

Sometimes, you need to modify a migration before applying it. For example:

  • You want to introduce a significant refactor, such as changing blog post tags from a String[] to a Tag[]
  • You want to rename a field (by default, Prisma Migrate will drop the existing field)
  • You want to change the direction of a 1-1 relationship
  • You want to add features that cannot be represented in Prisma Schema Language - such as a partial index or a stored procedure.

The --create-only command allows you to create a migration without applying it:

$npx prisma migrate dev --create-only

To apply the edited migration, run prisma migrate dev again.

Refer to Customizing migrations for examples.

Team development

See: Team development with Prisma Migrate .

Production and testing environments

In production and testing environments, use the migrate deploy command to apply migrations:

$npx prisma migrate deploy

Note: migrate deploy should generally be part of an automated CI/CD pipeline, and we do not recommend running this command locally to deploy changes to a production database.

This command:

  1. Compares applied migrations against the migration history and warns if any migrations have been modified:

    WARNING The following migrations have been modified since they were applied:
    20210313140442_favorite_colors
  2. Applies pending migrations

The migrate deploy command:

  • Does not issue a warning if an already applied migration is missing from migration history
  • Does not detect drift (production database schema differs from migration history end state - for example, due to a hotfix
  • Does not reset the database or generate artifacts (such as Prisma Client)
  • Does not rely on a shadow database

See also:

Advisory locking

Prisma Migrate makes use of advisory locking when you run production commands like:

  • prisma migrate deploy
  • prisma migrate resolve

This safeguard ensures that multiple commands cannot run at the same time - for example, if you merge two pull requests in quick succession.

Advisory locking has a 10 second timeout (not configurable), and uses the default advisory locking mechanism available in the underlying provider:

Prisma Migrate's implementation of advisory locking is purely to avoid catastrophic errors - if your command times out, you will need to run it again.

Commands

See Prisma Migrate CLI reference for all available Prisma Migrate commands.

Troubleshooting

Prisma detects when CLI commands are run in non-interactive environments, such as Docker, from Node scripts or in bash shells. When this happens a warning is displayed indicating that the environment is non-interactive and the migrate dev command is not supported.

To ensure the Docker environment picks up the command, run the image in interactive mode so that it can react to the migrate dev command.

$docker run --interactive --tty <image name>
$# or
$docker -it <image name>
$
$# Example usage
$docker run -it node
Edit this page on GitHub