Migration failed to apply cleanly to the shadow database after fixing a duplicated enum `@map`'d value - Error: P3006

Author: kasir-baratiCreated Jan 18, 2022Updated Sep 29, 2026

Labelsbug/2-confirmedkind/bugtech/enginestopic: enum

Note: @jkomyno has rewritten this issue for clarity and removed parts of the model that do not influence the problem. This issue is still present as of [email protected].

Bug description

Consider the following Prisma schema:

prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model File {
  id        Int       @id @default(autoincrement())
  mimetype  Mimetype
}

enum Mimetype {
  imageJpeg   @map("image/jpeg")
  imageBmp    @map("image/jpeg") // notice that this `@map`'d name is a duplicate
}

If I try running npx prisma migrate dev I get a unique constraint error, as expected:

bash

$ npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nest_base", schema "public" at "localhost:5432"

✔ Enter a name for the new migration: … add-mimetype-size
Applying migration `20220118144950_add_mimetype_size`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20220118144950_add_mimetype_size

Database error code: 23505

Database error:
ERROR: duplicate key value violates unique constraint "pg_enum_typid_label_index"
DETAIL: Key (enumtypid, enumlabel)=(16861, image/jpeg) already exists.

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("23505"), message: "duplicate key value violates unique constraint \"pg_enum_typid_label_index\"", detail: Some("Key (enumtypid, enumlabel)=(16861, image/jpeg) already exists."), hint: None, position: None, where_: None, schema: Some("pg_catalog"), table: Some("pg_enum"), column: None, datatype: None, constraint: Some("pg_enum_typid_label_index"), file: Some("nbtinsert.c"), line: Some(656), routine: Some("_bt_check_unique") }

However, even after updating the Mimetype enum as follows

prisma

// ...
enum Mimetype {
  imageJpeg   @map("image/jpeg")
  imageBmp    @map("image/bmp")
}

running npx prisma migrate dev raises another (unexpected) error:

bash

$ npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nest_base", schema "public" at "localhost:5432"

Error: P3006

Migration `20220118144950_add_mimetype_size` failed to apply cleanly to the shadow database. 
Error code: P2002
Error:
Unique constraint failed on the fields: (`enumtypid`,`enumlabel`)

I also went to check the generated migration for it. I saw that the generated SQL is like this without change even though I changed the mapped value:

sql

-- CreateEnum
CREATE TYPE "Mimetype" AS ENUM ('image/jpeg', 'image/jpeg');

-- AlterTable
ALTER TABLE "files" ADD COLUMN     "mimetype" "Mimetype",
ADD COLUMN     "size" INTEGER;

How to reproduce

Create a new prisma project
Change datasource to postgres
Change prisma file and add a enum type with duplicate mapped value

prisma

enum A {
  b @map("b/b")
  c @map("b/b")
}

Run the migrate commnad npx prisma migrate dev
It throw an error with P3018 error code and Database error code: 23505
Then fix the enum:

prisma

enum A {
  b @map("b/b")
  c @map("c/c")
}

Run the migrate commnad npx prisma migrate dev
Get the Error code: P3006 and P2002

Expected behavior

After updating the prisma schema file prisma changes the sql file too.

But now I have to change the sql by myself and it is not good. Because if I update the SQL it yields:

cmd

$ npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nest_base", schema "public" at "localhost:5432"

- The migration `20220118144950_add_mimetype_size` failed.
- The migration `20220118144950_add_mimetype_size` was modified after it was applied.

✔ We need to reset the PostgreSQL database "nest_base" at "localhost:5432".
Do you want to continue? All data will be lost. … no

Reset cancelled.

Environment & setup

DATABASE_URL=postgresql://postgres:008@localhost:5432/nest_base POSTGRES_EXPOSED_PORT=5432 POSTGRES_USER=postgres POSTGRES_PASSWORD=008 POSTGRES_DB=nest_base

Prisma Version

cmd

prisma                  : 3.6.0
@prisma/client          : 3.6.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : dc520b92b1ebb2d28dc3161f9f82e875bd35d727
Studio                  : 0.440.0

Source: prisma/prisma

View original on GitHub View discussion on GitHub