[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'),
});

[andresgutgon]

Not working for me

"drizzle-orm": "^0.31.2",
"drizzle-kit": "^0.22.7",

export const countriesEnum = pgEnum('country', [
  'us', // United States
  'uk', // United Kingdom
  'de', // Germany
  'fr', // France
  'it', // Italy
  'es', // Spain
  'jp', // Japan
  'in', // India
  'mx', // Mexico
  'br', // Brazil
  'au', // Australia
])

export const users = pgTable(
  'users',
  {
    country: countriesEnum('country').notNull().default('us'),
  },
)

Error

 applying migrations...error: type "country" does not exist

2 problems

1. Enum is not generated in my *.sql migration file.
2. If I do a custom SQL migration this does not work because generated column has quotes

CREATE TABLE IF NOT EXISTS "users" (
  "country" "country" DEFAULT 'us' NOT NULL,
);

[andresgutgon]

This fixed the error

export enum County {
  US = 'us',
  UK = 'uk',
  DE = 'de',
  FR = 'fr',
  IT = 'it',
  ES = 'es',
  JP = 'jp',
  IN = 'in',
  MX = 'mx',
  BR = 'br',
  AU = 'au',
}

export const CountriesEnum = pgEnum('country', [
  County.US,
  County.UK,
  County.DE,
  County.FR,
  County.IT,
  County.ES,
])

I still needed to generate a custom migration with this command:

drizzle-kit generate --custom

And then manually create the enum in SQL.

CREATE TYPE country AS ENUM (
  'us',
  'uk',
  'de',
  'fr',
  'it',
  'es',
  'jp',
  'in',
  'mx',
  'br',
  'au'
)

[Affan07]

This fixed the error

export enum County {
  US = 'us',
  UK = 'uk',
  DE = 'de',
  FR = 'fr',
  IT = 'it',
  ES = 'es',
  JP = 'jp',
  IN = 'in',
  MX = 'mx',
  BR = 'br',
  AU = 'au',
}

export const CountriesEnum = pgEnum('country', [
  County.US,
  County.UK,
  County.DE,
  County.FR,
  County.IT,
  County.ES,
])

I still needed to generate a custom migration with this command:

drizzle-kit generate --custom

And then manually create the enum in SQL.

CREATE TYPE country AS ENUM (
  'us',
  'uk',
  'de',
  'fr',
  'it',
  'es',
  'jp',
  'in',
  'mx',
  'br',
  'au'
)

Worked like a charm. thank you

[Maxth47]

I have tried all suggestions above but the bugs still persists

export enum VersionStatus {
	Draft = 'draft',
	Published = 'published',
	Changed = 'changed',
	Deleted = 'deleted',
}


export const statusEnum = pgEnum('status', [
	VersionStatus.Draft,
	VersionStatus.Published,
	VersionStatus.Changed,
	VersionStatus.Deleted,
])


export const version = pgTable(
	'version',
	{
		id: uuid('id').primaryKey(),
		name: text('name').notNull(),
		description: text('description'),
		status: statusEnum('status').notNull().default(VersionStatus.Draft),
	},
	table => {
		return {
			publishIdx: uniqueIndex('publish_idx').on(
				table.published_id,
				table.status,
			),
		}
	},
)

error: type "status" does not exist

[andresgutgon]

Did you created FIRST a custom migration with the enum?

drizzle-kit generate --custom

CREATE TYPE country AS ENUM (
  'us',
  'uk',
  'de',
  'fr',
  'it',
  'es',
  'jp',
  'in',
  'mx',
  'br',
  'au'
)

⚠️ This migration has to be implemented before running the other.

I have to admit is a poor hack. This should work without need of doing a custom migration as the docs says but for some reason is not working for some of us

[Maxth47]

I have tried to run the command, however it generates a empty migration file.

Maybe because of my drizzle kit version it different as yours. If I use your version, it face to the bug that asking me to install the latest version of drizzle orm - which is already at the latest version. LOL

[andresgutgon]

I have tried to run the command, however it generates an empty migration file.

That's expected. You have to write the content yourself.

~~Is a bit disgusting to write SQL by hand coming from Rails where migrations work perfectly but fine, let's just play the JS game 😂~~ all my fault Drizzle and JS rocks!

[Maxth47]

I didnt know that I had to put the SQL into that file. as expected, it works perfectly

@andresgutgon you are so nice, thanks for your explanation.

[Maxth47]

@errmayank thank you for your help. and I am so sorry that I revealed a part of my code. and my actual code has the published_id column.

already tried to switch to use drizzle-kit v0.22.7 and node v20.14.0 - did not help.

Even though it does NOT work in my turborepo project, good new is that it works like a charm on the stand-alone project.

[Maxth47]

I finally make it work in my turborepo codebase.

I have several schema files that are for each table, and export them from the index.ts where is declared as my schema in drizzle.config.ts

I did not know that I have to export my enum statusEnum inside the index.ts as well to make migration work

this is my schema/index.ts

export {default as version, version_relations, statusEnum} from './version'  // <== export statusEnum here
export {default as axis_header, axis_header_relations} from './axis_header'
export {default as item_price, item_price_relations} from './item_price'

[andresgutgon]

@Maxth47 that was my mistake too! Thanks for looking harder ❤️

[peteregbujie]

@Maxth47 You are a life saver. Thank you so much.

[ironbyte]

Tried everything, but the bug still persists :/. The interesting thing is, I haven't touched the DB schema in a while. I ran drizzle:generate and drizzle:migrate for the first time, and suddenly, they failed. Tbh, this is such a massive deal breaker for me.

Error message:

[⣽] applying migrations...PostgresError: type "education_level" does not exist

"drizzle-orm": "^0.32.0",

// schema/job-seeker.ts
export const educationLevelEnum = ['postgraduate', 'undergraduate'] as const
export const jobTypeEnum = [
	'Full-time',
	'Part-time',
	'Contract',
	'Internship',
] as const

// schema/index.ts
export * from './job-seeker'

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import { Resource } from 'sst'

export default defineConfig({
	dialect: 'postgresql',
	schema: './src/schema/*',
	out: './migrations',
	dbCredentials: {
		url: Resource.SupabaseDatabaseUrl.value,
	},
	verbose: true,
	strict: true,

	schemaFilter: ['public'],
})

[ironbyte]

Tried everything, but the bug still persists :/. The interesting thing is, I haven't touched the DB schema in a while. I ran drizzle:generate and drizzle:migrate for the first time, and suddenly, they failed. Tbh, this is such a massive deal breaker for me. Error message:

[⣽] applying migrations...PostgresError: type "education_level" does not exist

"drizzle-orm": "^0.32.0",

// schema/job-seeker.ts
export const educationLevelEnum = ['postgraduate', 'undergraduate'] as const
export const jobTypeEnum = [
	'Full-time',
	'Part-time',
	'Contract',
	'Internship',
] as const

// schema/index.ts
export * from './job-seeker'

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import { Resource } from 'sst'

export default defineConfig({
	dialect: 'postgresql',
	schema: './src/schema/*',
	out: './migrations',
	dbCredentials: {
		url: Resource.SupabaseDatabaseUrl.value,
	},
	verbose: true,
	strict: true,

	schemaFilter: ['public'],
})

You have to put the enum export inside index.ts as well

Wow, it worked! Thanks!!! I dont understand why I have to re-export them from the index file. So glad it's fixed now! Thanks again!