async-node-sqlite

Minimal asynchronous wrapper around Node.js node:sqlite (DatabaseSync) built on a worker thread pool. Queries and mutations run off the main thread; the API is Promise-based and resembles common SQLite ergonomics (run, get, all, transactions).

Requirements

• Node.js ≥ 22.0.0 (stable node:sqlite APIs)
• ES modules ("type": "module")
  • ESM projects: import { AsyncDatabase } from 'async-node-sqlite'
  • CJS projects: const { AsyncDatabase } = await require('async-node-sqlite')

Install

Install from your project (for example after cloning this repo):

npm install async-node-sqlite
# or
pnpm add async-node-sqlite

Why this exists

SQLite in Node runs synchronously inside the owning thread. Holding the main thread for heavy workloads can stall event-loop–driven servers. async-node-sqlite moves database work onto multiple workers:

• Uses one DatabaseSync instance per worker (connections share one file; WAL reduces writer contention).
• Routes each request through a MessageChannel per worker (not a single noisy ownerPort).
• Dispatches concurrent work across workers using a least-loaded heuristic.
• Pins transactions to a single worker (BEGIN IMMEDIATE … COMMIT / ROLLBACK) so isolation is correct.
• While a transaction() callback is executing, _currentTxId is active so db.exec, db.run, db.savepoint, and related calls inherit the same worker as the outer transaction unless you explicitly use another transactional context.

TypeScript typings ship in src/index.d.ts.

vs native node:sqlite

| | native node:sqlite | async-node-sqlite | |---|---|---| | Thread model | Synchronous, blocks calling thread | Worker pool, main thread never blocked | | API style | stmt = db.prepare() → stmt.run() | direct db.run() / db.get() / db.all() | | Prepared statements | Must prepare() before every query | Optional; ad-hoc queries auto-compile in worker | | Transactions | Manual BEGIN / COMMIT / ROLLBACK | transaction(fn) with auto commit/rollback | | Savepoints | Manual SQL | savepoint(name, fn) | | Concurrency | Single connection | Multi-worker pool with least-loaded dispatch | | Error handling | Plain Error thrown | SQLiteError with code + errno | | Timeouts | None | Per-operation timeout (TIMEOUT error) | | Worker resilience | N/A | Auto-recreate failed workers + reopen DB | | Utilities | None | isTableExists(), iterate() generator |

Features

| Area | What you get | |------|----------------| | Queries | get, all, async generator iterate (loads rows then yields) | | Execution | exec (multi-statement / DDL), run with bound parameters | | Prepared statements | prepare → AsyncStatement with run / get / all / finalize | | Transactions | transaction(fn) with tx.run, tx.get, tx.all; auto commit/rollback | | Nested units | savepoint(name, fn) using SAVEPOINT / RELEASE / ROLLBACK TO | | Utilities | isTableExists(name), close() | | Errors | SQLiteError with code, errno | | Resilience | Per-operation timeout; failed workers are terminated, recreated, and DB reopened |

Default worker PRAGMAS (per connection): busy_timeout (see options), journal_mode=WAL (unless disabled), foreign_keys=ON (unless disabled).

Quick start

import { AsyncDatabase } from 'async-node-sqlite';

const db = new AsyncDatabase('./app.db');

await db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age INTEGER,
    score REAL,
    bio TEXT,
    avatar BLOB,
    created_at TEXT DEFAULT (datetime('now'))
  )
