I had asked a question about SqlDelight in this ch...

# squarelibraries

[Daniele B]

I had asked a question about SqlDelight in this channel a couple of days ago, but I didn't get an answer.
As I don't know a better place to ask such a question, I try again, hoping to have more luck 🤞

I would like to ship a pre-populated sqlite database for a KMP app (Android, iOS, Desktop, and ideally Web too).
I have defined the db schema on the SqlDelight .sq file.
I verified (using the gradle tasks) that it matches the schema of my sqlite database.
What I don't understand is how to migrate a new database (with the same schema, but different data). I can't find anything on the SqlDelight documentation.

[jw]

SQLDelight is not involved here, really. You probably have to do something different for each platform. On Android you'd ship it in assets and copy it to the database location on the file system before trying to open with SQLDelight. In iOS you probably do something similar with a resource and a copy before opening with SQLDelight. Same with desktop, ship it as a resource and copy it to the path where the DB goes and then open with SQLDelight.

The common theme of these, though, is that it occurs before you do anything with SQLDelight.

[Soundlicious]

@Daniele B
https://akwasiio.github.io/posts/kmp-embedding-database/

[Daniele B]

Thanks.
I think it wouldn't be a bad idea for SqlDelight to provide helper functions for this.

[jw]

I don't think it makes sense. SQLDelight operates on an open database and this action is something you do before opening. It's also something you may want to do if you are not using SQLDelight but some other mechanism of querying.

[reline]

@Daniele B Room has an implementation for prepopulated databases that could work as a starting point for Android
https://android.googlesource.com/platform/frameworks/support/+/refs/heads/androidx-master[…]me/src/main/java/androidx/room/SQLiteCopyOpenHelper.java

val factory: SupportSQLiteOpenHelper.Factory = SQLiteCopyOpenHelper.Factory(...)
val driver: SqlDriver = AndroidSqliteDriver(
        context = applicationContext,
        schema = Database.Schema,
        factory = factory,
        name = "database.db"
)
val database = Database(driver)

shameless plug, I made a library to do exactly this
https://github.com/reline/SQLiteCopyOpenHelper

[Daniele B]

@reline exactly, I was expecting SqlDelight would make it as easy as Room does. Thanks for sharing the library

[ursus]

just copying a file should not be so hard

btw how about an actual migration? i.e. artificially bumping version to 2, and the have bunch of inserts in .sqm file?

[reline]

my implementation doesn't support that (I didn't need it) but it's easy enough to create a config model like Room has and just don't copy/overwrite the existing database if a migration is required.
androidx/room/SQLiteCopyOpenHelper.java
androidx/room/DatabaseConfiguration.java