This guide describes how to generate a down migration SQL file that reverses a given migration file.

About down migrations

When generating a migration SQL file, you may wish to also create a "down migration" SQL file that reverses the schema changes in the corresponding "up migration" file.

This guide explains how to use Prisma Migrate's migrate diff command to create a down migration, and how to apply it to your production database with the db execute command.

This guide applies to generating SQL down migrations for relational databases only. It does not apply to MongoDB.

The migrate diff and db execute commands are available in Preview in versions 3.9.0 and later, and are generally available in versions 3.13.0 and later.

Considerations when generating down migrations

When generating a down migration file, there are some considerations to be aware of:

  • The down migration will revert your database schema, but other changes to data and application code that are carried out as part of the up migration will not be reverted. For example, if you have a script that changes data during the migration, this data will not be changed back when you run the down migration.
  • You will not be able to use migrate diff to revert manually changed or added SQL in your migration files. If you have any custom additions, such as a view or trigger, you will need to:
    • Create the down migration following the instructions below
    • Create the up migration using migrate dev --create-only, so that it can be edited before it is applied to the database
    • Manually add your custom SQL to the up migration (e.g. adding a view)
    • Manually add the inverted custom SQL to the down migration (e.g. dropping the view)

How to generate and run down migrations

This section describes how to generate a down migration SQL file along with the corresponding up migration, and then run it on production.

As an example, take the following Prisma schema with a User and Post model as a starting point:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String @db.VarChar(255)
4 content String?
5 author User @relation(fields: [authorId], references: [id])
6 authorId Int
7}
8
9model User {
10 id Int @id @default(autoincrement())
11 name String?
12 posts Post[]
13}

You will need to create the down migration first, before creating the corresponding up migration.

Generating the migrations

  1. Edit your Prisma schema to make the changes you require for your up migration. In this example, you will add a new Profile model:

    schema.prisma
    1model Post {
    2 id Int @id @default(autoincrement())
    3 title String @db.VarChar(255)
    4 content String?
    5 author User @relation(fields: [authorId], references: [id])
    6 authorId Int
    7}
    +
    +model Profile {
    + id Int @id @default(autoincrement())
    + bio String?
    + user User @relation(fields: [userId], references: [id])
    + userId Int @unique
    +}
    15
    16model User {
    17 id Int @id @default(autoincrement())
    18 name String?
    19 posts Post[]
    + profile Profile?
    21}
  2. Generate the SQL file for the down migration. To do this, you will use migrate diff to make a comparison:

    • from the newly edited schema
    • to the state of the schema after the last migration

    and output this to a SQL script, down.sql.

    There are two potential options for specifying the 'to' state:

    • Using --to-migrations: this makes a comparison to the state of the migrations given in the migrations directory. This is the preferred option, as it is more robust, but it requires a shadow database. To use this option, run:

      $npx prisma migrate diff \
      $ --from-schema-datamodel prisma/schema.prisma \
      $ --to-migrations prisma/migrations --shadow-database-url $SHADOW_DATABASE_URL \
      $ --script > down.sql
    • Using --to-schema-datasource: this makes a comparison to the state of the database. This does not require a shadow database, but it does rely on the database having an up-to-date schema. To use this option, run:

      $npx prisma migrate diff \
      $ --from-schema-datamodel prisma/schema.prisma \
      $ --to-schema-datasource prisma/schema.prisma \
      $ --script > down.sql
  3. Generate and apply the up migration with a name of add_profile:

    $npx prisma migrate dev --name add_profile

    This will create a new <timestamp>_add_profile directory inside the prisma/migrations directory, with your new migration.sql up migration file inside.

  4. Copy your down.sql file into the new directory along with the up migration file.

Applying the down migration

To apply the down migration on your production database:

  1. Use db execute to run your down.sql file on the database server:

    $npx prisma db execute --file ./down.sql --schema prisma/schema.prisma
  2. Use migrate resolve to record that you rolled back the up migration named add_profile:

    $npx prisma migrate resolve --rolled-back add_profile
Edit this page on GitHub