[BUG]: Unable to Insert enum in postgres

[anasmohammed361]

What version of drizzle-orm are you using?

^0.28.2

What version of drizzle-kit are you using?

^0.19.12

Describe the Bug

Basic Setup

export const userRoles = pgEnum('role', ['admin', 'maintainer']);
export const users = pgTable("userAuth", {
  id: text("id").notNull().primaryKey(),
  name: text("name"),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("emailVerified"),
  image: text("image"),
  role:userRoles('role').default('maintainer').notNull()
});

 await db.insert(users).values({
        email:"a@a",
        role:userRoles.enumValues[0],
        id:"123"
    })

I get this error

 BadRequestException: ERROR: column "role" is of type role but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 125; SQLState: 42804 

Expected behavior

No response

Environment & setup

No response

[anasmohammed361]

Found out it was an issue with AWS DATA API . Found a simple work around , i switched from using enum to text , and chose validation at application level

text("type", { enum: [ 'a', 'b, '...']}).notNull()

[jakeleventhal]

@anasmohammed361 is there a GH issue tracking this for the data API?

[pantoninho]

I'm getting the same error when using drizzle's API, but I'm successful if I replace it with a raw SQL query, e.g:

// `type` is an enum
const res = await tx.execute(
  sql`INSERT INTO applications (type) VALUES ('silver') RETURNING *`
);

Although aws-data-api works with this raw sql query, I've noticed the query generated by the driver is way more complex. Can this be fixed in the driver?

[pantoninho]

After some research, I guess in my case it's just a limitation of AWS Data API.

from https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.limitations:

For Aurora PostgreSQL Serverless v2 and provisioned DB clusters, RDS Data API doesn't support enumerated types. For the list of supported types, see Comparison of RDS Data API with Serverless v2 and provisioned, and Aurora Serverless v1.

[anasmohammed361]

It is an issue with AWS, not with drizzle

[tqhoughton]

Are you sure it's an issue with the Data API? AWS shows that they support enums in the list of supported field types:

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.differences

As mentioned before, it seems like if the data API can support raw SQL that references enums, that the problem is with how Drizzle is mapping the incoming data to the RDSDataClient

[Thourum]

Same here, Drizzle and Drizzle kit both fail to update with column with pgEnum

ERROR: column "language" is of type language but expression is of type text; Hint: You will need to rewrite or cast the expression.

However if I run the SQL through Query Editor on AWS console it works just fine

UPDATE public.user
SET "language" = 'en'::"language"
WHERE id = ...

[philipbeber]

I find this annoying too. You can also work around it by using raw sql just for the enum value. E.g.

  await db.insert(histories).values({
    col1 "value1",
    col2: "value2",
    action: sql`'create'`, // or sql`'create'::history_action`
  });

I ended creating a wrapper around pgEnum and adding my own method for converting it safely:

type RetType<U extends string, T extends Readonly<[U, ...U[]]>> = PgEnum<
  Writable<T>
> & { sql: (value: T[number]) => ReturnType<typeof sql> };

export function pgEnum2<
  U extends string,
  T extends Readonly<[U, ...U[]]>
>(enumName: string, values: T | Writable<T>): RetType<U, T> {
  const pg = pgEnum(enumName, values) as RetType<U, T>;
  pg.sql = (value: (typeof pg.enumValues)[number]) =>
    sql.raw(`'${value}'::${enumName}`);
  return pg;
}

My enums are defined almost exactly as before:

export const historyActionEnum = pgEnum2("history_action", [
  "create",
  "update",
  "delete",
]);

And the update statement now looks like:

  await db.insert(histories).values({
    col1 "value1",
    col2: "value2",
    action: historyActionEnum.sql("create"),
  });

Everything is type safe so any typos will be caught right away.

It would be nice if the RDS adapter could do this itself. A task for another day but for now I hope this helps.