PostgreSQL, text array, migrations. drizzle-kit generate produces invalid syntax in migration ALTER statement

[gburtini]

An ALTER migration generated with drizzle-kit generate from a table schema like this:

pgTable("users", {
  ...
  permissions: text("permissions").array().notNull().default([]),
});

Produces a migration that looks like this:

ALTER TABLE "users" ADD COLUMN "permissions" text[] DEFAULT   NOT NULL;

Which produces an error in all recent Postgres versions I could test out including 16.3.

ERROR:  syntax error at or near "NOT"
LINE 1: ... "users" ADD COLUMN "permissions" text[] DEFAULT   NOT NULL;

The correct syntax would include a {} Postgres array literal.

[gburtini]

This appears to have been reported over here a year ago drizzle-team/drizzle-orm#1344 and closed as not a bug.

The types reported reject a pure string solution permissions: text("permissions").array().notNull().default("{}") and (IMO, correctly) indicate that the expected type of default is a string[].

That said, the prescribed tagged SQL string approach does work.

.default(sql`{}`)

That's a sufficient workaround, but it doesn't seem like an ideal solution.

• the DDL types should reject an array if an array isn't the valid input here.
  • a better solution would be for Drizzle to mediate any difference in a JS array and a Postgres array.
• drizzle-kit generate should fail, rather than produce invalid SQL, at least on inputs that do not involve any sql literals.