[BUG]: (Postgres) removing enum values in schema does not produce a migration

[jfcieslak]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.3

Describe the Bug

Deleting enum values in drizzle schema file, does not produce migrations. Note that this happen only if enum values are removed. Adding enum values produces a migration as expected. example:

1. change the following definition:

export const globalRole = pgEnum('global_role', ['SUSPENDED', 'REGISTERED', 'ADMIN', 'USELESS'])

to:

export const globalRole = pgEnum('global_role', ['SUSPENDED', 'REGISTERED', 'ADMIN'])

2. run drizzle-kit generate:pg

Expected behavior

If values of enums are removed in drizzle schema, it should produce new sql migration on drizzle-kit generate.

Environment & setup

No response

[michaelschufi]

For me, it creates a migration, but only adds enum values to the type. That's probably not what should happen. (orm: 0.28.1, kit: 0.19.12)

After some googling, it seems that it's not straightforward. The migration probably needs to

1. Add a new data type.
2. Alter the column's data type to the new one.
3. Drop the original one.
4. Rename the new type to the original one.

I'm not sure what this would imply and how easy it is to implement. But I use enums often enough that I miss having migrations for those.

[olafkrawczyk]

hi @michaelschufi have you found a working solution for it? I'm also facing this issue. Adding new enum seems to break a migration. It results with error error: ALTER TYPE ... ADD cannot run inside a transaction block

[kracas]

Hi, having similar issue where we removed an enum and created a table with the same name as the removed enum. The migration did not drop the enum and we got an error: "A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existing type." Resolved it by manually adding the drop of the enum to the migration file.

[michaelschufi]

@olafkrawczyk Sorry, for the late response. We have not yet found an automated solution for this problem. However, I believe you can alter the migrations file manually before actually migrating.

[olafkrawczyk]

@michaelschufi thanks for the response. We failed with manual edits, but that might be sql skill issue.

What I have discovered that the problem occurs on image postgres-11, postgres-12 and above works fine. Nevertheless it would be nice to see support for 11 in drizzle

[AndriiSherman]

There is no way to remove a value from an enum in PostgreSQL itself. Here is a great article about it: https://supabase.com/docs/guides/database/postgres/enums#removing-enum-values

We are releasing a patch update today that provides more explanation in an error message. However, the behavior is expected, and there is nothing we can do to change it as long as PostgreSQL can't handle it.

---

My off-topic recommendation: I would also not recommend using enums if the shape will change in the future. Even PostgreSQL developers suggest not removing values from enums or using them unless you already know the final shape of your enum

For example on our projects we are not using enums and instead doing this:

column: varchar("column_name").$type<"active" | "non_active">().notNull().default("active"),

column will be of type "active" | "non_active" And then we can change the type as much as we need; it's just a TypeScript helper for us

You can use $type with text, varchar or any other type