Hassle-Free Database Migrations with Prisma Migrate

Contents

• 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 💚

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.

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.

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.

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.

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:

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

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.

model User {
  id    Int      @id @default(autoincrement())
  name  String // maps to the `text` type in PostgreSQL
}

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

 -- 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:

• Stabilize and promote prisma db push and prisma db seed to General Availability.
• Improve environment management configuration.
• Add support for configuring cascading deletes in the Prisma schema.

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:

• 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 #prisma-migrate channel.

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