Customizing migrations

In some scenarios, you need to edit a migration file before you apply it. For example, to change the direction of a 1-1 relation (moving the foreign key from one side to another) without data loss, you need to move data as part of the migration - this SQL is not part of the default migration, and must be written by hand.

This guide explains how to edit migration files and gives some examples of use cases where you may want to do this.

To edit a migration file before applying it, the general procedure is the following:

1. Make a schema change that requires custom SQL (for example, to preserve existing data)

2. Create a draft migration using:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --create-only

   pnpm prisma migrate dev --create-only

   yarn prisma migrate dev --create-only

   npx prisma migrate dev --create-only

   3. Modify the generated SQL file.
   4. Apply the modified SQL by running:

3. Modify the generated SQL file.

4. Apply the modified SQL by running:

   • Tab 1
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev

   pnpm prisma migrate dev

   yarn prisma migrate dev

   npx prisma migrate dev

By default, renaming a field in the schema results in a migration that will:

• CREATE a new column (for example, fullname)
• DROP the existing column (for example, name) and the data in that column

To actually rename a field and avoid data loss when you run the migration in production, you need to modify the generated migration SQL before applying it to the database. Consider the following schema fragment - the biograpy field is spelled wrong.

model Profile {

  id       Int    @id @default(autoincrement())

  biograpy String

  userId   Int    @unique

  user     User   @relation(fields: [userId], references: [id])

}

To rename the biograpy field to biography:

1. Rename the field in the schema:

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

2. Run the following command to create a draft migration that you can edit before applying to the database:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name rename-migration --create-only

   pnpm prisma migrate dev --name rename-migration --create-only

   yarn prisma migrate dev --name rename-migration --create-only

   npx prisma migrate dev --name rename-migration --create-only

   3. Edit the draft migration as shown, changing DROP / DELETE to a single RENAME COLUMN:

3. Edit the draft migration as shown, changing DROP / DELETE to a single RENAME COLUMN:

   • Tab 1
   • After

   ALTER TABLE "Profile" DROP COLUMN "biograpy",
   
   ADD COLUMN  "biography" TEXT NOT NULL;

   ALTER TABLE "Profile"
   RENAME COLUMN "biograpy" TO "biography"

   For SQL Server, you should use the stored procedure sp_rename instead of ALTER TABLE RENAME COLUMN.

   • Tab 1

    EXEC sp_rename 'dbo.Profile.biograpy', 'biography', 'COLUMN';

4. Save and apply the migration:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev

   pnpm prisma migrate dev

   yarn prisma migrate dev

   npx prisma migrate dev

   You can use the same technique to rename a model - edit the generated SQL to rename the table rather than drop and re-create it.

You can use the same technique to rename a model - edit the generated SQL to rename the table rather than drop and re-create it.

Making schema changes to existing fields, e.g., renaming a field can lead to downtime. It happens in the time frame between applying a migration that modifies an existing field, and deploying a new version of the application code which uses the modified field.

You can prevent downtime by breaking down the steps required to alter a field into a series of discrete steps designed to introduce the change gradually. This pattern is known as the expand and contract pattern.

The pattern involves two components: your application code accessing the database and the database schema you intend to alter.

With the expand and contract pattern, renaming the field bio to biography would look as follows with Prisma:

1. Add the new biography field to your Prisma schema and create a migration

   model Profile {
   
     id        Int    @id @default(autoincrement())
   
     bio       String
   
     biography String
   
     userId    Int    @unique
   
     user      User   @relation(fields: [userId], references: [id])
   
   }

2. Expand: update the application code and write to both the bio and biography fields, but continue reading from the bio field, and deploy the code

