After an update, some users' Expo apps fail at startup because an expo-sqlite migration stopped halfway; how do you diagnose it and make migrations safe?

seniorshould knowfreq 30%

answer

rerun meets a half-applied schema
multi-statement execAsync is not atomic
step plus bump, one commit
idempotent where a transaction cannot help
fixture file per shipped version

basics

~20 s

Statements that ran without a transaction, or without bumping user_version in the same commit, leave a schema the rerun cannot handle. Wrap each step with its version bump in one transaction, keep untransactable work idempotent, and test from every shipped version.

solid answer

~40 s

The startup error usually names the symptom: duplicate column name or table already exists means a step's work committed but user_version did not move; no such column means the version moved without the work. Reproduce with a copy of an affected file: read PRAGMA user_version and PRAGMA table_info(...). Typical causes are a multi-statement execAsync outside a transaction, where each statement autocommits until the failing one; a catch that swallows an error so the rest continues; and a step that assumes clean data. The fix: each step and its PRAGMA user_version write in one withTransactionAsync, guards such as checking table_info before ADD COLUMN, journal_mode outside the transaction, and tests that open fixture databases from every shipped version. Repair already-broken devices with a new, idempotent step, not a wipe.

The startup error usually names the symptom: `duplicate column name` or `table already exists` means a step's work committed but `user_version` did not move; `no such column` means the version moved without the work. Reproduce with a copy of an affected file: read `PRAGMA user_version` and `PRAGMA table_info(...)`. Typical causes are a multi-statement `execAsync` outside a transaction, where each statement autocommits until the failing one; a `catch` that swallows an error so the rest continues; and a step that assumes clean data. The fix: each step and its `PRAGMA user_version` write in one `withTransactionAsync`, guards such as checking `table_info` before `ADD COLUMN`, `journal_mode` outside the transaction, and tests that open fixture databases from every shipped version. Repair already-broken devices with a new, idempotent step, not a wipe.

code

typescript · 32 lines

typescript

import type { SQLiteDatabase } from 'expo-sqlite';

type Step = (db: SQLiteDatabase) => Promise<void>;

// STEPS[i] migrates version i to version i + 1. Append only.
const STEPS: Step[] = [
  async (db) => {
    await db.execAsync(
      'CREATE TABLE IF NOT EXISTS entries (id INTEGER PRIMARY KEY NOT NULL, body TEXT NOT NULL, created_at INTEGER NOT NULL)'
    );
  },
  async (db) => {
    const columns = await db.getAllAsync<{ name: string }>('PRAGMA table_info(entries)');
    if (!columns.some((c) => c.name === 'mood')) {
      await db.execAsync('ALTER TABLE entries ADD COLUMN mood TEXT');
    }
  },
];

export async function migrate(db: SQLiteDatabase): Promise<void> {
  const row = await db.getFirstAsync<{ user_version: number }>('PRAGMA user_version');
  const current = row?.user_version ?? 0;
  if (current > STEPS.length) {
    throw new Error(`Database schema ${current} is newer than this build (${STEPS.length})`);
  }
  for (let version = current; version < STEPS.length; version++) {
    await db.withTransactionAsync(async () => {
      await STEPS[version](db);
      await db.execAsync(`PRAGMA user_version = ${version + 1}`);
    });
  }
}

go deeper

for a junior

Recall that a migration's statements and its user_version bump must commit together, or the next launch reruns work that already happened.

for a middle

Explain why a multi-statement execAsync outside a transaction leaves partial changes, and read the startup error to tell which half committed.

for a senior

Diagnose from a real file with user_version and table_info, ship an idempotent repair step for stuck devices, and test upgrades from every released schema.

for a principal

Set the team's migration policy: append-only steps, additive-first schema changes, fixture files per release, and what the app does when a file is newer than the build.

What "halfway" means for an on-device database

