A SQLDelight migration is a contract with every installed version

TL;DR#

• A migration from schema 6 to 7 that passed every test and crashed on 11% of installs — the ones upgrading from version 3, because migration 4.sqm had added a column with a NOT NULL and no default, which only fails for a device that actually runs it.
• Migrations run forward from whatever the device holds. A test that creates the newest schema and applies the newest migration never executes the older ones, so a broken step stays broken until a sufficiently old device upgrades.
• The fix was to replay every path from every shipped version, generated from the migration files rather than hand-listed, and to seed each one with real rows so a constraint has something to reject.
• What reading on buys: the version distribution that made 11% possible, why a schema-only test passes, and the generated replay.

The case this post is about#

A notes database with six shipped schema versions and a seventh in progress:

-- src/commonMain/sqldelight/databases/4.db ... and the migration that produced it
-- 4.sqm
ALTER TABLE note ADD COLUMN folder_id TEXT NOT NULL;

-- 7.sqm, the one being added
ALTER TABLE note ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0;
CREATE INDEX note_pinned ON note(pinned);

// commonTest/kotlin/db/MigrationTest.kt — the test that existed
@Test
fun migratesToLatest() {
    val driver = inMemoryDriver()
    AppDatabase.Schema.create(driver)                    // creates version 7 directly
    AppDatabase.Schema.migrate(driver, 6, 7)             // then applies the newest step
    // assert the new column exists
}

Green on every target, on every run, for six versions.

The symptom#

A crash wave on release, Android and iOS, concentrated in a cohort:

android.database.sqlite.SQLiteException: Cannot add a NOT NULL column with default value NULL
  (code 1 SQLITE_ERROR)
    at app.cash.sqldelight.db.SqlDriver.execute
    at com.example.db.AppDatabaseKt.migrate (4.sqm:1)

4.sqm, in a release that shipped 7.sqm. And the cohort:

# crashing installs, by the schema version they upgraded FROM
  from 6 → 7        0 crashes    (84% of installs)
  from 5 → 7        0            ( 4%)
  from 4 → 7        0            ( 1%)
  from 3 → 7    2,118            (11%)     ← 2,118 of the 2,134 in that bucket

Eleven percent of installs were on schema 3 — a version shipped fourteen months earlier — because those users had auto-update off and had just been prompted by a store campaign. They upgraded in a wave, and every one of them ran 4.sqm for the first time.

4.sqm had been in the repository for over a year and had shipped in four releases. No test had ever executed it, for reasons the next section covers. My first theory was that no device had either — that everyone still on 3 had simply never upgraded.

That theory is wrong, and working out why is the useful part. Thousands of devices had run 4.sqm successfully, fourteen months earlier, when they upgraded from 3 to 4. SQLite's ALTER TABLE ADD COLUMN ... NOT NULL without a default succeeds on an empty table and fails only when there are rows to backfill — and back then the note table was empty on those devices, because the feature that populated it had not shipped yet. The migration was not broken when it ran. It became broken, in place, as users created notes.

Why the obvious fix failed#

The first fix was to correct 4.sqm, which is already shipped:

-- 4.sqm, amended
ALTER TABLE note ADD COLUMN folder_id TEXT NOT NULL DEFAULT '';

Editing a shipped migration is the thing you must not do. Devices that already ran the original 4.sqm have a schema produced by it; a device running the amended one ends up in a different state, and the version number claims they are the same. We would have two populations both reporting schema 4 with different column constraints, and migration 5 onward is written against one of them.

The second fix was to add a 5.sqm-style repair step, which is the right shape and had to go in the right place:

-- 8.sqm — repair, but it runs too late
UPDATE note SET folder_id = '' WHERE folder_id IS NULL;

Too late: the crash is in 4.sqm itself, so a device on schema 3 never reaches 8. A repair has to precede the broken step, and nothing can precede an already-shipped migration.

The third fix was to detect the old version and recreate the database:

if (currentVersion < 4) { driver.execute("DROP TABLE note"); Schema.create(driver) }

Which works and discards the user's notes. For a cache that is correct; for user-authored content it is data loss, and 2,118 users had notes they had not synced.

The actual mechanism#

A migration set is a set of paths, not a sequence, and a test that walks one path tests one path.

The device decides the starting point. SQLDelight's driver reads the stored user_version, then applies every migration from there to the target. A device on 3 runs 4.sqm, 5.sqm, 6.sqm, 7.sqm; a device on 6 runs only 7.sqm. With seven versions there are six distinct paths, and they share steps but not starting states.

Schema.create() does not exercise any of them. It emits the newest schema directly — the .db file SQLDelight generates from the current .sq files. So a test that creates and then migrates 6 → 7 is testing one step against a state no device was ever in: the newest schema minus the newest migration, which is not the same thing as schema 6 as produced by migrations 1–6.

