Enum to String Migration: Avoiding Data Loss in Production

A step-by-step guide to altering enum columns to text without data loss or schema drift using prisma migrate dev --create-only

The migration Prisma doesn’t want you to run

If you’ve used Prisma long enough, you already know the drill: prisma migrate dev, hit enter, move on. It works beautifully — until the day it quietly tries to destroy your production data.

Last week, I hit exactly that wall. And the fix taught me something about how Prisma actually thinks about schema changes versus how Postgres thinks about them.

The Setup

I had a LifeArea enum defined in my Prisma schema, used across multiple tables in production. Classic case: the enum worked fine early on, but the product evolved, and we needed arbitrary string values instead of a fixed set. So the change looked simple enough:

// schema.prisma 

// Before
lifeArea  LifeArea?

// After
lifeArea  String?

One-line diff. How bad could it be?

Where It Goes Wrong

I ran prisma migrate dev and skimmed the generated SQL. That's when I saw it:

sql

ALTER TABLE "tasks" DROP COLUMN "lifeArea";
ALTER TABLE "tasks" ADD COLUMN "lifeArea" TEXT;

Prisma’s default strategy for a type change is brutal: drop the column, recreate it empty. On a fresh dev database, nobody notices. On production, every row’s lifeArea value is gone.

My first instinct was to make the field optional first, thinking Prisma would preserve data across an optional-to-optional change. It doesn’t. The underlying Postgres type is changing from "LifeArea" (enum) to text, and Prisma doesn't know how to bridge that — so it falls back to the nuclear option.

Why Raw SQL Alone Doesn’t Save You

The obvious next thought: just write the ALTER COLUMN TYPE myself.

sql

ALTER TABLE "tasks" ALTER COLUMN "lifeArea" TYPE TEXT;

Postgres rejects this. Enum-to-text isn’t an implicit cast, so you need a USING clause:

sql

ALTER TABLE "tasks" ALTER COLUMN "lifeArea" TYPE TEXT USING "lifeArea"::TEXT;

Better. But now a second trap: if you drop the enum type before the column conversion, the ::TEXT cast fails because the enum type no longer exists. Order matters.

And running any of this outside Prisma’s migration system creates schema drift — the database state no longer matches Prisma’s migration history. The next prisma migrate dev will notice, panic, and try to "fix" your database back to what it thinks reality should be.

The Solution

The trick is to let Prisma generate a migration file without applying it, then rewrite the SQL inside that file. Prisma still tracks it as a proper migration — no drift — but the actual SQL is yours.

Step 1 — Create the migration without running it:

bash

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

This produces a new folder under prisma/migrations/ with a migration.sql file containing Prisma's destructive default.

Step 2 — Replace the generated SQL with safe custom SQL:

sql

-- Convert columns FIRST, while the enum still exists
ALTER TABLE "tasks"
  ALTER COLUMN "lifeArea" TYPE TEXT USING "lifeArea"::TEXT;

-- Repeat for every table using the enum
ALTER TABLE "goals"
  ALTER COLUMN "lifeArea" TYPE TEXT USING "lifeArea"::TEXT;

-- Now it's safe to drop the enum type
DROP TYPE "LifeArea";

-- Handle indexes defensively
CREATE INDEX IF NOT EXISTS "tasks_lifeArea_idx" ON "tasks"("lifeArea");

Two things worth calling out here. First, the order: convert every column before dropping the enum, otherwise the USING cast has nothing to cast from. Second, CREATE INDEX IF NOT EXISTS — if Prisma generated any indexes earlier that already exist, this avoids the migration failing on a duplicate.

Step 3 — Apply it locally:

bash

npx prisma migrate dev

Prisma applies your custom SQL, records it in _prisma_migrations, and treats it as a first-class migration. No drift.

Step 4 — Deploy to production:

bash

npx prisma migrate deploy

This is the part I want to emphasize. On production, you don’t run migrate dev — you run migrate deploy. It applies pending migration files in order, with no schema comparison, no prompts, no ability to generate new migrations on the fly. It's the safe, idempotent, CI-friendly command. Same migration file, same SQL, same result. Zero data loss.

The Key Insight

Prisma’s migration system is two things stacked on top of each other: a SQL generator (which can be dumb about destructive changes) and a migration tracker (which is very smart about ordering and drift). --create-only lets you keep the tracker and throw out the generator when you need to.

Any time Prisma wants to drop a column that holds real data, reach for --create-only. Write the SQL yourself. Let Prisma track it.

Takeaways

• prisma migrate dev on a type change will happily drop your column. Always read the generated SQL on non-trivial schema changes.
• Enum-to-text needs an explicit USING "column"::TEXT cast in Postgres.
• Convert columns before dropping the enum type, not after.
• --create-only is the escape hatch. Use it whenever the default migration is unsafe.
• migrate deploy — not migrate dev — is what runs in production.

The whole thing ends up being one migration file, tracked cleanly, zero data loss, zero drift. But it takes understanding that Prisma’s default isn’t always the safe default.