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 I think this approach won't work but I have a test that should confirm that..

If I understand this correctly, migrations can now go haywire with FKs, but at the same time, they are guaranteed to not have you lose data acidentally because of ON DELETE CASCADE constraints. This was pretty daunting until now. However, is simply disabling FKs a good idea? It will now be possible to write migrations that break the internal consistency of the database. Until now, setting PRAGMA foreign_keys=OFF was tedious, but at least you had to know what you were doing.

FYI for anybody that comes accross this trying to figure out how to use PRAGMA foreign_keys=OFF in migrations, this change doesn't seem to apply to migrations:run (or when using migrationsRun: true), or migrations:revert. I don't know if that was intentional or not.

Explanation in case it wasn't intentional:
MigrationExecuter.executeMigration() was updated for this change (to use PRAGMA foreign_keys=OFF if using an sqlite driver). However Connection.runMigrations() uses MigrationExecuter.executePendingMigrations() which doesn't use MigrationExecuter.executeMigration() so hence this change didn't do anything for migrations:run.
For migrations:revert queryRunner.beforeMigration() and queryRunner.afterMigration() do get called, but inside the transaction that wraps the down() migration and so the PRAGMA foreign_keys=OFF in beforeMigration() doesn't do anything

As a result the workaround to do this for 0.2.x is still to run the migrations yourself like this (and not use migrationsRun: true), however this means you can't use the cli for migrations:run or revert.

connection.query(`PRAGMA foreign_keys=OFF`);
connection.runMigrations();
connection.query(`PRAGMA foreign_keys=ON`);

A longer term solution could be to have a beforeTransaction and afterTransaction hook inside MigrationInterface which would allow the migration author to set PRAGMA foreign_keys=ON if they want it.

Hello, this might be working well when we execute one specific migration.
But it does not apply for pending migrations being processed automatically when DataSource is initialize (if any):

Adding here as well beforeMigration and afterMigration would be consistent

@imnotjames Hi 👋🏽 ,
was it intentional that the beforeMigration / afterMigration calls were not added in executePendingMigrations ?