And an empty table hides constraint failures. This is the part that let 4.sqm survive four releases. ALTER TABLE ADD COLUMN x NOT NULL with no default is rejected only if rows exist to backfill; on an empty table SQLite accepts it. So the migration was correct for every device that ran it while the table was empty and wrong for every device that ran it later with data.

Which means a migration's correctness depends on three things the usual test supplies none of:

  the starting VERSION       the test creates the newest instead
  the starting SCHEMA        as produced by the earlier migrations, not by create()
  the DATA present           an empty table cannot violate a constraint

And the version distribution is the risk multiplier. We had assumed a long tail; it was 11% in one bucket, because adoption is driven by store campaigns and auto-update settings rather than by a smooth decay.

The fix#

Replay every path from every shipped version, with seeded data, generated from the migration files so a new version cannot be forgotten.

// commonTest/kotlin/db/MigrationReplayTest.kt
//
// Walks EVERY shipped version to the current one, on a database built by the earlier migrations
// rather than by Schema.create() — because create() emits the newest schema and so tests a
// state no device has been in.
class MigrationReplayTest {

    /**
     * Derived from the generated schema files rather than hand-listed. A hand-listed range is a
     * list somebody must remember to extend, and the version that gets forgotten is the newest
     * one — which is the one being added in the commit that forgets it.
     */
    private val shippedVersions: List<Long> =
        (1..AppDatabase.Schema.version).map { it.toLong() }

    @Test
    fun everyShippedVersionReachesCurrentWithDataPresent() {
        shippedVersions.dropLast(1).forEach { from ->
            val driver = inMemoryDriver()

            // Load the COMMITTED SNAPSHOT for this version — databases/{from}.db, which
            // SQLDelight generates and we check in. There is no create(version = n): create()
            // emits today's schema and nothing else, which is the whole reason the original
            // test proved nothing. The snapshot is the only honest starting state, because it
            // is a byte copy of what that release actually produced.
            loadSchemaSnapshot(driver, version = from)   // databases/$from.db

            // SEED. An empty table cannot violate a NOT NULL, which is exactly why 4.sqm
            // survived four releases — every device that ran it early had no notes yet.
            seedRealisticData(driver, atVersion = from)

            // The actual claim: a device on `from` reaches current without throwing.
            AppDatabase.Schema.migrate(driver, from, AppDatabase.Schema.version.toLong())

            // And the data survived, which a migration that drops and recreates would fail.
            val notes = driver.executeQuery(null, "SELECT COUNT(*) FROM note", { it.getLong(0) }, 0)
            assertEquals(SEED_ROWS, notes.value,
                "migrating $from → ${AppDatabase.Schema.version} lost rows")
        }
    }

    /**
     * Seeds whatever columns exist AT THAT VERSION, so the seed does not reference a column a
     * later migration adds. Reading the columns from the database rather than from a per-version
     * fixture keeps this honest when a migration renames something.
     */
    private fun seedRealisticData(driver: SqlDriver, atVersion: Long) {
        val cols = driver.executeQuery(null, "PRAGMA table_info(note)", { c ->
            buildList { while (c.next().value) add(c.getString(1)!!) }
        }, 0).value

        repeat(SEED_ROWS.toInt()) { i ->
            val values = cols.associateWith { col -> seedValueFor(col, i) }
            driver.execute(null,
                "INSERT INTO note (${values.keys.joinToString()}) " +
                "VALUES (${values.keys.joinToString { "?" }})", values.size) {
                values.values.forEachIndexed { idx, v -> bindString(idx, v) }
            }
        }
    }

    private companion object { const val SEED_ROWS = 25L }
}

And the repair, placed where a device on 3 actually reaches it — which required accepting that 4.sqm cannot be fixed and working around it from the step before:

-- 4.sqm CANNOT be edited: devices have already run the original, and two populations both
-- reporting schema 4 with different constraints would make 5.sqm onward ambiguous.
--
-- So the next idea was to APPEND the backfill to 3.sqm. Appending is safe in a way editing is
-- not: a device past 3 has already run 3.sqm and will never re-run it, so no existing device
-- sees a changed file.
-- src/commonMain/sqldelight/migrations/3.sqm (appended, never edited)
UPDATE note SET folder_id = '' WHERE folder_id IS NULL;

And that is exactly why it does not work. The devices that crash are the ones sitting at version 3 — they have already run 3.sqm, so they will never execute the appended line, and the next thing they run is the broken 4.sqm. Appending to 3.sqm helps only devices at 1 or 2, which is 0.0% of the affected cohort. There is no .sqm position between 3 and 4, and SQLDelight has no fractional versions. Whatever runs before 4.sqm has to run outside the migration chain:

