SQLDelight Destructive migration

Question

Hi I'm new to Kotlin multiplatform, I'm using SQLDelight for my KMM project. is there a way to completely delete all old contents of old schema from db and point to new schema. i red it supports migration with versions https://cashapp.github.io/sqldelight/2.0.0-alpha05/android_sqlite/migrations/ i was looking for destructive migration without the need of carrying old content and schema. If there is any way by passing some value to driver or by setting some value to build.gradle

sqldelight {
database("ExploreLiveDataBase") {
    packageName = "com.test.lib_test_imp.sqldelight"
    sourceFolders = listOf("sqldelight")
    linkSqlite = true
}

lateinit var context: Context
actual fun createDriver(): SqlDriver {
    return AndroidSqliteDriver(test.Schema, context, "test.db")
}

If it's supported, will it also work cross platform on both iOS and Android. Any help is appreciated, Thank You.

score 0 · Answer 1 · answered Feb 21 '23 at 09:46

From iOS side you can use co.touchlab.sqliter.DatabaseFileContext:

DatabaseFileContext.deleteDatabase(filename)

From Android side you need android context:

context.deleteDatabase(filename)

And don't forget to call driver.close() before deletion, in case it has being opened.

skajar · Answer 2 · 2023-02-25T12:22:22.083

I solved this issue by deleting the old database file and creating a new one. It may not be a great solution, but it's pretty simple and sufficient (for me) until sqlDelight has a built-in mechanism to handle destructive migrations.

Code for koin:

val databaseModule = module {

single<SqlDriver> {
    val dbVersion = 2
    androidApplication().deleteDatabase("hotelApp${dbVersion-1}.db")

    AndroidSqliteDriver(
        schema = HotelDatabase.Schema,
        context = androidApplication(),
        name = "hotelApp$dbVersion.db"
    )
}

single { HotelDatabase(get()) }

single<StayDataSource> { StayDataSourceImpl(get()) }
single<LicenseDataSource> { LicenseDataSourceImpl(get(), get()) }

}

score 0 · Answer 3 · answered Jul 20 '23 at 14:38

In SqlDelight 2: You can have a destructive migration with this.

internal object DestructiveMigrationSchema :
SqlSchema<QueryResult.Value<Unit>> by SupportSdkDatabase.Schema {

override fun migrate(
    driver: SqlDriver,
    oldVersion: Long,
    newVersion: Long,
    vararg callbacks: AfterVersion
): QueryResult.Value<Unit> {
    val tables = driver.executeQuery(
        identifier = null,
        sql = "SELECT name FROM sqlite_master WHERE type='table';",
        parameters = 0,
        mapper = { cursor ->
            QueryResult.Value(buildList {
                while (cursor.next().value) {
                    val name = cursor.getString(0)!!
                    if (name != "sqlite_sequence" && name != "android_metadata") {
                        add(name)
                    }
                }
            })
        }
    ).value

    for (table in tables) {
        driver.execute(identifier = null, sql = "DROP TABLE $table", parameters = 0)
    }

    return create(driver)
 }
}

This is platform-independent. No need to delete the DB files.

SQLDelight Destructive migration

3 Answers3