Unexpected persistence of PRAGMA journal_mode after transaction rollback in expo-sqlite

Answer

In expo‑sqlite the PRAGMA journal_mode statement is executed directly against the underlying SQLite engine. SQLite treats PRAGMA changes as part of the connection state, not as part of a transaction. Therefore a ROLLBACK only restores data rows and, in some cases, schema objects, but it never reverts a PRAGMA that was executed inside the same BEGIN/COMMIT block.

Confirmed behavior

• Running PRAGMA journal_mode=WAL inside a transaction and then issuing ROLLBACK keeps the database in WAL mode.
• Querying PRAGMA journal_mode; after the rollback will return wal and the -wal and -shm sidecar files will remain on disk.
• This is the same behavior in all Expo SDK releases that ship with SQLite 3.36+ (the version bundled with Expo SDK 49 and newer).

Impact on migration safety

Data integrity is preserved because the rollback undoes all DML/DDL changes. However, the operational mode of the database file is altered permanently, which can affect:

• concurrency: other processes that open the same file may start using WAL even if they expected DELETE mode.
• file size and cleanup: WAL files persist until a PRAGMA wal_checkpoint(TRUNCATE) or a new PRAGMA journal_mode=DELETE is issued.

Pattern to isolate non‑rollbackable pragmas

1. Set the required PRAGMA before starting the migration transaction. For example:

   await db.execAsync('PRAGMA journal_mode=WAL');
   await db.execAsync('BEGIN');
   // migration statements
   await db.execAsync('COMMIT');
   

   This guarantees the PRAGMA is applied regardless of transaction outcome.
2. Reset the PRAGMA after the migration (or in a separate transaction). If the migration must leave the database in its original mode, run:

   await db.execAsync('PRAGMA journal_mode=DELETE');
   

3. Avoid mixing PRAGMA changes with DDL/DML in the same BEGIN/COMMIT block. Keep them in separate statements outside the transaction, or wrap the entire migration in a SAVEPOINT that you explicitly release or rollback, but remember that PRAGMAs still persist.

Practical verification steps

1. Start a migration script that includes a PRAGMA change and intentionally throws an error after the PRAGMA.
2. After the rollback, run PRAGMA journal_mode; and check the mode.
3. Look for *.wal and *.shm files in the database directory.

Missing diagnostic detail

If you are using a very old Expo SDK (pre‑SDK 49) that bundles SQLite < 3.36, the exact behavior may differ slightly. Knowing the SDK version will help confirm whether the same pattern applies.