Adding an `enum` field on a model and removing an `enum` value at the same time generates a failing migration (Postgresql)

[Errorname]

Bug description

When adding an enum field on a model and removing an enum value at the same time, Prisma will generate a migration in two steps:

1. AlterEnum to replace the enum with a new enum with the new values, using a postgres transaction
2. AlterTable to add the field with that enum on the model

However, the AlterEnum step tries to replace the enum type of the field that is created in the AlterTable, resulting in an SQL error.

How to reproduce

See reproduction repository here: https://github.com/Errorname/prisma-alter-enum-error (steps in readme)

Expected behavior

The generated AlterEnum step transaction should not try to replace the type of the field that is not yet created.

Prisma information

See reproduction repository here: https://github.com/Errorname/prisma-alter-enum-error

Environment & setup

• OS: macOS
• Database: Postgresql
• Node.js version: 20.10.0

Prisma Version

Environment variables loaded from .env
prisma                  : 5.14.0
@prisma/client          : 5.14.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v20.10.0
Query Engine (Node-API) : libquery-engine e9771e62de70f79a5e1c604a2d7c8e2a0a874b48 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli e9771e62de70f79a5e1c604a2d7c8e2a0a874b48 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.14.0-25.e9771e62de70f79a5e1c604a2d7c8e2a0a874b48
Default Engines Hash    : e9771e62de70f79a5e1c604a2d7c8e2a0a874b48
Studio                  : 0.500.0

[janpio]

I can confirm this bug.

Adding an enum to a model, while adding and removing an enum value indeed leads to this error:

Applying migration `20240528131646_failing`
Error: ERROR: current transaction is aborted, commands ignored until end of transaction block
   0: schema_core::commands::apply_migrations::Applying migration
           with migration_name="20240528131646_failing"
             at schema-engine/core/src/commands/apply_migrations.rs:91
   1: schema_core::state::ApplyMigrations
             at schema-engine/core/src/state.rs:202

The generated SQL:

/*
  Warnings:

  - The values [user] on the enum `UserType` will be removed. If these variants are still used in the database, this will fail.
  - Added the required column `accessType` to the `Post` table without a default value. This is not possible if the table is not empty.

*/
-- AlterEnum
BEGIN;
CREATE TYPE "UserType_new" AS ENUM ('admin', 'api', 'something_else');
ALTER TABLE "User" ALTER COLUMN "type" TYPE "UserType_new" USING ("type"::text::"UserType_new");
ALTER TABLE "Post" ALTER COLUMN "accessType" TYPE "UserType_new" USING ("accessType"::text::"UserType_new");
ALTER TYPE "UserType" RENAME TO "UserType_old";
ALTER TYPE "UserType_new" RENAME TO "UserType";
DROP TYPE "UserType_old";
COMMIT;

-- AlterTable
ALTER TABLE "Post" ADD COLUMN     "accessType" "UserType" NOT NULL;

I don't understand yet why this fails.

[janpio]

Ok, changing the enum used in Post before actually adding it of course fails:

ALTER TABLE "Post" ALTER COLUMN "accessType" TYPE "UserType_new" USING ("accessType"::text::"UserType_new");
...
ALTER TABLE "Post" ADD COLUMN     "accessType" "UserType" NOT NULL;

We need to change the order between AlterEnum and AlterTable here.

[Errorname]

Not sure if possible, but it could be interesting to remove the SQL command altering the not-yet-created column instead of switching the order of AlterEnum and AlterTable ? That's one less SQL command to execute

[janpio]

Good point. We should check if that is possible, or if it makes the logic too complex. This is most probably not such a common case to justify this if it severely complicates the implementation.

[hls-app]

Another thing I found is, if you try to remove a value from the enum that is used, then you get a migration failure and honestly I had no way of recovering without restoring the DB.

Easy to reproduce.

1. Have a table with an enum type column
2. Seed some data
3. Remove one of the enum value using a migration
4. You will find yourself in a situation of failed migration

Error :

Error: ERROR: current transaction is aborted, commands ignored until end of transaction block
   0: schema_core::commands::apply_migrations::Applying migration
           with migration_name="20240930105245_drop_added_report_from_user_submission_type"
             at schema-engine/core/src/commands/apply_migrations.rs:91
   1: schema_core::state::ApplyMigrations
             at schema-engine/core/src/state.rs:226

Not very helpful error and why is it so hard to rollback a migration? I don't know if this is skill issue or am I missing something? I manually deleted the entry from _prisma_migrations and removed the generated migration from migrations. Some how Prisma was still complaining about the drift in migration.