prisma: Use enum with reference table does not work until 5.4.0

Bug description

With the previous versions of prisma and @prisma/client we were able to use the enum inside our schema.prisma and point on our reference table that matches this enum. Since 5.4.0, which includes improvements around how prisma handle enums, we are not able to upgrade because the code now throw errors while using those enums in a where of a findMany or in the data of a create.

The documentation does not mention this was something supported, but it worked for us and we prefer working with table instead of an enum type on the database point of view, for the visibility that gives while debugging.

How to reproduce

Have a currency table with only one column called abbreviation and some values stored in this table
Create another table that referes the currency table, in our case billing_settings that has a currency column

In the prisma.schema we have

enum Currency {
  CAD
  USD
  [...] <-- all the currencies we are supporting are listed here

  @@map("currency")
}

model BillingSettings {
    [...]
    currency                                               Currency        @default(USD)
}

In the code just call prisma with

await getPrismaClient().billingSettings.findMany({ where: { currency: Currency.CAD } });

await getPrismaClient().billingSettings.create({
  data: { ...billingSettings, currency: Currency.CAD }
});

The code will fail findMany

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "42883", message: "operator does not exist: text = ecurrency", severity: "ERROR", detail: None, column: None, hint: Some("No operator matches the given name and argument types. You might need to add explicit type casts.") }), transient: false })

create

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "22P02", message: "malformed record literal: \"CAD\"", severity: "ERROR", detail: Some("Missing left parenthesis."), column: None, hint: None }), transient: false })

Expected behavior

Both queries would work like they were on 5.3.0 and below

Prisma information

Already provided in the description

Environment & setup

OS: macOS
Database: PostgreSQL
Node.js version: v18.16.0

Prisma Version

prisma                  : 5.5.2
@prisma/client          : 5.5.2
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.5.1-1.aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Default Engines Hash    : aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Studio                  : 0.494.0
Preview Features        : views

About this issue

Original URL
State: open
Created 8 months ago
Reactions: 6
Comments: 15 (7 by maintainers)

Most upvoted comments

@Jolg42 There you go, I’ve been able to reproduce it in this project: https://github.com/jimmyfortinx/prisma-issue-21693

jimmyfortinx on Oct 31, 2023

@@represent-as-table as described would be a good option for us. We are not using prisma’s generated migration SQL, so it seems like the issue about the column name won’t affect us. Am I wrong?

@@represent-as-table(column: "abbreviation") sounds like a good option to provide the column name if I’m wrong on the previous part.

jimmyfortinx on Nov 1, 2023

On 5.3.0

prisma:query INSERT INTO "public"."billing_settings" ("id","currency") VALUES ($1,$2) RETURNING "public"."billing_settings"."id", "public"."billing_settings"."currency"
prisma:query SELECT "public"."billing_settings"."id", "public"."billing_settings"."currency" FROM "public"."billing_settings" WHERE "public"."billing_settings"."currency" = $1 OFFSET $2

On 5.5.2

prisma:query INSERT INTO "public"."billing_settings" ("id","currency") VALUES ($1,CAST($2::text AS "public"."currency")) RETURNING "public"."billing_settings"."id", "public"."billing_settings"."currency"::text
prisma:query SELECT "public"."billing_settings"."id", "public"."billing_settings"."currency"::text FROM "public"."billing_settings" WHERE "public"."billing_settings"."currency" = CAST($1::text AS "public"."currency") OFFSET $2

The issue seems to be coming from the cast. $2 vs CAST($2::text AS "public"."currency") and $1 vs CAST($1::text AS "public"."currency")

jimmyfortinx on Nov 1, 2023

We favour the use of a reference table for constraining values because:

It’s more explicit and useful to be able to see and inspect types from a table
You cannot fetch all values from the enum (that’s often useful when doing search endpoints)
Enums are not part of the ANSI/ISO SQL, so it’s not portable (for exemple to a Data Warehouse) and less people know about it
There is no real performance impact of having a separate table for this

jimmyfortinx on Nov 1, 2023

Ok, I only now looked deeper at the reproduction and undertstood that the currency field that is supposed to point to the enum, is replaced with a TEXT field instead which has a reference to the currency.abbreviation field (which contains all the enum values): https://github.com/jimmyfortinx/prisma-issue-21693/blob/4859f79ad6943340eb0b5f598d048fbe262cb90e/database/migrations/20231031133254_create-tables.js#L16 That gives similar guarantees like a real enum in that only existing values can be set, and updated are potentially also handled correctly.

Going into speculative mode:

The correct way forward here would then be to add this as a proper feature to Prisma, that one can choose for enums to be represented via tables instead of enums. It would probably have to be an attribute to enum blocks (@@represent-as-table, hopefully a bit more elegant) and then we would need to update the generated migration SQL for enums accordingly.

The name of the table we could just get from the enum, but the column name (in your case abbreviation) does not exist yet. Would you be ok with a generic name for that or is being able to call it a specific name relevant and important for you? (That would probably mean another option for the new attribute, @@represent-as-table(column: "abbreviation").

janpio on Nov 1, 2023

It would probably have to be an attribute to enum blocks (@@represent-as-table, hopefully a bit more elegant) and then we would need to update the generated migration SQL for enums accordingly.

@janpio would you be able to provide some guidance as to where this SQL generation is made in the code? Maybe we could help by contributing if this is not expected to be fixed anytime soon?

Thank you 🙏

sambegin on Feb 26, 2024