[BUG]: using `pgEnum` generates faulty sql migration

[errmayank]

What version of drizzle-orm are you using?

0.26.1

What version of drizzle-kit are you using?

0.18.1

Describe the Bug

Creating a user schema with role enum

export const roleEnum = pgEnum('Role', ['ADMIN', 'USER']);

export const user = pgTable('user', {
  id: varchar('id', { length: 256 }).primaryKey().notNull(),
  email: varchar('email', { length: 256 }).notNull(),
  role: roleEnum('role').default('USER').notNull(),
  name: varchar('name', { length: 256 }).notNull(),
  fullName: varchar('full_name', { length: 256 }).notNull(),
  locale: varchar('locale', { length: 256 }).default('en-US').notNull(),
  timezone: varchar('timezone', { length: 256 }),
  imageURL: text('image_url'),
  createdAt: timestamp('created_at', { precision: 6, withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { precision: 6, withTimezone: true }).defaultNow().notNull(),
  deletedAt: timestamp('deleted_at', { precision: 6, withTimezone: true }),
});

generates this sql migration

DO $$ BEGIN
 CREATE TYPE "Role" AS ENUM('ADMIN', 'USER');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "user" (
	"id" varchar(256) PRIMARY KEY NOT NULL,
	"email" varchar(256) NOT NULL,
	"role" Role DEFAULT 'USER' NOT NULL,
	"name" varchar(256) NOT NULL,
	"full_name" varchar(256) NOT NULL,
	"locale" varchar(256) DEFAULT 'en-US' NOT NULL,
	"timezone" varchar(256),
	"image_url" text,
	"created_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp (6) with time zone
);

which fails when running with error PostgresError: type "role" does not exist unless i wrap Role in double quotes manually like this

"role" "Role" DEFAULT 'USER' NOT NULL,

Expected behavior

Double quotes should be added automatically

Environment & setup

node v18.16.0

[patrickcurl]

I'm getting the same error, except it also works if you unquote the CREATE TYPE I've found, I think the quotes is casting the name of the type to something else than just the TYPENAME symbol or whatever (I'm not sure on the internals).

[naftali100]

I'm getting the same error, except it also works if you unquote the CREATE TYPE I've found, I think the quotes is casting the name of the type to something else than just the TYPENAME symbol or whatever (I'm not sure on the internals).

When you unquote the type, it creates the type in all lowercase. However, when you quote it, it is created in the way you wrote it. That's why...

[paulwongx]

In my latest usage, the ENUMs aren't even generated anymore... bump on this issue.

[embersee]

I've found out, that whenever you use the type in any case apart from all lowercase, e.g. :

export const orderStatusEnum = pgEnum("orderStatus", [
  "created",
  "failed",
  "cancelled",
  "successful",
  "delivery",
  "complete",
]);

It would error out like so whenever migrating:

PostgresError: type "orderstatus" does not exist

So, the solution for me has been to just use the enum type in all lowercase, like so:

export const orderStatusEnum = pgEnum("orderstatus", [
  "created",
  "failed",
  "cancelled",
  "successful",
  "delivery",
  "complete",
]);

and that seemed to do it!

[embersee]

In my latest usage, the ENUMs aren't even generated anymore... bump on this issue.

Are you exporting the enum? I've found out that while generating, if the enum is not exported then it won't generate in the migration.

[Rykuno]

Exporting the pgEnum works and the migration script picks up on it as intended.

export const eventPrivacyEnum = pgEnum('privacy', ['public', 'private']);

@AndriiSherman if this is intended behavior, the docs might need to be updated at https://orm.drizzle.team/docs/column-types/pg#enum.

[AndriiSherman]

In the latest drizzle-kit it works as expected ([email protected]) It should be fixed for all versions >0.19.0

[rajanshresth]

I'm facing similar issues.

But It doesn't make changes in the supabase database.

I'm using "drizzle-orm": "^0.28.6",

[sjdonado]

I had the same problem PostgresError: type "role" does not exist with this schema:

const userRoles = pgEnum('role', ['admin', 'teacher', 'student']);
export const usersTable = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: userRoles('roles').notNull().default('teacher'),
});

fixed after exporting userRoles

export const userRoles = pgEnum('role', ['admin', 'teacher', 'student']);
export const usersTable = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: userRoles('role').notNull().default('teacher'),
});