Developing with Prisma Migrate

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:

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

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:

    idchecksumfinished_atmigration_namelogsrolled_back_atstarted_atapplied_steps_count
    5f285eeb-3714-461c-bb3a-03a7adad6aad1beaf4ec9e8cfdc1f fa546aa0b0d94ba2b382293d 7b7237619202f2372c2021-03-09 14:55:38.71288320210305110829_first_migrationNULLNULL2021-03-09 14:55:38.5424581

Second migration: Add new fields

  1. 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[]
    }
  2. Generate a migration:

    $npx prisma migrate dev --name add-fields

    Your _prisma_migrations table now has two entries:

    idchecksumfinished_atmigration_namelogsrolled_back_atstarted_atapplied_steps_count
    5f285eeb-3714-461c-bb3a-03a7adad6aad1beaf4ec9e8cfdc1ffa 546aa0b0d94ba2b382293d 7b7237619202f2372c2021-03-09 14:55:38.71288320210305110829_first_migrationNULLNULL2021-03-09 14:55:38.5424581
    034e198c-dd9f-482a-afdc-26437708ed1eb12be494d0f81 716d6732ae221b02a65677c6 2aafbb76f6e3fea4929f77f3d2021-03-09 14:55:38.76789320210305120829_add_fieldsNULLNULL2021-03-09 14:55:38.7245871

    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

  1. 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])
}
  1. Generate a migration:

    $npx prisma migrate dev --name type-change

    Your _prisma_migrations table now has three entries:

    idchecksumfinished_atmigration_namelogsrolled_back_atstarted_atapplied_steps_count
    5f285eeb-3714-461c-bb3a-03a7adad6aad1beaf4ec9e8cfdc1ffa 546aa0b0d94ba2b382293d 7b7237619202f2372c2021-03-09 14:55:38.71288320210305110829_first_migrationNULLNULL2021-03-09 14:55:38.5424581
    034e198c-dd9f-482a-afdc-26437708ed1eb12be494d0f81 716d6732ae221b02a65677c6 2aafbb76f6e3fea4929f77f3d2021-03-09 14:55:38.76789320210305120829_add_fieldsNULLNULL2021-03-09 14:55:38.7245871
    932e198c-2d9f-182a-afdc-26437708ed1ecd14e494d0f81 216d3732ae221b02a65677c6 fddfbb76f6e3fea4929f77f3d2021-03-09 14:55:38.76789320210308102042_type_changeNULLNULL2021-03-09 14:55:38.7245871

    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.

Edit this page on GitHub