drizzle-team / drizzle-team/drizzle-orm
[BUG]: Migration generator silently causes cascade data loss during SQLite table recreation without warning
- 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
Assessment
This issue has not been assessed yet.