Developing with Prisma Migrate

/ Guides / Database migrations

This guide takes you through a typical development workflow with Prisma Migrate, from defining a schema to committing migrations to source control. This guide starts with an empty database, but you can also add Prisma Migrate to an existing project.

In a development environment, you use the migrate dev command to create and apply migrations:


$npx prisma migrate dev

Prototyping? Use the db push command if you are prototyping and are not concerned with data loss or replicating your changes in other environments. You can start or continue your migration history when you are happy with your changes.

When you are comfortable with using Prisma Migrate in development, consider the following more advanced guides:

This guide does not apply for MongoDB.
Instead of migrate dev, db push is used for MongoDB.

Create a schema

This guide uses the following schema as a starting point:


generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id      Int      @id @default(autoincrement())
  name    String
  posts   Post[]
  profile Profile?
}

model Profile {
  id       Int    @id @default(autoincrement())
  biograpy String // Intentional typo!
  userId   Int    @unique
  user     User   @relation(fields: [userId], references: [id])
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  published  Boolean    @default(true)
  content    String
  authorId   Int
  author     User       @relation(fields: [authorId], references: [id])
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]

  @@unique([name])
}

Refine your schema with native type attributes

Prisma Migrate supports native field type attributes , which allow you to specify which underlying database type should be created.

For example, the PostgreSQL provider maps String to text by default. To change the default mapping:

Add a native type annotation to make the title field a varchar(200) in the underlying database:


// ...

model Post {
  id         Int        @id @default(autoincrement())
  title      String     @db.VarChar(200)
  published  Boolean    @default(true)
  content    String
  authorId   Int
  author     User       @relation(fields: [authorId], references: [id])
  categories Category[]
}

// ...

Configure the shadow database

In a development environment only, Prisma Migrate uses a temporary shadow database to perform tasks such as detecting schema drift.

If you are developing against a locally hosted database, make sure your user has sufficient privileges
If you are developing against a cloud-hosted database, you need to provision a shadow database and set the shadowDatabaseUrl field

Create migrations

To create a migration, make a change to your schema and run the following command to create and apply migrations:


$npx prisma migrate dev

The following example results in a migration history with three migrations.

First migration: Initialize migration history

Run the following command to initialize a migration history and get started with Prisma Migrate:


$npx prisma migrate dev --name first-migration

Show CLI results

Note: If you do not provide a --name, Prisma CLI will prompt you for a name.

When you run the migrate dev command for the first time, Prisma Migrate:

Creates a ./prisma/migrations folder with your initial migration:


migrations/
└─ 20210305110829_first_migration/
  └─ migration.sql

Creates a table named _prisma_migrations in the database with an entry for the first migration:

id	checksum	finished_at	migration_name	logs	rolled_back_at	started_at	applied_steps_count
5f285eeb-3714-461c-bb3a-03a7adad6aad	1beaf4ec9e8cfdc1f fa546aa0b0d94ba2b382293d 7b7237619202f2372c	2021-03-09 14:55:38.712883	20210305110829_first_migration	NULL	NULL	2021-03-09 14:55:38.542458	1

Second migration: Add new fields

Add two fields to your schema:


model User {
  id       Int      @id @default(autoincrement())
  name     String
  jobTitle String   @db.VarChar(100)
  posts    Post[]
  profile  Profile?
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  published  Boolean    @default(true)
  content    String
  authorId   Int
  author     User       @relation(fields: [authorId], references: [id])
  tags       String[]
  categories Category[]
}

Generate a migration:


$npx prisma migrate dev --name add-fields

Your _prisma_migrations table now has two entries:

id	checksum	finished_at	migration_name	logs	rolled_back_at	started_at	applied_steps_count
5f285eeb-3714-461c-bb3a-03a7adad6aad	1beaf4ec9e8cfdc1ffa 546aa0b0d94ba2b382293d 7b7237619202f2372c	2021-03-09 14:55:38.712883	20210305110829_first_migration	NULL	NULL	2021-03-09 14:55:38.542458	1
034e198c-dd9f-482a-afdc-26437708ed1e	b12be494d0f81 716d6732ae221b02a65677c6 2aafbb76f6e3fea4929f77f3d	2021-03-09 14:55:38.767893	20210305120829_add_fields	NULL	NULL	2021-03-09 14:55:38.724587	1

Your migrations folder now has two migrations:


migrations/
└─ 20210305110829_first_migration/
  └─ migration.sql
└─ 20210305120829_add_fields/
  └─ migration.sql

Third migration: Change a field type to a compatible type

Change the name field to a varchar(50) (default is text):


model Category {
  id    Int    @id @default(autoincrement())
  name  String
  name  String @db.VarChar(50)
  posts Post[]

  @@unique([name])
}

Generate a migration:


$npx prisma migrate dev --name type-change

Your _prisma_migrations table now has three entries:

id	checksum	finished_at	migration_name	logs	rolled_back_at	started_at	applied_steps_count
5f285eeb-3714-461c-bb3a-03a7adad6aad	1beaf4ec9e8cfdc1ffa 546aa0b0d94ba2b382293d 7b7237619202f2372c	2021-03-09 14:55:38.712883	20210305110829_first_migration	NULL	NULL	2021-03-09 14:55:38.542458	1
034e198c-dd9f-482a-afdc-26437708ed1e	b12be494d0f81 716d6732ae221b02a65677c6 2aafbb76f6e3fea4929f77f3d	2021-03-09 14:55:38.767893	20210305120829_add_fields	NULL	NULL	2021-03-09 14:55:38.724587	1
932e198c-2d9f-182a-afdc-26437708ed1e	cd14e494d0f81 216d3732ae221b02a65677c6 fddfbb76f6e3fea4929f77f3d	2021-03-09 14:55:38.767893	20210308102042_type_change	NULL	NULL	2021-03-09 14:55:38.724587	1

Your migrations folder now has three migrations:


migrations/
└─ 20210305110829_first_migration/
  └─ migration.sql
└─ 20210305120829_add_fields/
  └─ migration.sql
└─ 20210308102042_type-change/
  └─ migration.sql

Advanced scenarios

Schema changes are often additive, such as adding a new field, model, or relation. If you move, rename, or significantly refactor a part of your schema, you will most likely need to edit the generated SQL before applying it to preserve existing data.

See Advanced migration scenarios for examples.

Write a seed script

You should expect to reset your database in a development environment - for example, if you regularly switch between branches that have different migration histories, Prisma Migrate will prompt you to migrate reset when you run prisma migrate dev. If a seed script is available, Prisma Migrate runs that script after resetting the database.

See Seeding your database for examples.

Was this helpful?

Edit this page on GitHub

Developing with Prisma Migrate

Create a schema

Refine your schema with native type attributes

Configure the shadow database

Create migrations

First migration: Initialize migration history

Second migration: Add new fields

Third migration: Change a field type to a compatible type

Advanced scenarios

Write a seed script

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter