Sqldelight: How to enable foreign keys? PRAGMA statement?

[ursusursus]

Hi, Ive noticed my ON CASCADE DELETE doesnt work, is it because of foreign keys being turned off on android by default? If so, how can turn them on?

Naively putting them at the top of Foo.sq file PRAGMA foreign_keys = ON; does not compile, and PRAGMA foreign_keys = 1;does not work

[AlecStrong]

You need to do it onOpen instead of on create. You'll have to provide a callback to the driver and override the onOpen where you can then turn the pragma on

[ursusursus]

For future readers

val driver = AndroidSqliteDriver(
    schema = Database.Schema,
    context = context,
    name = "mydb",
    callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
        override fun onOpen(db: SupportSQLiteDatabase) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    })

[AlecStrong]

:+1: thanks

[LouisCAD]

It'd be great to be able to run SQL when opening the database in a multiplatform way, to avoid subtle bugs that would happen on iOS but not on Android for example.

[AlecStrong]

Yea that's a fair point. The best solution would probablybe to add an onOpen to SqlDatabase.Schema that you can override and drivers have to call into it.

[eygraber]

Is there no way to enable/disable them while the connection is open?

My use case is that I want to use INSERT OR REPLACE but if I have a FK with an ON DELETE CASCADE those rows will get deleted.

The way I want to deal with it is by sandwiching the INSERT OR REPLACE with turning foreign keys off then on:

PRAGMA foreign_keys = OFF;
INSERT OR REPLACE ...
PRAGMA foreign_keys = ON;

Of course this would be less necessary if upsert was allowed :wink:

EDIT: using 0 and 1 instead of OFF and ON worked

[mirokolodii]

@AlecStrong How to do the same for NativeSqliteDriver?

[vanniktech]

I'm doing driver.execute(null, "PRAGMA foreign_keys=ON", 0)

[mirokolodii]

@vanniktech great, thanks for quick reply.