Client-Side Storage Engines: Optimizing SQLite WASM for ObjectOS

ObjectOS's "Local-First" promise relies heavily on the client's ability to execute complex queries offline. This isn't just about key-value storage; enterprise apps need joins, aggregations, and filtering.

This article explores how we implemented the storage layer using SQLite WASM and how we optimize query performance on the browser main thread (and Workers).

1. The Storage Abstraction Interface

We do not bind directly to SQLite. Instead, we defined a generic StorageAdapter interface. This allows ObjectOS to run on:

• Web: SQLite WASM (OPFS) or RxDB (IndexedDB).
• React Native: react-native-quick-sqlite (JSI).
• Electron: Native better-sqlite3.

OPFS (Origin Private File System)

The game-changer for Web SQL is OPFS. Unlike the old Logical File System, OPFS allows synchronous handles in Web Workers.

// The Worker Thread implementation of the VFS
async function getFileHandle() {
  const root = await navigator.storage.getDirectory();
  const fileHandle = await root.getFileHandle('objectos.db', { create: true });
  const accessHandle = await fileHandle.createSyncAccessHandle();
  return accessHandle;
}

By moving SQLite to a Web Worker and using SharedArrayBuffer, we achieve near-native read performance without blocking the UI.

2. Compiling ObjectQL to SQL (in the Browser)

The core challenge: ObjectQL acts as an ORM. The client sends:

const leads = await objectos.find('lead', {
  filters: [['status', '=', 'new'], ['owner.name', 'contains', 'John']],
  fields: ['name', 'amount', 'owner.email']
});

On the client, we must compile this into a SQL query.

The Compiler Pipeline

1. AST Transformation: The JSON filter is parsed into an Abstract Syntax Tree.
2. Schema Resolution: We check the local metadata cache to resolve owner.name. We see that owner is a ManyToOne to users.
3. Join Strategy:
   • Server-Side: We might use huge JOINs.
   • Client-Side: We prefer Lazy Loading or Sub-Selects because keeping huge indices in browser memory is expensive.

Eventually, it emits:

SELECT t0.name, t0.amount, t1.email 
FROM leads AS t0 
LEFT JOIN users AS t1 ON t0.owner = t1._id 
WHERE t0.status = 'new' AND t1.name LIKE '%John%'

3. Query Planning & Indexing

We cannot trust the browser to automatically index everything. ObjectOS plugins define indices in their manifest.

indices:
  - { fields: [status, created_at] }

When the specific plugin loads on the client, the Kernel runs a schema migration on the encapsulated SQLite instance:

CREATE INDEX IF NOT EXISTS idx_leads_status_created ON leads (status, created_at);

Performance Metrics

We benchmarked 10,000 records on a mid-range Android device:

• Insert 1k records: 120ms (WASM) vs 800ms (IndexedDB).
• Complex Query (Join + Sort): 15ms (WASM) vs 400ms (IndexedDB scan).

This performance gap is why we mandate SQLite WASM for data-heavy enterprise plugins.

4. Conclusion

Local-First is not "offline support". It is "embedded database engineering". By shipping a full SQL engine to the client and implementing a cross-platform VFS layer, ObjectOS delivers server-grade query capabilities to static web apps.