`);

await db.exec('CREATE INDEX IF NOT EXISTS idx_users_name ON users (name)');

// Add a column later
await db.exec('ALTER TABLE users ADD COLUMN phone TEXT');

const { changes, lastInsertRowid } = await db.run(
  'INSERT INTO users (name) VALUES (?)',
  'Ada'
);

const row = await db.get('SELECT * FROM users WHERE id = ?', lastInsertRowid);
console.log(row);

// Query multiple rows
const users = await db.all('SELECT * FROM users WHERE name LIKE ?', 'A%');
console.log(users.length, 'users found');

// Update and delete
const { changes: updated } = await db.run('UPDATE users SET name = ? WHERE id = ?', 'Bob', lastInsertRowid);
const { changes: deleted } = await db.run('DELETE FROM users WHERE id = ?', lastInsertRowid);

await db.close();

In-memory database

const db = new AsyncDatabase(':memory:'); // workers is always 1 for :memory:

CommonJS

// CJS projects use await require() — the package is ESM internally
const { AsyncDatabase } = await require('async-node-sqlite');

const db = new AsyncDatabase('./app.db');
// ... same API as above
await db.close();

Multiple workers against :memory: are separate empty databases — workers is forced to 1 for in-memory to avoid this pitfall.

Transaction

await db.transaction(async (tx) => {
  await tx.run('UPDATE accounts SET balance = balance - ? WHERE id = ?', 100, 1);
  await tx.run('UPDATE accounts SET balance = balance + ? WHERE id = ?', 100, 2);
  return tx.get('SELECT * FROM accounts WHERE id = ?', 1);
});

Prepared statement

const insert = await db.prepare('INSERT INTO users (name) VALUES (?)');
for (const name of names) {
  await insert.run(name);
}
await insert.finalize();

Batch insert

For high-volume inserts, combine prepare, transaction, and reuse a single statement:

const insert = await db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');

await db.transaction(async (tx) => {
  // prepared statement 已绑定到某个 worker，transaction 内部会自动走同一个 worker
  for (const user of hugeArray) {
    await insert.run(user.name, user.email);
  }
});

await insert.finalize();

💡 Why this is fastest: SQL is compiled once (prepare), all inserts share a single transaction (no per-row fsync), and the statement is reused without repeated compilation.

Nested savepoint

await db.transaction(async (tx) => {
  await tx.run('UPDATE t SET x = 1 WHERE id = ?', 1);
  await db.savepoint('nested', async () => {
    await db.run('INSERT INTO log (msg) VALUES (?)', 'ok');
  });
});

Inside an active transaction(), db.savepoint and db.run participate in the same worker as tx (via internal transaction affinity).

Iterating rows

for await (const row of db.iterate('SELECT id, name FROM users')) {
  // row is one object per iteration
}

Internally iterate runs ALL once, then yields from the resulting array — suitable for simplifying consumer code; very large sets still pay the memory cost of the full result set until streamed APIs exist.

Error handling

import { AsyncDatabase, SQLiteError } from 'async-node-sqlite';

try {
  await db.run('INSERT INTO users (id) VALUES (?)', 1);
  await db.run('INSERT INTO users (id) VALUES (?)', 1); // duplicate key
} catch (err) {
  if (err instanceof SQLiteError) {
    console.error(`SQLite error: ${err.code} (errno: ${err.errno})`);
  } else {
    throw err;
  }
}

Utility: check if a table exists

if (await db.isTableExists('users')) {
  console.log('Table users exists');
}

JSON columns

SQLite stores JSON as plain TEXT but provides built-in functions to query and manipulate it:

await db.exec(`
  CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY,
    data TEXT  -- stored as JSON string
  )
