> **Status:** Published
> **Date:** 2026-09-12
> **Scope**: API Reference
> **Standard**: Memorio API Specification v5
>
---
# SQLite - Memorio

> The `sql.js` package is **optional** - if it is not installed, Memorio loads `sql.js` on first use by injecting the `sql-wasm-browser.js` UMD build from the jsDelivr CDN as a classic `<script>` (which exposes the `initSqlJs` factory on `globalThis`). No bare `import('sql.js')` is ever emitted, so bundlers never resolve the optional dependency at build time. Not available in Node.js/Deno.

SQLite brings a full SQL engine into the browser through [`sql.js`](https://sql.js.org), a port of SQLite to JavaScript via WebAssembly. Memorio loads it **lazily** on first use, so it only affects bundles that actually use the `sqlite` module.

## Installation

```bash
npm install memorio
# Optional: install the SQLite engine locally (the default loader fetches it from the CDN)
npm install sql.js
```

```javascript
import { sqlite } from 'memorio';
```

> **Global API**: `import 'memorio/global'` is the opt-in entrypoint that exposes `state`, `store`, `session`, `cache`, `idb`, `sqlite`, `observer` on `globalThis`. `useObserver` is a React hook and is not exposed globally; import it from `memorio/react`.

> The default loader injects the sql.js UMD build (`sql-wasm-browser.js`) from the jsDelivr CDN as a classic `<script>`, exposing the `initSqlJs` factory on `globalThis`. This keeps `sql.js` (and its WASM) out of your build graph entirely - it's only fetched when `sqlite` is first used. For production/self-hosted setups, install `sql.js` and configure a custom `loader` (e.g. `sqlite.config({ loader: () => import('sql.js') })` under a bundler), or set the wasm base with `sqlite.config({ wasmUrl })` / `sqlite.config({ locateFile })`.

---

## Quick Examples

### Example 1: Basic Usage

```javascript
// Create an in-memory database and a table
await sqlite.db.create('myApp');
await sqlite.query.run('myApp', 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');

// Insert data
await sqlite.query.run('myApp', 'INSERT INTO users (name) VALUES (?)', ['Mario']);

// Select data
const rows = await sqlite.query.select('myApp', 'SELECT * FROM users');
console.debug(rows); // [{ id: 1, name: 'Mario' }]
```

### Example 2: CRUD shortcuts

```javascript
const db = 'store';

await sqlite.db.create(db);
await sqlite.query.run(db, 'CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)');
await sqlite.data.set(db, 'INSERT INTO products (name, price) VALUES (?, ?)', ['Apple', 1.5]);
await sqlite.data.set(db, 'INSERT INTO products (name, price) VALUES (?, ?)', ['Banana', 0.8]);

// First matching row
const apple = await sqlite.data.get(db, 'SELECT * FROM products WHERE name = ?', ['Apple']);
console.debug(apple); // { id: 1, name: 'Apple', price: 1.5 }
```

### Example 3: Export / Import

```javascript
await sqlite.db.create('myApp');
await sqlite.query.run('myApp', 'CREATE TABLE todos (id INTEGER PRIMARY KEY, task TEXT)');
await sqlite.data.set('myApp', 'INSERT INTO todos (task) VALUES (?)', ['Write docs']);

// Export the in-memory database to a portable binary dump
const dump = await sqlite.db.export('myApp');

// Later... import it back
await sqlite.db.import('restored', dump);
const rows = await sqlite.query.select('restored', 'SELECT * FROM todos');
```

### Example 4: Engine control

```javascript
// Configure the wasm location BEFORE first use (sets the locateFile base)
sqlite.config({ wasmUrl: '/static/sql.js/' });

// Or provide a fully custom loader (e.g. a local/npm build of sql.js)
sqlite.config({ loader: async () => {
  return await import('sql.js');   // local install - resolves at runtime
}});

// Enable automatic persistence of one or more databases (see Example 5)
sqlite.config({ persistence: true });

// Wait until the engine is ready
await sqlite.ready;
console.debug('SQLite version:', sqlite.db.version());
```

### Example 5: Persistence & dev download

> **Important:** sql.js databases live in **WebAssembly memory** - they are
> in-memory and **volatile** (lost on page refresh) unless you opt into persistence.

```javascript
// Opt into persistence at create time (global config or per-create)
await sqlite.db.create('app', { persistence: true });

await sqlite.query.run('app', 'CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)');
// writes are journaled incrementally; checkpoints are saved automatically

await sqlite.db.persist('app');          // force an immediate checkpoint (full snapshot + clear journal)
// After a refresh, reopening restores the data:
await sqlite.db.create('app', { persistence: true });

// Dev convenience: download the current db as a .sqlite file from the browser
await sqlite.db.download('app', 'app.sqlite');

// Flush + persist + release the handle
await sqlite.db.close('app');
```

Persistence uses a **journal + checkpoint** strategy for incremental durability:

1. **Journal**: every `db.run(sql, params)` write is recorded as a journal entry
   (with an HLC timestamp for causal ordering) in `store` (localStorage). The
   journal is append-only and incremental - only the changed statement is stored.
2. **Checkpoint**: every 50 operations (configurable), or after 250 ms of write
   idle, a full database snapshot is taken and written to `store`. The journal is
   then cleared up to that point.
3. **Recovery**: on `create(name, { persistence: true })`, the latest checkpoint
   is loaded first, then any journal entries that were written after the
   checkpoint are replayed on top - so no writes are lost even if the page closes
   between checkpoints.

Persistence is stored on `store` (localStorage, namespaced per
`sqlite.config({ namespace })` / memorio context) as:
- Checkpoint: `memorio:sqlite:db:<ns>:<name>` (base64 binary snapshot)
- Journal entries: `memorio:sqlite:journal:<ns>:<name>:<seq>` (individual SQL ops)

It is best-effort: if `store` is unavailable the database simply behaves as
in-memory. The journal is also available directly:

```javascript
sqlite.db.persist('app');              // force checkpoint (alias: flush)
sqlite.db.flush('app');                // force checkpoint
const entries = sqlite.db.journal.entries('app'); // inspect pending ops
await sqlite.db.journal.checkpoint('app'); // manual checkpoint
```

---

## API Reference

### Engine Control

| Method | Parameters | Returns | Description |
|--------|------------|---------|-------------|
| `sqlite.config(opts)` | `opts: { loader?, locateFile?, wasmUrl?, persistence?, namespace? }` | `sqlite` | Configure the engine. `loader` fully replaces the initializer (default = CDN `<script>`); `locateFile` customizes wasm resolution; `wasmUrl` sets the wasm base directory; `persistence` toggles journal + checkpoint persistence; `namespace` partitions persisted data. Chainable. |
| `sqlite.db.create(name, opts)` | `name: string, opts?: { data?, persistence? }` | `Database` | Opens/creates a named in-memory db. With `persistence: true` (or global config), the latest checkpoint is restored and pending journal entries are replayed for full crash recovery. |
| `sqlite.db.persist(name)` | `name: string` | `Promise<boolean>` | Force an immediate checkpoint (full snapshot + journal clear) of a persisted database to `store`. Alias of `flush`. |
| `sqlite.db.flush(name)` | `name: string` | `Promise<boolean>` | Force an immediate checkpoint (alias of `persist`). |
| `sqlite.db.download(name, filename?)` | `name: string, filename?: string` | `Promise<boolean>` | Dev-only: trigger a browser download of the db as a `.sqlite` file. |
| `sqlite.db.close(name)` | `name: string` | `Promise<void>` | Checkpoint (if enabled), close, and release the handle. |
| `sqlite.db.journal` | `name: string` | Object | Journal namespace: `append`, `entries`, `count`, `clear`, `replay`, `checkpoint`. |
| `sqlite.ready` | none | `Promise<void>` | Resolves once `sql.js` has been initialized. Rejects (and sets `_disabled`) if the engine can't load. |
| `sqlite.db.version()` | none | `string \| null` | Engine version, available after initialization. |
| `sqlite._disabled` | none | `boolean` | `true` when the module is disabled (non-browser env / load failure). |
| `sqlite._warning` | none | `string \| undefined` | Reason text when disabled, if applicable. |

### Database Methods

| Method | Parameters | Returns | Description |
|--------|------------|---------|-------------|
| `sqlite.db.support()` | none | `boolean` | Check whether SQLite can run in this environment. |
| `sqlite.db.create(name, opts?)` | `name: string`, `opts?: { data }` | `Promise<Database>` | Create an in-memory database (or reopen one from a binary dump). |
| `sqlite.db.get(name)` | `name: string` | `Database` | Retrieve an open database handle. |
| `sqlite.db.delete(name)` | `name: string` | `Promise<boolean>` | Checkpoint (if enabled), close, and remove a database handle. |
| `sqlite.db.list()` | none | `string[]` | List open database names. |
| `sqlite.db.size(name?)` | `name?: string` | `Promise<number>` | Size in bytes of one or all databases. |
| `sqlite.db.export(name)` | `name: string` | `Promise<Uint8Array>` | Export a database to a binary dump. |
| `sqlite.db.import(name, data)` | `name: string`, `data` | `Promise<Database>` | Create/replace a database from a binary dump. |

### Query Methods

| Method | Parameters | Returns | Description |
|--------|------------|---------|-------------|
| `sqlite.query.run(name, sql, params?)` | `name: string`, `sql: string`, `params?: any[]` | `Promise<number>` | Run a statement that does not return rows (INSERT / UPDATE / DELETE / CREATE). Returns modified-row count. |
| `sqlite.query.select(name, sql, params?)` | `name: string`, `sql: string`, `params?: any[]` | `Promise<object[]>` | Run a query and return the rows as plain objects. |

### Data Methods (CRUD shortcuts)

| Method | Parameters | Returns | Description |
|--------|------------|---------|-------------|
| `sqlite.data.set(name, sql, params?)` | `name: string`, `sql: string`, `params?: any[]` | `Promise<number>` | Alias of `sqlite.query.run`. |
| `sqlite.data.get(name, sql, params?)` | `name: string`, `sql: string`, `params?: any[]` | `Promise<object \| null>` | Alias of `sqlite.query.select` returning the first row (or `null`). |

---

## Platform Support

| Platform | Support | Notes |
|----------|---------|-------|
| Browser | ✅ Full | Requires WebAssembly + the `sql.js` package (or CDN fallback) |
| Edge Worker | ⚠️ Limited | WebAssembly may be available; configure a `loader` manually |
| Node.js | ❌ Not available | Use `store` or `session` instead |
| Deno | ❌ Not available | Use `store` or `session` instead |

---

## Best Practices

1. Configure the wasm path for production: `sqlite.config({ wasmUrl })` or `sqlite.config({ loader })`.
2. Create one named database per feature and reuse the handle: `const db = await sqlite.db.create('app')`.
3. Use `await sqlite.ready` before running statements to ensure the engine is loaded.
4. Use `?` placeholders and `params` to avoid SQL injection.
5. Batch writes and call `sqlite.db.persist()` / `sqlite.db.flush()` once after the batch, not inside a loop. The journal captures each write incrementally, but the checkpoint (full snapshot) is the expensive operation.
6. Always release databases you no longer need: `await sqlite.db.delete('temp')`.
