DT
Drizzle Team•2y ago
Mohammed Anas

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
Angelelz•2y 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
Mohammed AnasOP•2y 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
Angelelz•2y 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 ⚡
rphlmr ⚡•2y 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
Angelelz•2y 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
Mohammed AnasOP•2y 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
Jeff•2y ago

Did you find this page helpful?