Improving Prisma Migrate DX with two new commands

Better DX for troubleshooting schema migrations

We are excited to launch two new low-level Migrate commands in Preview: migrate diff and db execute as part of the 3.9.0 release of Prisma.

The two commands are very versatile — they can be used to troubleshoot and resolve failed schema migrations and get quick feedback about schema-related discrepancies between environments, branches, and representations (Prisma data model, migration history, database schema).

Prisma Migrate is a schema migrations tool that strikes a balance between automation and predictability – on the one hand, automatically generating SQL migrations based on changes in your Prisma schema, on the other, giving you the flexibility to inspect and customize the generated SQL before execution.

Schema migrations are tricky

Since launching Migrate, we heard from many developers and teams using Prisma in fast-moving projects. Such teams make frequent schema changes with sustained user traffic.

We learned that as such projects grow, schema migrations tend to get tricky.

Schema migrations require great care and often become time-consuming and challenging, especially when a migration fails in production.

After talking to many users, sifting through related issues, and researching possible solutions, it became clear that schema migrations are tricky because they depend on many factors (data, concurrent access) and tend to be unpredictable across environments.

When you take multiple environments with different data into account, the migration might succeed on all environments only to fail in production due to violated unique constraints, nullability errors, or failing type casts.

When a schema migration fails

When a schema migration is successful, the following three sources of schema related state are in sync:

• The live database schema
• The Prisma data model
• The migration history (SQL files)

Note: the migration history table (_prisma_migrations), which tracks which migrations have been applied and whether they succeeded, also contains state, but you typically don't interact with it directly.

However, when a migration fails, you begin a 3-partner dance to determine discrepancies between the three because the database schema doesn't reflect the migration history or the Prisma schema.

What's more, if the migration failed in production, you're probably worried –or worse, panicking– about the extent of the blast radius:

• How many of your users are affected by this?
• What failure mode is the system in?
• How much downtime does the service level agreement afford you to resolve the failed migration?

A failed schema migration leaves your production database in an unknown state that typically requires manual intervention: determine what in the migration succeeded, what failed, and how, so you can craft a script to recover.

What if you could roll back schema migrations?

Many schema migration tools allow you to define down migrations – a set of steps to reverse changes carried out by the up migration. Typically the down migration is written at the same time as the up migration. Ostensibly, down migrations allow you to roll back the migration and bring back the database schema to its state before the migration.

Down migrations are a common feature request for Prisma Migrate. However, after careful consideration, we believe that rollbacks (down migrations) give a false sense of security and often exacerbate the situation.

Consider a migration that removes four columns. The corresponding down migration would add the four columns again, which leads to the following problems:

• If the up migration fails, and not all four fields were removed, re-adding those in the down migration will also fail
• The data in the removed columns is gone; re-adding the columns won't help recover the lost data.

What if schema migrations were atomic

Another potential solution to the problem of failed migrations is transactional or atomic schema migrations. The idea is that you wrap your schema migration in a transaction and guarantee that the schema migration will either be entirely carried out or not at all.

While transactions are immensely useful for CRUD operations, they are not as straightforward for schema migrations for several reasons:

• Not all relational databases support transactional DDL (data definition language).
• Performance: wrapping schema migrations in a transaction can incur significant performance costs. Large migrations will be much heavier and slower, risking long locks and increased resource consumption by the database to maintain additional state.

Migrate comes with sensible defaults that incorporate established best practices for each of the supported databases:

• SQL Server and Azure SQL: Migrate explicitly wraps the generated migration in a transaction.
• PostgreSQL: You can opt-in by adding BEGIN; and COMMIT; to the generated schema migrations. By default, Migrate does not wrap migrations in a transaction.
• MySQL: Transactional DDL is not supported.

In summary, making schema migrations atomic with a transaction is valid in some scenarios, but depends on whether your database supports them and their potential cost.

Our general recommendation is to strive for smaller, non-breaking migrations. Sometimes it's more upfront work, but it makes the migration more predictable with fewer failure modes and is the only thing that works on a large scale.

This brings us to the new Migrate commands and their broad applicability.

Migrate's new swiss army knife

prisma migrate diff

The new prisma migrate diff command compares the database schema from two arbitrary sources. It outputs either a human-readable summary (by default) or an executable SQL script.

You can compare ("diff") any combination of two of the following

• Live database schema
• Prisma data model
• Migration history folder
• Nothing (representing a new empty database)

migrate diff is a read-only command, so it does not write anything to your examined database. It's helpful in many situations, e.g. comparing two shared environments that might be out of sync.

Resolving a failed schema migration with migrate diff

When a migration fails, you can migrate diff your migration history directory with the current database schema to know what further changes are necessary to bring your database schema to your desired state.

Another potential use-case is when you are merging Git branches, and you want to know if the merged migration history corresponds to the database schema.

prisma db execute

The db execute command takes SQL as input (either from a file or stdin) and executes it against a database.

You can pipe the output from migrate diff --script to db execute to immediately execute the SQL output, or alternatively, write the SQL output to a file, inspect it, and run db execute:

The examples above demonstrate just some of the many possible use-cases. You can also use them to roll back migrations, detect schema drift, and more.

To learn more about the new commands, check out the docs, or run them with the help flag: prisma migrate diff --help.

Note: db execute is not supported on MongoDB.

General schema migration recommendations

The new commands are versatile tools for schema migrations. But many established practices assist in avoiding schema migrations going awry in the first place:

• Keep schema migrations small.
• Stick to non-breaking additive changes and use the expand and contract pattern for renames and breaking-changes.
• Carefully consider the cost of wrapping schema migrations in transactions (if your database supports it). The potential impact is usually a function of the size of your database and the level of concurrent access.

Try the new Migrate commands and share your feedback

We built the new commands for you and are keen to hear your feedback.

🐛 Tried it out and found that it's missing something or stumbled upon a bug? Please file an issue so we can look into it.

👷‍♀️ We are thrilled to share the Preview version of the new Migrate commands and looking forward to your feedback.