drizzle-team / drizzle-team/drizzle-orm

[BUG]: Migration generator silently causes cascade data loss during SQLite table recreation without warning

Open
#4,938 20 comments 17 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?

0.44.2

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

0.31.4

### Other packages

_No response_

### Describe the Bug

- Database: SQLite (Cloudflare D1)
- Platform: macOS

When Drizzle generates migrations for SQLite schema changes, it uses table recreation (DROP TABLE + recreate) but completely ignores cascade delete effects. This silently destroys related data without any warning or protection.

Expected behavior:
- Migration generator should detect cascade delete relationships
- Either refuse to generate dangerous migrations, OR
- Automatically include backup/restore logic for affected tables
- At minimum, show prominent warnings about potential data loss

Actual behavior:
- Generates innocent-looking DROP TABLE account migration
- Silently destroys all related data via cascade deletes
- No warnings, no protection, no indication of data loss risk

Reproduction:

1. Create tables with cascade delete relationships:
```
-- Parent table
CREATE TABLE account (account_id INTEGER PRIMARY KEY, ...);

-- Child tables with CASCADE DELETE
CREATE TABLE property (
property_id INTEGER PRIMARY KEY,
account_id INTEGER REFERENCES account(account_id) ON DELETE CASCADE
);
```

2. Run drizzle-kit generate when any account table change is detected
3. Generated migration contains:
DROP TABLE account; -- Silently destroys ALL related data
ALTER TABLE __new_account RENAME TO account;

Minimal reproduction:
```
// Schema with cascade relationships
export const account = sqliteTable("account", {
accountId: integer("account_id").primaryKey(),
name: text("name"),
});

export const property = sqliteTable("property", {
propertyId: integer("property_id").primaryKey(),
accountId: integer("account_id").references(() => account.accountId, {
onDelete: "cascade"
}),
});

// Any schema change to account triggers dangerous migration
```

Impact:
- Data loss: All related records silently deleted
- Silent failure: No indication that data will be lost
- Production risk: Appears safe but destroys data

Workaround:
Manually rewrite generated migrations with backup/restore pattern:
-- Safe approach: backup related data first
CREATE TABLE backup_property AS SELECT * FROM property;
DROP TABLE account;
-- recreate account table
INSERT INTO property SELECT * FROM backup_property;
DROP TABLE backup_property;

Related issues:
- #4155: Similar cascade constraint issues
- #1813: Foreign key constraint problems during migrations

This is a critical data safety issue that affects anyone using SQLite with meaningful foreign key relationships.

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.