npm package: @loro-dev/unisqlite
Description: Cross-platform concurrent SQLite access layer
Latest version: 0.6.0

# UniSQLite

Universal SQLite adapter for Node.js, Cloudflare Workers, and browsers.

## Features

- **Cross-platform**: Works in Node.js, Cloudflare Workers, and browsers
- **Type-safe**: Full TypeScript support with strong typing
- **Transaction support**: Both synchronous and asynchronous transactions
- **Connection type detection**: Identify and enforce proper transaction usage
- **Leader election**: Browser adapter uses leader election for multi-tab coordination
- **Role awareness (browser multi-tab)**: Exposes `getRole()` / `subscribeRoleChange()` so apps can avoid redundant host-only work
- **Cloudflare DO Support**: Native adapter for Cloudflare Durable Objects' SQLite persistence API

## Usage

### Standard Usage

```typescript
import { openStore } from "@loro-dev/unisqlite";

const store = await openStore({ path: "my-database.db" });

// Basic operations
await store.run("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
await store.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);
const users = await store.query("SELECT * FROM users");

// Transactions
await store.transaction(async (txn) => {
  await txn.run("INSERT INTO users (name) VALUES (?)", ["Bob"]);
  await txn.run("INSERT INTO users (name) VALUES (?)", ["Charlie"]);
});

await store.close();
```

### Cloudflare Durable Objects

For Cloudflare Durable Objects with the new SQLite persistence API:

```typescript
import { CloudflareDOAdapter, createCloudflareDOAdapter } from "@loro-dev/unisqlite";

export class MyDurableObject extends DurableObject {
  private db: CloudflareDOAdapter;

  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    // Use the factory function for easy setup
    this.db = createCloudflareDOAdapter(ctx.storage.sql);
  }

  async fetch(request: Request) {
    // Use the adapter like any other UniSQLite connection
    await this.db.run("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");
    await this.db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);
    const users = await this.db.query("SELECT * FROM users");

    return Response.json({ users });
  }
}
```

## Connection Types

UniSQLite provides three types of connections with different transaction capabilities:

### Direct Connection (`"direct"`)

- Primary database connections
- Can execute both `transaction()` and `asyncTransaction()`
- Entry point for all database operations

```typescript
const store = await openStore({ path: "database.db" });
console.log(store.getConnectionType()); // "direct"

// Both transaction types are supported
await store.transaction(async (txn) => {
  /* sync transaction */
});
await store.asyncTransaction(async (txn) => {
  /* async transaction */
});
```

### Sync Transaction Connection (`"syncTxn"`)

- Created within `transaction()` calls
- Can call `transaction()` using itself (for nested operations)
- **Cannot** call `asyncTransaction()` - will throw an error

```typescript
await store.transaction(async (txn) => {
  console.log(txn.getConnectionType()); // "syncTxn"

  // ✅ Allowed: nested transaction using same connection
  await txn.transaction(async (nested) => {
    // nested === txn (same connection)
    await nested.run("INSERT INTO users (name) VALUES (?)", ["user"]);
  });

  // ❌ Not allowed: will throw error
  await txn.asyncTransaction(async (nested) => {
    // Error: "asyncTransaction is not supported in syncTxn connections"
  });
});
```

### Async Transaction Connection (`"asyncTxn"`)

- Created within `asyncTransaction()` calls
- Can call both `transaction()` and `asyncTransaction()` using itself
- Supports timeout configuration and async operations

```typescript
await store.asyncTransaction(
  async (txn) => {
    console.log(txn.getConnectionType()); // "asyncTxn"

    // ✅ Both transaction types are supported
    await txn.transaction(async (nested) => {
      // nested === txn (same connection)
    });

    await txn.asyncTransaction(async (nested) => {
      // nested === txn (same connection)
    });
  },
  { timeoutMs: 30000 }
);
```

## Browser Storage Backends

The browser adapter supports multiple storage backends for persistence. By default, it uses `'auto'` mode which tries each backend in order until one succeeds.

### Storage Backend Options

| Backend | Description | Requirements |
|---------|-------------|--------------|
| `'opfs'` | Origin Private File System - best for persistent storage with custom database names | Secure context (HTTPS/localhost), modern browser |
| `'localStorage'` | localStorage-backed storage | Path must be `'local'` or `'session'` only |
| `'memory'` | In-memory storage (no persistence) | None |
| `'auto'` | Try OPFS → localStorage → memory (default) | None |

