Document enabling foreign keys for the SQLDelight Multiplatform drivers

[dellisd]

Discussed in #3681

^{Originally posted by darronschall November 19, 2022}
SQLite3 does not enable foreign key constraints by default.

There isn't a well-documented way to do this for the SQLDelight Multiplatform drivers that I could find. The issue at #1406 has a few links, but doesn't collect everything in a single place.

It took me some time and digging to gather everything I needed to enable foreign key constraints in my KMM project. I thought I'd open this discussion to share my findings to help others in the future. I pulled these snippets from the issues linked under each class heading.

These are all working for me in SQLDelight 1.5.4.

AndroidSqliteDriver

See: #1241 (comment)

AndroidSqliteDriver(
   schema = Database.Schema,
   context = get(),
   name = "Database",
   callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
      override fun onOpen(db: SupportSQLiteDatabase) {
           db.setForeignKeyConstraintsEnabled(true)
      }
   }
)

NativeSqliteDriver

See #1356 (comment). I believe once SQLDelight 2.0.0 is released, we'll be able to simplify the NativeSqliteDriver setting per #3493.

NativeSqliteDriver(
    DatabaseConfiguration(
        name = "Database",
        version = Database.Schema.version,
        create = { connection ->
            wrapConnection(connection) { Database.Schema.create(it) }
        },
        upgrade = { connection, oldVersion, newVersion ->
            wrapConnection(connection) { Database.Schema.migrate(it, oldVersion, newVersion) }
        },
        extendedConfig = DatabaseConfiguration.Extended(
            foreignKeyConstraints = true
        )
    )
)

JdbcSqliteDriver

See #2421 (comment).

(In KMM projects, JdbcSqliteDriver is useful for creating an in-memory database for androidTest tests that run via the JVM).

JdbcSqliteDriver(
    JdbcSqliteDriver.IN_MEMORY,
    Properties().apply { put("foreign_keys", "true") }
).apply {
    Database.Schema.create(this)
}
```</div>