Manual Transaction Wrapping vs. No Transaction: Which Guarantees Atomic Schema Migrations in expo-sqlite?

The Short Answer

To guarantee atomic schema migrations in expo-sqlite, you must use manual transaction wrapping. Without an explicit transaction, SQLite operates in autocommit mode, meaning every successful statement is committed immediately. If a migration script fails halfway through without a transaction, the database is left in a "half-migrated" state, which violates atomicity.

Technical Explanation

Because expo-sqlite provides a thin wrapper over the native SQLite API, it does not provide an implicit migration context. In standard SQLite behavior, Data Definition Language (DDL) statements—such as CREATE TABLE and ALTER TABLE—are transactional. This means they can be rolled back if they are wrapped within a BEGIN TRANSACTION and ROLLBACK block.

If you omit the transaction wrapper, each migration step is its own atomic unit. A failure at step three of five will leave steps one and two persisted in the database, making it difficult to recover or retry the migration without manual cleanup or complex idempotency logic.

Implementation Pattern

To ensure a migration either completes fully or leaves the database unchanged, implement the following pattern:

try {
  await db.execAsync('BEGIN TRANSACTION');
  
  // Perform migration steps
  await db.execAsync('ALTER TABLE users ADD COLUMN age INTEGER');
  await db.execAsync('CREATE TABLE profiles (id INTEGER PRIMARY KEY, bio TEXT)');
  
  await db.execAsync('COMMIT');
} catch (error) {
  await db.execAsync('ROLLBACK');
  throw error; // Re-throw to handle the migration failure at the app level
}

Verification Steps

To verify that your migration logic is truly atomic, perform the following test:

1. Initiate a migration containing two distinct schema changes (e.g., creating two different tables).
2. Intentionally introduce a syntax error or a constraint violation immediately after the first successful statement.
3. Query the sqlite_master table. If the transaction worked, the first table should not exist in the database.

Assumptions and Constraints

• Version Assumption: This assumes a modern version of expo-sqlite using execAsync or similar asynchronous execution methods.
• Platform Consistency: While SQLite behavior is generally consistent, transactional DDL support is a feature of the SQLite engine itself; ensure you are not using a highly customized native build that disables this.
• Locking Risk: Failure to call either COMMIT or ROLLBACK in the catch block will leave the database locked, preventing subsequent write operations.

Missing Diagnostic: Are you using a specific migration library (like drizzle-orm or typeorm) on top of expo-sqlite, or are you writing raw SQL strings?