3. Create an empty migration and copy existing data from the bio to the biography field

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name copy_biography --create-only

   pnpm prisma migrate dev --name copy_biography --create-only

   yarn prisma migrate dev --name copy_biography --create-only

   npx prisma migrate dev --name copy_biography --create-only

   UPDATE "Profile" SET biography = bio;

   4. Verify the integrity of the biography field in the database
   5. Update application code to read from the new biography field
   6. Update application code to stop writing to the bio field
   7. Contract: remove the bio from the Prisma schema, and create a migration to remove the bio field

      model Profile {  id        Int    @id @default(autoincrement())  bio       String  biography String  userId    Int    @unique  user      User   @relation(fields: [userId], references: [id])}

   UPDATE "Profile" SET biography = bio;

4. Verify the integrity of the biography field in the database

5. Update application code to read from the new biography field

6. Update application code to stop writing to the bio field

7. Contract: remove the bio from the Prisma schema, and create a migration to remove the bio field

   model Profile {
   
     id        Int    @id @default(autoincrement())
   
     bio       String
   
     biography String
   
     userId    Int    @unique
   
     user      User   @relation(fields: [userId], references: [id])
   
   }

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name remove_bio

   pnpm prisma migrate dev --name remove_bio

   yarn prisma migrate dev --name remove_bio

   npx prisma migrate dev --name remove_bio

   By using this approach, you avoid potential downtime that altering existing fields that are used in the application code are prone to, and reduce the amount of coordination required between applying the migration and deploying the updated application code.

   Note that this pattern is applicable in any situation involving a change to a column that has data and is in use by the application code. Examples include combining two fields into one, or transforming a 1:n relation to a m:n relation.

   To learn more, check out the Data Guide article on the expand and contract pattern

By using this approach, you avoid potential downtime that altering existing fields that are used in the application code are prone to, and reduce the amount of coordination required between applying the migration and deploying the updated application code.

Note that this pattern is applicable in any situation involving a change to a column that has data and is in use by the application code. Examples include combining two fields into one, or transforming a 1:n relation to a m:n relation.

To learn more, check out the Data Guide article on the expand and contract pattern

To change the direction of a 1-1 relation:

1. Make the change in the schema:

   model User {
   
     id        Int      @id @default(autoincrement())
   
     name      String
   
     posts     Post[]
   
     profile   Profile? @relation(fields: [profileId], references: [id])
   
     profileId Int      @unique
   
   }
   
   model Profile {
   
     id        Int    @id @default(autoincrement())
   
     biography String
   
     user      User
   
   }

2. Run the following command to create a draft migration that you can edit before applying to the database:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name rename-migration --create-only

   pnpm prisma migrate dev --name rename-migration --create-only

   yarn prisma migrate dev --name rename-migration --create-only

   npx prisma migrate dev --name rename-migration --create-only

   ⚠️  There will be data loss when applying the migration:
   
   • The migration will add a unique constraint covering the columns `[profileId]` on the table `User`. If there are existing duplicate values, the migration will fail.

   3. Edit the draft migration as shown:

   ⚠️  There will be data loss when applying the migration:
   
   • The migration will add a unique constraint covering the columns `[profileId]` on the table `User`. If there are existing duplicate values, the migration will fail.

3. Edit the draft migration as shown:

   • Before
   • After

   
   -- DropForeignKey
   
   ALTER TABLE "Profile" DROP CONSTRAINT "Profile_userId_fkey";
   
   -- DropIndex
   
   DROP INDEX "Profile_userId_unique";
   
   -- AlterTable
   
   ALTER TABLE "Profile" DROP COLUMN "userId";
   
   -- AlterTable
   
   ALTER TABLE "User" ADD COLUMN     "profileId" INTEGER NOT NULL;
   
   -- CreateIndex
   
   CREATE UNIQUE INDEX "User_profileId_unique" ON "User"("profileId");
   
   -- AddForeignKey
   
   ALTER TABLE "User" ADD FOREIGN KEY ("profileId") REFERENCES "Profile"("id") ON DELETE CASCADE ON UPDATE CASCADE;

    EXEC sp_rename 'dbo.Profile.biograpy', 'biography', 'COLUMN';

   4. Save and apply the migration:

4. Save and apply the migration:

   • Tab 1
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev

   pnpm prisma migrate dev

   yarn prisma migrate dev

   npx prisma migrate dev