LocalHtmlDataTest/sql-js/README.md

IndexSyncFile -- sql.js Variant

Local-first key-value and document store using sql.js (SQLite compiled to JavaScript via asm.js) as the local cache, syncing with a user-selected folder via the File System Access API.

Why sql.js

• Works from file:// -- pure JavaScript, no .wasm file to fetch, no COOP/COEP headers needed
• Real SQL -- full SQLite query engine with proper indexes, joins, and transactions
• Single-file friendly -- bun/webpack/rollup can inline everything into one HTML file
• Correct numeric ordering -- range queries on numbers compare as numbers, not strings
• Portable database file -- db.export() produces a standard SQLite binary that any SQLite tool can open

Architecture

Write: app ---> sql.js in-memory (immediate) ---> /events/timestamp_hash.json
                                              ---> /data/store.sqlite (debounced)

Sync:  /events/*.json ---> sort by timestamp ---> skip applied ---> apply to sql.js
                                                                ---> persist store.sqlite

Startup: load /data/store.sqlite (instant) ---> sync only NEW events from /events/

Two persistence layers

Layer	Purpose	Format
/events/	Sync mechanism -- immutable event files enable multi-browser sync without data loss	One JSON file per mutation
/data/store.sqlite	Fast reload -- full SQLite database binary so startup doesn't replay the entire event history	Standard SQLite 3 file

The event log is essential for sync. The SQLite file is a startup optimization. Without it, every page load would replay all events from scratch.

The SQLite file is a real database

The store.sqlite file written to /data/ is a standard SQLite database. You can open it with any SQLite tool (DB Browser for SQLite, the sqlite3 CLI, Python's sqlite3 module) to inspect, query, or debug your data.

SQL schema (5 tables)

kv          (key TEXT PK, value TEXT, ts INTEGER, rev INTEGER)
docs        (store TEXT, id TEXT, data TEXT, ts INTEGER, rev INTEGER, deleted INTEGER, PK(store,id))
idx_entries (store TEXT, index_name TEXT, id TEXT, value ANY, PK(store,index_name,id))
applied     (filename TEXT PK, applied_at INTEGER)
meta        (key TEXT PK, value TEXT)

How it differs from the sqlite/ variant

	sql-js/ (this)	sqlite/
Engine	sql.js (asm.js, pure JS)	@sqlite.org/sqlite-wasm (WebAssembly)
.wasm file	None	Required, fetched at runtime
file:// support	Yes	No
HTTP headers	None needed	May need COOP/COEP for OPFS
Speed	~2-3x slower than WASM	Fastest SQLite in browser
Browser persistence	None (in-memory only)	OPFS (survives reload natively)
Disk persistence	Exports full .sqlite to folder	Event log only
Bundle size	~2MB (asm.js is large)	~800KB + separate .wasm

For this library's use case (small-to-medium datasets, infrequent queries, folder-based persistence), the speed difference is negligible.

Install and build

npm install
npm run build

Dependency: sql.js -- SQLite compiled to JavaScript. The asm.js build is used (not the WASM build) so everything works from file:// with zero fetches.

Usage

import { FolderSyncDB } from './dist/index.js';

const db = await FolderSyncDB.open({
  dbName: 'MyApp',
  autoSyncIntervalMs: 5000,
});

await db.selectFolder();

// KV
await db.kv.set('config', { theme: 'dark', lang: 'en' });

// Collections
const tasks = db.collection({
  name: 'tasks',
  indexes: [{ name: 'byPriority', fields: ['priority'] }],
});
await tasks.put({ id: 't1', title: 'Deploy', priority: 1 });

// Range queries with proper numeric ordering
const urgent = await tasks.queryByIndex('byPriority', { gte: 1, lte: 3 });

Folder layout

your-folder/
  events/
    1710000000000_a1b2c3d4e5f6.json
    1710000001000_f6e5d4c3b2a1.json
  data/
    store.sqlite     <-- standard SQLite 3 database file

Variant-specific notes

• sql.js runs entirely in-memory; persistence comes from the event log and the exported .sqlite file
• The .sqlite file is written with a 250ms debounce to avoid excessive disk writes during batch operations
• Directory handle is stored in a tiny IDB sidecar (sql.js cannot store DOM objects)
• On close(), any pending writes are flushed before releasing resources
• Index values are stored with SQLite type affinity, so numbers compare as numbers in range queries