drizzle-team / drizzle-team/drizzle-orm
[BUG]:drizzle-kit generates DROP COLUMN before DROP INDEX causing PostgreSQL error 42704
- 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
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