drizzle-orm: [BUG]: Cannot update SQLite database due to foreign key constraints.
What version of drizzle-orm are you using?
0.29.3
What version of drizzle-kit are you using?
0.20.13
Describe the Bug
I don’t have a reproducible demo created, but after making the following change:
and trying to run pnpm drizzle-kit sqlite:push, I initially got this error:
> drizzle-kit push:sqlite
No config path provided, using default path
Reading config file '/home/derock/Documents/my-cool-project/drizzle.config.ts'
drizzle-kit: v0.20.9
drizzle-orm: v0.29.3
SqliteError: FOREIGN KEY constraint failed
at BetterSqlite.run (/home/derock/Documents/my-cool-project/node_modules/.pnpm/drizzle-kit@0.20.9/node_modules/drizzle-kit/bin.cjs:25392:32)
at Command.<anonymous> (/home/derock/Documents/my-cool-project/node_modules/.pnpm/drizzle-kit@0.20.9/node_modules/drizzle-kit/bin.cjs:63329:33) {
code: 'SQLITE_CONSTRAINT_FOREIGNKEY'
}
When I run it again, it prompts me to confirm that I want to delete __old_push_projects
> drizzle-kit push:sqlite
No config path provided, using default path
Reading config file '/home/derock/Documents/my-cool-project/drizzle.config.ts'
drizzle-kit: v0.20.9
drizzle-orm: v0.29.3
Warning Found data-loss statements:
· You're about to delete __old_push_projects table with 1 items
THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED
Do you still want to push changes?
❯ No, abort
Yes, I want to remove 1 table
and upon agreeing, I get the same error:
SqliteError: FOREIGN KEY constraint failed
at BetterSqlite.run (/home/derock/Documents/my-cool-project/node_modules/.pnpm/drizzle-kit@0.20.9/node_modules/drizzle-kit/bin.cjs:25392:32)
at Command.<anonymous> (/home/derock/Documents/my-cool-project/node_modules/.pnpm/drizzle-kit@0.20.9/node_modules/drizzle-kit/bin.cjs:63329:33)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
code: 'SQLITE_CONSTRAINT_FOREIGNKEY'
}
(I updated my drizzle-kit to latest and it produced the same error)
Expected behavior
Drizzle-kit should update my database.
I’m not sure what the best approaches to this would be, since SQLite’s table alters are pretty limited in what they can do, but maybe we can do something similar to how the Knex ORM resolved a similar issue in #4155 where they disabled foreign key checks temporarily using PRAGMA foreign_keys = ON/OFF; and retroactively checking post-migration with PRAGMA foreign_key_check; (see 7. Making Other Kinds Of Table Schema Changes on the sqlite.org site)
Environment & setup
node v21.5.0 better-sqlite3 9.2.2
edit: just realized I probably should’ve opened this issue on drizzle-kit, not sure how to transfer issues on GitHub though.
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Reactions: 4
- Comments: 17 (2 by maintainers)
I’m working on fixing this one; I’ve already had a chance to reproduce it. I’m not sure if it’s the exact same case, but it looks closely related. I will keep you updated
Any updates with this issue? It’s making it impossible to have a proper workflow when using SQLite and it also happen to be what Cloudflare D1 database is based on. See https://github.com/nuxt-hub/core/issues/155
Any updates on this issue? Still facing problems trying to migrate here.
The issue comes because sqlite doesn’t allow modifying
PRAGMA foreign_keysin a multi-statement transaction as per the manual https://www.sqlite.org/foreignkeys.html:So the code should be changed to reflect that. I don’t know @AndriiSherman if your fix involved this. If not, I can give it a try at fixing it.
Meanwhile, this modification in the migration script works:
WARNING: this disables foreign keys during the entire migration.
EDIT: I have found a much more elegant solution to this issue. Instead of modifying
foreign_keys, we can make use ofPRAGMA defer_foreign_keyshttps://www.sqlite.org/pragma.html#pragma_defer_foreign_keys. Making this simple change to generated sql, replacing foreign_keys with this pragma makes everything just work.Until this is fixed, I’ve came up with my own solution. Can’t say this fixes the problem in general, since I do not know how drizzle kit behaves internally.
Fix - pragmas
I manually edited the
./node_modules/drizzle-kit/bin.cjsfile – the one that runs when i applynpx drizzle-kit. This is as far as I can get without any access to the source code. Luckily the code forpush:sqliteis only a handful, and it seems to be using commander.js and is straightforward to work with.Inside the
dbPushSqliteCommandaction handler, there is a for-loop iteratingstatementsToExecutenear the end. Simply add the next two lines:Why
In my case, the problem happens while dropping a table, where kit tries to change the table by:
ALTER TABLE user RENAME TO __old_push_userCREATE TABLE user (...)INSERT INTO "user" SELECT * FROM "__old_push_user"DROP TABLE __old_push_userThis is how drizzle-kit tries to apply a change to the table.
However, it does not take
FOREIGN KEYinto account, where if there are any other table that has a foreign key reference to theusertable, dropping the__old_push_usertable will fail, because there exist a reference on it (hence the FOREIGN KEY constraint fail error) ^1.Now, sqlite comes with many switches (or PRAGMAs) you can control, and one of them is
PRAGMA foreign_keys, as @ItzDerock said. This “checks” whether there are any invalidations, and errors if so. So in theory, by turning it off (PRAGMA foreign_keys = OFF;), there won’t be any error.But this does not make a successful migration. There is still a problem.
During altering table’s name from
userto__old_push_user(step 2), sqlite also converts existing foreign key references to “__old_push_user”. And if you drop that table, you are left with an non-existing reference. There is another switch –PRAGMA legacy_alter_table– that controls the behavior duringALTER TABLE. By turning this on, the foreign key reference would not follow the table renaming, and is left as “user” ^2.I don’t know if this works for everybody, but it did for me. Until there is an official fix from drizzle-kit, I’m sticking it with this approach.
Meanwhile, I’ll try to come up with a reproducible example.
Hey @ItzDerock / @AndriiSherman, can we reopen this issue? Many people have mentioned that this is still happening.
@AndriiSherman seems the issue still exists.
Migrations do not appear to follow the required approach to handle this in SQLite:
Migrations drizzle generated:
Migrations needed: https://stackoverflow.com/a/1884893
Still experiencing this issue as well
still an issue
It’s is still an issue