Overview

Prisma Migrate is a tool that lets you change your database schema, e.g. by creating new tables or adding columns to existing tables. These changes are called schema migrations. Prisma Migrate is available as part of the Prisma CLI via the prisma migrate command.

Prisma Migrate is currently in an experimental state. This means that it is not recommended to use Prisma Migrate in production. Instead, you can perform schema migrations using plain SQL or another migration tool of your choice and then bring the changes into your Prisma schema using introspection.

Prisma Migrate vs SQL migrations

Prisma Migrate is a declarative migration system, as opposed to SQL which can be considered imperative:

  • SQL (imperative): Provide the individual steps to get from the current schema to the desired schema.
  • Prisma Migrate (declarative): Define the desired schema as a Prisma data model (Prisma Migrate takes care of generating the necessary steps).

Here's a quick comparison. Assume you have the following scenario:

  1. You need to create the User table to store user information (name, email, ...)
  2. Create two new tables Post and Profile with foreign keys to User
  3. Add a new column with a default value to the Post table

SQL

In SQL, you'd have to send three subsequent SQL statements to account for this scenario:

1. Create the User table to store user information (name, email, ...)

1CREATE TABLE "User" (
2 id SERIAL PRIMARY KEY,
3 name VARCHAR(255),
4 email VARCHAR(255) NOT NULL
5);

2. Create two new tables Post and Profile with foreign keys to User

1CREATE TABLE "Profile" (
2 id SERIAL PRIMARY KEY,
3 bio TEXT NOT NULL,
4 "user" integer NOT NULL UNIQUE,
5 FOREIGN KEY ("user") REFERENCES "User"(id)
6);
7CREATE TABLE "Post" (
8 id SERIAL PRIMARY KEY,
9 title VARCHAR(255) NOT NULL,
10 author integer NOT NULL,
11 FOREIGN KEY (author) REFERENCES "User"(id)
12);

3. Add a new column with a default value to the Post table

1ALTER TABLE "Post"
2ADD COLUMN published BOOLEAN DEFAULT false;

Prisma Migrate

With Prisma Migrate, you write the desired database schema in the form of a Prisma data model inside your Prisma schema file. To map the data model to your database schema, you then have to run these two commands:

1prisma migrate save --experimental
2prisma migrate up --experimental

The first command saves a new migration to the prisma/migrations directory in the file system of your project and updates the _Migration table in your database. Each time you run this command to save a new migration, it creates a dedicated directory inside of prisma/migrations for that specific migration, which will have its own README.md file containing detailed information about the migration (e.g. the generated SQL statements which will be executed when you run prisma migrate up).

The second command executes the migration against your database.

1. Create the User table to store user information (name, email, ...)

Add the model to your Prisma schema:

1model User {
2 id Int @id @default(autoincrement())
3 name String?
4 email String @unique
5}

Now run the two commands mentioned above:

1prisma migrate save --experimental
2prisma migrate up --experimental

2. Create two new tables Post and Profile with foreign keys to User

Add two models with relation fields to your Prisma schema:

1model User {
2 id Int @id @default(autoincrement())
3 name String?
4 email String @unique
5 posts Post[]
6 profile Profile?
7}
8
9model Profile {
10 id Int @id @default(autoincrement())
11 bio String
12 user User
13}
14
15model Post {
16 id Int @id @default(autoincrement())
17 title String
18 author User
19}

Notice that in addition to the annotated relation fields and its relation scalar field (which represent the foreign keys), you must also specify the Prisma-level relation fields on the other side of the relation.

Now run the two commands mentioned above:

1prisma migrate save --experimental
2prisma migrate up --experimental

3. Add a new column with default value to the Post table

Add a field to the Post model:

1model User {
2 id Int @id @default(autoincrement())
3 name String?
4 email String @unique
5 posts Post[]
6 profile Profile?
7}
8
9model Profile {
10 id Int @id @default(autoincrement())
11 bio String
12 user User
13}
14
15model Post {
16 id Int @id @default(autoincrement())
17 title String
18 published Boolean @default(false)
19 author User
20}

Now run the two commands mentioned above:

1prisma migrate save --experimental
2prisma migrate up --experimental

Supported operations

The following table shows which SQL operations are currently supported by Prisma Migrate. If an operation is not yet supported, it links to a workaround that uses plain SQL and introspection to enable this feature in Prisma Client.

OperationSQLSupported
Create a new tableCREATE TABLE✔️
Rename an existing tableALTER TABLE + RENAMENot yet (workaround coming soon)
Delete an existing tableDROP TABLE✔️
Add a column to an existing tableALTER TABLE + ADD COLUMN✔️
Rename an existing columnALTER TABLE + RENAME COLUMNNot yet (workaround coming soon)
Delete an existing columnALTER TABLE + DROP COLUMN✔️
Set primary keys (IDs)PRIMARY KEY✔️
Define relations (foreign keys)FOREIGN KEY + REFERENCES✔️
Make columns optional/requiredNOT NULL✔️
Set unique constraintsUNIQUE✔️
Set default valuesDEFAULT✔️
Define enumsENUM✔️
Create indexesCREATE INDEX✔️
Cascading deletesON DELETENot yet (workaround)
Cascading updatesON UPDATENot yet (workaround coming soon)
Data validationCHECKNot yet (workaround)

Note that this table assumes that the operation is also supported by the underlying database. For example, ENUM is not supported in SQLite. This means that you also can't use enum when using Prisma Migrate.

Migration history

Prisma Migrate stores the migration history of your project in two places:

  • A directory called migrations on your file system
  • A table called _Migration in your database

The migrations directory

The migrations directory stores information about the migrations that have been or will be executed against your database. You should never make any manual changes to the files in migrations. The only way to change the content of this directory should be using the prisma migrate save command.

The migrations directory should be checked into version control (e.g. Git).

The _Migration table

The _Migration table additionally stores information about each migration that was ever executed against the database by Prisma Migrate.

Typical workflow

With Prisma Migrate, the workflow looks slightly different:

  1. Manually adjust your Prisma data model
  2. Migrate your database using the prisma migrate CLI commands
  3. (Re-)generate Prisma Client
  4. Use Prisma Client in your application code to access your database

Troubleshooting

Since Prisma Migrate is currently experimental, you might end up in a state where the migrations directory and/or the _Migrations table are out of sync with the actual state of the database. In these cases, it often helps to "reset" Prisma Migrate by deleting the migrations folder and deleting all entries from the _Migration table.

Delete the migrations directory

1rm -rf migrations

Delete all entries from the _Migration table

1TRUNCATE _Migration;
Edit this page on Github