Prisma Migrate Preview - Database Migrations Simplified

Contents

• Schema migrations with Prisma Migrate
• How does Prisma Migrate work?
• What has changed since the Experimental version?
• What's next
• Try Prisma Migrate and share your feedback

Schema migrations with Prisma Migrate

Today we're excited to share the new version of Prisma Migrate! 🎊

Prisma Migrate is a data modeling and migrations tool that simplifies evolving the database schema with the application in-tandem. Migrate is based on the Prisma schema – a declarative data model definition that codifies your database schema.

This Preview release is the evolution of the Experimental version of Migrate that we released last year. Since then, we've been gathering feedback from the community and incorporating it into Prisma Migrate.

Making schema migrations predictable

Database schema migrations play a crucial role in software development workflows and affect the most critical component in your application – the database. We've built Migrate to be predictable while allowing you to control how database schema changes are carried out.

Prisma Migrate generates migrations as plain SQL files based on changes in your Prisma schema. These SQL files are fully customizable and allow you to use any feature of the underlying database, such as manipulating data supporting a migration, setting up triggers, stored procedures, and views.

Prisma Migrate treads the balance between productivity and control by automating the repetitive and error-prone aspects of writing database migrations while giving you the final say over how they are executed.

Integration with Prisma Client

Prisma Migrate integrates with Prisma Client using the Prisma schema as their shared source of truth. In other words, both Prisma Client and migrations are generated based on the Prisma schema. This makes synchronizing and verifying database schema changes in your application code easier by leveraging Prisma Client's type safety.

Prisma Migrate is ready for broader testing

Prisma Migrate has passed rigorous testing internally and is now ready for broader testing by the community. You can use it with PostgreSQL, MySQL, SQLite, and SQL Server. However, as a Preview feature, it is not fully production-ready yet. To read more about what Preview means, check out the maturity levels in the Prisma docs.

Thus, we're inviting you to try it out and give us feedback so we can bring Prisma Migrate to General Availability. 🚢

Your feedback and suggestions will help us shape the future of Prisma Migrate. 🙌

---

How does Prisma Migrate work?

Prisma Migrate is based on the Prisma schema and works by generating .sql migration files that are executed against your database.

The Prisma schema is the starting point for schema migrations and provides an overview of your desired end-state of the database. Prisma Migrate inspects changes in the Prisma schema and generates the necessary .sql migration files to apply.

Applying migrations looks very different depending on the stage of development. For example, during development, there are scenarios where resetting the database can be tolerated for quicker prototyping, while in production, great care must be taken to avoid data loss and breaking changes.

Prisma Migrate accommodates for this with workflows for local development and applying migrations in production.

Evolving the schema in development

To use the new version of Prisma Migrate, you should have at least version 2.13.0 of the @prisma/cli package installed.

During development, you first define the Prisma schema and then run the prisma migrate dev --preview-feature command, which generates the migration, applies it, and generates Prisma Client:

Here is an example showing it in action:

1. Define your desired database schema using the Prisma schema:

2. Run prisma migrate dev --preview-feature to create and execute the migration.

After the migration has been executed, the migration files are typically committed to the repository so that the migration can be applied in other environments.

Further changes to the database schema follow the same workflow and begin with updating the Prisma schema.

Customizing SQL migrations

You can customize the migration SQL with the following workflow:

1. Run prisma migrate dev --create-only --preview-feature to create the SQL migration without applying it.
2. Edit the migration SQL.
3. Run prisma migrate dev --preview-feature to apply it.

Applying migrations in production and other environments

To apply migrations to other environments such as production, you pull changes to the repository containing the migrations and run the prisma migrate deploy command:

---

What has changed since the Experimental version?

The most significant change since the Experimental version is the use of SQL as the format for migrations, making migrations deterministic. In other words, the exact steps of the migration are determined when the migration is created, allowing you to inspect the SQL (and make changes if necessary) before running.

This approach has the following benefits:

• The generated SQL is editable, thereby allowing you to control the exact schema changes.
• The migration is predictable with the exact SQL that will be applied.
• You don't need to write SQL unless you want to change a migration.
• You can perform data migrations using SQL as part of a migration.

Editable SQL for migrations is useful in scenarios where there are multiple ways to map changes in the Prisma schema to the database, and the desired path cannot be automatically determined.

For example, when you rename a field in the Prisma schema, that can be interpreted as either deleting the column and adding an unrelated new one or as you renaming the column. By allowing you to inspect and edit the migration SQL, you can decide whether to rename the column (and retain the data in the column) or drop it and add a new one.

If you're upgrading Prisma Migrate from the Experimental version, check out the upgrade guide.

---

What's next

This Preview version of Prisma Migrate lays the foundations for the upcoming General Availability release. Some of the improvements we are considering are improved support for native database types, seeding functionality, and finding a way to make database resets in development less disruptive.

Native database types

One of the most requested features in Prisma is support for the database's native types. This release is a step closer to that – however, there's still more work to be done for native types to be fully supported.

Currently, the Prisma schema can only represent a limited set of types: String, Int, Float, Boolean, DateTime, and Json. Each of these types has a default mapping to an underlying database type that's specified for each database connector (see the mappings for PostgreSQL and MySQL).

In version 2.11.0, we released the nativeTypes Preview feature – the ability to annotate fields in the Prisma schema with the specific native database type that it should be mapped to. However, the native types preview feature doesn't work with Prisma Migrate yet.

Even so, you can still change the types of columns in the generated SQL as long as they are supported, as documented in the PostgreSQL and MySQL connector docs.

---

Try Prisma Migrate and share your feedback

We built Prisma Migrate for you and are keen to hear your feedback.

We want to understand how Prisma Migrate fits into your development workflow and how we can help you stay productive and confident while building and evolving data-centric applications.

🐛 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.

🏗 Share your feedback about how the new Prisma Migrate is working out for you on GitHub.

🌍 Join us on our Slack in the #prisma-migrate channel for help.

👷‍♀️ We are thrilled to finally share the Preview version of Prisma Migrate and can't wait to see what you all build with it.