SQLite schema migration in mobile app

Implementing Database Schema Migration (SQLite Migration) in Mobile Applications

Direct SQLite work without ORM layer — React Native, Flutter, Capacitor, or native Android/iOS with SQLiteOpenHelper. No automatic migration infrastructure here — only SQL and your hands. Main trap: SQLite is extremely limited in ALTER TABLE, and on Android API < 29 available operations are even fewer.

SQLiteOpenHelper (Android) — Basic Mechanics

class AppDatabase(context: Context) : SQLiteOpenHelper(context, "app.db", null, DB_VERSION) {

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE_TRANSACTIONS)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        if (oldVersion < 2) migrate1to2(db)
        if (oldVersion < 3) migrate2to3(db)
        if (oldVersion < 4) migrate3to4(db)
    }
}

This is a chain of if checks, not when or switch. User with version 1 sequentially goes through all migrations to current. Can't skip versions — only add new blocks.

Flutter: sqflite and drift

sqflite — most popular SQLite package for Flutter. Migrations via onUpgrade:

final db = await openDatabase(
  'app.db',
  version: 3,
  onCreate: (db, version) async {
    await db.execute('''CREATE TABLE transactions (
      id TEXT PRIMARY KEY,
      amount REAL NOT NULL,
      created_at INTEGER NOT NULL
    )''');
  },
  onUpgrade: (db, oldVersion, newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE transactions ADD COLUMN category TEXT DEFAULT ""');
    }
    if (oldVersion < 3) {
      // Table recreation for column rename
      await _recreateTransactionsTable(db);
    }
  },
);

drift (formerly moor) — typed ORM over SQLite for Flutter/Dart with declarative migrations. Generates code from schema, has Migrator with createTable, addColumn, renameColumn. For medium-sized projects — preferred over sqflite.

React Native: react-native-sqlite-storage / expo-sqlite

expo-sqlite with SQLite 3.39+:

const db = SQLite.openDatabase('app.db');

db.transaction(tx => {
  tx.executeSql('PRAGMA user_version', [], (_, result) => {
    const version = result.rows.item(0).user_version;
    if (version < 1) {
      tx.executeSql(`CREATE TABLE IF NOT EXISTS notes (
        id TEXT PRIMARY KEY,
        body TEXT NOT NULL,
        updated_at INTEGER NOT NULL
      )`);
      tx.executeSql('PRAGMA user_version = 1');
    }
    if (version < 2) {
      tx.executeSql('ALTER TABLE notes ADD COLUMN title TEXT DEFAULT ""');
      tx.executeSql('PRAGMA user_version = 2');
    }
  });
});

PRAGMA user_version — built-in SQLite mechanism for schema version storage.

Table Recreation: Universal Recipe

SQLite only supports ADD COLUMN from all ALTER TABLE operations (before 3.35.0). Rename column, delete it, change type — only through recreation:

BEGIN TRANSACTION;

CREATE TABLE transactions_new (
    id TEXT NOT NULL PRIMARY KEY,
    amount REAL NOT NULL,
    description TEXT NOT NULL DEFAULT '',  -- renamed from 'note'
    created_at INTEGER NOT NULL
);

INSERT INTO transactions_new (id, amount, description, created_at)
SELECT id, amount, note, created_at FROM transactions;

DROP TABLE transactions;
ALTER TABLE transactions_new RENAME TO transactions;

-- Restore indexes
CREATE INDEX idx_transactions_created_at ON transactions(created_at);

COMMIT;

Everything inside transaction — if something goes wrong, data not lost. Index restoration after RENAME — mandatory: they don't transfer automatically.

Foreign Keys During Recreation

If there are foreign keys — temporarily disable them during recreation:

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
-- ... table recreation ...
COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA integrity_check;

PRAGMA integrity_check after — ensure data is consistent.

Testing

SQLite migration testing without ORM — manual. Need test that:

1. Opens database version N (from prepared file or creates programmatically)
2. Inserts test data
3. Calls onUpgrade to version N+1
4. Checks structure and data via PRAGMA table_info

For Flutter/drift — SchemaVerifier from drift_dev package.

Work Scope

• Analyze current schema and version
• Write SQL migrations for all changes
• Transactional table recreation if needed
• Index and foreign key restoration
• Tests with real data

Timeline

Simple ADD COLUMN migrations: 0.5 day. Structural changes (table recreation, foreign keys, indexes) with tests: 1–2 days.