Baselining a database

Baselining is the process of initializing a migration history for a database that:

• ✔ Existed before you started using Prisma Migrate
• ✔ Contains data that must be maintained (like production), which means that the database cannot be reset

Baselining tells Prisma Migrate to assume that one or more migrations have already been applied. This prevents generated migrations from failing when they try to create tables and fields that already exist.

Note: We assume it is acceptable to reset and seed development databases.

Baselining is part of adding Prisma Migrate to a project with an existing database.

warning

This guide does not apply for MongoDB.
Instead of migrate deploy, db push is used for MongoDB.

Why you need to baseline

When you add Prisma Migrate to an existing project, your initial migration contains all the SQL required to recreate the state of the database before you started using Prisma Migrate:

tip

You can edit the initial migration to include schema elements that cannot be represented in the Prisma schema - such as stored procedures or triggers.

You need this initial migration to create and reset development environments:

However, when you prisma migrate deploy your migrations to databases that already exist and cannot be reset - such as production - you do not want to include the initial migrations.

The target database already contains the tables and columns created by the initial migration, and attempting to create these elements again will most likely result in an error.

Baselining solves this problem by telling Prisma Migrate to pretend that the initial migration(s) have already been applied.

Baselining a database

To create a baseline migration:

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

2. Run the following command to create a migrations directory inside with your preferred name. This example will use 0_init for the migration name:

   mkdir -p prisma/migrations/0_init
   info

   Then 0_ is important because Prisma Migrate applies migrations in a lexicographic order. You can use a different value such as the current timestamp.

3. Generate a migration and save it to a file using prisma migrate diff

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

4. Run the prisma migrate resolve command for each migration that should be ignored:

   npx prisma migrate resolve --applied 0_init

This command adds the target migration to the _prisma_migrations table and marks it as applied. When you run prisma migrate deploy to apply new migrations, Prisma Migrate:

1. Skips all migrations marked as 'applied', including the baseline migration
2. Applies any new migrations that come after the baseline migration