React Native offline: SQLite migrations without pain

• I ship schema changes in an offline-first Expo app.
• I run migrations inside a single SQLite transaction.
• I use PRAGMA user_version as my only “migration table”.
• I added a tiny verifier to catch broken migrations fast.

Context

I’m building a React Native fitness app. Expo. SQLite. Offline-first.

Workouts must log without internet. Always.

Then the real problem hits. Schema changes.

I started with “I’ll just add a column”. Brutal.
One bad ALTER in production and you’ve got users stuck on an old schema, with data you can’t read, and no easy way to recover.

I tried keeping migrations in my head. That lasted 2 days.
Then I spent 4 hours debugging “no such column: sets.rpe”. Most of it was wrong.

What finally worked: PRAGMA user_version + transactional migrations + a startup verifier.
No ORM. No magic.

1) I treat migrations like app code. Versioned.

I don’t keep a migrations table.
I keep one number.

SQLite already has it: PRAGMA user_version.

So my app has a single source of truth:

• current schema version in code
• current schema version in the database
• a deterministic path forward

Here’s the core helper I run at app startup.

// db/migrate.ts
import * as SQLite from 'expo-sqlite';

export const SCHEMA_VERSION = 4;

export async function getUserVersion(db: SQLite.SQLiteDatabase) {
  const row = await db.getFirstAsync<{ user_version: number }>(
    'PRAGMA user_version;'
  );
  return row?.user_version ?? 0;
}

export async function setUserVersion(db: SQLite.SQLiteDatabase, v: number) {
  // PRAGMA doesn't support bindings here.
  await db.execAsync(`PRAGMA user_version = ${v};`);
}

export async function migrateIfNeeded(db: SQLite.SQLiteDatabase) {
  const from = await getUserVersion(db);
  if (from === SCHEMA_VERSION) return;
  if (from > SCHEMA_VERSION) throw new Error(`DB version ${from} > app ${SCHEMA_VERSION}`);

  await db.execAsync('BEGIN;');
  try {
    for (let v = from + 1; v <= SCHEMA_VERSION; v++) {
      await runMigration(db, v);
      await setUserVersion(db, v);
    }
    await db.execAsync('COMMIT;');
  } catch (e) {
    await db.execAsync('ROLLBACK;');
    throw e;
  }
}

async function runMigration(db: SQLite.SQLiteDatabase, toVersion: number) {
  switch (toVersion) {
    case 1:
      await db.execAsync(`
        CREATE TABLE IF NOT EXISTS workouts (
          id TEXT PRIMARY KEY NOT NULL,
          started_at INTEGER NOT NULL,
          ended_at INTEGER
        );
      `);
      return;
    case 2:
      await db.execAsync(`
        CREATE TABLE IF NOT EXISTS sets (
          id TEXT PRIMARY KEY NOT NULL,
          workout_id TEXT NOT NULL,
          exercise_id TEXT NOT NULL,
          reps INTEGER NOT NULL,
          weight_kg REAL NOT NULL,
          created_at INTEGER NOT NULL
        );
      `);
      await db.execAsync('CREATE INDEX IF NOT EXISTS idx_sets_workout ON sets(workout_id);');
      return;
    case 3:
      // Add RPE. Nullable first. Backfill later.
      await db.execAsync('ALTER TABLE sets ADD COLUMN rpe INTEGER;');
      return;
    case 4:
      // New index for my “last set per exercise” screen.
      await db.execAsync('CREATE INDEX IF NOT EXISTS idx_sets_exercise_created ON sets(exercise_id, created_at);');
      return;
    default:
      throw new Error(`Missing migration for version ${toVersion}`);
  }
}

One thing that bit me — ALTER TABLE ... ADD COLUMN is forgiving, but not free.
If you later want NOT NULL or a default, you’re in table-rebuild territory.
So I add columns nullable first. Then I backfill. Then I enforce later (if I still care).

2) I don’t trust my migrations. I verify them.

SQLite will happily let you ship a schema that “kinda works”.
Until one query hits a missing index or column.

So I verify after migrating.
Not with a test suite. On-device.
A tiny check. Runs in dev and prod.

This caught a real bug: I renamed weight to weight_kg in code, but never migrated old installs.
My UI was fine on fresh installs. Broken on my phone.

// db/verify.ts
import * as SQLite from 'expo-sqlite';
import { SCHEMA_VERSION, getUserVersion } from './migrate';

type Col = { name: string };

export async function verifySchema(db: SQLite.SQLiteDatabase) {
  const v = await getUserVersion(db);
  if (v !== SCHEMA_VERSION) throw new Error(`verifySchema: expected v${SCHEMA_VERSION}, got v${v}`);

  const cols = await db.getAllAsync('PRAGMA table_info(sets);');
  const names = new Set(cols.map(c => c.name));

  // Guard the query paths my app uses every session.
  for (const required of ['id', 'workout_id', 'exercise_id', 'reps', 'weight_kg', 'created_at', 'rpe']) {
    if (!names.has(required)) {
      throw new Error(`verifySchema: missing sets.${required}`);
    }
  }

  // Also verify my hot index exists.
  const idx = await db.getAllAsync<{ name: string }>("PRAGMA index_list('sets');");
  const idxNames = new Set(idx.map(i => i.name));
  if (!idxNames.has('idx_sets_exercise_created')) {
    throw new Error('verifySchema: missing idx_sets_exercise_created');
  }
}

