How do i insert a Enum value into a table

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()
});

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"
    })

 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

 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

7 Replies

Angelelz•15mo ago

 await db.insert(users).values({
        email:"a@a",
        role:"admin",
        id:"123"
    })

 await db.insert(users).values({
        email:"a@a",
        role:"admin",
        id:"123"
    })

The Typescript type for role is basically "admin" | "maintainer"

Mohammed Anas•15mo ago

does nt seem to work i even tried "admin" I get this

 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: 32; SQLState: 42804

 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: 32; SQLState: 42804

when i do

    await db
      .update(users)
      .set({
        role:"admin"
      })
      .where(eq(users.email, email));

    await db
      .update(users)
      .set({
        role:"admin"
      })
      .where(eq(users.email, email));

Angelelz•15mo ago

Can you show the migration where this column was created? Or can you do describe userAuth on your database and show the result?

rphlmr ⚡•15mo ago

Reminds me https://discord.com/channels/1043890932593987624/1056966312997429278/1100436967017164880 I have dropped enum in favor of:

const clubStatuses = ["active", "closed"] as const;
export type ClubStatus = (typeof clubStatuses)[number];

...
 status: text("status", { enum: clubStatuses })
            .notNull()
            .default("active"),

const clubStatuses = ["active", "closed"] as const;
export type ClubStatus = (typeof clubStatuses)[number];

...
 status: text("status", { enum: clubStatuses })
            .notNull()
            .default("active"),

But it is no longer reinforced on the database side (until you add constraints). In time I found it easier to use when you want to alter enum values.

Angelelz•15mo ago

Isn't typeof clubStatuses[number] equivalent to "active" | "closed"? Oh, I get it now, you have to write the array because drizzle needs it. And you derive the type from it with as const

Mohammed Anas•15mo ago

 DO $$ BEGIN
 CREATE TYPE "role" AS ENUM('admin', 'maintainer');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
ALTER TABLE "userAuth" ADD COLUMN "role" "role" DEFAULT 'maintainer';

 DO $$ BEGIN
 CREATE TYPE "role" AS ENUM('admin', 'maintainer');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
ALTER TABLE "userAuth" ADD COLUMN "role" "role" DEFAULT 'maintainer';

Jeff•15mo ago

Having same issue, found this https://discord.com/channels/1043890932593987624/1120919676457848946/1120919676457848946

Gaming

Programming

How do i insert a Enum value into a table