⚠️ Performing SQLite migrations that require foreign keys off (PRAGMA foreign_keys=OFF;)

[matthewlarkin]

I recently had trouble preventing SQLPage (or maybe just sqlx) from automatically removing records during SQLite migrations involving foreign keys and on cascade delete, despite setting PRAGMA foreign_keys=off; at the top of the migration file.

It would seem if we need to truly disable foreign keys during migration, we need to commit the initial (ghost) transaction that SQLPage sets up.

COMMIT TRANSACTION;
PRAGMA foreign_keys=OFF; # must be set *before* a transaction
BEGIN TRANSACTION;

-- migration statements here

COMMIT TRANSACTION;
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;

---

@lovasoa, would you say this is a safe move? I haven't run into issues yet, but I feel this approach is a little hacky, and I wonder if it would cause any issues with SQLPage's ability to stop a faulty migration, detect faulty file changes before committing, etc. I don't know what's going on under the hood here, so I'm not sure what's being affected by committing the transaction early like that.

At any rate, I'm just glad I was able to catch this one out ahead of accidental deletion, even if I "knew for sure the migration should work"! 😅😬

[lovasoa]

Hey !

Indeed, this behavior is defined in sqlx. But, considering the alternative, I think it's a good one.
I think the general expected behavior of migrations is that a single migration is a single indivisible unit that can either succeed or fail. We need to keep track of the status of each migration in the db, and there is no such state as "partially applied".

So yes, this requires making a "ghost" transaction, but it's probably better than ending up in a state where a migration is partially failed.

[matthewlarkin]

Awesome! Yes, I think that's a sensible default. So, if I'm understanding correctly:

• sqlx is really just helping us get either 100% success or nothing at all (no half-baked migrations)
• SQLPage is just checksum-validating using the _sqlx_migrations table to help us enforce a clean transaction history

If that's all, then the code I posted above should be fine, as long as it only has one primary transaction (technically there are three, but essentially we only have our one substantive migration, and that will still either pass or fail), yes?

And that workaround is necessary for sqlite migrations that require foreign keys turned off, correct? I'm not overlooking a simpler solution?

Thanks for clarifying @lovasoa! 🙏

[lovasoa]

Yes, your usage should be fine. But I would still try to make the migration work with foreign keys on if possible.