sql.js Practice: In-Memory SQLite, Foreign Keys, and Saves

sql.js compiles SQLite to WebAssembly and exposes a JavaScript interface. ChatCrystal uses it in its Node service to load database bytes, execute SQL, and explicitly export a saved copy. An INSERT does not automatically write the application’s database file. Understanding that boundary is more useful than starting with a performance ranking.

Read the actual initialization path

The fixed database entry point loads WASM, reads the entire existing file with readFileSync, and constructs SQL.Database; without a file, it creates a new database. Packaged Electron locates sql-wasm.wasm under resourcesPath through locateFile. Initialization then sets PRAGMAs, runs migrations, backfills memory metadata, and saves. Existing embedding rows also cause corresponding notes to become done at startup. That does not prove the Vectra files are intact; see index recovery.

The source executes PRAGMA journal_mode = WAL, and the isolated exercise below also returns wal in the checked sql.js 1.14.2 environment. That return value does not prove each commit automatically synchronizes the application’s database file or coordinates file locks with another host process. Application persistence still uses a full export and writeFileSync. Check both the PRAGMA result and the actual host-file write path.

An exercise that never reads personal data

Use Node 24 or later in an empty directory. Install the fixed version, save the following code as lesson.mjs, and run it:

npm init -y
npm install sql.js@1.14.2
node lesson.mjs

// lesson.mjs: synthetic data, entirely in memory.
import assert from 'node:assert/strict';
import initSqlJs from 'sql.js';

const SQL = await initSqlJs();
const db = new SQL.Database();
let restored;
try {
  db.run('PRAGMA foreign_keys = ON');
  console.log('journal_mode:', db.exec('PRAGMA journal_mode = WAL')[0].values[0][0]);
  db.run(`CREATE TABLE parent (id INTEGER PRIMARY KEY);
    CREATE TABLE child (
      id INTEGER PRIMARY KEY,
      parent_id INTEGER REFERENCES parent(id)
    );`);
  assert.equal(db.run('INSERT INTO parent VALUES (?)', [1]), db);
  assert.equal(db.getRowsModified(), 1);
  db.run('INSERT INTO child VALUES (?, ?)', [1, 1]);
  assert.throws(() => db.run('INSERT INTO child VALUES (?, ?)', [2, 99]));

  db.run('BEGIN');
  try {
    db.run('INSERT INTO parent VALUES (?)', [2]);
    db.run('INSERT INTO child VALUES (?, ?)', [3, 99]);
    db.run('COMMIT');
  } catch {
    db.run('ROLLBACK');
  }
  assert.deepEqual(db.exec('SELECT id FROM parent ORDER BY id')[0].values, [[1]]);

  let bytes;
  try {
    bytes = db.export();
  } finally {
    db.run('PRAGMA foreign_keys = ON');
  }
  assert.equal(db.exec('PRAGMA foreign_keys')[0].values[0][0], 1);
  assert.throws(() => db.run('INSERT INTO child VALUES (?, ?)', [4, 99]));
  restored = new SQL.Database(bytes);
  restored.run('PRAGMA foreign_keys = ON');
  assert.deepEqual(restored.exec('SELECT id, parent_id FROM child')[0].values, [[1, 1]]);
  console.log('foreign keys, rollback, and exported bytes checked');
} finally {
  restored?.close();
  db.close();
}

This creates synthetic parent and child rows. It neither starts ChatCrystal nor reads or overwrites a database file. Deliberately remove the foreign-key restoration statement in the finally block after export: the foreign-key assertion should fail. Restore it and check again. A successful SQL transaction and a successfully saved file require separate verification.

What the APIs and transactions guarantee

exec() returns an array of result sets containing columns and values; a query without rows may produce no result set. run() ignores result rows but returns the Database itself, rather than undefined. getRowsModified() reports the most recent INSERT, UPDATE, or DELETE’s affected rows, rather than a SELECT count. Check these return boundaries against the official Database API.

The application’s withTransaction tracks depth with a WeakMap. It uses BEGIN/COMMIT at the outer level, SAVEPOINT/RELEASE within it, and rolls back the corresponding level on an exception. The callback runs synchronously. An async callback returns a Promise that this wrapper does not await, so writes after await are not protected by the same transaction. Finish network or model requests before entering the synchronous database modification block.

Persistence needs its own design

saveDatabase exports a Uint8Array, converts it with Buffer.from, and writes the entire database with writeFileSync. The export finally block restores foreign_keys. The default autosave interval is 30 seconds; closeDatabase also saves before closing. A timer cannot guarantee a successful disk save every 30 seconds. Crashes, disk errors, or shutdown timeouts can still lose unsaved changes.

The current implementation has neither a temporary file followed by atomic replacement nor a write lock for multiple processes sharing the file. Avoid simultaneous writers to the same data directory. Record actual file size, export time, peak memory, and restoration results; see import memory budgeting. WASM and an in-memory database alone do not support promises of second-scale execution or safety below a particular database size.

After the exercise, follow migrations to constraints on the real notes, messages, and association tables. Verify repeated migrations, cascading deletion, and reload after saving in a synthetic database before deciding whether those paths behave as expected.