Can't do SQLite migrations involving temporarily disabling foreign key enforcement

[v21]

I'm trying to do a migration involving adding a constraint to a field within a table that is referenced as a foreign key by other tables in SQLite. As you might know, SQLite's ALTER syntax is quite limited, so the best way to do this is by creating a new temporary table, inserting all the data from the original table, dropping the old table and renaming the temporary table to take its place.

This is where the problem comes in! Removing the original table invalidates all those foreign key constraints. The normal fix for this is to run PRAGMA foreign_keys = OFF; at the start of the migration, and then PRAGMA foreign_keys = ON; at the end. However, this can't be set while in the middle of a transaction... and sqlx, very sensibly, performs all migrations within a transaction.

I'm not sure exactly what the best resolution to this is - it seems like it would be pretty fussy to add options to disable foreign key enforcement temporarily, or not wrap migrations within a transaction. Possibly it would be reasonable to automatically run PRAGMA defer_foreign_keys = ON at the start of every migration?

Additionally, I will say that I was confused for a little while as to why the migration would work when I applied it manually, but wouldn't work when it was run from the command line (the answer is that sqlx defaults PRAGMA foreign_keys to be off).

[v21]

follow up: it seems like bracketing it with PRAGMA defer_foreign_keys = ON; and PRAGMA defer_foreign_keys = OFF; does make it work. I am not familiar enough with arcane SQLite stuff to give a super informed view on whether this should be automatically applied to every migration, but I suspect it might be a good idea - my understanding is that the foreign key constraints will still be checked, but just at the time that the transaction is committed.

[ikai104]

I have a workaround. Try to write following code in your migrations/*.sql:

-- remove the original TRANSACTION
COMMIT TRANSACTION;

-- tweak config
PRAGMA foreign_keys=OFF;

-- start your own TRANSACTION
BEGIN TRANSACTION;

-- #####################################
-- ## Do anything you want to do here ##
-- #####################################
--
-- See Also: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

-- check foreign key constraint still upholding.
PRAGMA foreign_key_check;

-- commit your own TRANSACTION
COMMIT TRANSACTION;

-- rollback all config you setup before.
PRAGMA foreign_keys=ON;

-- start a new TRANSACTION to let migrator commit it.
BEGIN TRANSACTION;

It pretty ugly but work fine for me with sqlx 0.6.2. Though I still want a proper fix if it's possible :D

[crepererum]

BTW: this hack does NOT work w/ Postgres. E.g.

COMMIT;
CREATE INDEX CONCURRENTLY idx ON table (col);
BEGIN;

results in:

CREATE INDEX CONCURRENTLY cannot run inside a transaction block