Partial unique index migration does not generate valid where clause

[raggesilver]

The following schema does not generate a valid migration. The value for the where clause is not substituted in the generated SQL.

export const invitationLinks = pgTable(
  "invitation_links",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    workspaceId: uuid("workspace_id")
      .notNull()
      .references(() => workspaces.id, {
        onDelete: "cascade",
      }),
    active: boolean("active").notNull().default(true),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (table) => ({
    // We need to make sure that we don't have multiple active invitation links
    // for the same workspace.
    uniqueIndex: uniqueIndex()
      .on(table.workspaceId, table.active)
      .where(eq(table.active, true)),
  }),
);

Expected Output

--> [...]
CREATE UNIQUE INDEX IF NOT EXISTS "invitation_links_workspace_id_active_index" ON "invitation_links" USING btree ("workspace_id","active") WHERE "invitation_links"."active" = TRUE;

Actual Output

--> [...]
CREATE UNIQUE INDEX IF NOT EXISTS "invitation_links_workspace_id_active_index" ON "invitation_links" USING btree ("workspace_id","active") WHERE "invitation_links"."active" = $1;

Error

applying migrations...PostgresError: there is no parameter $1
    at ErrorResponse (/Users/paulo/Projects/tasks_app/node_modules/.pnpm/drizzle-kit@0.22.7/node_modules/drizzle-kit/bin.cjs:79677:27)
    at handle (/Users/paulo/Projects/tasks_app/node_modules/.pnpm/drizzle-kit@0.22.7/node_modules/drizzle-kit/bin.cjs:79454:7)
    at Socket.data (/Users/paulo/Projects/tasks_app/node_modules/.pnpm/drizzle-kit@0.22.7/node_modules/drizzle-kit/bin.cjs:79277:9)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P02',
  position: '177',
  file: 'parse_expr.c',
  line: '864',
  routine: 'transformParamRef'
}

Versions

drizzle-orm 0.31.2 drizzle-kit 0.22.7 postgres 3.4.4 Node v21.7.3 Pnpm 9.2.0

[akinwol]

Having the same issue. @raggesilver were you able to resolve?

[raggesilver]

Having the same issue. @raggesilver were you able to resolve?

I edited the migration file manually...

[dennismuench]

It seems that all conditional helpers that use a parameter cannot be used inside functions that create migrations. $1 is part of a behavior used in queries.

https://github.com/drizzle-team/drizzle-orm/issues/1845#issuecomment-1925306192

You have to use the sql util. This is not explained in the docs but used in the examples. This wont work eq(table.active, true) but this will:

sql`${table.active} IS TRUE`

[DudeRandom21]

I would also like to see the DX here improved so we can use the helpers and have a consistent feel for using drizzle both in the schema and when writing queries. With that said there's another workaround that allows you to keep a bit more of the structure I would like to see by pushing the magic sql operator as far down as possible.

.where(eq(table.active, sql`true`));

This tells drizzle you want to use the sql literal true rather than passing in a JS variable that needs to be parameterized for safety. This also works with strings if you quote them manually inside the sql string i.e.

sql`'my string'`