Adding Prisma Migrate to an existing project

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

This guide describes how to add Prisma Migrate to an existing project.

Update the development environment

As part of adding Prisma Migrate to your development environment, you must reset your development database. This will result in data loss in the development database only.

Production databases and any other database that cannot be reset should be baselined to avoid data loss.

Introspect to create or update your Prisma schema

Make sure your Prisma schema is in sync with your database schema. This should already be true if you are using a previous version of Prisma Migrate.

  1. Introspect the database to make sure that your Prisma schema is up-to-date:

    $prisma db pull

Initialize migration history

To initialize a new migration history:

  1. If you have a prisma/migrations folder, delete, move, rename, or archive this folder.

  2. Run the following command to create the first migration without applying it, in case you need to modify the initial migration:

    $prisma migrate dev --name initial-migration --create-only

    Prisma Migrate will create a new migration directory with a SQL migration file in it:

    ./prisma/migrations/20210426141759_initial-migration

Work around features not supported by Prisma Schema Language

To include unsupported database features that already exist in the database, you must replace or modify the initial migration SQL:

  1. Open the migration.sql file generated in the Initialize migration history section.

  2. Modify the generated SQL. For example:

    • If the changes are minor, you can append additional custom SQL to the generated migration - the following example creates a partial index:
    /* Generated migration SQL */
    CREATE UNIQUE INDEX tests_success_constraint ON posts (subject, target)
    WHERE success;
    • If the changes are significant, it can be easier to replace the entire migration file with the result of a database dump (mysqldump, pg_dump)
Note that the order of the tables matters when creating all of them at once, since foreign keys are created at the same step. Therefore, either re-order them or move constraint creation to the last step after all tables are created, so you won't face `can't create constraint` errors

Apply the initial migrations

To apply your initial migration(s):

  1. Run the following command against your development database:

    $npx prisma migrate dev

    Prisma Migrate detects that the database is out of sync with the migration history and will prompt you to reset it. Confirm the reset (in development environments only!)

  2. Review the database schema to ensure the migration leads to the desired end-state (for example, by comparing the schema to the production database).

The new migration history and the new database schema should now be in sync with your Prisma schema.

Commit the migration history and Prisma schema

Commit the following to source control:

  • The entire migration history folder
  • The schema.prisma file

Reset other development environments

To reset other development environments (for example, other team members' computers):

  1. Check out a copy of the repository with the new migration directory and the schema.prisma file

  2. Run the following command to reset the development database:

    $npx prisma migrate dev

Baseline your production environment

Baselining initializes a migration history for databases that contain data and cannot be reset - such as the production database. Baselining tells Prisma Migrate to assume that one or more migrations have already been applied.

A migration history represented by three migration files (file icon and name), surrounded by a a box labelled 'migration history'. The first migration is marked 'do not apply', and the second two migrations are marked 'apply'. An arrow labelled with the command 'prisma migrate deploy' points from the migration history to a database labelled 'production'.

To baseline a database:

  1. Switch to an environment that has access to the database you want to baseline.

  2. Make sure you have a working copy of the new migrations directory.

  3. Run the following command to baseline a migration - this example marks the migration generated by the Initialize migration history step as already applied:

    $prisma migrate resolve --applied 20210426141759_initial-migration
  4. If you need to baseline multiple migrations (for example, if you generated more migrations after transitioning your development environment), run the prisma migrate resolve command for each migration separately.

Resolve migration issues in production databases

In certain cases, you can run into issues with the migration history and that prevent you from applying further database migrations with prisma migrate resolve. This can happen if:

  • A migration failed, either because of an error or because it was interrupted while running (for example, unexpected shutdown)
  • The database needs to be baselined to skip certain migrations that are unnecessary on this database schema - this can be the case when a change to the database schema was done manually, such as a hotfix.
Edit this page on GitHub