--- title: "sql.js WASM Persistence: Memory, Exports & Nested Savepoints" description: Trace ChatCrystal’s database lifecycle with sql.js 1.14.2 fixtures, separating SQL commits, exports, file writes, foreign-key restoration, and nested rollback. image: "https://zengliangyi.com/og/blog/en/sqljs-wasm-persistence.png" --- 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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/server/src/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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/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. ```js 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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/server/src/db/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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/server/src/services/import.ts) 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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/server/src/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](https://github.com/ZengLiangYi/ChatCrystal/blob/764276814e5c5a33ecbfeae63754f74b9261a3a8/server/src/parser/vscdb.ts) 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](/en/blog/fastify-electron-service-lifecycle/) and [vector-index recovery](/en/blog/vectra-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](https://sql.js.org/documentation/Database.html) describes export and connection APIs. Share: ## Related Posts [## AI Conversation Formats: JSONL Events vs SQLite Snapshots Compare 5 AI adapter formats: separate JSONL events from SQLite keys, and inspect indexed, orphan, and unknown-version chat transcripts in memory stores. 曾亮懿 October 7, 2026 4 min read #ChatCrystal #Node.js #SQLite](/en/blog/ai-conversation-format-divergence) [## How Claude Code, Cursor, Copilot, and Trae Store Chats on Disk Compare Claude Code, Cursor, Copilot, and Trae storage paths. Distinguish raw copies from exports, and verify migration retention with test manifests. 曾亮懿 October 7, 2026 4 min read #ChatCrystal #Node.js #SQLite](/en/blog/ai-conversation-management-options) [## Vectra Vector Search: Metadata, Updates, and Local Cache Inspect Vectra 0.15.0 query and storage paths with temporary indexes, observing external metadata, files left after cancellation, and instance-cache boundaries. 曾亮懿 October 7, 2026 4 min read #ChatCrystal #Node.js #SQLite](/en/blog/vectra-search-internals) ## From writing to code Explore ChatCrystal, developer tools and daily learning notes on my GitHub. Questions about the implementation are welcome. [Visit my GitHub](https://github.com/ZengLiangYi) ```json { "@context": "https://schema.org", "@type": "WebSite", "name": "ZengLiangYi Blog", "alternateName": [ "Zeng Liangyi", "ZengLiangYi", "zengliangyi.com" ], "url": "https://zengliangyi.com", "description": "曾亮懿的个人网站。记录 AI Native 探索、开发者工具与全栈产品实践。" } { "@context": "https://schema.org", "@type": "BlogPosting", "headline": "sql.js WASM Persistence: Memory, Exports & Nested Savepoints", "description": "Trace ChatCrystal’s database lifecycle with sql.js 1.14.2 fixtures, separating SQL commits, exports, file writes, foreign-key restoration, and nested rollback.", "url": "https://zengliangyi.com/en/blog/sqljs-wasm-persistence/", "image": "https://zengliangyi.com/images/blog/sqljs-wasm-persistence.svg", "datePublished": "2026-10-07T00:00:00.000Z", "dateModified": "2026-10-23T00:00:00.000Z", "author": { "@type": "Person", "name": "曾亮懿", "url": "https://zengliangyi.com/en/about" }, "publisher": { "@type": "Organization", "name": "ZengLiangYi Blog", "logo": { "@type": "ImageObject", "url": "https://zengliangyi.com/favicon-96x96.png" } }, "mainEntityOfPage": { "@type": "WebPage", "@id": "https://zengliangyi.com/en/blog/sqljs-wasm-persistence/" } } { "@context": "https://schema.org", "@type": "BreadcrumbList", "itemListElement": [ { "@type": "ListItem", "position": 1, "name": "Home", "item": "https://zengliangyi.com/en" }, { "@type": "ListItem", "position": 2, "name": "Blog", "item": "https://zengliangyi.com/en/blog" }, { "@type": "ListItem", "position": 3, "name": "sql.js WASM Persistence: Memory, Exports & Nested Savepoints", "item": "https://zengliangyi.com/en/blog/sqljs-wasm-persistence/" } ] } ```