drizzle-team / drizzle-team/drizzle-orm

[BUG]: `eq()` function in partial unique index WHERE clause generates parameterized query instead of literal value

Open
#4,790 5 comments 5 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] 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.44.3

### What version of `drizzle-kit` are you using?

0.31.4

### Other packages

_No response_

### Describe the Bug

When using the `eq()` function in a partial unique index WHERE clause, Drizzle Kit generates SQL with a parameterized placeholder (`$1`) instead of the literal boolean value, resulting in invalid SQL.

## Environment
- `drizzle-orm`: 0.44.3
- `drizzle-kit`: 0.31.4
- Database: PostgreSQL

## Reproduction

### Incorrect Implementation (Bug)

```typescript
import { eq, relations } from 'drizzle-orm'
import { boolean, pgTable, uniqueIndex, varchar } from 'drizzle-orm/pg-core'

export const fields = pgTable(
'fields',
{
id: varchar().primaryKey(),
tableId: varchar('table_id'),
isPrimary: boolean('is_primary').default(false),
},
(table) => [
// Using eq() function - THIS IS BUGGY
uniqueIndex('idx_one_primary_per_table')
.on(table.tableId)
.where(eq(table.isPrimary, true)),
]
)
```

**Generated SQL (Incorrect):**
```sql
CREATE UNIQUE INDEX "idx_one_primary_per_table" ON "fields" USING btree ("table_id") WHERE "fields"."is_primary" = $1;
```

The `$1` placeholder is not replaced with the actual `true` value, making the SQL invalid and will cause a crash when running `drizzle-kit`

### Correct Implementation (Workaround)

```typescript
import { relations, sql } from 'drizzle-orm'
import { boolean, pgTable, uniqueIndex, varchar } from 'drizzle-orm/pg-core'

export const fields = pgTable(
'fields',
{
id: varchar().primaryKey(),
tableId: varchar('table_id'),
isPrimary: boolean('is_primary').default(false),
},
(table) => [
// Using sql template literal - THIS WORKS
uniqueIndex('idx_one_primary_per_table')
.on(table.tableId)
.where(sql`${table.isPrimary} = true`),
]
)
```

**Generated SQL (Correct):**
```sql
CREATE UNIQUE INDEX "idx_one_primary_per_table" ON "fields" USING btree ("table_id") WHERE "fields"."is_primary" = true;
```

## Expected Behavior

The `eq()` function should generate the same SQL as the `sql` template literal when used in partial index WHERE clauses, producing literal values instead of parameterized placeholders.

## Actual Behavior

The `eq()` function generates parameterized SQL with `$1` placeholder that doesn't get replaced with the actual value, resulting in invalid SQL for index creation.

## Impact

This prevents developers from using the more ergonomic `eq()` function for partial unique indexes and forces them to use raw SQL template literals as a workaround.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.