drizzle-team / drizzle-team/drizzle-orm

[BUG]:drizzle-kit generates DROP COLUMN before DROP INDEX causing PostgreSQL error 42704

Open
#6,045 0 comments 0 reactions 0 assignees View on GitHub
bug
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?

1.0.0-rc.4

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

1.0.0-rc.4

### Other packages

_No response_

### Describe the Bug

### **What is the undesired behavior?**

When removing columns that belong to an index from a Drizzle schema, drizzle-kit generate places `ALTER TABLE ... DROP COLUMN` statements before the `DROP INDEX` statement in the generated .sql migration file.

In PostgreSQL, dropping a column automatically drops any index referencing that column under the hood. Because of this, when drizzle-kit migrate reaches the explicit `DROP INDEX "index_name";` line, PostgreSQL throws error: index "index_name" does not exist `(code 42704)` and fails the migration.

### **Current generated SQL order (Fails during execution):**

**This was orignal schema**
```
export const notesToTagsTable = p.pgTable(
"notes_to_tags", {
noteId: p.uuid().references(() => notesTable.id, { onDelete: "cascade", onUpdate: "cascade", }).notNull(),
tagAuthorId: p.uuid().notNull(),
tagName: p.varchar().notNull()
},
(t) => [
p.primaryKey({ name: "notes_to_tags_id", columns: [t.noteId, t.tagAuthorId, t.tagName] }),
p.index("notes_to_tags_tagId_idx").on(t.tagAuthorId, t.tagName),
p.foreignKey({
name: "notes_to_tags_foreignKey", columns: [t.tagAuthorId, t.tagName],
foreignColumns: [tagsTable.authorId, tagsTable.name]
})
],
);
```
**Generated SQL First migration snipset**
```
CREATE INDEX "notes_to_tags_tagId_idx" ON "notes_to_tags" ("tagAuthorId","tagName");
```
**I changed the schema**:
```
export const notesToTagsTable = p.pgTable(
"notes_to_tags", {
noteId: p.uuid().references(() => notesTable.id, { onDelete: "cascade", onUpdate: "cascade", }).notNull(),
tagId : p.uuid().references(() => tagsTable.id, { onDelete: "cascade", onUpdate: "cascade", }).notNull(),
},
(t) => [
p.primaryKey({name: "notes_to_tags_id", columns: [t.noteId, t.tagId]}),
p.index("notes_to_tags_tagId_idx").on(t.tagId)
],
);
```
**Second Migration snipset**
```
ALTER TABLE "notes_to_tags" DROP COLUMN "tagAuthorId";
ALTER TABLE "notes_to_tags" DROP COLUMN "tagName";

DROP INDEX "notes_to_tags_tagId_idx";
CREATE INDEX "notes_to_tags_tagId_idx" ON "notes_to_tags" ("tagId")
```
**-- 💥 FAILS HERE**:
PostgreSQL already dropped this index when dropping the columns above
`DROP INDEX "notes_to_tags_tagId_idx";`

### **What are the steps to reproduce it?**

Define a PostgreSQL table schema with columns and an index referencing those columns:

```
export const notesToTags = pgTable("notes_to_tags", {
noteId: uuid("note_id"),
tagAuthorId: text("tagAuthorId"),
tagName: text("tagName"),
}, (t) => [
index("notes_to_tags_tagId_idx").on(t.tagAuthorId, t.tagName)
]);
```

Run drizzle-kit generate and drizzle-kit migrate to apply the initial schema.

`Update the TypeScript schema by removing the tagAuthorId and tagName columns as well as the notes_to_tags_tagId_idx index definition.`

- Run drizzle-kit generate to create the secondary migration file.

- Run drizzle-kit migrate.

**What is the desired result?**

drizzle-kit should generate statements in a sequence that respects PostgreSQL's column-index dependency cascade, or make the drop statement idempotent:

## Primary Solution:
Order `DROP INDEX ` statements before `DROP COLUMN` statements in the `generated .sql` file:

```
DROP INDEX "notes_to_tags_tagId_idx";
ALTER TABLE "notes_to_tags" DROP COLUMN "tagAuthorId";
ALTER TABLE "notes_to_tags" DROP COLUMN "tagName";
```

## Alternative Solution:
Use `IF EXISTS` when generating index drops `(DROP INDEX IF EXISTS "notes_to_tags_tagId_idx";)` so that implicit cascades in PostgreSQL do not crash the migration runner.

Contributor guide

Open the contributing guide

Research direction

Reproduce the issue with the PostgreSQL schema shown, then run drizzle-kit generate and inspect the generated .sql migration before running drizzle-kit migrate. Trace the migration-generation entry point that orders DROP INDEX and DROP COLUMN statements. Done means the migration applies without PostgreSQL error 42704, using either dependency-safe ordering or an idempotent index drop.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
databases, tooling
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.