drizzle-team / drizzle-team/drizzle-orm

[BUG]: [SQLite/Turso] LibSQLModifyColumn generates duplicate DROP INDEX (without IF EXISTS) causing "no such index" on db:push

Open
#5,564 0 comments 0 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.41.0

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

0.31.9

### Other packages

_No response_

### Describe the Bug

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

0.41.0

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

0.31.9 (also confirmed present in 0.31.10)

## Describe the Bug

`drizzle-kit push` with the Turso/libSQL dialect fails with `no such index` when the schema diff requires modifying multiple columns (or columns on tables with many indexes). The root cause is in `LibSQLModifyColumn.convert()` in `bin.cjs`.

### Root cause

`LibSQLModifyColumn.convert()` (bin.cjs ~line 26118-26126) generates `DROP INDEX` for **every index on every table in the entire schema** whenever it needs to modify any column:

```js
// bin.cjs line 26118-26126
for (const table6 of Object.Values(json2.tables)) {
for (const index6 of Object.values(table6.indexes)) {
const unsquashed = SQLiteSquasher.unsquashIdx(index6);
sqlStatements.push(`DROP INDEX "${unsquashed.name}";`); // <-- bare DROP INDEX
indexes.push({ ...unsquashed, tableName: table6.name });
}
}
```

When `drizzle-kit push` needs to modify **multiple** columns (e.g., adding NOT NULL columns to different tables, or adding check constraints), `LibSQLModifyColumn.convert()` is called multiple times. Each invocation emits the full set of `DROP INDEX` statements for the entire schema. The second call's `DROP INDEX "some_idx"` fails because the first call already dropped it.

Additionally, `SqliteDropIndexConvertor.convert()` (bin.cjs ~line 26883-26885) generates bare `DROP INDEX` for explicit index drop operations on SQLite/Turso:

```js
// bin.cjs line 26883-26885
convert(statement) {
const { name } = PgSquasher.unsquashIdx(statement.data);
return `DROP INDEX \`${name}\`;`; // <-- bare DROP INDEX
}
```

### Reproduction

Schema with multiple tables, each having indexes. Push a schema change that requires modifying columns on one or more tables (e.g., adding a NOT NULL column, changing a default, adding a check constraint).

```ts
// Simplified schema — 2 tables with indexes
import { sqliteTable, text, integer, index, uniqueIndex } from "drizzle-orm/sqlite-core"
import { sql } from "drizzle-orm"

export const users = sqliteTable("users", {
id: text("id").primaryKey(),
email: text("email").notNull(),
name: text("name"), // will change to .notNull() in next push
}, (t) => [
uniqueIndex("users_email_idx").on(t.email),
])

export const projects = sqliteTable("projects", {
id: text("id").primaryKey(),
ownerId: text("owner_id").notNull().references(() => users.id),
title: text("title"), // will change to .notNull() in next push
createdAt: text("created_at").default(sql`(datetime('now'))`),
}, (t) => [
index("projects_owner_idx").on(t.ownerId),
])
```

1. Push this schema to a Turso database: `drizzle-kit push`
2. Change both `name` and `title` to `.notNull()`
3. Push again: `drizzle-kit push`

The second push calls `LibSQLModifyColumn` twice (once per column change). Each call emits `DROP INDEX` for both `users_email_idx` and `projects_owner_idx`. The second call fails:

```
LibsqlError: SQLITE_UNKNOWN: SQLite error: no such index: users_email_idx
at async Object.batchWithPragma (drizzle-kit/bin.cjs:81427:13)
```

### Expected behavior

`DROP INDEX IF EXISTS` should be used instead of bare `DROP INDEX` in both locations. `IF EXISTS` makes the operation idempotent — duplicate drops become no-ops.

This is consistent with drizzle-kit's own usage of `CREATE INDEX IF NOT EXISTS` and `CREATE TABLE IF NOT EXISTS` elsewhere in the codebase.

### Suggested fix

Two one-line changes in `bin.cjs`:

**1. `LibSQLModifyColumn.convert()` (~line 26123):**
```diff
- sqlStatements.push(`DROP INDEX "${unsquashed.name}";`);
+ sqlStatements.push(`DROP INDEX IF EXISTS "${unsquashed.name}";`);
```

**2. `SqliteDropIndexConvertor.convert()` (~line 26885):**
```diff
- return `DROP INDEX \`${name}\`;`;
+ return `DROP INDEX IF EXISTS \`${name}\`;`;
```

### Environment

- **Database:** Turso (libSQL) via `@libsql/client`
- **Driver:** `drizzle-orm/libsql`
- **drizzle-kit config:** `dialect: "turso"` with `push` command
- **OS:** Linux (also reproduced on macOS)
- **Monorepo:** pnpm workspace (but the bug is independent of monorepo setup)

### Workaround

We are currently using `pnpm patch` on `drizzle-kit@0.31.9` to apply the two-line fix above. This eliminates the entire class of failures.

### Additional context

- The `LibSQLModifyColumn.can()` method gates on `dialect6 === "turso"`, and handles 7 statement types: `alter_table_alter_column_set_type`, `alter_table_alter_column_drop_notnull`, `alter_table_alter_column_set_notnull`, `alter_table_alter_column_set_default`, `alter_table_alter_column_drop_default`, `create_check_constraint`, `delete_check_constraint`. All 7 trigger the full-schema DROP INDEX sweep.
- The `SqliteDropIndexConvertor.can()` gates on `dialect6 === "sqlite" || dialect6 === "turso"`, so the second fix benefits both plain SQLite and Turso.
- The `SQLiteRecreateTableConvertor` and `LibSQLRecreateTableConvertor` (the other table recreation paths) avoid this issue by dropping the entire table rather than individual indexes.
- No MySQL or PostgreSQL code paths are affected — those use separate convertor classes.
- Possibly related: #4574 (duplicate CREATE INDEX on push with check constraints), #5489 (Turso table recreation transaction failures), #887 (destructive index handling on SQLite push).

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.