[BUG]: Drizzkle kit generate wrong SQL for Postgres enum arrays and double precision array.

[Hebilicious]

trafficstars

What version of drizzle-orm are you using?

0.29.1

What version of drizzle-kit are you using?

0.20.7

Describe the Bug

Hello there, just finished migrating a big~ish project from prisma to drizzle (prisma.schema was 800lines) and I encountered this bug :

error: type "notificationtype[]" does not exist
 code: "42704"

error: type "double precision[]" does not exist
 code: "42704"

SQL line generated :

"disabledNotificationTypes" NotificationType[],

ALTER TABLE "VidStat" ADD COLUMN "watchHours" "double precision"[];

correct line

"disabledNotificationTypes" "NotificationType"[],

ALTER TABLE "VidStat" ADD COLUMN "watchHours" double precision[];

Manually editing the quotes in the sql file works.

I tried to generate them in isolation and got the same result :

ALTER TABLE "UserNotificationSetting" ADD COLUMN "disabledNotificationTypes" NotificationType[];--> statement-breakpoint
ALTER TABLE "UserNotificationSetting" ADD COLUMN "disabledEmailTypes" EmailTypes[];--> statement-breakpoint
ALTER TABLE "VidStat" ADD COLUMN "watchHours" "double precision"[];

For reference this is the TS code :

export const notificationType = pgEnum('NotificationType', ["foo", "bar"]);

const somethingLikeThis = {
    disabledNotificationTypes: notificationType('disabledNotificationTypes').array(),
    watchHours: doublePrecision('watchHours').array(),
}

Expected behavior

I expect the SQL to be generated correctly.

Environment & setup

node --version v20.5.0 macos

[Hebilicious]

@Angelelz Investigating this a little I think it might be because of the fact that I'm using camelCase / pascalCase and drizzle incorrectly quote things in this scenario.

[Angelelz]

Can you show what the statement that got generated by the pgEnum?

[Hebilicious]

Can you show what the statement that got generated by the pgEnum?

--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "EmailTypes" AS ENUM('WEEKLY_DIGEST', 'IMPORTANT_UPDATES', 'PROMOTIONS_DISCOUNTS_OFFERS', 'FEEDBACK_SURVEY', 'SUBSCRIPTION_UPDATES');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "NotificationType" AS ENUM('NEW_COURSE', 'NEW_INSTRUCTOR_ANNOUNCEMENTS', 'COMMUNITY_ENGAGEMENT', 'ACHIEVEMENT_CELEBRATIONS');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

These 2 statements.

[risenxxx]

I also encountered this. Maybe this issue should be created in the drizzle-kit-mirror repository? It would be great if the problem was resolved soon, I really don't want to change name case everywhere =(

[Hebilicious]

I also encountered this. Maybe this issue should be created in the drizzle-kit-mirror repository? It would be great if the problem was resolved soon, I really don't want to change name case everywhere =(

As a workaround you can fix the generated migration manually. Only need to be done once.

[dalechyn]

Also hit this, yet not with double precision array, but with a enum of strings.

[SiNONiMiTY]

I am hitting this issue with an enum of strings

pnpm drizzle-kit -v
drizzle-kit: v0.20.14
drizzle-orm: v0.29.4

[gunhaxxor]

Also stumbled upon this issue with doublePrecison('columnName').array() generating the following sql "columnName" "double precision[]", In my case i can simply use real instead of doublePrecision as a workaround.

[jperezr21]

Same issue here: doublePrecision("embedding").array() gets converted to "embedding" "double precision[]" which fails with the following error:

error: type "double precision[]" does not exist

Can't use real for embeddings, since the precision is needed.

[jperezr21]

Solved the issue by implementing a float8 column builder:

float8-pg-column-builder.ts

import type {
  ColumnBaseConfig,
  ColumnBuilderBaseConfig,
  ColumnBuilderRuntimeConfig,
  MakeColumnConfig,
} from "drizzle-orm";
import {
  PgColumn,
  PgColumnBuilder,
  type AnyPgTable,
} from "drizzle-orm/pg-core";

export type PgFloat8BuilderInitial<TName extends string> = PgFloat8Builder<{
  name: TName;
  dataType: "number";
  columnType: "PgFloat8";
  data: number;
  driverParam: string | number;
  enumValues: undefined;
}>;

const entityKind: unique symbol = Symbol("entityKind");

export class PgFloat8Builder<
  T extends ColumnBuilderBaseConfig<"number", "PgFloat8">,
> extends PgColumnBuilder<T> {
  static readonly [entityKind]: string = "PgFloat8Builder";

  constructor(name: T["name"]) {
    super(name, "number", "PgFloat8");
  }

  /** @internal */
  build<TTableName extends string>(
    table: AnyPgTable<{ name: TTableName }>,
  ): PgFloat8<MakeColumnConfig<T, TTableName>> {
    return new PgFloat8<MakeColumnConfig<T, TTableName>>(
      table,
      this.config as ColumnBuilderRuntimeConfig<any, any>, // eslint-disable-line
    );
  }
}

export class PgFloat8<
  T extends ColumnBaseConfig<"number", "PgFloat8">,
> extends PgColumn<T> {
  static readonly [entityKind]: string = "PgFloat8";

  getSQLType(): string {
    return "float8";
  }

  override mapFromDriverValue(value: string | number): number {
    if (typeof value === "string") {
      return Number.parseFloat(value);
    }
    return value;
  }
}

export function float8<TName extends string>(
  name: TName,
): PgFloat8BuilderInitial<TName> {
  return new PgFloat8Builder(name);
}