[BUG]: Error on Malformed Array Literal

[vanvilecks-not-dead]

What version of drizzle-orm are you using?

0.31.2

What version of drizzle-kit are you using?

0.20.18

Describe the Bug

While running the db:push command in our project, an error occurs due to a malformed array literal. The error traceback indicates that the issue arises within the drizzle-kit package, specifically in handling the array literal for setting weights to the text search vectors.

Code Example:

The following code works correctly:

export const products = pgTable(
  'product',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    name: text('name').notNull().unique(),
    slug: text('slug').notNull().unique(),
    short_description: text('short_description').notNull(),
    description: text('description').notNull(),
    website: text('website'),
    fullPreviewLink: text('full_preview_link'),
    status: productStatusEnum('product_status').notNull().default('review'),
    createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { mode: 'date' }).notNull().defaultNow(),
  },
  (table) => {
    return {
      productNameIdx: index('product_name_idx').on(table.name),
      productSlugIdx: index('product_slug_idx').on(table.slug),
      productStatusIdx: index('product_status_idx').on(table.status),
      productSearchIndex: index('product_search_idx').using(
        'gin',
        sql`(
          setweight(to_tsvector('english', ${table.name}), 'A') ||
          setweight(to_tsvector('english', ${table.description}), 'B')
           )`,
      ),
    };
  },
);

However, adding more weights results in the error from the stack trace:

export const products = pgTable(
  'product',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    name: text('name').notNull().unique(),
    slug: text('slug').notNull().unique(),
    short_description: text('short_description').notNull(),
    description: text('description').notNull(),
    website: text('website'),
    fullPreviewLink: text('full_preview_link'),
    status: productStatusEnum('product_status').notNull().default('review'),
    createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { mode: 'date' }).notNull().defaultNow(),
  },
  (table) => {
    return {
      productNameIdx: index('product_name_idx').on(table.name),
      productSlugIdx: index('product_slug_idx').on(table.slug),
      productStatusIdx: index('product_status_idx').on(table.status),
      productSearchIndex: index('product_search_idx').using(
        'gin',
        sql`(
          setweight(to_tsvector('english', ${table.name}), 'A') ||
          setweight(to_tsvector('english', ${table.description}), 'B') ||
          setweight(to_tsvector('english', ${table.short_description}), 'C') ||
          setweight(to_tsvector('english', ${table.website}), 'D')
      )`,
      ),
    };
  },
);

If revert to a working state with two weights and run db:push, the same error from the stack trace will occur.

The only solution that helped return to a working state is to completely remove the search index from the code and then run db:push.

stack trace:

error: malformed array literal: "{(setweight(to_tsvector('english'::regconfig, name), 'A'::"char") || setweight(to_tsvector('english'::regconfig, description), 'B'::"char"))}"
    at /Users/vanvilecks/projects/secret-project/secret-project/packages/database/node_modules/drizzle-kit/bin.cjs:77696:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (/Users/vanvilecks/projects/secret-project/secret-project/packages/database/node_modules/drizzle-kit/bin.cjs:119635:26)
    at async /Users/vanvilecks/projects/secret-project/secret-project/packages/database/node_modules/drizzle-kit/bin.cjs:21822:31 {
  length: 254,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Unexpected array element.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'arrayfuncs.c',
  line: '546',
  routine: 'ArrayCount'
}

Node.js v21.7.1
npm ERR! Lifecycle script `db:push` failed with error: 
npm ERR! Error: command failed 
npm ERR!   in workspace: @repo/database@0.0.0 
npm ERR!   at location: /Users/vanvilecks/projects/secret-project/secret-project/packages/database 

Expected behavior

The db:push command should execute successfully without any errors related to array literals. It is expected that weights can be set for more than two fields.

Environment & setup

• Node.js: v21.7.1
• pg: "^8.11.5"
• @neondatabase/serverless: "^0.9.3"

[juspky]

What version of drizzle-orm are you using?
0.32.2
What version of drizzle-kit are you using?
0.23.2

Describe the Bug
I'm having the same issue even without adding new weights. It seems that the issue is not adding more weights but querying the schemas indexes while using weights.
I've followed the guide https://orm.drizzle.team/learn/guides/postgresql-full-text-search

My table looks roughly like this.

export const items = pgTable(
  'item',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    title: text('title'),
    description: text('description'),
  },
  (table) => ({
    searchIndex: index('item_search_index').using(
      'gin',
      sql`(
            setweight(to_tsvector('english', ${table.title}), 'A') ||
            setweight(to_tsvector('english', ${table.description}), 'B')
        )`
    ),
  })
);

I can run db:push once but if I try to run it again even without changing anything on that index i get
error: malformed array literal: "{(setweight(to_tsvector('english'::regconfig, title), 'A'::"char") || setweight(to_tsvector('english'::regconfig, description), 'B'::"char"))}"

This happens while pulling the schema in the dbIndexes query of pgSerializer.ts in drizzle-kit

As @vanvilecks-not-dead already said the only workaround right now is to remove the index before every db:push. Which is a major issue to me.

Environment & setup

Node.js: v21.7.0
pg: "8.12.0"

[juspky]

This is the problematic query
https://github.com/drizzle-team/drizzle-orm/blob/7d2ae842da5e57ea161aa708ea567fb4afc4911c/drizzle-kit/src/serializer/pgSerializer.ts#L963C5-L963C11

Unfortunately I can't fix it myself because I'm bad at raw SQL

[juspky]

#2744 could be related

[codewithbugs01]

same issue

[artem-alek]

any updates on this? i am having trouble too

[vanvilecks-not-dead]

It seems to me that the issue with drizzle-orm won’t be fixed this year, and patching it feels like a bad technical solution.

[juspky]

I'm kinda stressed by this not getting any attention but I guess there is only 2 choices.
patching it locally until it is fixed or switching to prisma (which is no option for me).

since the big update is out now. maybe @AndriiSherman has some spare time to look into the PR 😏

[davidchalifoux]

Just ran into this myself.

[marko1010]

Same error here.

[ShazimC]

Same issue.