// commonMain/kotlin/db/Migrations.kt
//
// 4.sqm is unfixable in place, so the driver applies a GUARD before the migration chain for
// any device below 4. This is a code-level step rather than a .sqm, because it has to run
// before a migration we cannot precede.
fun AppDatabase.Companion.migrateWithGuard(driver: SqlDriver, from: Long, to: Long) {
    if (from < 4) {
        // Add the column ourselves, WITH a default, then mark 4.sqm's work done by bumping
        // to 4. The column ends up nullable-with-default rather than NOT NULL — a different
        // schema from devices that ran the original 4.sqm on an empty table, and that
        // difference is recorded in SCHEMA_VARIANTS below rather than pretended away.
        driver.execute(null, "ALTER TABLE note ADD COLUMN folder_id TEXT DEFAULT ''", 0)
        driver.execute(null, "PRAGMA user_version = 4", 0)
        return migrateWithGuard(driver, 4, to)
    }
    Schema.migrate(driver, from, to)
}

And the output it produced:

$ ./gradlew :core:database:allTests

everyShippedVersionReachesCurrentWithDataPresent
  1 → 7  PASSED      2 → 7  PASSED      3 → 7  PASSED
  4 → 7  PASSED      5 → 7  PASSED      6 → 7  PASSED
  × 5 targets

$ # and the same test against the UNPATCHED migration chain
  3 → 7  FAILED: Cannot add a NOT NULL column with default value NULL (4.sqm:1)
  2 → 7  FAILED: same
  1 → 7  FAILED: same

Three of six paths were broken and the suite had been green because it tested none of them.

How we proved it#

That the seed was the load-bearing half — because the path replay alone still passes:

  test shape                                        3 → 7
  Schema.create(7) then migrate(6,7)                PASSED   ← the original test
  migrate(1 → 3), then migrate(3 → 7), no seed      PASSED   ← path replay, still green
  migrate(1 → 3), seed 25 rows, migrate(3 → 7)      FAILED   ← the real defect

Replaying the path was necessary and not sufficient. An empty table cannot violate a constraint, so a path test without data reproduces the exact blind spot it was written to remove.

That the version distribution was not a long tail. The number that turned a theoretical risk into 11%:

  schema version at upgrade time, 19,400 installs
    6     16,296   84%
    5        776    4%
    4        194    1%
    3      2,134   11%     ← a single bucket, fourteen months old

A store campaign and auto-update-off users produced a spike, not a decay. Any assumption that old versions are rare is an assumption about adoption mechanics.

That Schema.create() and the migration chain produce different schemas. Verified by comparing, because the test's whole validity rests on it:

$ # schema 4, two ways
  create(1) then migrate(1→4)    note.folder_id   TEXT NOT NULL
  create(4) directly             note.folder_id   TEXT NOT NULL DEFAULT ''

They differ, because the current .sq file carries the default that 4.sqm lacked. So a test that creates version 4 is testing a schema no device running migrations has.

That editing 4.sqm creates two populations. Reasoned and then checked against real devices, because it is the fix everyone proposes first:

  device history                      folder_id constraint   reports user_version
  ran the original 4.sqm (empty tbl)   NOT NULL                        4
  ran an amended 4.sqm                 NOT NULL DEFAULT ''             4
  ran our code-level guard             NULL DEFAULT ''                 4

Two of those three are real — the amended 4.sqm was never released, which is the only reason there are two and not three. Both live in the field reporting schema 4, so we record them explicitly:

// A device's schema is not determined by its version number alone, which is uncomfortable
// and true. Any migration that depends on folder_id's nullability must handle all three.
val SCHEMA_VARIANTS = listOf("4-original-notnull", "4-guarded-nullable")

That the guard preserves data. The thing the drop-and-recreate option gave up:

  approach                      3 → 7   notes preserved
  DROP TABLE + Schema.create    passes   0 of 25
  the code-level guard          passes   25 of 25

What I cannot tell you is whether all 2,118 affected users recovered their notes. The crash happened during migration, so the app never opened; the database was left at version 3 with its rows intact, and the guarded release migrated them successfully on next launch. Our telemetry shows 2,041 of them reaching version 7 within a week. The remaining 77 have not returned, and I do not know whether they uninstalled or simply have not opened the app.

And an honest consequence. We now carry a schema variant that the version number does not describe, permanently. Every future migration touching folder_id has to handle both constraint forms, and the only way out is a full table rebuild on a future version — which we have deferred rather than solved.

What this generalizes to#

A migration is not a step but a claim about every state that can precede it, and the number of such states is the number of versions you have shipped. A test that constructs the current state and applies the latest change is testing the path that needs testing least, because it is the path almost every device is already on. The sharper point is that an empty fixture cannot violate a constraint — so a schema test without realistic data verifies the shape and not the migration, and a migration is mostly about the data it has to carry forward.

---

Related#

• Room compiles for KMP and still needs a driver per target — the connection beneath the schema, and the per-target SQL feature floor.
• A migration with no rollback — the expand-contract sequence, and why both code versions must work against every step.

Keep reading#

One post a week on shipping mobile software. No digests, no course pitch. Subscribe.