Hey, question about SQLDelight migrations in Compo...

# compose-web

[Eugene Sorokin]

Hey, question about SQLDelight migrations in Compose Multiplatform (JS target)

I'm using SQLDelight with SQLite WASM (@sqlite.org/sqlite-wasm) in my Compose Multiplatform project targeting JS, leveraging OPFS for storage.
While the setup works great overall, I'm having issues with database migrations.

The automatic migrations described in the SQLDelight documentation don't seem to be supported by the WebWorkerDriver.
Currently, I'm inclining to do migrations manually using something like this:

Database.Schema.migrate(
    driver = database,
    oldVersion = 0,
    newVersion = Database.Schema.version,
    AfterVersion(3) { driver -> driver.execute(null, "INSERT INTO test (value) VALUES('hello')", 0) },
)

What's the recommended way to handle SQLDelight migrations in this context?
Should I maintain the schema version manually in a separate storage?

Looking for best practices and implementation approaches.
Tech stack:
• Compose Multiplatform (JS target)
• SQLDelight
• SQLite WASM with OPFS

[Artem Kobzar]

If you find a solution, please share it here too ❤️

[eygraber]

That should be the way to do it. IIRC there's a pragma that you can use in Sqlite that stores the version for you

[Eugene Sorokin]

This solution provides a unified approach for handling database migrations across JavaScript and Android platforms (with potential iOS compatibility) while maximising code reuse and minimising platform-specific implementations.

What I Used
• SQLDelight with .sqm files
◦ This was actually pretty nice - keeps all SQL stuff in one place
• multiplatform-settings for version tracking
◦ Yeah, it's an extra dependency, but it works. I tried the PRAGMA way first but ran into some weird issues (database either wouldn't save or read it properly, and honestly I was lazy to dig in)
• A shared migration class called ConorMigrator ✨
• Platform-specific drivers
◦ Can't avoid these, they're just necessary
The Tricky Part
The AndroidSqliteDriver was being a bit stubborn - it wants to do migrations right when you create it. But I wanted all that migration logic in my shared code. Had to get creative with that.

Here's what it looks like:

class ConorMigrator(private val prefs: Settings) {
    suspend fun migrateOrCreate(
        driver: SqlDriver,
        schema: SqlSchema<QueryResult.AsyncValue<Unit>>
    ) {
        val currentVersion = prefs.userVersion
        when {
            currentVersion == DB_NOT_CREATED_VERSION -> {
                schema.awaitCreate(
                    driver = driver
                )
                prefs.userVersion = schema.version
            }

            currentVersion < schema.version -> {
                schema.awaitMigrate(
                    driver = driver,
                    oldVersion = currentVersion,
                    newVersion = schema.version
                )
                prefs.userVersion = schema.version
            }

            else -> {
                //Database already exists, no need for migration
            }
        }
    }

    private companion object {
        const val DB_NOT_CREATED_VERSION = -1L
        const val USER_VERSION_KEY = "DB.USER_VERSION.KEY"

        var Settings.userVersion: Long
            get() = getLongOrNull(USER_VERSION_KEY) ?: DB_NOT_CREATED_VERSION
            set(value) {
                putLong(USER_VERSION_KEY, value)
            }
    }
}

Using It
The cool part is how simple it is to use in your shared code:

kotlin


private suspend fun createDb(driverFactory: DriverFactory): AppDatabase {
    return AppDatabase(
        driver = driverFactory.createDriver(AppDatabase.Schema, conorMigrator),
        ...
    )
}

That's it! It handles creating the database and running any needed migrations automatically.

Quick Notes
• Yes, you need multiplatform-settings (or similar) as an extra dependency. Not ideal, but it gets the job done
• Haven't tested on iOS yet, but should work fine there too
• Had to work around some AndroidSqliteDriver quirks, but nothing too crazy
• gists with the Android and JS adapters