Advanced migration scenarios

In some scenarios, you need to edit a migration before applying 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.

To edit a migration file before applying it:

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

  2. Create a draft migration using:

    $npx prisma migrate dev --create-only
  3. Modify the generated SQL file.

  4. Apply the modified SQL by running:

    $npx prisma migrate dev

Renaming a field

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
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
    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:

    $npx prisma migrate dev --name rename-migration --create-only
  3. Edit the draft migration as shown, changing DROP / DELETE to a single RENAME COLUMN:

    Before
    After
    ./prisma/migrations/20210308092620_rename_migration/migration.sql
    1ALTER TABLE "Profile" DROP COLUMN "biograpy",
    2ADD COLUMN "biography" TEXT NOT NULL;
  4. Save and apply the migration:

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

Changing the direction of a 1-1 relation

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?
    profileId @relation(fields: profileId, references: id)
    }
    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:

    $npx prisma migrate dev --name rename-migration --create-only
    Hide CLI results
    ⚠️ 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;
  4. Save and apply the migration:

    $npx prisma migrate dev
Edit this page on GitHub