Could anyone give me some pointers on debugging SQ...

# squarelibraries

[humblehacker]

Could anyone give me some pointers on debugging SQLDelight (1.5.5) migrations? Our app is using KMM, and this migration adds foreign key constraints to the database for the first time. That's the only thing I see as different from our previous (working) migrations. I'm doing the dance of create new table with the changes, insert from old table, drop old table and rename new table for 5 tables. All but one of the tables silently fails copying the data from the old table, but only on iOS - Android works fine. I've also applied the migration manually outside the app and verified it does what it's supposed to.

KMM makes debugging on iOS a challenge, and my log-based debugging hasn't gotten me anywhere other than to verify that, yes, the data is there before the migration and is not there after (by querying directly before migration and again after within the upgrade block on DatabaseConfiguration).

So any pointers on where I should look or any other things to try would be greatly appreciated!

[humblehacker]

Well, I discovered that, for reasons I don't understand, turning on foreign key constraints is causing this silent failure. If I take this out:

extendedConfig = DatabaseConfiguration
    .Extended(foreignKeyConstraints = true)

The migration works fine. I tried turning it back on and adding

upgrade = { connection, oldVersion, newVersion ->                    connection.updateForeignKeyConstraints(false)
  wrapConnection(connection) {
    Database.Schema.migrate(it, oldVersion, newVersion)
  }
},
extendedConfig = DatabaseConfiguration.Extended(foreignKeyConstraints = true)

hoping it would turn them off for the migrations only, but that had no effect.

[humblehacker]

So the above didn't work since the upgrade block is called within a transaction, and This pragma is a no-op within a transaction.

[humblehacker]

So I'm still looking for why having foreign keys on is making my migration silently fail, and how I can work around it.

[humblehacker]

I have a hacky workaround. Since migrations happen automatically as a side effect of driver instantiation, if I create and immediately close a driver without foreign keys turned on, my migrations succeed. Then I create my real driver to be used by my app with foreign keys enabled, bit with an empty upgrade block so it doesn't try to migrate again. It seems to work, inasmuch as my app starts with all it's data intact. Still testing to see if there are any unintended side-effects.

// Workaround for failure to copy data during migrations with foreign keys enabled
NativeSqliteDriver(schema = schema, name = Constants.databaseName).close()

val nativeDriver = NativeSqliteDriver(
    schema = schema,
    name = Constants.databaseName,
    onConfiguration = { config ->
        config.copy(
            upgrade = { _, _, _ -> },
            extendedConfig = DatabaseConfiguration.Extended(foreignKeyConstraints = true)
        )
    }
)

[humblehacker]

Also, I still don't understand why this is a problem to begin with. The exact same migrations work on Android with foreign keys enabled, and also when I test them manually in sqlite. And the broken iOS migrations still result in the correct schema - only the INSERT INTO tableNew SELECT * FROM tableOld fails to copy any data for all but one table.