A migration that stops halfway is one where some of its changes are durable and some are not, and the version stamp (PRAGMA user_version) does not match what is actually in the file. On the next launch the migration code reads the stamp, decides which steps to run, and meets a schema it did not expect. The step fails again, and because migrations run before anything renders (typically in SQLiteProvider's onInit), the app fails at startup every time. The user cannot fix it; only a new release can.

Diagnosing it

Start from the error the migration throws, then look at a real affected file.

Error at startup	What it usually means
`duplicate column name: mood`	the step's `ALTER TABLE` committed, but `user_version` was not bumped
`table entries_new already exists`	a table-rebuild step died between creating the copy and swapping it in
`no such column` / `no such table`	`user_version` was bumped before, or without, the work it describes
`NOT NULL constraint failed`	the step assumed data that some users do not have

To confirm, get a copy of an affected database (from a test device reproducing the upgrade, or a support export) and inspect it:

PRAGMA user_version for the stamp;
PRAGMA table_info(entries) for the actual columns;
SELECT name FROM sqlite_master for leftover tables.

The SQLiteProvider onError prop, or an error boundary when using useSuspense, is where the app sees the failure and can report the stamp and error message.

The usual causes

Several statements in one execAsync outside a transaction. execAsync runs the statements in turn; in autocommit mode each one commits as it finishes. If the third fails, the first two are permanent.
A version bump that is not in the same commit as the work, either written first or written later in a separate call.
A swallowed error. A try/catch around one statement that logs and continues lets the migration carry on, commit and bump the version over incomplete work.
A step that assumes clean data, such as a backfill that works on the developer's data and fails on a user's.
Races. expo-sqlite's own key-value store hit one: its 57.0.2 changelog fixes no such table: storage thrown permanently when its sync and async APIs raced the first-run migration. The fix made the baseline CREATE TABLE IF NOT EXISTS idempotent so the next open repairs the file.

Making migrations safe

One transaction per step, bump included. Wrap each step and its PRAGMA user_version = N in withTransactionAsync. ALTER TABLE, table rebuilds and the user_version write are all transactional in SQLite, so a crash or throw rolls back to the previous version exactly.
Idempotent where a transaction cannot help. Some statements cannot run inside one: SQLite refuses to switch journal_mode into WAL during a transaction, so it runs before. Guard the rest defensively: check PRAGMA table_info before ADD COLUMN, use IF NOT EXISTS. That also lets a new release repair devices already stuck halfway.
Rethrow, never continue. A failed step must stop the migration; a partial upgrade that reports success is worse than a clear error.
Refuse to run backwards. If the stamp is higher than the build knows, an older build is opening a newer file; stop rather than guess.
Test from every shipped version. Keep a fixture database file per released schema version, open a copy, run the migration and assert the stamp and columns. Fresh installs are the easy path; upgrades from old versions are where failures live.
Keep user data. Deleting the database to "recover" erases a journal. For a destructive step, expo-sqlite's backupDatabaseAsync can copy the database to another one first.

Rolling out a risky schema change

A large change, such as splitting entries into two tables, is safer in stages:

Ship an additive step first (new table, new nullable column) and code that tolerates both shapes.
Watch the startup error reports from that release for migration failures, reported with the stamp and the message.
Only in a later release, ship the step that removes or rewrites the old shape.

Each release then has a small blast radius, and a failure affects one additive statement rather than a whole rebuild.

## What "halfway" means for an on-device database A migration that stops halfway is one where some of its changes are durable and some are not, and the version stamp (`PRAGMA user_version`) does not match what is actually in the file. On the next launch the migration code reads the stamp, decides which steps to run, and meets a schema it did not expect. The step fails again, and because migrations run before anything renders (typically in `SQLiteProvider`'s `onInit`), the app fails at startup every time. The user cannot fix it; only a new release can. ## Diagnosing it Start from the error the migration throws, then look at a real affected file. | Error at startup | What it usually means | |---|---| | `duplicate column name: mood` | the step's `ALTER TABLE` committed, but `user_version` was not bumped | | `table entries_new already exists` | a table-rebuild step died between creating the copy and swapping it in | | `no such column` / `no such table` | `user_version` was bumped before, or without, the work it describes | | `NOT NULL constraint failed` | the step assumed data that some users do not have | To confirm, get a copy of an affected database (from a test device reproducing the upgrade, or a support export) and inspect it: - `PRAGMA user_version` for the stamp; - `PRAGMA table_info(entries)` for the actual columns; - `SELECT name FROM sqlite_master` for leftover tables. The `SQLiteProvider` `onError` prop, or an error boundary when using `useSuspense`, is where the app sees the failure and can report the stamp and error message. ## The usual causes 1. **Several statements in one `execAsync` outside a transaction.** `execAsync` runs the statements in turn; in autocommit mode each one commits as it finishes. If the third fails, the first two are permanent. 2. **A version bump that is not in the same commit as the work**, either written first or written later in a separate call. 3. **A swallowed error.** A `try/catch` around one statement that logs and continues lets the migration carry on, commit and bump the version over incomplete work. 4. **A step that assumes clean data**, such as a backfill that works on the developer's data and fails on a user's. 5. **Races.** expo-sqlite's own key-value store hit one: its 57.0.2 changelog fixes `no such table: storage` thrown permanently when its sync and async APIs raced the first-run migration. The fix made the baseline `CREATE TABLE IF NOT EXISTS` idempotent so the next open repairs the file. ## Making migrations safe - **One transaction per step, bump included.** Wrap each step and its `PRAGMA user_version = N` in `withTransactionAsync`. `ALTER TABLE`, table rebuilds and the `user_version` write are all transactional in SQLite, so a crash or throw rolls back to the previous version exactly. - **Idempotent where a transaction cannot help.** Some statements cannot run inside one: SQLite refuses to switch `journal_mode` into WAL during a transaction, so it runs before. Guard the rest defensively: check `PRAGMA table_info` before `ADD COLUMN`, use `IF NOT EXISTS`. That also lets a new release repair devices already stuck halfway. - **Rethrow, never continue.** A failed step must stop the migration; a partial upgrade that reports success is worse than a clear error. - **Refuse to run backwards.** If the stamp is higher than the build knows, an older build is opening a newer file; stop rather than guess. - **Test from every shipped version.** Keep a fixture database file per released schema version, open a copy, run the migration and assert the stamp and columns. Fresh installs are the easy path; upgrades from old versions are where failures live. - **Keep user data.** Deleting the database to "recover" erases a journal. For a destructive step, expo-sqlite's `backupDatabaseAsync` can copy the database to another one first. ## Rolling out a risky schema change A large change, such as splitting `entries` into two tables, is safer in stages: 1. Ship an **additive** step first (new table, new nullable column) and code that tolerates both shapes. 2. Watch the startup error reports from that release for migration failures, reported with the stamp and the message. 3. Only in a later release, ship the step that removes or rewrites the old shape. Each release then has a small blast radius, and a failure affects one additive statement rather than a whole rebuild.

How would you test migrations before shipping a release?
Keep one fixture database file per released schema version, including realistic messy data. For each, open a copy on a device or simulator build, run the migration, and assert `user_version` and the columns from `PRAGMA table_info`. Test a fresh install too, but upgrades are where failures hide.
Users are already stuck with mood added but user_version still at 1; how does the next release fix them?
Ship the version 2 step again in a form that tolerates the column: check `PRAGMA table_info(entries)` and skip the `ALTER` when `mood` exists, then bump. The stuck devices pass the guard and move to 2, and fresh ones still add the column.

saying these in an interview costs you the question

A migration that throws leaves the file untouched even without a transaction
A multi-statement execAsync is atomic on its own
Deleting and recreating the user's database is a reasonable recovery path
Passing on a fresh install proves the migration is correct
An older build can safely migrate a file with a higher user_version

After an update, some users' Expo apps fail at startup because an expo-sqlite migration stopped halfway; how do you diagnose it and make migrations safe?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What "halfway" means for an on-device database

Diagnosing it

The usual causes

Making migrations safe

Rolling out a risky schema change

▾common follow-ups2 questions

▾red flags5 flags

What "halfway" means for an on-device database

Diagnosing it

The usual causes

Making migrations safe

Rolling out a risky schema change

answer

go deeper