If you set `storageBackend: 'opfs'`, UniSQLite will **not** silently fall back — `openStore()` will throw if OPFS cannot be opened. Use `storageBackend: 'auto'` if you want automatic fallback.

### Using OPFS for Custom Database Names

OPFS is ideal for multi-workspace applications that need separate persistent databases:

```typescript
import { openStore } from "@loro-dev/unisqlite";

// Each workspace gets its own persistent database
const db = await openStore({
  path: `workspace-${workspaceId}.db`,
  sqlite: {
    loadStrategy: 'cdn',
    storageBackend: 'opfs'  // Use OPFS for custom database names
  }
});

// Check which storage backend is active
const info = db.getSQLiteInfo();
console.log('Storage backend:', info.activeStorageBackend); // 'opfs'

// Or use the dedicated method
console.log('Storage backend:', db.getStorageBackend()); // 'opfs'
```

### Auto Mode (Default)

In `'auto'` mode, the adapter tries backends in order and falls back gracefully:

```typescript
const db = await openStore({
  path: 'my-database.db',
  sqlite: {
    storageBackend: 'auto'  // Default - tries OPFS first
  }
});

// Will use OPFS if available, otherwise falls back
console.log('Using:', db.getStorageBackend()); // 'opfs', 'localStorage', or 'memory'
```

### OPFS VFS Types

SQLite WASM supports multiple OPFS-backed VFS implementations. Configure via `sqlite.opfsVfsType`:

```typescript
const db = await openStore({
  path: 'my-database.db',
  sqlite: {
    storageBackend: 'opfs',
    opfsVfsType: 'auto', // 'auto' (default) | 'opfs' | 'sahpool'
  }
});
```

**Note:** SQLite WASM OPFS backends are worker-only. When UniSQLite is used from the **main thread** with
`storageBackend: 'opfs'`, it will transparently use SQLite WASM's wrapped-worker API under the hood
(i.e. it will **start a Worker**). There is currently no separate "allow worker" option — requesting
OPFS implies Worker usage.

### OPFS Requirements

- **Secure context**: HTTPS or localhost
- **Browser support**: Chrome 86+, Firefox 111+, Safari 15.2+
- **Worker requirement**: SQLite WASM OPFS backends are only available in Worker contexts. When running on the main thread, UniSQLite will automatically run SQLite in a Worker (wrapped-worker). Your `sqlite.loadStrategy` must be able to load `sqlite3Worker1Promiser` (use `'npm'` or `'cdn'`, or provide it globally).
- **For VFS `'opfs'`**: Requires COOP/COEP headers (`window.crossOriginIsolated === true`) so `SharedArrayBuffer` is available.
- **Benefits over localStorage**:
  - No 5MB size limit
  - Binary storage (better performance)
  - Custom database filenames
  - Better suited for large datasets

### localStorage Limitations

The `localStorage` backend only accepts `'local'` or `'session'` as database paths:

```typescript
// ✅ Works with localStorage
const db = await openStore({
  path: 'local',  // or 'session'
  sqlite: { storageBackend: 'localStorage' }
});

// ❌ Throws error - invalid path for localStorage
const db = await openStore({
  path: 'my-custom-db.db',
  sqlite: { storageBackend: 'localStorage' }
});
// Error: localStorage storage backend requires path to be 'local' or 'session'
```

## Bundler Integration (Vite, Webpack, Rollup)

When using bundlers in production, the default CDN-based Worker loading may not be ideal. UniSQLite provides a `workerUrl` option for proper bundler integration.

### Why Custom Worker URL?

By default, UniSQLite creates Workers dynamically using Blob URLs that load SQLite WASM from a CDN. This works for development but has production drawbacks:

| Default (CDN) | Custom workerUrl |
|---------------|------------------|
| ❌ Requires runtime CDN access | ✅ Fully bundled, works offline |
| ❌ Bypasses bundler optimizations | ✅ Tree-shaking, minification |
| ❌ May violate CSP | ✅ CSP compliant |
| ❌ Version mismatch risk | ✅ Consistent versions |

### Vite Setup

**Step 1: Create a Worker file**

