D1 is Cloudflare’s managed database service. It provides a serverless database that is compatible with SQLite.

That said, being compatible doesn’t equal feature parity.

D1 can’t disable FK via PRAGMA foreign_keys = OFF, which makes CASCADE actions unavoidable during migration.

This means that unless you structure your migrations in a very specific way, your data will be silently wiped.

In this post I will dig into this problem and how it can be solved.

foreign_keys vs defer_foreign_keys

If you migrate something with SQL constraints enabled in SQLite, you need to frame your migration in PRAGMA foreign_keys.

This way you disable constraints from firing and re-enable them when the migration is done.

D1 doesn’t allow PRAGMA foreign_keys = OFF, but has a “similar” option also available in SQLite, PRAGMA defer_foreign_keys = ON.

It’s easy to get tricked into thinking that these are the same, with the only difference being that one is a switch and the other is delayed execution. But this is not true.

While PRAGMA defer_foreign_keys = ON indeed defers the FK check, constraints like ON DELETE CASCADE are not disabled and fire immediately.

So all referenced data will be silently wiped during this migration.

Drizzle ORM doesn’t handle this limitation

You might think that Drizzle — a popular ORM with first-party D1 support — may solve this issue for you, as it autogenerates migrations.

But Drizzle actually treats D1 as actual SQLite:

1. It frames the migration in PRAGMA foreign_keys=OFF, which doesn’t work in D1. The rest of the migration is based on the fact that we have FK disabled
2. Knowing so, Drizzle does alphabetical ordering of migrations instead of relation-based ordering

As a result, this migration leads to data loss when run against D1, due to ON DELETE CASCADE firing.

Solving this issue

In case your migration drops a parent referenced by an FK at any point, you need to edit your autogenerated migrations:

1. Frame the migration in PRAGMA defer_foreign_keys. You need that to perform operations on any referenced FK.
2. Rewrite migrations in a very specific way that temporarily removes FK constraints from children when doing deletes on parents

Example with a database that has parent and child tables, where the child has constraints:

-- Start deferring FK
-- PRAGMA defer_foreign_keys = ON;

-- 1. DETACH: rebuild each cascade child of a table you're about to drop with the
--    FK temporarily demoted to NO ACTION (deepest children first).
-- 2. REBUILD: rebuild the tables that are actually changing.
-- 3. REATTACH: rebuild those children again with ON DELETE CASCADE restored,
--    now that their parents exist.

-- Do not forget to turn this off at the end of the migration – otherwise you will run into an error
-- PRAGMA defer_foreign_keys = OFF;

PRAGMA defer_foreign_keys = ON;

-- DETACH child → NO ACTION
CREATE TABLE __detach_child (id integer PRIMARY KEY, parent_id integer REFERENCES parent(id));
INSERT INTO __detach_child SELECT id, parent_id FROM child;
DROP TABLE child;
ALTER TABLE __detach_child RENAME TO child;

-- REBUILD parent (child is NO ACTION now, so this DROP cascades nothing)
CREATE TABLE __new_parent (id integer PRIMARY KEY, label text);
INSERT INTO __new_parent (id) SELECT id FROM parent;
DROP TABLE parent;
ALTER TABLE __new_parent RENAME TO parent;

-- REATTACH child → CASCADE
CREATE TABLE __new_child (id integer PRIMARY KEY, parent_id integer REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO __new_child SELECT id, parent_id FROM child;
DROP TABLE child;
ALTER TABLE __new_child RENAME TO child;

PRAGMA defer_foreign_keys = OFF;

This is a universal solution that can be automated. Of course, you can craft a more eloquent version tuned for each scenario.

Takeaways

1. PRAGMA defer_foreign_keys doesn’t disable ON DELETE CASCADE
2. Right now you can’t rely on migrations autogenerated by Drizzle ORM
3. If you use FK constraints, you need to rewrite autogenerated migrations in a way that manually removes constraints during migration (Detach -> Rebuild -> Reattach)

I hope that in the future this issue will be resolved, maybe with proper D1 support from Drizzle, the introduction of a way to postpone constraints in D1, or at least a proper notice in the D1 docs.

Currently I resolve this issue by either relying on programmatic deletes, or by autogenerating proper migrations based on Drizzle data like journals and migrations.

But I dislike both solutions :(