skip to content

In an Expo app using expo-sqlite, how do you migrate the on-device schema when version 2 of a journaling app adds a mood column?

level: middleimportance: must knowfreq 55%

answer

  1. the device file keeps the old schema
  2. an integer in the database header
  3. starts at 0
  4. ladder of if-below-N steps
  5. bump in the same transaction

basics

~20 s

Keep a schema number in PRAGMA user_version. On startup read it and run every numbered step above it in order; for version 2 that is ALTER TABLE entries ADD COLUMN mood. Then set user_version to 2, inside the same transaction.

solid answer

~40 s

An app update ships new JavaScript, but the database file on the device still has the version 1 schema; nothing migrates it for you. SQLite keeps a free integer, `PRAGMA user_version`, in the file header; it reads 0 until you set it. On startup, before any screen queries, read it and run a ladder: if below 1, create the tables; if below 2, `ALTER TABLE entries ADD COLUMN mood TEXT`. Finish with `PRAGMA user_version = 2`. Run the steps and the bump in one `withTransactionAsync`, so an interrupted upgrade leaves version 1 intact and simply reruns. The Expo docs put this function in `SQLiteProvider`'s `onInit`. Never edit a shipped step; append a new one, because devices out there are at every older version.

code

typescript · 28 lines
typescript
import type { SQLiteDatabase } from 'expo-sqlite';

const DATABASE_VERSION = 2;