```typescript
// src/sqlite-worker.ts
import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

sqlite3InitModule().then(async (sqlite3) => {
  // Optional: Install SAHPool VFS (no COOP/COEP required)
  if (typeof sqlite3.installOpfsSAHPoolVfs === 'function') {
    try {
      await sqlite3.installOpfsSAHPoolVfs({
        name: 'opfs-sahpool',
        directory: '/unisqlite-sahpool',
      });
    } catch (e) {
      console.warn('SAHPool VFS installation failed:', e);
    }
  }
  
  // Initialize Worker API
  sqlite3.initWorker1API();
});
```

**Step 2: Import and use the Worker URL**

```typescript
// src/db.ts
import { openStore } from '@loro-dev/unisqlite';

// Vite processes this import and bundles the worker
import sqliteWorkerUrl from './sqlite-worker?worker&url';

export async function createDatabase(name: string) {
  return await openStore({
    name,
    sqlite: {
      workerUrl: sqliteWorkerUrl,
      storageBackend: 'opfs',
    },
  });
}
```

**Step 3: Configure Vite (if needed)**

```typescript
// vite.config.ts
import { defineConfig } from 'vite';

export default defineConfig({
  optimizeDeps: {
    exclude: ['@sqlite.org/sqlite-wasm'],
  },
  worker: {
    format: 'es',
  },
});
```

### Webpack 5 Setup

**Step 1: Create a Worker file**

```javascript
// src/sqlite-worker.js
import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

sqlite3InitModule().then(async (sqlite3) => {
  // Optional: Install SAHPool VFS
  if (typeof sqlite3.installOpfsSAHPoolVfs === 'function') {
    try {
      await sqlite3.installOpfsSAHPoolVfs({
        name: 'opfs-sahpool',
        directory: '/unisqlite-sahpool',
      });
    } catch (e) {
      console.warn('SAHPool VFS installation failed:', e);
    }
  }
  
  sqlite3.initWorker1API();
});
```

**Step 2: Import and use the Worker URL**

```typescript
// src/db.ts
import { openStore } from '@loro-dev/unisqlite';

// Webpack 5 processes this with asset modules
const sqliteWorkerUrl = new URL('./sqlite-worker.js', import.meta.url);

export async function createDatabase(name: string) {
  return await openStore({
    name,
    sqlite: {
      workerUrl: sqliteWorkerUrl.href,
      storageBackend: 'opfs',
    },
  });
}
```

### TypeScript Declarations

If TypeScript complains about the `?worker&url` import, add this to your declarations:

```typescript
// src/vite-env.d.ts or src/global.d.ts
declare module '*?worker&url' {
  const workerUrl: string;
  export default workerUrl;
}
```

### Choosing VFS Type

When creating your Worker, you can choose which VFS to install:

| VFS | Requirements | When to Use |
|-----|--------------|-------------|
| `opfs` (default) | COOP/COEP headers | Best performance, requires `SharedArrayBuffer` |
| `opfs-sahpool` | None | No special headers needed, works everywhere |

**For SAHPool (recommended for wider compatibility):**

```typescript
// sqlite-worker.ts
import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

sqlite3InitModule().then(async (sqlite3) => {
  // Install SAHPool - works without COOP/COEP
  if (typeof sqlite3.installOpfsSAHPoolVfs === 'function') {
    await sqlite3.installOpfsSAHPoolVfs({
      name: 'opfs-sahpool',
      directory: '/unisqlite-sahpool',
    });
  }
  sqlite3.initWorker1API();
});
```

Then configure UniSQLite to use SAHPool:

```typescript
const db = await openStore({
  name: 'mydb',
  sqlite: {
    workerUrl: sqliteWorkerUrl,
    storageBackend: 'opfs',
    opfsVfsType: 'sahpool', // Use SAHPool VFS
  },
});
```

### Fallback Behavior

If `workerUrl` is not provided, UniSQLite falls back to CDN-based Worker creation. This is useful for:

- Development (zero configuration)
- Quick prototyping
- Environments where bundling Workers is not possible

```typescript
// Development: no workerUrl needed
const db = await openStore({
  name: 'mydb',
  sqlite: {
    loadStrategy: 'cdn', // Load from CDN
    storageBackend: 'opfs',
  },
});
```

## Platform-specific entrypoints

