sql.js WASM Persistence: Memory, Exports & Nested Savepoints

After a successful SQL commit, the database file can remain unchanged. ChatCrystal operates on SQLite through sql.js in memory, then explicitly exports and saves it. This article checks the pinned initialization, saving, migration, and close paths, using an isolated temporary database to observe their boundaries.

WASM resources and database files are separate inputs

db/index.ts initializes sql.js when no instance exists. Development uses the package’s default WASM location. When ELECTRON_PACKAGED is set, locateFile points to sql-wasm.wasm under process.resourcesPath, paired with extraResources in electron-builder.yml. Existing database files are read entirely with readFileSync and passed to SQL.Database; otherwise a new database is created.

This reduces the database binding’s reliance on native addon compilation in this project, while still requiring delivery of WASM resources. WASM does not establish verification on every platform, a fixed performance penalty, or suitability for any database size. Loading, export, Buffer conversion, and writing can retain several copies of bytes. This article has no peak-memory or latency benchmark.

Initialization also requests journal_mode=WAL, enables foreign_keys, runs migrations and backfills, and saves. Issuing a WAL PRAGMA does not establish a native database-file/WAL/checkpoint protocol. Read the returned mode and check the library version and persistence path rather than treating virtual-filesystem behavior as a guarantee for concurrent writes to the application’s disk file.

Export and file writing have observable intermediate states

SaveDatabase uses exportDatabasePreservingForeignKeys to obtain a Uint8Array, converts it to Buffer, and calls writeFileSync on the database path. The wrapper restores foreign_keys=ON in finally: in this article’s pinned sql.js 1.14.2 fixture, a bare export changes that switch to 0. A reopened connection also needs its own settings; file bytes do not preserve every connection PRAGMA.

The current function overwrites the destination directly, without a temporary-file replacement, fsync, or coordination of multiple processes. A returning function and a durability guarantee after power loss are different acceptance claims. Stronger saving policies require separate design, fault injection, and verification before being described as existing mechanisms.

Observe savepoints and persistence in a temporary directory

Install npm install --ignore-scripts --save-exact sql.js@1.14.2 in an isolated directory, save lesson.mjs, and run with Node 24+. It reads and writes only its own temporary fixture.sqlite and cleans up afterward. It does not open ChatCrystal or a personal database. Savepoints are demonstrated with direct SQL, without calling the application’s withTransaction.

import assert from 'node:assert/strict';
import { mkdtempSync, readFileSync, writeFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import initSqlJs from 'sql.js';

const SQL = await initSqlJs();
const folder = mkdtempSync(join(tmpdir(), 'sql-persistence-lesson-'));
const file = join(folder, 'fixture.sqlite');
const db = new SQL.Database();
function exportPreservingForeignKeys(activeDb) {
  try {
    return activeDb.export();
  } finally {
    activeDb.run('PRAGMA foreign_keys = ON;');
  }
}
function save() {
  writeFileSync(file, exportPreservingForeignKeys(db));
}
function count(database, table) {
  return database.exec('SELECT count(*) FROM ' + table)[0].values[0][0];
}
function savedParents() {
  const copy = new SQL.Database(readFileSync(file));
  try {
    return count(copy, 'parent');
  } finally {
    copy.close();
  }
}
try {
  db.run(`PRAGMA foreign_keys = ON;
    CREATE TABLE parent(id INTEGER PRIMARY KEY);
    CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);
    INSERT INTO parent VALUES (1); INSERT INTO child VALUES (10, 1);`);
  db.export();
  assert.equal(db.exec('PRAGMA foreign_keys')[0].values[0][0], 0);
  save();
  assert.equal(db.exec('PRAGMA foreign_keys')[0].values[0][0], 1);
  db.run(`BEGIN; INSERT INTO parent VALUES (2); INSERT INTO child VALUES (20, 2);
    SAVEPOINT nested; INSERT INTO parent VALUES (3); INSERT INTO child VALUES (30, 3);
    ROLLBACK TO nested; RELEASE nested; COMMIT;`);
  assert.deepEqual(db.exec('SELECT id FROM parent ORDER BY id')[0].values, [[1], [2]]);
  assert.equal(savedParents(), 1);
  save();
  assert.equal(savedParents(), 2);
  db.run('DELETE FROM parent WHERE id = 2');
  assert.equal(count(db, 'child'), 1);
  console.log('savepoint, export, foreign keys, and file boundaries pass');
} finally {
  db.close();
  rmSync(folder, { recursive: true, force: true });
}

The sample checks the foreign-key switch after a bare export, then saves through the wrapper. Rolling back the inner SAVEPOINT preserves the outer work and leaves two parent rows. After committing the second row in memory, the old file still reopens with one; an explicit save makes two visible. Deleting the second parent cascades to its child.

Delete the wrapper’s foreign-key restoration and its switch assertion fails. Omit ROLLBACK TO and the parent-ID assertion fails. Replace save’s file write with a no-op and file reading or the persistence assertion fails. Restore each change to pass again. These checks do not simulate process crashes, corrupted disks, or multiple writers.

The actual transaction wrapper covers synchronous SQL callbacks

transaction.ts tracks nesting per Database in a WeakMap. It uses BEGIN/COMMIT/ROLLBACK at the top level and SAVEPOINT/RELEASE/ROLLBACK TO inside, restoring depth on success and failure. Parsing does not automatically become part of the SQL transaction; the importer enters its write transaction after parsing.

WithTransaction invokes fn and commits without await. Passing an asynchronous callback can separate the SQL commit from Promise completion; a TypeScript generic does not turn this runtime behavior into an asynchronous transaction. SQL atomicity also excludes Vectra files and the saveDatabase file write.

ResultToObjects in db/utils.ts converts only the first exec result set, using columns to name each row’s values. It neither combines all statements’ results nor validates field types.

Migrations, autosave, and exit need separate failure checks

ApplySchemaMigrations runs table/index statements, then checks incremental columns through PRAGMA table_info. EnsureIndexColumns compares index-column count, order, and names, dropping and rebuilding on a mismatch. Reflection can avoid adding a column twice without making every migration safe. Existing data, foreign keys, index changes, and interruption recovery still require samples.

StartAutoSave schedules synchronous saveDatabase every 30 seconds by default and avoids duplicate timers. This is not a clock guarantee that saving finishes every 30 seconds. Event-loop blocking and write errors affect outcomes. CloseDatabase stops the timer, then saves, closes, and clears the instance; a save exception has no finally ensuring subsequent steps run.

OpenVscdb likewise loads main-file bytes into sql.js, retries once after 500ms, and returns null after another failure. It does not retain the original file handle as a database connection, without guaranteeing that readFileSync succeeds or merging a native WAL on this path. Caching the initialized module rather than its in-flight Promise also does not establish one initialization across every concurrent call.

Continue with the desktop lifecycle and vector-index recovery to check shutdown and cross-store state separately. SQL commits, exports, file saves, and successful exit need their own evidence. The sql.js Database documentation describes export and connection APIs.