[BUG]: enum as array, not possible?

[nathanielrich]

What version of drizzle-orm are you using?

0.29.0

What version of drizzle-kit are you using?

0.20.4

Describe the Bug

a simple example:

export const userRoles = pgEnum("user_role", ["admin", "member"]);

export const user = pgTable("user", {
	id: uuid("id").notNull().defaultRandom().primaryKey(),
	roles: userRoles("roles").notNull().default("member").array(),
});

when i try to migrate this, i got the error:

...

/postgres@3.4.3/node_modules/postgres/cjs/src/connection.js:790
    const error = Errors.postgres(parseError(x))
                         ^
PostgresError: type "user_role[]" does not exist

...

is it not implemented or is this a bug...?

thx.

Expected behavior

No response

Environment & setup

No response

[IRediTOTO]

so im not alone

[dvtkrlbs]

since you are using an array you cannot have the literal as default you need to do it like this

export const userRoles = pgEnum("user_role", ["admin", "member"]);

export const user = pgTable("user", {
	id: uuid("id").notNull().defaultRandom().primaryKey(),
	roles: userRoles("roles").array().notNull().default(sql`'{"member"}'`),
});

[onursagir]

The sql generated by Drizzle probably looks something like this

ALTER TABLE "users" ADD COLUMN "roles" userRoles[]

while it should be looking like

ALTER TABLE "users" ADD COLUMN "roles" "userRoles"[]

I cannot figure out where Drizzle actually loads the sql from but modifying db/migrations/00000_migration_file.sql does not seem to work. For I've chosen to run the migration manually as I am not in production yet

[diego-lipinski-de-castro]

I do not think this problem is related to the usage of .array(). I am also having this problem with the following code

export const methodEnum = pgEnum('method', ['email', 'sms', 'whatsapp'])

export const authCodes = pgTable('auth_codes', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  to: varchar('to', { length: 256 }).notNull(),
  method: methodEnum('method').notNull(),
  code: varchar('code', { length: 6 }).unique().notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  expiresAt: timestamp('expires_at'),
  verifiedAt: timestamp('verified_at'),
}, (table) => ({
  codeIdx: index('code_idx').on(table.code),
}));

[Infiee]

I do not think this problem is related to the usage of .array(). I am also having this problem with the following code

export const methodEnum = pgEnum('method', ['email', 'sms', 'whatsapp'])

export const authCodes = pgTable('auth_codes', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  to: varchar('to', { length: 256 }).notNull(),
  method: methodEnum('method').notNull(),
  code: varchar('code', { length: 6 }).unique().notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  expiresAt: timestamp('expires_at'),
  verifiedAt: timestamp('verified_at'),
}, (table) => ({
  codeIdx: index('code_idx').on(table.code),
}));

I also encountered this issue. If I replace 'mood' with 'moodtype' or any other word, it will fail to execute!

// will success
// const menuTypeEnum = pgEnum('mood', SystemMenuTypeEnum);

// will fail
const menuTypeEnum = pgEnum('moodtype', SystemMenuTypeEnum);

[ngethan]

Any resolution on this?

[Lukem121]

❌ Does not work:
roles: userRoleEnum("roles").array().notNull().default(["user"]),

✅ Works
roles: userRoleEnum("roles").array().default(sqlARRAY['user']::user_role_enum[]).notNull(),

[mikkelwf]

Still an issue.. :/

[AndriiSherman]

Should be fixed in drizzle-kit0.24.0
Please check release notes before updating

[Charioteer]

Hi @AndriiSherman,

unfurtonuately, this issue is still not resolved by drizzle-kit@0.24.0. I just tried it with Postgres:

export const myEnum = pgEnum('my_enum', [
  'VALUE1',
  'VALUE2'
])

export const myTable = pgTable('my_table', {
  id: bigserial('id', { mode: 'bigint' }).primaryKey(),
  enum: myEnum('enum').array().notNull(), // <-- does not work
})

export const myTableWithDefault = pgTable('my_table_with_default', {
  id: bigserial('id', { mode: 'bigint' }).primaryKey(),
  enum: myEnum('enum').array().default([]).notNull(), // <-- does not work with default value either
})

Running drizzle-kit push still throws the same error type "my_enum[]" does not exist because the generated SQL remains wrong as pointed out by @onursagir.

Maybe someone else could confirm this behavior. My local ESM setup uses workarounds mentioned here. However, I checked the correct drizzle-kit version by running pnpm drizzle-kit --version and it is indeed 0.24.0. I don't think there would be any difference in classic ("no ESM") setups.

[andygott]

You can work around this by using lowercase enum names.

The test cases for this use snake case, which works fine. If your enum is named using camel case (or any uppercase characters) it will trigger this bug, as @onursagir suggested here:

#1564 (comment)

Postgres folds unquoted names to lowercase, so if your enum is named testEnum the sql generated looks like testenum[] which doesn't exist. Here are the existing test cases:

drizzle-orm/drizzle-kit/tests/pg-array.test.ts

Line 302 in c8359a1

test('array #11: enum array default', async (t) => {

I think the problem is here:

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/common.ts#L305

As far as I can tell (I've only had a quick look) quoting the enum name in PgArray should fix it:

return `"${this.baseColumn.getSQLType()}"[${typeof this.size === 'number' ? this.size : ''}]`;

[fendyk]

I tried drizzle-kit@0.24.2:

muscleTargets: muscleTargetEnum("muscle_targets")
	.array()
	.$type<MuscleTarget[]>()
	.default([])
	.notNull(),

tested with postgres and works.

[agreenspan]

This is still broken for PascalCase enums

export const GameType = pgEnum('GameType', ['boardgame', 'deckbuilder', 'generic', 'lcg', 'tcg', 'ttrpg', 'wargame']);

gameTypes: GameType('gameTypes').array().notNull(),

error: type "gametype[]" does not exist

[bestickley]

this still doesn't work for me on 0.24.2. I get:
"day_of_week" day_of_week[] NOT NULL,
but it needs to be:
"day_of_week" "flt"."day_of_week"[] NOT NULL,
So the issues are that it's not properly escaping and the schema isn't being included.

[icep0ps]

The way I worked around this is I just created the enum at the top of the migration file.