The default `unisqlite` export stays minimal and uses dynamic imports so optional peers are only loaded when their platform adapter is actually needed. To make bundling and installations more intentional, use platform-specific entrypoints:

- **Node** (requires peer `better-sqlite3`): `import { openNodeStore, NodeAdapter } from "@loro-dev/unisqlite/node";`
- **Browser** (needs `broadcast-channel`, and `@sqlite.org/sqlite-wasm` if you load SQLite from npm): `import { openStore } from "@loro-dev/unisqlite/browser";`
- **Cloudflare Durable Objects**: `import { openCloudflareStore, CloudflareDOAdapter, createCloudflareDOAdapter } from "@loro-dev/unisqlite/cloudflare";`

These entrypoints avoid pulling in other platform adapters, keeping browser bundles slim and Node installs free from WASM/downloaded assets.

## Multi-tab role awareness (browser)

In browsers, UniSQLite elects a single writable **host** tab for a given database. SQL calls are already transparently forwarded through RPC, but applications may want to run background tasks (sync, persistence/flush, etc.) only on the host tab.

The browser adapter exposes a lightweight, capability-based role API:

- `getRole(): "host" | "participant" | "unknown"`
- `subscribeRoleChange(listener): () => void`
- `waitForRole(role, timeoutMs?): Promise<void>`

These methods are **optional** on `UniStoreConnection` for compatibility with other adapters/older versions, so feature-detect them:

```ts
import { openStore } from "@loro-dev/unisqlite/browser";

const db = await openStore({ path: "my.db" });

if (db.getRole?.() === "host") {
  // Start background sync/persistence only on the host tab.
}

const unsubscribe = db.subscribeRoleChange?.((role) => {
  if (role === "host") {
    // Became host.
  } else if (role === "participant") {
    // Lost host.
  } else {
    // "unknown": initializing/closing; treat conservatively.
  }
});
```

Role semantics:

- `host` means **host-ready** (safe to run host-only work)
- `participant` means initialized but not host
- `unknown` is a transitional state during initialization/close; treat as `participant`

### Debug logging (browser)

UniSQLite browser logs are gated by `localStorage.debug` (errors always log). Examples:

```ts
// All UniSQLite logs
localStorage.debug = "unisqlite:*";

// Only adapter + host election logs
localStorage.debug = "unisqlite:adapter,unisqlite:host";
```

## API Reference

### Connection Type Detection

```typescript
interface UniStoreConnection {
  getConnectionType(): "direct" | "syncTxn" | "asyncTxn";
  // Optional (browser multi-tab only)
  getRole?: () => "host" | "participant" | "unknown";
  subscribeRoleChange?: (listener: (role: "host" | "participant" | "unknown") => void) => () => void;
  waitForRole?: (role: "host" | "participant" | "unknown", timeoutMs?: number) => Promise<void>;
  // ... other methods
}
```

### Transaction Method Signatures

```typescript
// Synchronous transaction - function cannot return Promise
transaction<T>(fn: (tx: UniStoreConnection) => T extends Promise<unknown> ? never : T): T extends Promise<unknown> ? never : T;

// Asynchronous transaction - function can return Promise
asyncTransaction<T>(fn: (tx: UniStoreConnection) => Promise<T>, options?: { timeoutMs?: number }): Promise<T>;
```

### Transaction Enforcement Rules

| Connection Type | `transaction()` | `asyncTransaction()` |
| --------------- | --------------- | -------------------- |
| `"direct"`      | ✅ Supported    | ✅ Supported         |
| `"syncTxn"`     | ✅ Supported    | ❌ Throws Error      |
| `"asyncTxn"`    | ✅ Supported    | ✅ Supported         |

### TypeScript Constraints

The `transaction()` method enforces synchronous functions at compile time:

```typescript
// ✅ Valid - returns non-Promise value
const result = store.transaction((txn) => {
  return "sync-result";
});

// ❌ TypeScript Error - returns Promise
const invalid = store.transaction((txn) => {
  return Promise.resolve("async-result"); // Type error!
});

// ❌ TypeScript Error - async function
const invalid2 = store.transaction(async (txn) => {
  return "result"; // Type error!
});
```

This design ensures:

- Type safety at the connection level
- Prevention of async operations in sync-only contexts
- Clear error messages for invalid usage patterns
- Consistent behavior across all adapter implementations
- Compile-time enforcement of synchronous transaction functions