ForeignKey: Inconsistent behavior across different KMP targets, especially during migration

[tamimattafi]

Problem

Enabling and disabling foreign keys currently works through the API provided by sqldelight, however, this leads to inconsistent behavior between android and iOS.

Explanation

Inside NativeSqliteDriver, and exactly during the creation of a connection through DatabaseManager, PRAGMA foreign_keys = ON; is called before migrate.

migrate is executed inside a transaction, therefore, our PRAGMA foreign_keys = OFF; call inside KabinSqlSchema.migrate function has no effect, because according to the official docs, it should be called before BEGIN TRANSACTION or after COMMIT https://www.sqlite.org/pragma.html#pragma_foreign_keys

Even tho we have defer_foreign_keys calls inside our transactions, some errors triggered by foreign keys during migration are not handled, since they are not directly related to constraints, for example, having table not found errors when dropping related tables. This throws an error immediately and doesn't wait for COMMIT to recheck the integrity of the keys https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys

On android, foreign keys are enabled inside the callback onOpen, here's the official documentation of the method:

Called when the database has been opened. The implementation should check SupportSQLiteDatabase.isReadOnly before updating the database.
This method is called after the database connection has been configured and after the database schema has been created, upgraded or downgraded as necessary. If the database connection must be configured in some way before the schema is created, upgraded, or downgraded, do it in onConfigure instead.
Params:
db - The database.

As we can see, onOpen is called after all the work with the scheme is done, including migration, which means during migration, foreign keys are disabled, and enabled only after everything is set.

Solution

Make a unified logic to enabled and disable foreign keys during driver creation, using raw SQL calls, rather than using platform specific config. This will help to increase the consistency and similarity.