Yes, it’s a little paranoid.
But “no such column” in production is worse.

If verification throws, I show a blocking screen with a support code.
Because letting the app limp forward corrupts data.

3) I learned table rebuilds the hard way

At some point you’ll want:

• NOT NULL
• real defaults
• dropping columns
• changing types

SQLite doesn’t support most of that directly.
So you rebuild the table.

I messed this up once and duplicated rows.
I forgot to copy the primary key constraint.
Spent an evening staring at “UNIQUE constraint failed: sets.id”.

This is the rebuild pattern I use now.
It’s boring. That’s the point.

// db/migrations/rebuildSets.ts
import * as SQLite from 'expo-sqlite';

export async function rebuildSetsWithDefaults(db: SQLite.SQLiteDatabase) {
  // 1) New table with the final shape.
  await db.execAsync(`
    CREATE TABLE sets_new (
      id TEXT PRIMARY KEY NOT NULL,
      workout_id TEXT NOT NULL,
      exercise_id TEXT NOT NULL,
      reps INTEGER NOT NULL,
      weight_kg REAL NOT NULL,
      rpe INTEGER NOT NULL DEFAULT 0,
      created_at INTEGER NOT NULL
    );
  `);

  // 2) Copy data over. COALESCE handles old NULL rpe.
  await db.execAsync(`
    INSERT INTO sets_new (id, workout_id, exercise_id, reps, weight_kg, rpe, created_at)
    SELECT id, workout_id, exercise_id, reps, weight_kg, COALESCE(rpe, 0), created_at
    FROM sets;
  `);

  // 3) Swap.
  await db.execAsync('DROP TABLE sets;');
  await db.execAsync('ALTER TABLE sets_new RENAME TO sets;');

  // 4) Recreate indexes.
  await db.execAsync('CREATE INDEX IF NOT EXISTS idx_sets_workout ON sets(workout_id);');
  await db.execAsync('CREATE INDEX IF NOT EXISTS idx_sets_exercise_created ON sets(exercise_id, created_at);');
}

I only do this inside the migration transaction.
If anything fails, rollback leaves the old table untouched.

And yeah — on huge tables this can be slow.
For my app it’s fine.
My largest real device had 3,482 set rows when I tested it.

4) I wire it into Expo startup. No race conditions.

My first attempt ran migrations after the UI mounted.
Bad move.

The app rendered screens that queried the DB while migrations were running.
I got:

• SQLiteException: no such table: sets
• and once, a silent empty list because my query failed and I swallowed the error

Now I block rendering until:

• DB opens
• migrations run
• verification passes

// App.tsx
import React from 'react';
import * as SQLite from 'expo-sqlite';
import { migrateIfNeeded } from './db/migrate';
import { verifySchema } from './db/verify';

export default function App() {
  const [ready, setReady] = React.useState(false);
  const [err, setErr] = React.useState(null);

  React.useEffect(() => {
    let cancelled = false;
    (async () => {
      try {
        const db = await SQLite.openDatabaseAsync('gym.db');
        await migrateIfNeeded(db);
        await verifySchema(db);
        if (!cancelled) setReady(true);
      } catch (e: any) {
        if (!cancelled) setErr(e?.message ?? String(e));
      }
    })();
    return () => {
      cancelled = true;
    };
  }, []);

  if (err) return DB init failed: {err};
  if (!ready) return Opening database…;

  return ;
}

function Text(props: any) {
  return <>{/* replace with RN Text in real app */}{props.children};
}

function MainApp() {
  return App ready;
}

In my real project I pass db via context.
But the point stands.
No UI until the DB is safe.

Results

I shipped 4 schema versions across 3 physical devices (Pixel 7, iPhone 13, a cheap Android 11 burner).

Before this setup, upgrading from my old build failed on 2 of those 3 devices with no such column: sets.rpe.
After switching to user_version + transactional migrations + verification, I did 27 cold starts in a row without a single migration-related crash.

The biggest win wasn’t performance.
It was confidence.
I can add a column at midnight and not fear the next morning.

Key takeaways

• Use PRAGMA user_version for schema state. One number. No drama.
• Wrap every migration in BEGIN/COMMIT. Always rollback on error.
• Add columns nullable first. Rebuild later if you need constraints.
• Verify the schema after migrating with PRAGMA table_info and PRAGMA index_list.
• Block UI until migrations + verification finish, or you’ll chase race-condition ghosts.

Closing

I’m still deciding how aggressive to be on verification failures.
Right now I hard-block the app and show an error.
No “continue anyway” button.

If you ship offline-first SQLite apps, do you ever auto-reset the local DB on schema mismatch, or do you always force a manual recovery path?