- Database schema migrations with Prisma Migrate
- How does Prisma Migrate work?
- What has changed since the Preview version?
- What's next
- Thank you to our community 💚
Today's data-driven applications demand constant change. When working with a relational database, managing a continually evolving schema can be a challenge.
Prisma Migrate is a database schema migration tool that simplifies evolving the database schema with the application in-tandem. It makes schema changes predictable and easy to verify and execute – especially for teams collaborating on a project.
After running the Experimental and Preview versions of Prisma Migrate for over a year and gathering lots of helpful feedback from our community, we are excited to launch Prisma Migrate for General Availability 🎉.
Database schema migrations play a crucial role in software development workflows and affect your application's most critical component – 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 you make to your Prisma schema – a declarative definition of your desired database schema. The generated SQL migrations are fully customizable and allow you to use any underlying database feature, such as manipulating data supporting a migration, setting up triggers, stored procedures, and views.
In contrast to other migration tools that offer full control at the expense of manually crafting SQL migrations line by line, with Migrate, most migrations can be done on "auto-pilot" with optional full control via SQL when you need it.
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.
Check out the guide for advanced migration scenarios
With Prisma Migrate, generated migrations can be tracked in your Git repository, giving you insight into your schema's evolution over time. Moreover, it eases reasoning about schema changes as part of a given change to your application.
One of the benefits of using SQL is that team members unfamiliar with the Prisma schema can still review migrations.
Learn more about team development with Prisma Migrate
Prisma Migrate can be adopted in an existing project using PostgreSQL, MySQL, SQLite, and SQL Server (Preview). This enables you to take any existing project and increase productivity with Migrate's auto-generated migrations. Additionally, you can generate Prisma Client for type-safe database access.
Because Prisma Migrate is language agnostic, it can be adopted in any project that uses one of the supported databases.
Follow the guide on adding Prisma Migrate to an existing project
Prisma Migrate integrates with Prisma Client using the Prisma schema as their shared source of truth. In other words, both Prisma Client and the migrations generated by Prisma Migrate are derived from the Prisma schema.
This makes synchronizing and verifying database schema changes in your application code easier by leveraging Prisma Client's type safety.
Since the Preview release of Prisma Migrate last year, we have polished and improved the following aspects of Prisma Migrate:
- More control: Native types give you full control over the database types you would like to use, directly from the Prisma schema.
- Stability: Migrate's commands have been stabilized to support workflows from prototyping to production.
- Production readiness: Prisma Migrate has passed rigorous testing internally and by many community members, making it ready for production use.
There are various ways for getting started with Prisma Migrate:
Learn more about how Prisma Migrate works
Prisma Migrate is based on the Prisma schema and works by generating
.sql migration files that are executed against the 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 whether you're prototyping and developing locally or applying migrations in production. 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 this with workflows for local development and applying migrations in production.
During development, there are two ways to create your database schema:
prisma db push: Creates the database schema based on the Prisma schema without any migrations. Intended for use while locally prototyping. The command is currently in Preview
prisma migrate dev: Creates an SQL migration based on changes in the Prisma schema, applies it and generates Prisma Client.
Choosing which of the two approaches depends on the stage of prototyping you're at. If you're starting to implement a new feature and want to try changing your database schema quickly,
prisma db push provides a quick way to achieve that.
Once you're comfortable with the changes, the
prisma migrate dev will generate the SQL migration and apply it:
Here is an example showing
prisma migrate dev in action:
1. Define your desired database schema using the Prisma schema:
prisma migrate dev to create and execute the migration.
After the migration has been executed, you will typically commit the migration files to the code 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.
You can customize the migration SQL with the following workflow:
prisma migrate dev --create-onlyto create the SQL migration without applying it.
- Edit the migration SQL.
prisma migrate devto apply it.
To apply migrations to other environments such as production, you pull changes to the repository containing the migrations and run the following command:
Prisma Migrate can also be used to apply migrations in continuous integration pipelines for testing purporses and continuous delivery pipelines for deployment.
In principle, the
prisma migrate deploy CLI command is intended for use in non-interactive automation environments, e.g. GitHub Actions. This is useful when you want to spin up a database and run migrations in order to run integration tests.
The most significant change since the Preview version is the introduction of native types, integrated seeding, and support for cloud native development.
If you're upgrading from the Preview version, you can remove the
--preview-feature flag from your Migrate scripts.
Previously, Prisma Migrate only supported a subset of the wide range of available types in the supported databases. With this release, we expand that set and allow you to define the exact database type in the Prisma schema.
Previously, Fields in the Prisma schema would be annotated with a type from the scalar types that Prisma exposed.
Each of these scalar types had a default mapping to a specific database type. For example, the
String Prisma scalar type would map to
text in PostgreSQL:
With this release, we have broadened the set of supported database types you can define by allowing you to add native type annotations.
In the example, the
@db.VarChar(100) attribute denotes that Migrate should use the
VARCHAR type of PostgreSQL. This is also visible in the SQL that Migrate generates for the model:
To learn more about native type attributes, check out the docs.
Migrate comes with built-in support for seeding. Seeding enables you to bootstrap a usable test environment with data quickly. Depending on your desired approach, you can use seeding both locally and in shared environments.
Note: The seeding functionality is still in Preview and will be stabilized in a future release.
To use seeding, define the script inside the
prisma folder and run the
prisma db seed command.
The seeding functionality is automatically triggered whenever
prisma migrate reset is called to reset and repopulate the database in development. It's also triggered when the database is reset interactively after calling prisma migrate dev.
This is particularly useful when reviewing collaborators' work, as it allows reviewing schema migrations with actual data.
With Migrate reaching General Availability, it is ready for adoption in production. But it doesn't stop here – we are continuing to develop and improve Prisma Migrate actively.
For example, we'll be improving the handling of column renames in Prisma Migrate.
Beyond that, the following roadmap items will introduce further improvements to the general management of database schemas:
- Stabilize and promote
prisma db pushand
prisma db seedto General Availability.
- Improve environment management configuration.
- Add support for configuring cascading deletes in the Prisma schema.
We've been overwhelmed by the positive response to the Preview release in December, and we'd like to thank everyone who tested and provided insightful feedback – Prisma Migrate is the product of those efforts 🙌
To get started with Prisma Migrate, checkout the following resources:
- Start from scratch guide covers both Prisma Migrate and Client.
- Prisma Migrate docs delves deeper into the different Migrate workflows.
- Developing with Migrate guides you through a typical development workflow.
- Advanced migration scenarios goes into customizing the migration SQL.
- Team development with Migrate explains collaboration with Migrate as a team.
- Adding Prisma Migrate to an existing project describes how to add Prisma Migrate to an existing project with a database.
- Prisma Slack: Support for Migrate in the
👷♀️ We are thrilled to share today's General Availability of Prisma Migrate and can't wait to see what you all build with it.
Don’t miss the next post!
Sign up for the Prisma Newsletter