drizzle-team / drizzle-team/drizzle-orm

[BUG]: [SQLite/Turso] Expression indexes containing a comma generate invalid SQL

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

Description

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

0.45.2

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

0.31.10

### Describe the Bug

`drizzle-kit generate` emits invalid SQL for any expression index whose expression contains a comma.

`SQLiteSquasher` stores an index's columns as a comma-joined string and `unsquashIdx` recovers them with `columnsString.split(",")`. An expression like `(json_extract(payload, '$.ref'))` contains its own comma, so it is torn into two fragments, and each fragment is then emitted backtick-quoted as if it were a column name. The result names columns that do not exist and SQLite refuses it.

`CreateSqliteIndexConvertor` does try to handle this — it checks `statement.internal.indexes[name].columns[it].isExpression` and emits expressions raw. But that lookup is keyed by the **whole** expression, while `it` is now a **fragment**, so it never matches and every fragment falls through to the quoted branch. The guard is silently defeated by the splitting that happens upstream of it.

This affects `dialect: "sqlite"` and `dialect: "turso"` identically, and it happens on the very first `generate` — no column change or migration history required.

### Reproduction

`src/schema.ts`

```ts
import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { sql } from 'drizzle-orm'

export const events = sqliteTable(
'events',
{
id: integer('id').primaryKey({ autoIncrement: true }),
kind: text('kind').notNull(),
payload: text('payload'),
status: text('status').default('new'),
},
(t) => [
index('events_kind_idx').on(t.kind),
// the expression contains a comma
index('events_payload_ref_idx').on(t.kind, sql`(json_extract(payload, '$.ref'))`),
],
)

export const other = sqliteTable(
'other',
{ id: integer('id').primaryKey({ autoIncrement: true }), label: text('label') },
(t) => [index('other_label_idx').on(t.label)],
)
```

`drizzle.config.ts`

```ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
schema: ['./src/schema.ts'],
out: './migrations',
dialect: 'turso', // identical output with 'sqlite'
})
```

```bash
npx drizzle-kit generate --name initial
```

**Generated (invalid):**

```sql
CREATE INDEX `events_payload_ref_idx` ON `events` (`kind`,`(json_extract(payload`,` '$.ref'))`);
```

**Expected:**

```sql
CREATE INDEX `events_payload_ref_idx` ON `events` (`kind`,(json_extract(payload, '$.ref')));
```

Applying the generated file fails:

```
$ sqlite3 test.db < migrations/0000_initial.sql
Parse error near line 8: no such column: (json_extract(payload
```

A comma-free expression in the same schema round-trips correctly, which isolates the comma as the trigger:

```sql
CREATE INDEX `events_lower_kind_idx` ON `events` ((lower(kind))); -- correct
CREATE INDEX `events_payload_ref_idx` ON `events` (`kind`,`(json_extract(payload`,` '$.ref'))`); -- broken
```

### Second symptom, same reproduction

Continuing from above, change one column default on `events`:

```diff
- status: text('status').default('new'),
+ status: text('status').default('pending'),
```

```bash
npx drizzle-kit generate --name alter-status
```

```sql
DROP INDEX "events_kind_idx";--> statement-breakpoint
DROP INDEX "events_payload_ref_idx";--> statement-breakpoint
DROP INDEX "other_label_idx";--> statement-breakpoint -- unrelated table
ALTER TABLE `events` ALTER COLUMN "status" TO "status" text DEFAULT 'pending';--> statement-breakpoint
CREATE INDEX `events_kind_idx` ON `events` (`kind`);--> statement-breakpoint
CREATE INDEX `events_payload_ref_idx` ON `events` (`kind`,`(json_extract(payload`,` '$.ref'))`);--> statement-breakpoint
CREATE INDEX `other_label_idx` ON `other` (`label`);
```

Two things here:

1. `other_label_idx` belongs to a table that was not touched. `LibSQLModifyColumn.convert()` iterates `Object.values(json2.tables)` — every table in the schema — rather than the table being altered. The scope half of this overlaps with #5564, which has PRs open.

2. `LibSQLModifyColumn` carries its **own** copy of index recreation, with no `isExpression` handling at all:

```js
const uniqueString = index6.columns.map((it) => `\`${it}\``).join(",");
```

So even once the scope is fixed, an expression index on the altered table is still corrupted. This part does not appear to be covered by the linked PRs.

### Why this is worth attention

The two combine badly on a real schema. On a database with ~400 indexes, changing one column default generated ~780 statements: every index dropped and recreated, with the expression indexes coming back invalid. Run against a copy of production it exits non-zero **after** the `DROP INDEX` statements have already executed — so the migration fails having silently removed indexes, and the failure is not atomic.

The corruption is also easy to miss. A migration generated on a schema that merely *contains* an expression index is broken even if that index was not the thing being changed.

### Suggested direction

The real fix looks like the squash/unsquash round-trip rather than the call sites: a column list that can contain arbitrary SQL cannot be stored as a comma-joined string. Splitting on commas that are not inside parentheses, or keeping expressions in a separate field, would fix every consumer at once — including the `isExpression` guard that already exists and currently cannot fire.

Patching the call sites individually also works, but there are at least two of them (`CreateSqliteIndexConvertor` and `LibSQLModifyColumn`) and they would keep drifting apart, which is how the second one ended up without the guard in the first place.

Happy to open a PR if a maintainer confirms which direction is preferred.

### Environment

- Node 25
- pnpm 10
- libSQL/Turso and local SQLite, same result

Contributor guide

Open the contributing guide

Research direction

Start with SQLiteSquasher and unsquashIdx, then inspect CreateSqliteIndexConvertor and LibSQLModifyColumn, which contain the affected index handling. Reproduce the issue using src/schema.ts, drizzle.config.ts, and `npx drizzle-kit generate --name initial`; done means comma-containing expressions remain intact and generated SQL is valid for both initial generation and column changes.

Written by the indexing model from the issue text.

Assessment

Tech stack
sqlite, typescript
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.