March 16, 2021

Hassle-Free Database Migrations with Prisma Migrate

Prisma Migrate helps developers evolve their database schema at every step of development, from prototyping to production. Today we are excited to launch Prisma Migrate for General Availability! Try it out.

Contents

Database schema migrations with Prisma Migrate

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

Predictable schema migrations with full control

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.

model User {
id Int @id @default(autoincrement())
email String @unique
name String?
birthdate DateTime @db.Date
activated Boolean @default(false)
}

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

Version control for your database

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

Bring your own project

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

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 the migrations generated by Prisma Migrate are derived from the Prisma schema.

The Prisma schema is a single source of truth for both database schema migrations and Prisma Client which gives you end-to-end type safetyThe Prisma schema is a single source of truth for both database schema migrations and Prisma Client which gives you end-to-end type safety

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 use in production

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.

You can use Migrate with PostgreSQL, MySQL, SQLite. SQL Server support is available in Preview.

There are various ways for getting started with 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 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.

Evolving the schema in development

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:

Development workflow with `prisma migrate dev`Development workflow with `prisma migrate dev`

Here is an example showing prisma migrate dev in action:

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

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
name String
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String @db.VarChar(100)
published Boolean @default(true)
authorId Int
author User @relation(fields: [authorId], references: [id])
}

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

-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"name" TEXT NOT NULL,
PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Post" (
"id" SERIAL NOT NULL,
"title" VARCHAR(100) NOT NULL,
"published" BOOLEAN NOT NULL DEFAULT true,
"authorId" INTEGER NOT NULL,
PRIMARY KEY ("id")
);
-- AddForeignKey
ALTER TABLE "Post" ADD FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

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.

Customizing SQL migrations

You can customize the migration SQL with the following workflow:

  1. Run prisma migrate dev --create-only to create the SQL migration without applying it.
  2. Edit the migration SQL.
  3. Run prisma migrate dev 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 following command:

prisma migrate deploy

Production workflowProduction workflow

Running migrations in CI/CD

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.

This is covered in the Migrate docs and the production troubleshooting guide in more detail.


What has changed since the Preview version?

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.

Native database types

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:

model User {
id Int @id @default(autoincrement())
name String // maps to the `text` type 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.

model User {
id Int @id @default(autoincrement())
name String @db.VarChar(100) // Maps to the `varchar` type in PostgreSQL
}

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:

-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"name" VARCHAR(100) NOT NULL,
PRIMARY KEY ("id")
);

To learn more about native type attributes, check out the docs.

Integrated seeding

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.

Seeding is currently supported via scripts written in TypeScript, JavaScript, and Shell.

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.


What's next

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:


Thank you to our community 💚

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:

👷‍♀️ We are thrilled to share today's General Availability of Prisma Migrate and can't wait to see what you all build with it.

Join the discussion

Follow @prisma on Twitter

Don’t miss the next post!

Sign up for the Prisma newsletter