# ⚙️ node-sqlite3

Asynchronous, non-blocking [SQLite3](https://sqlite.org/) bindings for [Node.js](http://nodejs.org/). Forked from TryGhost/node-sqlite3.

![NPM Downloads](https://img.shields.io/npm/dm/%40appthreat%2Fsqlite3)
[![Latest release](https://img.shields.io/github/release/AppThreat/node-sqlite3.svg)](https://www.npmjs.com/package/@appthreat/sqlite3)

# Features

- Straightforward query and parameter binding interface
- Full Buffer/Blob support
- Extensive [debugging support](https://github.com/AppThreat/node-sqlite3/wiki/Debugging)
- [Query serialization](https://github.com/AppThreat/node-sqlite3/wiki/Control-Flow) API
- [Extension support](https://github.com/AppThreat/node-sqlite3/wiki/API#databaseloadextensionpath-callback), including bundled support for the [json1 extension](https://www.sqlite.org/json1.html)
- Big test suite
- Written in modern C++ and tested for memory leaks
- Bundles SQLite v3.53.4, or you can build using a local SQLite [amalgamation](https://www.sqlite.org/amalgamation.html)

# Compared with `node:sqlite`

Node ships a built-in SQLite module. It has grown a lot and now covers
most of what a synchronous driver needs, so the honest summary is: **if
your workload is synchronous and fits `node:sqlite`, use `node:sqlite`**
— nothing needs installing and nothing needs compiling. This package
exists for the parts it does not cover.

Verified against `@appthreat/sqlite3` 9.0.2 on **Node v24.18.0 and
v26.7.0**, which expose an identical `node:sqlite` surface and behave
identically on every point below — so this table holds across the whole
range this package supports. `node:sqlite` did grow quickly during 24.x
(`backup` and `createTagStore` among the later additions) and is still
gaining features, so on an older 24.x patch release, or a newer Node
than the two above, check
[the `node:sqlite` docs](https://nodejs.org/api/sqlite.html) before
relying on a ❌ below.

## What only this package has

| Capability                                   | `@appthreat/sqlite3`                                            | `node:sqlite`                            |
| -------------------------------------------- | --------------------------------------------------------------- | ---------------------------------------- |
| Asynchronous API (event loop stays free)     | ✅ callbacks + promises                                         | ❌ synchronous only                      |
| `async` iteration (`for await`), streams     | ✅ `iterate`, `stream`                                          | ❌ (sync `iterate` only)                 |
| Worker-thread connection pool                | ✅ `pool()`                                                     | ❌                                       |
| Transaction helper with savepoints           | ✅ `transaction()`, reusable `createTransaction()`               | ❌ hand-rolled `BEGIN`/`COMMIT`          |
| Statement cache                              | ✅ `cacheStatements()`, implicit on sync                        | ❌ prepare per call                      |
| Custom collations                            | ✅ `collation()` / `removeCollation()`                          | ❌                                       |
| Incremental blob I/O                         | ✅ `openBlob()`                                                 | ❌ read/write whole values               |
| Update / commit / rollback / preupdate hooks | ✅ EventEmitter                                                 | ❌                                       |
| Progress handler                             | ✅ `progress()`                                                 | ❌                                       |
| Query cancellation                           | ✅ `cancellationToken()`, connection-wide                       | ❌                                       |
| WAL checkpoint control                       | ✅ `checkpoint()`                                               | ❌                                       |
| Schema introspection                         | ✅ `tableInfo()`, `columns()`, `parameterNames`                 | ⚠️ `columns()` only                      |
| Changeset utilities                          | ✅ concat / invert / iterate / **rebase** / `session.diff()`    | ⚠️ apply + create only                   |
| JavaScript virtual tables                    | ✅ `db.table()` generator tables + `db.values()` array tables   | ❌                                       |
| Atomic `batch()`                             | ✅ multi-statement, libsql-style modes                          | ❌                                       |
| `pragma()` / `explain()` helpers             | ✅ parsed rows, `EXPLAIN QUERY PLAN`                            | ❌                                       |
| SQL text dump (`.dump`)                      | ✅ `db.dump()`, streaming `iterdump()`                          | ❌                                       |
| Error token byte offset                      | ✅ `err.offset` on failed prepares                              | ❌                                       |
| `diagnostics_channel` query spans            | ✅ `subscribeQueries()` + the `sqlite.db.query` mirror          | ⚠️ `sqlite.db.query` only                |
| Backup control                               | ✅ handle you step yourself (`remaining`, `idle`, retry policy) | ⚠️ one-shot promise (`rate`, `progress`) |
| Integer read modes                           | ✅ `number` / `mixed` / `bigint`, per connection or statement   | ⚠️ `setReadBigInts()` per statement      |
| Electron support                             | ✅ tested in CI, main + utility process                         | ⚠️ works, untested by us                 |

## What both have

User-defined functions and aggregates (including window functions via
`inverse`) — on the async paths here through a worker round trip, and on
the synchronous fast path through a direct re-entrant call (see below) —
sessions and changesets, the authorizer, extension loading,
`serialize`/`deserialize`, incremental online backup with progress
reporting, `readOnly` and busy-timeout connection options, array row
mode, bare and unknown named-parameter control, tagged-template queries
(`createTagStore()` here is promise-native and carries the
`raw`/`join`/`identifier` composition helpers), `inTransaction`, and
extended result codes on errors. Both also **refuse to truncate** an
INTEGER outside the safe range rather than silently losing precision —
they differ only in how you opt into `BigInt`.

## What only `node:sqlite` has

| Capability                                                             | Why it matters                   |
| ---------------------------------------------------------------------- | -------------------------------- |
| **Zero install** — built in, no compiler, no prebuild, no supply chain | Usually the deciding factor      |
| `enableDefensive()` as a method                                        | ⚠️ this package has `dbConfig()` |

### The UDF story

`node:sqlite` runs SQLite on the main thread, so a JavaScript callback
runs inline while a query is stepping. This package runs asynchronous
queries on a worker, and a JS callback fires safely there — one blocking
round trip to the JS thread per call (a few microseconds; fine for
bounded-row logic, wrong for per-row bulk predicates). Since 9.1 the
**synchronous fast path runs UDFs directly**: on `getSync`/`allSync`/
`runSync` the JS thread is the one executing SQL, so the callback is
invoked re-entrantly on that same thread — exactly like `node:sqlite` —
with the one hard rule preserved (a UDF cannot drive *its own* statement
re-entrantly; other statements on the connection work). A throwing
callback surfaces through the step error with the thrown value as
`cause`, and JS collations/progress callbacks still refuse on the sync
path (they have no error channel).

The cost model in one line: **sync-path UDFs are direct calls; async-path
UDFs pay one worker→JS round trip each.**

Migrating from `node:sqlite` is mostly mechanical — `DatabaseSync` maps
to `Database` plus the `*Sync` methods — and since 9.1 there is a
drop-in shim: `import { DatabaseSync } from '@appthreat/sqlite3/compat'`
maps the `node:sqlite` surface onto this package's sync fast path (with
the documented divergences where a synchronous form cannot exist). See
[MIGRATING-TO-V9.md](MIGRATING-TO-V9.md) for the value-marshalling
differences, which are where the surprises live.

# Installing

Use whichever package manager you like:

```bash
npm install @appthreat/sqlite3
# or
pnpm add @appthreat/sqlite3
# or
yarn add @appthreat/sqlite3
```

On the Bun runtime use Bun's built-in `bun:sqlite` — this package's
addon needs N-API behaviour Bun 1.4 does not provide (see
[docs/install.md](docs/install.md#bun-install-works-runtime-does-not-yet)).

- GitHub's `master` branch: `npm install https://github.com/AppThreat/node-sqlite3/tarball/master`

Requires Node.js >= 24. See [docs/install.md](docs/install.md) for the full
installation guide: prebuild coverage, source builds, custom SQLite/SQLCipher,
and troubleshooting.

### Prebuilt binaries

`@appthreat/sqlite3` v6+ was rewritten to use [Node-API](https://nodejs.org/api/n-api.html), so a single prebuilt binary per platform covers every supported Node version — nothing is compiled or downloaded at install time for the platforms below:

- `darwin-arm64`
- `darwin-x64`
- `linux-arm64` (glibc and musl)
- `linux-x64` (glibc and musl)
- `win32-arm64`
- `win32-x64`

The prebuilds are bundled **inside the npm tarball** and resolved at
**runtime**, not by an install script. In particular, **pnpm 10+ users need no
`onlyBuiltDependencies` allowlist**: pnpm blocks dependencies' install scripts
by default, and that block is a no-op here because `lib/sqlite3-binding.js`
locates the prebuild itself when the module is first imported.

Support for other platforms and architectures may be added in the future if CI
supports building on them. Everywhere else, `@appthreat/sqlite3` builds from
source via `node-gyp` — see
[docs/install.md](docs/install.md#source-builds) for the toolchain
requirements and the pnpm specifics for source builds.

### Other ways to install

It is also possible to make your own build of `sqlite3` from its source instead of its npm package ([See below.](#source-install)).

SQLite's [SQLCipher extension](https://github.com/sqlcipher/sqlcipher) is also supported. [(See below.)](#sqlcipher-encrypted-databases)

## Electron

No rebuild, no `electron-rebuild`: v9 ships Node-API 10 prebuilds, and Node-API
is ABI-stable across runtimes — the same binary Node loads is the one Electron
loads. The minimum is **Electron 35** (the first major whose bundled Node
exposes Node-API 10; verified by loading the prebuild in Electron 35 and 44),
recorded in `engines.electron`. Source builds against Electron headers are only
needed for SQLCipher or a custom `sqlite_magic` — see
[docs/electron.md](docs/electron.md) for process placement (main vs. utility
process vs. preload), ASAR/`asarUnpack` configuration for electron-builder and
electron-forge, bundler externals, `userData` paths, and the SQLCipher rebuild
path.

# API

See the [API documentation](https://github.com/AppThreat/node-sqlite3/wiki/API) in the wiki.

# Usage

**Note:** the module must be [installed](#installing) before use.

This package is now ESM only.

```js
import sqlite3 from "sqlite3";
const db = new sqlite3.verbose().Database(":memory:");

db.serialize(() => {
  db.run("CREATE TABLE lorem (info TEXT)");

  const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  for (let i = 0; i < 10; i++) {
    stmt.run("Ipsum " + i);
  }
  stmt.finalize();

  db.each("SELECT rowid AS id, info FROM lorem", (err, row) => {
    console.log(row.id + ": " + row.info);
  });
});

db.close();
```

## Promises, async iteration and disposal (v9)

Every data method is dual-mode: pass a trailing callback for the classic
behaviour (returning `this`, chainable), or omit it to get a promise. `run`
resolves `{ lastID, lastIDBigInt, changes }`; `get`/`all`/`map` resolve the
rows; `exec`/`close`/`wait` resolve `undefined`. Errors carry the v9
`code`/`errno`/`primaryCode` triple.

```js
const db = await sqlite3.open(":memory:"); // promise-native open
await db.exec("CREATE TABLE lorem (info TEXT)");
const { lastID } = await db.run("INSERT INTO lorem VALUES (?)", "Ipsum 1");
const row = await db.get("SELECT * FROM lorem WHERE rowid = ?", lastID);
```

Stream large results with real backpressure — batches are pulled from
SQLite (64..1024 rows) only as fast as the consumer reads them:

```js
for await (const row of db.iterate("SELECT * FROM big")) { ... }
db.stream("SELECT * FROM big").pipe(someTransform);   // object-mode Readable
```

Transactions, cancellation and `await using` disposal:

```js
await db.transaction(async (tx) => {
  // ROLLBACK on throw, nested savepoints
  await tx.run("INSERT INTO lorem VALUES (?)", "Ipsum 2");
});

const rows = await db.all("SELECT * FROM big", { signal }); // AbortSignal:
// an already-aborted signal rejects before scheduling; aborting in flight
// interrupts the whole connection (a SQLite constraint) and rejects with
// the signal's reason.

await using db2 = await sqlite3.open("app.db"); // closed however the block exits
await using stmt = db2.prepare("SELECT 1"); // finalized the same way
```

`each()` stays callback-only — the async iterator is its promise-based
replacement. `db.backup()` keeps its synchronous return in every form.
`db.prepare()` returns the statement synchronously in its callback form; the
no-callback form returns the statement wrapped so that `await db.prepare(sql)`
resolves only once the prepare has completed and the introspection accessors
(`columns`, `parameterCount`, `parameterNames`, `readonly`) are populated —
and yields the statement itself. The wrapper still forwards every statement
method, so `db.prepare(sql).run(...)` chaining is unchanged.

## Performance options

Two opt-in fast paths avoid the per-call prepare and threadpool round-trip
costs. Both keep the default asynchronous behaviour untouched.

### Statement cache

```js
db.cacheStatements(); // or db.cacheStatements(16) to cap the LRU size
```

`run/get/all/each/map` then reuse prepared statements (LRU, keyed on the SQL
string, 64 entries by default). The cache is bypassed, falling back to a
per-call prepare, whenever ordering guarantees would otherwise be lost: under
`serialize()`, and while an exclusive operation (`exec`, `close`, `wait`,
`loadExtension`) is running or queued. `close()` finalizes cached statements.

A cached statement retains its most recently bound text/blob parameters until
it is rebound or finalized, so a large blob bound through a cached statement
stays resident while that entry lives in the cache.

### Synchronous fast path

```js
db.cacheStatements();
const row = db.getSync("SELECT * FROM t WHERE rowid = ?", 42); // row | undefined
const info = db.runSync("INSERT INTO t (a) VALUES (?)", 42); // { lastID, changes }
const rows = db.allSync("SELECT * FROM t");
const stmt = db.prepareSync("SELECT ? AS v"); // statement-level variants
// Bulk-reader row shape: one array per row, values in result-column order.
const flat = db.allSync("SELECT * FROM t", { rowMode: "array" });
```

`getSync`/`allSync` (not the async paths) accept a trailing
`{ rowMode: 'array' }` option: rows come back as arrays instead of
objects — duplicate column names keep every value instead of collapsing,
and the per-cell property stores disappear entirely, making it the
fastest row shape the sync paths can build. CSV export, ETL and bulk
feeds are the intended users; the default object shape is unchanged.
(A named bind parameter could never have the bare key `rowMode` — bind
keys carry a sigil — so the option is unambiguous.)

`getSync/runSync/allSync` execute on the calling thread. On the benchmark
suite (`pnpm run bench`, [docs/performance.md](docs/performance.md)),
cached single-row lookups are **8–12× faster** than the cached async
`get`/`run`
equivalents on arm64 macOS (10.4–11.8× for `getSync`, flat
from batches of 1 to 10,000; `runSync` 8.2× at one operation rising to
~11.5× at 10,000 as per-round overhead amortises) — and **22–31×** on
Linux, where the async threadpool round trip costs more. For large
result sets sync and async are level (20,000 rows × 4 cols measured
within the noise floor): the marshalling is the same work either way,
and it dominates the threadpool round trip. They throw when the
database is not fully idle: async work in flight or queued, or when called
from inside an async completion callback (defer with `setImmediate` or use
`db.wait`). They accept no callback argument. Like any synchronous database
API, a busy database file can block the event loop for up to the configured
`busyTimeout`.

These `Database`-level forms keep their own statement cache, so they do
not prepare and finalize a statement per call; that is automatic and
does not need `cacheStatements()`, which is opt-in and governs the
asynchronous calls.

### Scheduling change

The database queue is now strictly FIFO. Previously a non-exclusive call
could dispatch immediately while an exclusive one (`exec`, `close`, `wait`,
`loadExtension`) was still waiting in the queue, so it could overtake that
call and run concurrently with it — for example a write landing outside a
transaction opened by `exec("BEGIN")`. Code that implicitly relied on the
old queue-jumping behaviour may see operations complete in a different
order. Parallel throughput is unchanged: the queue is only non-empty once
something has had to wait.

## Value marshalling (v9)

### Integer modes

```js
db.getSync("SELECT COUNT(*) AS n FROM t").n; // number (default)
db.configure("integerMode", "mixed"); // or 'number' | 'bigint'
db.integerMode; // 'mixed'
```

Integers are stored as true 64-bit values on both the bind and the read
path, and `BigInt` parameters bind exactly. Reads follow the configured
mode:

| Mode                 | INTEGER columns and `lastID`                                                                     |
| -------------------- | ------------------------------------------------------------------------------------------------ |
| `'number'` (default) | `number` when safely representable, otherwise a `RangeError` — never a silently truncated double |
| `'bigint'`           | always `BigInt`                                                                                  |
| `'mixed'`            | `number` when safe, `BigInt` otherwise — recommended for anything touching `rowid`s              |

`Statement#lastIDBigInt` returns the last insert rowid as a `BigInt` in
every mode, so `'number'`-mode code can still read a large rowid without
switching modes.

### Accepted bind values

`string`, `number` (integral values within the int64 range bind as
INTEGER; the double `2**63` clamps to `2**63-1`), `bigint` (`RangeError`
outside the signed 64-bit range), `boolean` (0/1), `null` and `undefined`
(both NULL), `Date` (epoch milliseconds as REAL — documented, lossy in
type), `RegExp` (its source string), and any binary view: Node `Buffer`,
`Uint8Array`/`Float64Array`/… (byte range honoured), `DataView`
(byte range honoured), `ArrayBuffer`.

Everything else — plain objects, arrays, `Map`, class instances, symbols,
functions — throws a `TypeError` naming the parameter index and the
constructor. Bind the number of parameters the statement takes: too few
(previously silently NULL) and too many (previously ignored) are both
errors now, and a named parameter absent from the SQL (`sqlite3_bind_parameter_index`
returning 0) throws as well.

### Extended result codes

Errors carry three properties: `err.code` (the extended name, e.g.
`SQLITE_CONSTRAINT_UNIQUE`), `err.errno` (the extended number) and
`err.primaryCode` (the primary name, e.g. `SQLITE_CONSTRAINT`). The
`SQLITE_CONSTRAINT_*`, `SQLITE_BUSY_*`, `SQLITE_READONLY_*`,
`SQLITE_IOERR_*`, `SQLITE_CANTOPEN_*`, `SQLITE_LOCKED_*`,
`SQLITE_CORRUPT_*`, `SQLITE_ERROR_*`, `SQLITE_ABORT_ROLLBACK` and
`SQLITE_AUTH_USER` constants are exported, as are the previously missing
open flags `OPEN_NOMUTEX`, `OPEN_MEMORY` and `OPEN_EXRESCODE`.

## User-defined functions, aggregates and collations (v9)

```js
// Scalar functions — this makes WHERE x REGEXP ? work:
db.function("regexp", { deterministic: true }, (pattern, value) =>
  new RegExp(pattern).test(value) ? 1 : 0,
);

// Aggregates: start() builds an accumulator, step() folds a row into it,
// result() produces the value. Providing inverse makes it a window
// function usable with OVER (...).
db.aggregate("median", {
  start: () => [],
  step: (acc, v) => {
    acc.push(v);
    return acc;
  },
  result: (acc) => {
    acc.sort((a, b) => a - b);
    return acc.length ? acc[acc.length >> 1] : null;
  },
});
await db.get("SELECT median(salary) AS m FROM employees");

// Collations — ORDER BY, indexes, COLLATE:
db.collation("german", (a, b) => a.localeCompare(b, "de"));
await db.all("SELECT name FROM t ORDER BY name COLLATE german");

db.removeFunction("regexp");
db.removeCollation("german");
```

Arguments and return values use exactly the bind-marshalling rules above
(int64/BigInt, buffers for blobs, strict types: an unsupported return
value is an error, never a coerced string). Without `varargs: true` the
arity comes from the implementation's `length` (minus the accumulator for
aggregates), and calls with any other argument count are SQL errors.

Options: `deterministic` (required for index/generated-column use, and a
false claim corrupts results — opt-in), `directOnly` (default **true**:
schema SQL — triggers, views, CHECK constraints, index expressions —
cannot invoke the function; opt out explicitly), `innocuous`, `varargs`.
Window functions (aggregates with `inverse`) are registered through
`sqlite3_create_window_function`, which has no flag slot, so the flag
options do not apply to them.

### The threading model, and what it costs

SQLite invokes a function callback on whatever thread is executing the
statement — here a worker thread. Each call therefore makes a blocking
round trip to the JS thread: the worker marshals the arguments and waits
while the JS thread runs your function and posts the result back.

Measured cost (`pnpm run bench`, Apple Silicon, Node 26): **~18 µs per
call**. Consequences, with one decimal of honesty:

| Filtering 100,000 rows                 | Time     |
| -------------------------------------- | -------- |
| the predicate in SQL                   | 5 ms     |
| the predicate in JS after `all()`      | 25 ms    |
| the predicate in a JS function per row | 1,830 ms |

A JS function called per row is the wrong tool for bulk filtering —
fetch and filter in JS (or write the predicate in SQL). A JS collation is
even sharper: sorting 100k rows costs O(N log N) round trips (~17 s).
Where they shine is pushing _logic_ into a query — a regexp, a domain
checksum, a custom aggregate over a bounded group. SQLite invokes a
scalar function per row and needs each result before the next row is
read, so there is no batched/array form: the per-call cost is
structural. [docs/performance.md](docs/performance.md#javascript-functions-the-crossover-to-fetch-and-filter)
works out the crossover — a few thousand candidate rows is where
fetch-and-filter overtakes a UDF predicate, measured at ~60× on a real
16k-row scan.

Two deliberate restrictions follow from the threading model:

- A JS function reached from a **synchronous method**
  (`getSync`/`runSync`/`allSync`/`prepareSync` steps) runs **directly**:
  the JS thread is the one executing SQL there, so the callback is
  invoked re-entrantly on that same thread — same-thread, no round
  trip, like `node:sqlite`. (Since 9.1; before that this refused.) The
  one rule: such a callback cannot drive *its own* statement
  re-entrantly — use another statement or the async API. Nor can it do
  anything that flushes the statement cache mid-step, since the cache
  holds the executing statement: registering or removing a function,
  aggregate, collation, virtual table or authorizer policy, clearing a
  tag store, or closing the connection all refuse with a message saying
  so. Do those before or after the query.
- While a JS **collation** is registered, the synchronous methods refuse
  to run entirely (remove it with `removeCollation()` or use the async
  API): a comparison would need the blocked JS thread, and unlike
  functions, a collation callback has no way to report an error.
  `db.withCollation(name, cmp, fn)` scopes a registration to the awaited
  body, registering before it runs and removing after — even when the
  body throws — so the sync methods are only gated for the block:

  ```js
  const rows = await db.withCollation(
    "locale",
    (a, b) => a.localeCompare(b, "de"),
    () => db.all("SELECT name FROM t ORDER BY name COLLATE locale"),
  ); // here the collation is removed and getSync() works again
  ```

Errors: a throwing callback surfaces as a `SQLITE_ERROR` whose message
names the function, with the original JS error attached as `err.cause`;
the connection stays usable. Registration and replacement are refused
with `SQLITE_BUSY` (reported on the connection's `'error'` event) while
a cursor is suspended mid-query; the statement cache is flushed on every
registration, replacement and removal, so no statement compiled against
the old implementation is handed back.

## Hooks, authorizer, progress and introspection (v9)

```js
// Transaction hooks. commit fires after the transaction commits — every
// change event of that transaction is delivered first, which is what
// makes the pair useful for cache invalidation. The hooks are
// observational: the commit (or rollback) has already happened when the
// listener runs, and no return value can veto it.
db.on("change", (type, database, table, rowid) => {
  /* ... */
});
db.on("commit", () => {
  /* ... */
});
db.on("rollback", () => {
  /* ... */
});

// WAL hook: fires after a commit appends frames to the WAL.
db.on("wal", (database, pages) => {
  /* ... */
});
```

A hook's native sqlite callback exists only while at least one listener
is registered — an installed-but-unused hook costs nothing. In WAL mode,
`db.checkpoint()` is the lever for keeping the WAL bounded:

```js
const { busy, logFrames, checkpointedFrames } = await db.checkpoint({
  mode: "truncate",
});
```

### Sandboxing SQL with the authorizer

```js
db.authorizer({
  default: "deny",
  allow: [{ action: sqlite3.SELECT }, { action: sqlite3.READ, table: "users" }],
});
db.authorizer(null); // remove
```

The policy is a rule list evaluated inside SQLite itself, in C++ — no
JavaScript runs on the prepare path, so it is fast and thread-safe by
construction. `deny` rules win over `allow` rules; a denied action fails
the statement with `SQLITE_AUTH` ("not authorized"). The ~35 action
constants (`sqlite3.SELECT`, `sqlite3.READ`, `sqlite3.INSERT`,
`sqlite3.ATTACH`, …) and the decisions (`sqlite3.DENY`,
`sqlite3.IGNORE`) are exported. The statement cache is flushed on every
policy change: a cached statement was compiled under the old policy and
would bypass the new one.

### Cancelling queries

```js
// The cancellation token: an atomic flag in a SharedArrayBuffer that
// the native progress handler polls. Zero JS cost per check, and
// cancel() works from any thread — post token.buffer to a Worker.
const token = db.cancellationToken();
db.all(longRunningSql).catch(() => {});
setTimeout(() => token.cancel(), 100);

// token.signal is a real AbortSignal, so the promise form rejects with
// your reason:
db.all(longRunningSql, { signal: token.signal }).catch((reason) => {});
```

Cancellation is connection-wide, like `db.interrupt()`: the abort
reaches every statement running on the connection. While a token exists,
each query pays one relaxed atomic load per `period` VM instructions
(default 1000) — within measurement noise in the benchmark suite.

One timing detail worth knowing: an `AbortSignal` rejection is delivered
as soon as the signal fires, which is *before* the interrupted statement
has finished unwinding on its worker. The connection is therefore not yet
idle at the moment the rejection is observed, so a synchronous method
called right there refuses with "database is busy: sync methods require a
fully idle database". `await db.wait()` (or any awaited query) drains the
teardown:

```js
try {
  await db.all(longRunningSql, { signal });
} catch (err) {
  await db.wait(); // the interrupted statement has finished unwinding
  db.allSync("SELECT 1"); // now the sync path is available again
}
```

A cancellation token's own rejection (`token.cancel()` with no signal)
arrives with the statement already unwound, so it needs no drain.

A JavaScript callback form exists for progress reporting —
`db.progress(10000, () => shouldStop)` calls the callback every 10,000
VM instructions and aborts the statement when it returns truthy — but
each invocation is a blocking round trip to the JS thread (the same
~18 µs class as JS functions), so it is for progress bars over long
queries, not per-row work. While it is registered, the synchronous
methods refuse to run (a callback would fire on the thread that must
service it). The token form has no such restriction.

### Statement and connection introspection

```js
const stmt = await db.prepare("SELECT name AS who FROM users WHERE id = $id", { $id: 1 });
stmt.readonly; // true — sqlite3_stmt_readonly
stmt.parameterCount; // 1
stmt.parameterNames; // ['$id'] — a fully positional statement (`?`) has
// no names at all and reports undefined; mixed statements keep null at
// every positional index so indices stay aligned
stmt.columns; // [{ name: 'who', declaredType: 'TEXT',
//    database: 'main', table: 'users', origin: 'name' }]
stmt.status(sqlite3.STMTSTATUS_FULLSCAN_STEP); // >0: the query scanned
// without an index

db.changes; // rows changed by the most recent statement (64-bit)
db.totalChanges; // every change since open (64-bit)
await db.tableInfo("users"); // column metadata incl. collation, defaults
await db.dbConfig(sqlite3.DBCONFIG_DEFENSIVE, true); // safe db_config switches
```

The statement accessors serve a snapshot taken when the statement was
prepared, so reading them never touches the sqlite handle and cannot
race a running query; `await db.prepare(sql)` resolves only after that
snapshot is published, so the accessors are populated at the first read.
Fields SQLite reports as absent (an expression
column has no origin, a typeless column no declared type) are omitted
rather than nulled. Integer modes apply to `changes`/`totalChanges` as
everywhere else. `tableInfo` runs a `PRAGMA table_info`, so a
deny-by-default authorizer must allow `sqlite3.PRAGMA`.

## Sessions, changesets and the preupdate event (v9)

A session records every INSERT, UPDATE and DELETE made through the
connection (tables need a primary key to be recordable), and harvests
them as a changeset — a `Uint8Array` you can store, ship, or apply to
another connection:

```js
const session = db.session({ table: "users" }); // or every table
await db.run("UPDATE users SET name = ? WHERE id = ?", "x", 1);
const changeset = await session.changeset(); // Uint8Array
const patchset = await session.patchset(); // new rows only, smaller
await session.close();

await target.applyChangeset(changeset, { conflict: "replace" });
await target.applyChangeset(changeset, {
  // the fully general form — runs per conflict:
  conflict: (info) => (info.conflict === "notFound" ? "omit" : "replace"),
  filter: (table) => table !== "audit",
});

for (const op of sqlite3.iterateChangeset(changeset)) {
  console.log(op.op, op.table, op.oldRow, op.newRow);
}
const inverse = sqlite3.invertChangeset(changeset); // undoes the apply
const both = sqlite3.concatChangeset(a, b); // a then b
```

### Rebasing (9.1): the fork-free sync loop

Apply with `{ rebase: true }` to harvest a **rebase buffer** — the
record of which conflicting changes this database omitted or replaced —
then rebase the changesets you recorded *before* that apply, so they
land upstream without anyone resolving the same conflicts twice.
Together with `session.diff()` (a changeset of the differences between
an attached database's table and this one, without recording anything)
this is a complete offline-first sync toolkit on stock SQLite; no other
JS driver has rebasing (rusqlite is the only binding anywhere):

```js
// This database is at S0 and records its own work (S0 → S1):
const session = db.session({ table: "t" });
await db.run("UPDATE t SET v = ? WHERE id = ?", "mine", 1);
const local = await session.changeset();
await session.close();

// A changeset based on S0 arrives from the peer. Apply it *here*,
// resolving conflicts, and keep the record of those resolutions:
const rebase = await db.applyChangeset(incoming, {
  conflict: "omit", // this database's row wins
  rebase: true, // resolves the rebase buffer (null if no conflicts)
});

// Rebasing rewrites the local changeset's old values to the ones the
// peer holds, so pushing it needs no conflict handling at all:
await peer.applyChangeset(sqlite3.rebaseChangeset(local, rebase));
```

The matching rule, because it is easy to get backwards: the rebaser
finds a buffer entry **by primary key** and rewrites the change's
`old.*` values to the values the buffer carries (for an omitted remote
UPDATE, the values that remote left in place). It does not check when
the change was recorded — so a changeset recorded *after* the apply is
rewritten just the same, and its old values then describe a state the
peer has already moved past. **One buffer belongs to the changesets
recorded before its apply**; later work gets its own round of the loop.
The direction matters too: the buffer must come from the apply performed
on the database whose changeset you are rebasing.

`session.diff('t', 'other')` records the changes that transform the
attached database `other`'s table into this connection's — the
"what changed between these two databases" primitive for verification
and sync tooling.

`applyChangeset` wraps the apply in one savepoint: either every change
lands or the whole apply rolls back. `conflict` decides what happens on
a collision — `'abort'` (the default) rolls back, `'omit'` skips the
change, `'replace'` overwrites the row — or a function returning one of
those per conflict. The function form is a blocking round trip from the
applying thread (like a user-defined JS function), so it must not use
the synchronous methods on that connection.

The `'preupdate'` event fires for every write with the row's before and
after values — the old values `change` events cannot give you:

```js
db.on("preupdate", ({ op, table, rowid, oldRowid, oldRow, newRow }) => {
  audit.log(op, table, oldRow, newRow);
});
```

`oldRowid` and `rowid` differ exactly on a rowid-changing update.
One preupdate hook exists per connection and is shared with the session
machinery, so a session and a `'preupdate'` listener cannot coexist on
one connection — attempting either direction fails loudly instead of
silently stopping the other.

## In-memory snapshots: serializeToBytes / deserializeFromBytes (v9)

The whole database as bytes — snapshotting, shipping a prebuilt
database, fast fixtures, moving a database between threads:

```js
const bytes = await db.serializeToBytes(); // Uint8Array snapshot
const copy = await sqlite3.deserializeFromBytes(bytes, {
  readonly: false,
  resizable: true,
});
```

`serializeToBytes` returns the exact bytes a file copy would contain
(the FIFO-ordering `db.serialize()` keeps its old meaning). The
snapshot includes every committed transaction — serialization reads
through the pager, and the pager reads through the WAL — and for a WAL
database the returned bytes are rewritten to rollback-journal format,
so the output always round-trips through `deserializeFromBytes` (a
deserialized copy has no `-wal` file and could not open a WAL-format
image demanding recovery). The live database's journal mode is
untouched. The bytes are named deliberately: overloading `serialize()`
would be the worst API decision available. `deserializeFromBytes`
**copies** into SQLite-owned memory — handing a JS buffer to SQLite
directly is a use-after-free waiting to happen — and rejects corrupt
input with `SQLITE_NOTADB` rather than crashing later.

## Incremental blob I/O (v9)

Reading a 500 MB blob as one value materialises it as a single buffer;
`openBlob` gives you a handle that streams it instead:

```js
const blob = await db.openBlob({ table: "files", column: "data", rowid: 1 });
const chunk = new Uint8Array(65536);
const n = await blob.read(chunk, 0); // n bytes at blob offset 0
await blob.write(source, 4096); // write at an offset
blob.size; // sqlite3_blob_bytes

await pipeline(blob.createReadStream(), fs.createWriteSink(path));
await pipeline(fs.createReadStream(path), blob.createWriteStream());
await blob.close();
```

Streams read and write in chunks (default 64 KiB), so memory stays flat
regardless of the blob's size. Any write to the row invalidates open
handles with `SQLITE_ABORT` (and a message saying so); an aborted handle
cannot be reopened — close and open a fresh one — while `blob.reopen(
rowid)` cheaply re-aims a _healthy_ handle at another row. The blob
cannot grow through the handle: size the column first (e.g.
`UPDATE ... SET data = zeroblob(n)`) and then stream into it. Writing
through a blob handle surfaces as a `'preupdate'` delete event (the new
values are not yet available inside `sqlite3_blob_write`).

## Ergonomics, virtual tables and migrations (9.1)

The sync-first drivers make a set of small things trivial; 9.1 adds the
whole bundle, promise-native:

```js
// pragmas with parsed results (the recommended way to run them)
await db.pragma("journal_mode = WAL");
const version = await db.pragma("user_version", { simple: true });

// the query planner's own account, without executing
const plan = await db.explain("SELECT * FROM users WHERE id = ?");

// atomic multi-statement batches (migrations, seeding)
await db.batch([
  "CREATE TABLE t (a)",
  { sql: "INSERT INTO t VALUES (?)", args: 1 },
]);

// reusable transactions with begin-mode variants
const move = db.createTransaction((tx, from, to, n) => /* ... */);
await move.immediate(1, 2, 50);

// .dump-style SQL export (streaming form: sqlite3.iterdump(db)) —
// AUTOINCREMENT counters, user_version and virtual-table content included
const sqlText = await db.dump();

// live state
db.inTransaction; // true inside BEGIN
db.txnState;      // 'none' | 'read' | 'write'
db.limits;        // the run-time limits
db.status("cacheHit"); // { current, highwater }
db.location();    // the attached file's path

// array/pluck row modes on the async paths too
const ids = await db.all("SELECT id FROM t", { rowMode: "pluck" });

// failed prepares carry the failing token's byte offset
try { db.prepareSync("SELECT * FRUM t"); }
catch (err) { err.offset; } // 9

// per-statement integer mode
const stmt = db.prepareSync(sql, { integerMode: "bigint" });

// connection-free namespace helpers
sqlite3.complete("SELECT 1;"); // true — a complete statement (REPL input)
sqlite3.compileOptions(); // ['ENABLE_FTS5', 'ENABLE_SESSION', …]
```

**JavaScript virtual tables** — generator-computed, read-only, working
from both the async paths (rows are pulled in batches through the worker
round trip) and the sync methods (direct re-entrant calls):

```js
db.table("sequence", {
  columns: ["value", "count"],
  parameters: ["count"], // HIDDEN → a table-valued function's argument
  rows: function* (count) {
    for (let i = 0; i < count; i++) yield [i, count];
  },
});
await db.all("SELECT value FROM sequence(5)");
```

Rows are pulled as the query consumes them (64 at first, growing to
1024), so an **unbounded generator is fine** — `SELECT … LIMIT 3` over an
infinite sequence stops after the first batch, and a table larger than
memory streams. A scan that stops early leaves the generator suspended
and never resumes it, so generator `finally` blocks are not a place to
release resources. An unconstrained HIDDEN parameter reaches the
generator as `undefined`; one the query constrained is also reported as
that column's value, so `SELECT count FROM sequence(5)` works without the
generator echoing it.

A parameter is a real (hidden) column, and `sequence(5)` is exactly
`WHERE count = 5` — **SQLite re-checks that predicate against every row
the generator produces**, rather than trusting the generator to have
applied it. So a row either leaves the parameter's column NULL — filled
with the argument, as above — or echoes the argument **as it was
received**: parameter columns carry no affinity (like every other column
here), so an echoed `String(5)` is the text `'5'`, which does not equal
the integer `5`, and the row is filtered out. A row reporting anything
else in that column contradicts the `WHERE` clause the argument came from
and is filtered out too; put unrelated output in its own column. The
generator is still free to pre-filter for speed, and should: a generator
that ignores a constraint it cannot satisfy again produces an endless
scan (correct, but unbounded — `LIMIT`, or a cancellation token on the
async path, is the stop). A row shorter than `columns` pads with NULL, so
a mis-ordered `yield` shows up as NULLs rather than an error, and a
throwing generator fails the query with a message naming the table and the
thrown value attached as `err.cause`.

`db.values(array)` exposes any JS array as a queryable table — the
rusqlite `rarray()` ergonomics no JS driver had: `JOIN` against
in-memory data instead of building IN-lists. `drop()` the handle when you
are done (anonymous registrations are capped at 32 per connection, the
oldest being dropped to make room).

**Tagged templates** — node:sqlite's tag store, promise-native, with
the composition helpers ORMs need (the ones Bun notably lacks):

```js
const store = db.createTagStore();
const table = store.identifier("users");
await store.all`SELECT * FROM ${table} WHERE id = ${id}`;
await store.all`SELECT * FROM ${table} WHERE id IN (${store.join(ids)})`;
```

Every interpolated value binds as a parameter unless it came from
`raw`/`identifier`/`identifierPath`/`join`/`empty` — a look-alike object
(`{ text, params }` out of `JSON.parse`) binds like anything else rather
than becoming SQL. `join()` takes values as well as fragments, which is
what an IN-list of user data needs. Creating a store turns the connection
statement cache on if it was off, since reusing statements is the point.

**Migrations** — `PRAGMA user_version`-based, sequential, each in one
transaction; from a directory of `NNN-name.sql` files or a list:

```js
await sqlite3.migrate(db, "migrations/");
```

**Observability** — finished-statement spans on diagnostics channels,
armed only while subscribed:

```js
const unsubscribe = sqlite3.subscribeQueries(({ sql, durationMs }) => {
  apm.record(sql, durationMs); // also mirrored to node's sqlite.db.query
});
```

Spans are delivered **asynchronously**: SQLite reports a statement's
timing on the thread that ran it, and the span crosses to the JS thread
through a queue drained on a later event-loop turn — so right after
`await db.all(sql)` the span for that query has usually not arrived yet.
`sqlite3.flushQuerySpans()` delivers everything pending, synchronously,
which is what a test or a shutdown flush wants; `unsubscribe()` drains
first as well, so nothing recorded before it is lost.

```js
const spans = [];
const stop = sqlite3.subscribeQueries((s) => spans.push(s.sql));
await db.all("SELECT 1");
sqlite3.flushQuerySpans(); // spans === ['SELECT 1']
stop();
```

Spans are per module instance, so `pool()` traffic is invisible here: a
pool's queries run in workers, each with its own copy of this module and
its own channels ([docs/concurrency.md](docs/concurrency.md#the-pool)).

**node:sqlite drop-in** — code written against the built-in module can
switch without rewriting:

```js
import { DatabaseSync } from "@appthreat/sqlite3/compat";
const db = new DatabaseSync(":memory:"); // opens synchronously
db.exec("CREATE TABLE t (a)");
const row = db.prepare("SELECT * FROM t WHERE a = ?").get(1);
```

The shim maps onto the sync fast path (re-entrant UDFs included) and
exposes the full async surface through `db.native`. The places a
synchronous form cannot exist keep this package's async signatures and
are listed at the top of [lib/compat.js](lib/compat.js): sessions,
`serialize`, and `close()`, which finalizes the statements it prepared
(as `node:sqlite` does) and then *starts* the close rather than
completing it (`await using`, or `await db.native.close()`, when a caller
must know the file is free — deleting or reopening it on Windows).
`StatementSync.iterate()` materialises its rows, because the sync path has
no mid-cursor suspension.

## Worker threads and the connection pool (v9)

The addon is context-aware: it loads cleanly in every `worker_threads`
worker, and each environment gets its own constructors. Two supported
ways to use it from workers — plus the pool, which is the batteries-
included version:

**Path handoff** — the worker opens its own connection to the same file
(WAL mode gives real read concurrency):

```js
const w = new Worker("./db-worker.js", {
  workerData: { filename: "app.db" },
});
```

**Bytes handoff** — move an in-memory database across threads with one
copy (`serializeToBytes()` → transfer → `deserializeFromBytes()`):

```js
const bytes = await db.serializeToBytes();
const movable = bytes.slice().buffer; // plain ArrayBuffer copy
w.postMessage({ bytes: movable }, [movable]);
// worker: await sqlite3.deserializeFromBytes(new Uint8Array(bytes))
```

**The pool** — one writer plus N read-only reader connections, each on
its own worker; writes queue instead of racing to `SQLITE_BUSY`:

```js
const pool = await sqlite3.pool("app.db", { readers: 4 });

const rows = await pool.read("SELECT * FROM t WHERE a = ?", [1]);
const one = await pool.get("SELECT b FROM t WHERE a = ?", [1]);
await pool.write("INSERT INTO t (b) VALUES (?)", ["hi"]);

await pool.transaction(async (tx) => {
  const row = await tx.get("SELECT a FROM t"); // pinned to the writer
  await tx.write("UPDATE t SET a = ?", [row.a + 1]);
});

await pool.close(); // drains, closes every connection, no worker survives
```

Queries accept `{ signal }` (cancellation crosses the thread boundary
through a shared-memory flag), errors keep `code`/`errno`/`primaryCode`,
and `await using pool` works. Rows are structured-cloned across the
boundary: blob columns come back as `Uint8Array` (not `Buffer`) and huge
result sets pay a copy — the pool is for many small queries, not bulk
reads. See [docs/concurrency.md](docs/concurrency.md) for the full
picture: `serialize()`/`parallelize()` semantics, WAL, busy timeouts,
and when to use one connection, several, or the pool.

## Using with Kysely and Drizzle

Both major ORMs need only what this package already has — no separate
dialect package. For **Kysely** (async-native, the natural fit) a
working dialect is ~40 lines over one connection:
`acquireConnection`/`releaseConnection` hand out the database, the
transaction verbs are raw `BEGIN`/`COMMIT`/`ROLLBACK`, and Kysely's own
`SqliteAdapter`/`SqliteIntrospector`/`SqliteQueryCompiler` fill the
rest —
[examples/kysely-dialect.mjs](examples/kysely-dialect.mjs) is a
zero-dependency template:

```js
import { kyselyFor } from "./examples/kysely-dialect.mjs";
const kysely = kyselyFor(db, {
  onCreateConnection: (c) => c.pragma("journal_mode = WAL"),
});
const rows = await kysely.selectFrom("users").selectAll().execute();
```

For **Drizzle** (whose SQLite dialect is synchronous), map onto the
sync fast path: `prepare` → `db.prepareSync`, `run`/`all`/`get` → the
`*Sync` methods, `transaction` → `db.transaction(fn, { mode: "immediate" })`.
Drizzle's better-sqlite3 driver is ~100 lines; the deltas are those
three substitutions plus reading `lastInsertRowid` from the run result.

## Source install

To skip searching for pre-compiled binaries, and force a build from source, use

```bash
npm install --build-from-source
```

The sqlite3 module depends only on libsqlite3. However, by default, an internal/bundled copy of sqlite will be built and statically linked, so an externally installed sqlite3 is not required.

If you wish to install against an external sqlite then you need to pass the `--sqlite` argument to `npm` wrapper:

```bash
npm install --build-from-source --sqlite=/usr/local
```

If building against an external sqlite3 make sure to have the development headers available. Mac OS X ships with these by default. If you don't have them installed, install the `-dev` package with your package manager, e.g. `apt-get install libsqlite3-dev` for Debian/Ubuntu. Make sure that you have at least `libsqlite3` >= 3.6.

Note, if building against homebrew-installed sqlite on OS X you can do:

```bash
npm install --build-from-source --sqlite=/usr/local/opt/sqlite/
```

## Custom file header (magic)

The default sqlite file header is “SQLite format 3”. You can specify a different magic, though this will make standard tools and libraries unable to work with your files.

```bash
npm install --build-from-source --sqlite_magic=”MyCustomMagic15”
```

Note that the magic _must_ be exactly 15 characters long (16 bytes including null terminator).

## SQLCipher (encrypted databases)

SQLCipher is supported via a **source build** — no prebuild ships with
SQLCipher, because the encryption runtime must come from your system's
SQLCipher. Build flags, Homebrew/Linux paths and the Electron variant are
in [docs/security.md#sqlcipher](docs/security.md#sqlcipher).

### Custom builds and Electron

The default build needs **no Electron-specific step at all**: v9 ships Node-API
10 prebuilds and Node-API is ABI-stable across runtimes, so the prebuild loads
in Electron >= 35 unchanged (see [Electron](#electron) above and
[docs/electron.md](docs/electron.md)).

Running a **source** build (SQLCipher, custom `sqlite_magic`) against Electron
headers needs extra flags for `npm install sqlite3 --build-from-source`
(replace the target with your Electron version):

```bash
--runtime=electron --target=44.0.0 --dist-url=https://electronjs.org/headers
```

The SQLite location and library name go through `GYP_DEFINES`, not
command-line flags — node-gyp 13 treats anything after `--` as a
build-file name. For macOS with Homebrew:

```bash
export GYP_DEFINES="sqlite=$(brew --prefix) sqlite_libname=sqlcipher"
npm install @appthreat/sqlite3 --build-from-source \
    --runtime=electron --target=44.0.0 --dist-url=https://electronjs.org/headers
```

SQLCipher needs the session extension enabled, which packaged builds
usually omit — see [docs/security.md](docs/security.md#sqlcipher).

# Security

The security posture — what this package does and does not protect
against, the Node `--permission` interaction (and how the checks refuse
out-of-scope file access), the `untrusted: true` recipe for hostile
database files, extension-loading policy, and the vendored-SQLite CVE
policy — is documented in [docs/security.md](docs/security.md).
Vulnerability reporting is in [SECURITY.md](SECURITY.md).

# Testing

```bash
pnpm run test
```

# Developing

Development of this repo itself requires **pnpm >= 11** (`corepack enable`, or a
standalone install). Clone, then:

```bash
pnpm install          # also builds the native binding via the install script
pnpm run rebuild      # recompile after changing C++ (node-gyp rebuild)
pnpm run lint         # biome check --write (autofix; CI runs lint:check)
pnpm run test         # node:test, 20s per-test timeout, files run in parallel
pnpm run prebuild     # produce the shipping prebuilds/ artifacts
pnpm run test:electron # the full suite + app-env harness inside Electron
pnpm run test:matrix   # the suite across glibc/musl containers (needs Docker)
```

`test:matrix` exists for the failures that do not reproduce on a developer
machine — a musl-only segfault, or a race that needs an older glibc, a
specific Node and a busy CPU before it shows up at all:

```bash
node tools/test-matrix.mjs --list          # the targets and why each exists
node tools/test-matrix.mjs --cpus=1 --load=6   # simulate a slow CI runner
node tools/test-matrix.mjs --repeat=20 --cmd='node --test test/foo.test.js'
```

It rebuilds the addon and regenerates fixtures inside each container,
ignoring your local `node_modules/`, `build/`, `prebuilds/` and `test/tmp/`,
so a result does not depend on working-tree leftovers.

Always use `pnpm run rebuild`, never bare `pnpm rebuild` — the latter is a
pnpm builtin that rebuilds _dependencies_, not this repo's `rebuild` script.
See [docs/install.md](docs/install.md#development) for the full guide,
including the stale-`prebuilds/` trap when iterating on C++.

# Copyright & license

Copyright (c) 2013-2025 Mapbox & Ghost Foundation
Copyright (c) 2025-2026 Team AppThreat

`@appthreat/sqlite3` is a fork of node-sqlite3 and is
[BSD-3-Clause licensed](https://github.com/AppThreat/node-sqlite3/raw/master/LICENSE),
the same terms as the original. The vendored SQLite amalgamation is public
domain. See [LICENSE](LICENSE) for the full text and the attribution of prior
work.
