How to make destructive changes to a Postgres DB with Prisma

TLDR:

• Make the changes to the Prisma schema
• Create the migration without executing it prisma migrate dev --create-only
• Edit the outputted migration file
• Execute the migration prisma migrate dev

Prerequisite:

You most likely want to make a replica of the DB so you can test out you changes against it. Here is an article that talks through how to do that:

How to make a replica copy of a DB

Making the destructive migration

Now we can start the migration.

1. Make the desired changes to your Prisma schema
2. Create a migration file without running it

pnpm prisma migrate dev --create-only

3. Go into the migration file and manually add any queries required to copy data etc.

Here is an example where I changed a one to one relationship to a one to many:

❗ In this example, I was able to run the data mutations after DROPping the relevant columns. Normally you will likely need to do this before dropping columns but in this example I had a copy of the needed data on another table

TLDR: Order matters so make sure to use any data you need before you DROP it

/*
  Warnings:

  - You are about to drop the column `blockAnswerUuid` on the `Path` table. All the data in the column will be lost.

*/
-- DropForeignKey
ALTER TABLE "Path" DROP CONSTRAINT "Path_blockAnswerUuid_fkey";

-- DropIndex
DROP INDEX "Path_blockAnswerUuid_key";

-- AlterTable
ALTER TABLE "BlockAnswer" ADD COLUMN     "pathUuid" TEXT;

-- AlterTable
ALTER TABLE "Path" DROP COLUMN "blockAnswerUuid";

-- AddForeignKey
ALTER TABLE "BlockAnswer" ADD CONSTRAINT "BlockAnswer_pathUuid_fkey" FOREIGN KEY ("pathUuid") REFERENCES "Path"("uuid") ON DELETE NO ACTION ON UPDATE CASCADE;

-- Here is where I make the data mutations. Order counts so be sure to double check it

UPDATE "BlockAnswer" AS ba
SET "pathUuid" = tps."pathUuid"
FROM "TesterPathSummary" AS tps
WHERE ba."uuid" = tps."blockAnswerUuid";

4. The final step is to then run the migration:

❗ This step is irreversible. If you have made a mistake in one of the steps, the other steps will still run regardless so you can end up is a mess. Test it on a replica DB first

prisma migrate dev