Allow sqlite foreign key constraints to be disabled and enabled in migrations (`PRAGMA foreign_keys`)

[aaroned]

The sqlite migrate uses a batch transaction to execute the migrations.

However:

1. certain changes to sqlite databases with foreign key constraints enabled will need to use PRAGMA foreign_keys = OFF and PRAGMA foreign_keys = ON https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes, and
2. these PRAGMA statements are not supported inside transactions https://www.sqlite.org/pragma.html#pragma_foreign_keys.

One idea is to script a transaction for each migration in the generated migration sql file. Then in migrate detect these transactions and execute them using batch, whilst using run for all other statements outside the transaction. (The migration table INSERT would also have to be scripted into the migration sql file inside the transaction)

[tacomanator]

Usually only upvote, but want to chime in here.

Currently the only way to remove a FK constraint, within the drizzle-kit framework, is to drop and recreate all tables involved. When the need arises, albeit rare, it's quite painful and error prone—and can quickly spiral out of control, having to recreate a significant number of tables.

That said, don't know how complicated this issue is to solve, so I appreciate your consideration and in the meantime have resigned myself to scheduling a downtime and running the migrations manually.

On a side note, haven't use this before but wonder if PRAGMA defer_foreign_keys = boolean; could be used here?

[tacomanator]

Well, answering my own question here, it seems that PRAGMA defer_foreign_keys = ON; actually works as is already, at least in my case. Here is how I'm doing it:

CREATE TABLE temp_table AS SELECT * FROM table;--> statement-breakpoint

PRAGMA defer_foreign_keys = ON;--> statement-breakpoint

DROP TABLE table;--> statement-breakpoint

CREATE TABLE table (column  INTEGER, ## used to be NOT NULL);--> statement-breakpoint

INSERT INTO table (column) SELECT column FROM temp_table;--> statement-breakpoint

DROP TABLE temp_table;

Tried it out right after writing the comment above. The migration did not fail and a cursory check looks good 😅

Still not ideal, but a lot better than recreating all of the tables with inbound FK references at the same time...

[lightyaer]

I never got drizzle kit to work with turso/local libsql without getting a bunch of

• Foreign Key constraint errors
• Relation does not exist __old_push_tablename
• table does not exist __old_push_tablename

Almost every time I had to manually create/remove indexes, columns and constraints. I have a template which I use for each migration;

PRAGMA foreign_Keys=off;
BEGIN TRANSACTION;

-- statements

COMMIT;
PRAGMA foreign_Keys=on;