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: image

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)

Most upvoted comments

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 modifyingPRAGMA foreign_keysin a multi-statement transaction as per the manual https://www.sqlite.org/foreignkeys.html:

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

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:

import Database from "better-sqlite3"
import "dotenv/config"
import { sql } from "drizzle-orm"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"

const sqlite = new Database(process.env.DATABASE_URL)
const db = drizzle(sqlite, { logger: true })

db.run(sql`PRAGMA foreign_keys=OFF;`)

try {
  migrate(db, { migrationsFolder: "./drizzle" })
} finally {
  db.run(sql`PRAGMA foreign_keys=ON;`)
}

sqlite.close()

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 of PRAGMA defer_foreign_keys https://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.

PRAGMA defer_foreign_keys=ON;--> statement-breakpoint

-- Your migration code

PRAGMA defer_foreign_keys=OFF;

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.cjs file – the one that runs when i apply npx drizzle-kit. This is as far as I can get without any access to the source code. Luckily the code for push:sqlite is only a handful, and it seems to be using commander.js and is straightforward to work with.

Inside the dbPushSqliteCommand action handler, there is a for-loop iterating statementsToExecute near the end. Simply add the next two lines:

var dbPushSqliteCommand = ...
  // ...
  .action(async (options) => {
    // ...
    statementsToExecute.unshift(`PRAGMA foreign_keys = OFF;`);      // <-- HERE
    statementsToExecute.unshift(`PRAGMA legacy_alter_table = ON;`); // <-- HERE
    for (const dStmnt of statementsToExecute) {
      await connection.client.run(dStmnt);
    }
    // ...
});

Why

In my case, the problem happens while dropping a table, where kit tries to change the table by:

  1. rename the old table to something else – ALTER TABLE user RENAME TO __old_push_user
  2. create a new table with the name – CREATE TABLE user (...)
  3. move data from old to new – INSERT INTO "user" SELECT * FROM "__old_push_user"
  4. drop old table – DROP TABLE __old_push_user

This is how drizzle-kit tries to apply a change to the table.

However, it does not take FOREIGN KEY into account, where if there are any other table that has a foreign key reference to the user table, dropping the __old_push_user table 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 user to __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 during ALTER 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.

[⣷] applying migrations...LibsqlError: SQLITE_ERROR: error in table items after drop column: unknown column "user_uuid" in foreign key definition

Migrations do not appear to follow the required approach to handle this in SQLite:

Migrations drizzle generated:

ALTER TABLE `items` DROP COLUMN `user_uuid`;--> statement-breakpoint

Migrations needed: https://stackoverflow.com/a/1884893

Still experiencing this issue as well

still an issue

It’s is still an issue