`);

// Insert JSON
await db.run(
  'INSERT INTO settings (id, data) VALUES (?, ?)',
  1,
  JSON.stringify({ theme: 'dark', lang: 'en', notifications: true })
);

// Query with json_extract
const row = await db.get("SELECT json_extract(data, '$.theme') AS theme FROM settings WHERE id = ?", 1);
console.log(row.theme); // 'dark'

// Update JSON field
await db.run(
  "UPDATE settings SET data = json_set(data, '$.lang', 'zh') WHERE id = ?",
  1
);

Best Practices

Transactions are serialized

Concurrent transaction() calls are automatically queued — no lock conflicts, no SQLITE_BUSY, no "cannot start a transaction within a transaction":

// Safe: these run one after another, not in parallel
const [r1, r2] = await Promise.all([
  db.transaction(async (tx) => { /* ... */ }),
  db.transaction(async (tx) => { /* ... */ }),
]);

Transaction dos and don'ts

| Do ✅ | Don't ❌ | |-------|---------| | Only DB calls inside fn | Network requests / file I/O / external APIs | | Always await every tx.run / tx.get | Forget await — the lock never releases | | Use savepoint() for nested units | Call transaction() inside another transaction() |

Workers = 1 is usually right

SQLite is a single-writer database. The default workers: 1 eliminates lock contention entirely — all operations are serialized on one connection, and busy_timeout never fires. Multi-worker only helps when you have heavy concurrent reads under WAL mode while a writer is active.

Graceful shutdown (optional)

For long-running servers, close() is not required on process exit (the OS cleans up). If you want explicit cleanup on SIGTERM/SIGINT:

process.on('SIGTERM', async () => {
  await db.close();
  process.exit(0);
});

API reference

new AsyncDatabase(path, options?)

• path: SQLite file path or ':memory:'.
• options: partial AsyncDatabaseOptions (below).

Initialization is asynchronous: the constructor starts workers and opens each connection; await the first operation (or rely on readiness after that first awaited call completes).

Options (AsyncDatabaseOptions)

| Option | Default | Description | |--------|---------|-------------| | workers | 1 | Worker (connection) pool size. Forced to 1 for :memory: databases | | timeout | 30000 | Per-request timeout (ms); exceeded → SQLiteError code TIMEOUT | | busyTimeoutMs | same as timeout | Passed to PRAGMA busy_timeout in each worker (waits on locks instead of failing immediately across connections). Only in JSDoc on the JS implementation — consider adding it to typings if you use TypeScript strictly. | | statementCacheSize | 100 | Present in typings and stored on options but not enforced by the current worker logic; ad-hoc run/get/all compile a fresh prepared statement each time inside the worker. Explicit prepare() handles are tracked until finalize(). | | wal | true | When false, uses journal_mode=DELETE. | | foreignKeys | true | When false, sets foreign_keys=OFF. |

AsyncDatabase methods

| Method | Returns | Notes | |--------|---------|--------| | exec(sql) | Promise<void> | DatabaseSync#exec; multiple statements OK | | run(sql, ...params) | Promise<RunResult> | Inserts/updates/deletes | | get(sql, ...params) | Promise<T \| undefined> | First row or undefined | | all(sql, ...params) | Promise<T[]> | All rows; empty array if none | | iterate(sql, ...params) | AsyncGenerator<T> | As described above | | prepare(sql) | Promise<AsyncStatement> | Binds to one worker until finalized | | transaction(fn) | Promise<T> | fn receives { run, get, all }; BEGIN IMMEDIATE | | savepoint(name, fn) | Promise<T> | SAVEPOINT/RELEASE or ROLLBACK TO | | isTableExists(name) | Promise<boolean> | Queries sqlite_master | | close() | Promise<void> | Closes all connections and terminates workers; idempotent-ish (double close is tolerated in tests; avoid use-after-close). |

Quick rule of thumb:

| When you need to… | Use | |------|------| | CREATE / ALTER / DROP, multi-statement scripts | exec | | INSERT / UPDATE / DELETE, need changes & lastInsertRowid | run | | Fetch a single row, might not exist | get | | Fetch multiple rows / list | all | | Stream large result sets row-by-row | iterate | | Call a statement many times (batch insert) | prepare | | Atomic multi-step writes with rollback | transaction |

RunResult

interface RunResult {
  changes: number;
  lastInsertRowid: number | bigint;
}

Transaction context (Transaction)

interface Transaction {
  run(sql: string, ...params: unknown[]): Promise<RunResult>;
  get<T = unknown>(sql: string, ...params: unknown[]): Promise<T | undefined>;
  all<T = unknown>(sql: string, ...params: unknown[]): Promise<T[]>;
}

There is no exec on tx — use tx.run / raw SQL batches or DDL outside the transaction callback if needed.

AsyncStatement

| Method | Description | |--------|-------------| | run(...params) | Same semantics as DB run | | get(...params) | Same as DB get | | all(...params) | Same as DB all | | finalize() | Removes worker-side handle; after finalize, methods throw |

Using a finalized statement throws SQLiteError with code FINALIZED.

Statements are not portable across workers: each prepare() picks a worker at creation time.

SQLiteError

Subclass of Error with:

• code: string (e.g. SQLite engine code, CLOSED, TIMEOUT, FINALIZED)
• errno: numeric SQLite error code when propagated from Node; -1 for wrapper-defined errors

Architecture notes

See doc/design-proposal.md for the full rationale, diagrams, and message-flow discussion (document language is largely Chinese + TypeScript snippets; this README summarizes the shipped JS implementation).

At a glance:

User code → AsyncDatabase (routing, timeouts, txn affinity)
         → MessageChannel per worker → worker.js → DatabaseSync (node:sqlite)

Workers open the database sequentially during bootstrap to reduce Windows SQLite file-opening races.

Development & tests

npm install
npm test

Vitest runs in forks with singleFork and disables file parallelism so worker-thread tests behave predictably (vitest.config.js).

Test suites:

| File | Covers | |------|--------| | test/basic.test.js | CRUD, iterate, :memory:, DDL, WAL/FK pragmas | | test/statement.test.js | prepare, reuse, finalize rules, mixed workers | | test/transaction.test.js | commit/rollback, SQL errors, savepoint, same-worker binding | | test/concurrent.test.js | parallel reads/writes, concurrent transactions | | test/error.test.js | SQLiteError, closed DB, constraints |

License

MIT — see package.json.