[BUG]: RDS Driver fails to insert arrays into PG

[amandaharlin]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.31.0

What version of drizzle-kit are you using?

0.22.0

Other packages

No response

Describe the Bug

RDS Driver fails to insert array values into PostgreSQL

Issue Description

When using the AWS RDS Driver with Drizzle ORM, attempting to insert an array into PG columns fails with UUID parsing errors. This happens when trying to insert string arrays directly, empty arrays, or enum values. What did work was using sql.raw() with (string_to_array('${items.join(',')}', ',')::text[])

I haven't seen any issues on GH or discord about this, but I ran into it last night.

---

Example column and table definition:

export const items_table = pgTable('items', {
  id: uuid('id').defaultRandom().primaryKey()
  type: text('type', { enum: item_type.enumValues }).$type<ItemType>(),
  tags: text('tags')
    .array()
    .notNull()
    .default(sql`ARRAY[]::text[]`)
    .$type<ItemTag[]>(),
});

The generated SQL from the migration file:

ALTER TABLE "resources" ALTER COLUMN "channels" SET DATA TYPE text[];

The generated SQL above didn't work. This casting change fixed that.

ALTER TABLE "items" ALTER COLUMN "tags" SET DATA TYPE text[] USING tags::text[];

---

What didn't work

1. Direct array insertion

  const items: (typeof items_table.$inferInsert)[] = MOCK_ITEMS.map((x) => {
    return {
      ...x,
      type: x. type,
      tags: ["EXAMPLE"],
    };
  });

  await pg.insert(items_table).values(items);

It gives this error:

/Users/amandaharlin/eyeframe/project/node_modules/@aws-sdk/client-rds-data/dist-cjs/index.js:912
  const exception = new DatabaseErrorException({
                    ^

DatabaseErrorException: Cannot parse UUID parameter: "{"EXAMPLE"}"

2. Empty array insertion

 const items: (typeof items_table.$inferInsert)[] = MOCK_ITEMS.map((x) => {
    return {
      ...x,
      type: x. type,
      tags: [],
    };
  });

  await pg.insert(items_table).values(items);

It gives this error:

DatabaseErrorException: Cannot parse UUID parameter: "{}"

3. Enum array insertion

 const items: (typeof items_table.$inferInsert)[] = MOCK_ITEMS.map((x) => {
    return {
      ...x,
      type: x. type,
      tags: [ItemTag.Example],
    };
  });

  await pg.insert(items_table).values(items);

It gives this error:

DatabaseErrorException: Cannot parse UUID parameter: "{"EXAMPLE"}"

---

What worked

Hardcoding the value, which will be the same as what the sql.raw spits out, does work:

  const items: (typeof items_table.$inferInsert)[] = MOCK_ITEMS.map((x) => {
    return {
      ...x,
      type: x. type,
      //This is cursed AF, and its to make RDS work.
      tags: sql`string_to_array('EXAMPLE,TEST', ',')::text[]`,
    };
  });

The following works:

  const items: (typeof items_table.$inferInsert)[] = MOCK_ITEMS.map((x) => {
    return {
      ...x,
      type: x. type,
      //This is cursed AF, and its to make RDS work.
      tags: rdsArray(x. tags as ItemTag[]) as unknown as ItemTag[],
    };
  });

// Our function
function rdsArray(items: string[]): SQL<unknown> {
  // We know this exact format works when used as a single literal
  const fullSql = `string_to_array('${items.join(',')}', ',')::text[]`;
  return sql.raw(fullSql);
}