drizzle-team / drizzle-team/drizzle-orm
[BUG]: [SQLite/Turso] LibSQLModifyColumn generates duplicate DROP INDEX (without IF EXISTS) causing "no such index" on db:push
- 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
Assessment
This issue has not been assessed yet.