Advanced migration scenarios

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