export async function migrateDbIfNeeded(db: SQLiteDatabase): Promise<void> {
  const row = await db.getFirstAsync<{ user_version: number }>('PRAGMA user_version');
  let currentVersion = row?.user_version ?? 0;
  if (currentVersion >= DATABASE_VERSION) {
    return;
  }
  if (currentVersion === 0) {
    // Journal mode cannot switch to WAL inside a transaction.
    await db.execAsync('PRAGMA journal_mode = WAL');
  }
  await db.withTransactionAsync(async () => {
    if (currentVersion === 0) {
      await db.execAsync(
        'CREATE TABLE entries (id INTEGER PRIMARY KEY NOT NULL, body TEXT NOT NULL, created_at INTEGER NOT NULL)'
      );
      currentVersion = 1;
    }
    if (currentVersion === 1) {
      await db.execAsync('ALTER TABLE entries ADD COLUMN mood TEXT');
      currentVersion = 2;
    }
    await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`);
  });
}

go deeper

for a junior

Recall that the database file survives an app update, and that PRAGMA user_version starts at 0 and is the app's own schema number.

for a middle

Walk through the ladder: read user_version, run each step above it in order, bump it, all in one transaction run from onInit before any query.

for a senior

Show the long-term rules: append-only steps, additive changes where possible, WAL set outside the transaction, and a policy for a file newer than the build.

for a principal

Weigh hand-written ladders against an ORM's generated migrations, and how schema changes are reviewed so a released step is never edited.

## Why an app update does not change the database A React Native app's code is replaced on update, but its **data directory is not**. The `journal.db` file that `expo-sqlite` created under version 1 of the app is still there, with version 1's `entries` table: `id`, `body`, `created_at`. Version 2's code expects a `mood` column. If the new code queries `mood` before anything changes the file, it fails with a "no such column" error. SQLite never migrates a schema by itself, and expo-sqlite has no built-in migration runner; the app must do it on every launch before any other query. ## The user_version pragma SQLite reserves a 32-bit integer in every database file's header for the application's own use, read and written with `PRAGMA user_version`. Key properties: - It reads **0** on a new file or one nobody has versioned. - SQLite never changes it by itself; it is not the app version or the SQLite version. - Writing it inside a transaction is part of that transaction, so a rollback restores the old number. - It lives in the file, so it travels with a backup or a prebuilt copy of the database. That makes it a natural "schema version" stamp. The expo-sqlite docs use exactly this pattern in their `migrateDbIfNeeded` example, which is passed to `SQLiteProvider` as its `onInit` callback so it runs before any child component renders. ## The migration ladder Write migrations as an ordered ladder of steps, each moving the file from version N-1 to N: 1. Read the current version: `getFirstAsync<{ user_version: number }>('PRAGMA user_version')`. 2. If it is already at the target, return. 3. For each step above the current version, in order, run its statements. 4. Set `PRAGMA user_version` to the target. For the journaling app: | Version | Step | A device at this version runs | |---|---|---| | 0 | none, empty file | create `entries` (step 1), add `mood` (step 2) | | 1 | `CREATE TABLE entries (...)` | add `mood` only | | 2 | `ALTER TABLE entries ADD COLUMN mood TEXT` | nothing | A fresh install walks the whole ladder, so fresh and upgraded devices end with the same schema. Some teams instead create the latest schema directly on a fresh install and set the version in one go; that works too, but then you maintain two definitions of the schema that must agree. ## Making the upgrade atomic Run the steps and the version bump inside one `db.withTransactionAsync(...)`. Inside `onInit` nothing else is querying the database yet, so the non-exclusive variant is safe. SQLite's `ALTER TABLE ... ADD COLUMN` and the `user_version` write are both transactional, so: - if the app is killed or a statement throws, `ROLLBACK` restores version 1 and its schema, and the next launch retries cleanly; - the schema can never say version 2 while `mood` is missing, or the reverse. One statement stays out: `PRAGMA journal_mode = WAL`, which the docs recommend setting when a database is created. SQLite refuses to switch into WAL while a transaction is open, so set it before the transaction starts. It persists in the file, so doing it once is enough. ## Where the migration must run The migration has to finish **before anything else queries the database**. With `SQLiteProvider`, passing it as `onInit` guarantees that, because the provider awaits `onInit` before rendering its children. Without the provider, call it immediately after `openDatabaseAsync` and only hand the database object to the rest of the app once it resolves. A screen that queries during the migration can read a half-changed schema, or, with the plain transaction, even run inside the migration's transaction. ## Rules that keep the ladder safe over years - **Append, never edit.** Devices exist at every version you ever shipped. Changing step 1 after release means some devices ran the old step 1 and some the new one. - **Additive changes are the easy case.** Adding a nullable column or a table is one statement. SQLite's `ALTER TABLE` is limited; changing a column's type or adding constraints needs SQLite's create-copy-swap table rebuild, which is SQL, not an expo-sqlite feature. - **Never drop and recreate a table with user data** to "apply" a new schema. For a journal, that deletes the user's entries. - **Guard against a newer file.** If `user_version` is higher than the build knows, an older build is opening a newer database; do not run anything backwards. - **ORMs follow the same idea.** The expo-sqlite docs point to Drizzle ORM, whose `drizzle-kit` companion generates SQL migration files; the stamp-and-ladder principle is the same.

  • Why store the schema version in PRAGMA user_version rather than in a migrations table?
    It needs no table of its own, costs one read, reads 0 on an unversioned file and is transactional, so bumping it commits with the schema change. A migrations table works too and can record timestamps, but for one app-owned file the header integer is the simpler stamp.
  • What should happen when version 1 of the app opens a database already at user_version 2?
    Nothing should run backwards. That happens when a user reinstalls an older build or an older JavaScript bundle is served again. Additive changes like a nullable `mood` column let old code keep working; for incompatible ones, fail with a clear message rather than corrupting data.
  • Why can't the version 2 step add mood as NOT NULL without a default?
    Existing rows would have no value for it, and SQLite's `ADD COLUMN` rejects a NOT NULL column without a non-null default. Add it nullable or with a default, then backfill in the same step if needed.

It works like a renovation logbook pinned inside a house: the builder reads the number of the last job finished and does only the later jobs, writing each number in the book at the moment that job is complete. Skip writing the number, and the next builder redoes a job that is already done.

saying these in an interview costs you the question

  • An app update migrates the on-device SQLite schema automatically
  • expo-sqlite sets PRAGMA user_version to the app's version number
  • Fix the schema by editing the shipped version 1 CREATE TABLE statement
  • Dropping and recreating the entries table is a fine way to add a column
  • Bump user_version first, then run the migration statements outside a transaction