Built-in SQLite

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Node.js 26 (26.10.0, released 2026-09-21, the Current line that becomes LTS on 2026-10-28), with the differences of Node.js 24 "Krypton" (24.21.0, Active LTS) and Node.js 22 "Jod" (22.23.3, Maintenance) called out; versions and stability levels were checked against the official documentation on 2026-10-03, and every example below was run on Node.js 26.10.0 and, unless the text says otherwise, on 24.21.0 and 22.23.3 as well. The node:sqlite module embeds the SQLite engine in the Node.js binary, so a program can store relational data in a single file (or in memory) with no server, no driver to install and no native build step. It is Stability 1.2 - Release candidate in the Node.js 26 documentation: it became a release candidate in v25.7.0, it no longer needs a flag since v23.4.0 and v22.13.0, and it was added in v22.5.0. Release candidate means the shape of the API is considered settled, but it can still change before it becomes Stable, so this page labels what changed in each line. The module is available only through the node: scheme (import …​ from 'node:sqlite'; the bare name sqlite does not resolve). The running scenario is the Bookshelf application that grows across this section: this page gives it a durable store, and Databases from Node.js covers the networked databases it can graduate to.

The stability and version map

Table 1. APIs covered on this page
API Stability (Node.js 26 docs) Version notes

node:sqlite module

1.2 Release candidate

Added in v22.5.0; no flag since v23.4.0 and v22.13.0; release candidate since v25.7.0. Node.js 22 still prints an ExperimentalWarning when the module is loaded (observed on 22.23.3); 24 and 26 print none.

DatabaseSync constructor, exec(), prepare(), close()

Release candidate

path accepts Buffer and URL since v23.10.0 and v22.15.0; timeout option since v24.0.0 and v22.16.0; more options since v24.4.0 and v22.18.0; defensive option since v25.1.0 and v24.12.0, on by default since v25.5.0 and v24.14.0.

StatementSync: run(), get(), all(), iterate()

Release candidate

iterate() since v23.4.0 and v22.13.0; typed arrays and DataView as parameters since v23.7.0 and v22.14.0; booleans and ArrayBuffer parameters since v26.8.0; undefined binds to NULL since v26.10.0.

statement.columns(), isTransaction, isOpen, location()

Release candidate

columns() since v23.11.0 and v22.16.0; isOpen since v23.11.0 and v22.15.0; isTransaction and location() since v24.0.0 and v22.16.0.

database.function(), database.aggregate()

Release candidate

function() since v23.5.0 and v22.13.0; aggregate() since v24.0.0 and v22.16.0.

database.setAuthorizer()

Release candidate

Added in v24.10.0; not available on Node.js 22.

database.createSession(), applyChangeset(), Session

Release candidate

Added in v23.3.0 and v22.12.0.

sqlite.backup()

Release candidate

Added in v23.8.0 and v22.16.0; returns a promise.

database.serialize(), database.deserialize()

Release candidate

Added in v26.1.0; not available on 24 or 22.

database.createTagStore() (SQLTagStore)

Release candidate

Added in v24.9.0; not available on 22. size is a getter since v25.5.0 and v24.13.1.

statement.close(), statement[Symbol.dispose](), statement.stat(), statement.resetStats()

Release candidate

Added in v26.8.0. database[Symbol.dispose]() is older (v23.11.0 and v22.15.0, no longer experimental since v24.2.0).

database.limits, database.loadExtension()

Release candidate

limits since v25.8.0; loadExtension() and enableLoadExtension() since v23.5.0 and v22.13.0.

sqlite.constants

Release candidate

Added in v23.5.0 and v22.13.0.

What it is and how Node.js implements it

SQLite is a self-contained, serverless SQL database engine that keeps a whole database in one ordinary file. It runs inside your process as a library, so there is no network hop, no authentication to configure and no separate service to deploy. Node.js bundles a copy of the C library (the module wraps functions such as sqlite3_prepare_v3() and sqlite3_step(); the documentation names the C function each method wraps) and exposes it through C++ bindings to a small JavaScript layer in lib/sqlite.js. The two classes you meet are DatabaseSync, one connection to a database, and StatementSync, one prepared statement. The Sync suffix is the key design fact: every method runs synchronously on the main thread and blocks the event loop until SQLite finishes. There is no promise or callback API; the documentation states that all APIs exposed by DatabaseSync and StatementSync execute synchronously (with one exception, sqlite.backup(), which returns a promise).

That is acceptable because a local file read or write is typically fast, and because an in-process call avoids the per-query latency of a network round trip. It becomes a problem for slow queries or big result sets, for which the page on Don’t Block the Event Loop explains the cost and the section on Worker Threads shows how to move such work to another thread (each thread opens its own connection).

flowchart LR A[Your JavaScript] -->|prepare / run / get / all| B[node:sqlite JS layer] B --> C[C++ bindings] C --> D[SQLite library in the node binary] D --> E[(bookshelf.db file)] D --> F[(bookshelf.db-wal write-ahead log)] A -. blocks the event loop until the call returns .-> D

A first program: open, create, insert, query

Open a database with new DatabaseSync(path[, options]). The path is a file path, a Buffer, a URL, or the special name :memory: for a private in-memory database that disappears when the connection closes. Use exec() to run one or more SQL statements that return no rows (schema creation, pragmas, scripts read from a file), and prepare() to compile a statement that you will run with parameters.

Create a table, insert with run(), read with get(), all() and iterate()
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year INTEGER
  ) STRICT
`);

const insert = db.prepare('INSERT INTO books (title, author, year) VALUES (?, ?, ?)');
console.log(insert.run('Dune', 'Frank Herbert', 1965));
insert.run('Emma', 'Jane Austen', 1815);
insert.run('Ubik', 'Philip K. Dick', 1969);

const byId = db.prepare('SELECT * FROM books WHERE id = :id');
console.log(byId.get({ id: 1 }));
console.log(byId.get({ id: 99 }));

const recent = db.prepare('SELECT title, year FROM books WHERE year > ? ORDER BY year');
console.log(recent.all(1900));
for (const row of recent.iterate(1900)) console.log('row:', row.title);

recent.setReturnArrays(true);
console.log(recent.all(1900));
console.log(byId.columns().map((c) => c.name));
db.close();

Running it on Node.js 26 prints (the Object: null prototype lines are shortened here):

{ changes: 1, lastInsertRowid: 1 }
{ id: 1, title: 'Dune', author: 'Frank Herbert', year: 1965 }
undefined
[ { title: 'Dune', year: 1965 }, { title: 'Ubik', year: 1969 } ]
row: Dune
row: Ubik
[ [ 'Dune', 1965 ], [ 'Ubik', 1969 ] ]
[ 'id', 'title', 'author', 'year' ]

Source: Node.js API: SQLite (usage example and StatementSync methods)

The four execution methods differ only in what they hand back:

Table 2. The statement execution methods
Method Returns Use it for

run(…​)

{ changes, lastInsertRowid } (numbers, or BigInt when the statement reads BigInts)

INSERT, UPDATE, DELETE, DDL: statements that return no rows.

get(…​)

The first row as an object, or undefined if there is none

Lookups by key, count(*), any query where you want one row.

all(…​)

An array of row objects (empty when no rows match)

Small, bounded result sets.

iterate(…​)

An iterable iterator of row objects

Large result sets you want to process one row at a time; stop early with break.

Two behaviours surprise newcomers. Rows are objects with a null prototype (observed in the output above), so they have no toString() and row.hasOwnProperty does not exist; use Object.hasOwn(row, 'title') or spread them into a normal object. And get() returns undefined for no row while a NULL column is read as null, never undefined.

Binding parameters and type conversion

Always pass values as parameters, never by building SQL text from them. Parameters are either anonymous (?, bound in order from the arguments of run(), get(), all() or iterate(); ?NNN assigns an explicit index) or named ($name, :name or @name, bound from an object passed as the first argument). By default node:sqlite allows bare names in the object: { id: 1 } binds :id. Binding a key that the statement does not use throws ERR_INVALID_STATE unless unknown names are allowed (allowUnknownNamedParameters option or statement.setAllowUnknownNamedParameters(true)); using both $k and @k in one statement makes a bare name ambiguous and throws.

Table 3. How JavaScript values map to SQLite storage classes
SQLite JavaScript to SQLite SQLite to JavaScript

NULL

null or undefined

null

INTEGER

number, bigint or boolean (written as 1 and 0)

number by default, bigint with readBigInts or statement.setReadBigInts(true)

REAL

number

number

TEXT

string

string

BLOB

TypedArray, DataView, ArrayBuffer or SharedArrayBuffer

Uint8Array

An INTEGER outside the safe-integer range cannot be returned as a number: reading it throws ERR_OUT_OF_RANGE unless you ask for BigInts. Any other JavaScript type (an object, a Date, a Map) throws ERR_INVALID_ARG_TYPE; convert it first (store dates as ISO-8601 text or epoch integers, objects as JSON text).

A BigInt round trip and the binding errors, run on Node.js 26
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE t (n INTEGER)');
db.prepare('INSERT INTO t VALUES (?)').run(9007199254740993n);

try {
  db.prepare('SELECT n FROM t').get();
} catch (e) {
  console.log(e.code);                       // ERR_OUT_OF_RANGE
}
const s = db.prepare('SELECT n FROM t');
s.setReadBigInts(true);
console.log(s.get());                        // { n: 9007199254740993n }

try {
  db.prepare('SELECT $a').get({ b: 1 });
} catch (e) {
  console.log(e.code, e.message);            // ERR_INVALID_STATE Unknown named parameter 'b'
}

Source: Node.js API: SQLite (type conversion and binding parameters)

Binding undefined changed recently. In Node.js 26.10.0 it binds NULL; on Node.js 24.21.0 and 22.23.3 get(undefined) throws a TypeError with code ERR_INVALID_ARG_TYPE (observed). Booleans as parameters are documented as added in v26.8.0, so on older lines convert them to 1 and 0 yourself.

Parameterised queries and SQL injection

A prepared statement separates the SQL text, which SQLite compiles once, from the values, which are bound afterwards and are never parsed as SQL. That is what makes a hostile string harmless. The mistake to avoid is string interpolation into the SQL text:

String interpolation against a bound parameter, with a hostile title
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL)');
db.exec("INSERT INTO books (title) VALUES ('Dune'), ('Emma'), ('Ubik')");

const title = "x' OR '1'='1";                    // untrusted input, e.g. from a query string

// WRONG: the value becomes part of the SQL text, so it rewrites the query
console.log(db.prepare(`SELECT * FROM books WHERE title = '${title}'`).all());
// RIGHT: the value travels separately as a bound parameter
console.log(db.prepare('SELECT * FROM books WHERE title = ?').all(title));

Output on Node.js 26, 24 and 22 (identical): the interpolated query returns every row, the bound one matches no title.

[
  [Object: null prototype] { id: 1, title: 'Dune' },
  [Object: null prototype] { id: 2, title: 'Emma' },
  [Object: null prototype] { id: 3, title: 'Ubik' }
]
[]

Source: Node.js API: statement.all()

Values can be parameters; identifiers cannot. A table or column name chosen at run time (for example a sort column) must be checked against an allow-list in your code before it goes into the SQL text. The sqlite.db.query diagnostics channel and statement.expandedSQL show the SQL with values substituted in, which is for debugging only; do not execute that string.

Transactions

node:sqlite has no transaction() helper: you drive transactions with plain SQL through exec(). BEGIN opens one, COMMIT makes it durable, ROLLBACK discards it, and database.isTransaction reports whether you are inside one. Without an explicit transaction, every run() is its own implicit transaction, which for file databases means a disk sync per statement: wrapping a batch of inserts in one transaction is the single biggest performance win. BEGIN IMMEDIATE takes the write lock up front, which avoids a later SQLITE_BUSY error when a read transaction tries to upgrade to a write.

A reusable transaction helper with rollback on error
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE loans (id INTEGER PRIMARY KEY, book TEXT NOT NULL UNIQUE, member TEXT NOT NULL)');
const loan = db.prepare('INSERT INTO loans (book, member) VALUES (?, ?)');

function transaction(fn) {
  db.exec('BEGIN IMMEDIATE');
  try {
    const result = fn();
    db.exec('COMMIT');
    return result;
  } catch (err) {
    if (db.isTransaction) db.exec('ROLLBACK');
    throw err;
  }
}

transaction(() => {
  loan.run('Dune', 'ana');
  loan.run('Emma', 'ben');
});

try {
  transaction(() => {
    loan.run('Ubik', 'ana');
    loan.run('Dune', 'cleo');   // violates UNIQUE: the whole transaction rolls back
  });
} catch (err) {
  console.log(err.code, err.errcode, err.errstr);
}
console.log(db.prepare('SELECT count(*) AS n FROM loans').get());
console.log(db.isTransaction);
ERR_SQLITE_ERROR 2067 constraint failed
[Object: null prototype] { n: 2 }
false

Source: Node.js API: SQLite (database.exec(), database.isTransaction)

SQLite failures surface as an Error with code ERR_SQLITE_ERROR plus the SQLite numeric result code (errcode, here 2067, a unique constraint violation) and its text (errstr); these two properties are what the run above shows (observed, not described in the API page, so treat them as informational and branch on them with care). Because the API is synchronous, a transaction cannot be interleaved with another await`ed task on the same connection by accident: as long as the callback passed to a helper like the one above is synchronous, nothing else in your process can run between `BEGIN and COMMIT. Do not put an await inside such a callback.

Configuring the connection

The constructor options (all Release candidate) set defaults for every statement of the connection:

Table 4. DatabaseSync options
Option Default Effect

open

true

When false, the constructor does not open the database; call database.open() later.

readOnly

false

Opens read-only; the database must already exist. Writes fail with attempt to write a readonly database (observed).

enableForeignKeyConstraints

true

Foreign keys are enforced (SQLite itself defaults to off, node:sqlite turns it on).

enableDoubleQuotedStringLiterals

false

Accepts "text" as a string literal; leave off, it hides typos in identifiers.

allowExtension

false

Enables loadExtension(); see the security notes below.

timeout

0

Busy timeout in milliseconds: how long SQLite waits for a lock before failing. Set a few seconds for a file shared by several connections.

readBigInts, returnArrays

false

Defaults for statements: BigInt integers; rows as arrays instead of objects.

allowBareNamedParameters, allowUnknownNamedParameters

true, false

Named-parameter strictness described above.

defensive

true

Disables SQL features that can deliberately corrupt the file. On by default since v25.5.0 and v24.14.0 (the option was added in v25.1.0 and v24.12.0).

limits

SQLite defaults

Caps on string length, SQL length, columns, expression depth and more, useful when the SQL or data is not fully trusted. database.limits reads and sets them at run time since v25.8.0.

SQLite settings that are not constructor options go through PRAGMA statements with exec(). Two matter for almost every file-backed database. PRAGMA journal_mode = WAL switches to write-ahead logging, in which readers do not block the writer and the writer does not block readers; the mode persists in the file, and it does not apply to :memory: databases (the pragma reports memory, observed). WAL needs a filesystem with shared memory, so it is unsuitable for databases on network filesystems. PRAGMA synchronous = NORMAL is the usual companion setting in WAL mode. PRAGMA user_version is a free integer slot in the file header that is handy for schema versions, shown next.

Open the Bookshelf database with pragmas and a tiny migration runner using user_version
import { DatabaseSync } from 'node:sqlite';

const migrations = [
  'CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL) STRICT',
  'ALTER TABLE books ADD COLUMN year INTEGER',
  'CREATE INDEX books_author ON books (author)',
];

export function openBookshelf(path) {
  const db = new DatabaseSync(path, { timeout: 5000 });
  db.exec('PRAGMA journal_mode = WAL');
  db.exec('PRAGMA synchronous = NORMAL');
  const { user_version: current } = db.prepare('PRAGMA user_version').get();
  for (let v = current; v < migrations.length; v++) {
    db.exec('BEGIN');
    try {
      db.exec(migrations[v]);
      db.exec(`PRAGMA user_version = ${v + 1}`);   // v is our own loop counter, not user input
      db.exec('COMMIT');
    } catch (err) {
      db.exec('ROLLBACK');
      throw err;
    }
  }
  return db;
}

const db = openBookshelf(':memory:');
console.log(db.prepare('PRAGMA user_version').get());
[Object: null prototype] { user_version: 3 }

Source: SQLite: PRAGMA statements, Node.js API: SQLite

Calling the function twice on a file path applies each migration exactly once, because the stored user_version is read first. For anything beyond a handful of migrations use a migration tool; see Choosing a Migration Tool.

Tagged statements and the statement cache

Calling prepare() for every query wastes the compile step, and keeping a map of prepared statements by hand is boilerplate. database.createTagStore([maxSize]) (since v24.9.0) returns an SQLTagStore: a least-recently-used cache of prepared statements, keyed by the SQL text, which you use as a template-literal tag. The ${value} slots become bound parameters, not string interpolation. The store has run, get, all and iterate tags, size, capacity (default 1000 statements), db and clear().

Tagged statements: caching and injection safety, run on Node.js 26 and 24
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)');
const sql = db.createTagStore(100);

sql.run`INSERT INTO books (title, year) VALUES (${'Dune'}, ${1965})`;
sql.run`INSERT INTO books (title, year) VALUES (${'Emma'}, ${1815})`;

const minYear = 1900;
console.log(sql.all`SELECT title FROM books WHERE year > ${minYear}`);
console.log(sql.get`SELECT count(*) AS n FROM books`);
console.log(sql.size, sql.capacity);          // 3 100

const evil = "x'; DROP TABLE books; --";
console.log(sql.get`SELECT * FROM books WHERE title = ${evil}`);   // undefined: it is just data
console.log(sql.get`SELECT count(*) AS n FROM books`);              // { n: 2 }

Source: Node.js API: SQLite (database.createTagStore(), SQLTagStore)

The cache matches on the exact SQL text (case-sensitive, with placeholders in the same positions): a query that writes a literal 12345 where another used a slot is a different statement. Never put ? placeholders inside a tagged template, and never build the SQL with an untagged template literal passed to prepare(), where a substitution is plain string interpolation and therefore an injection hole.

Resource management: close, using and statement lifetime

Statements and connections hold native memory. Close a connection with database.close() (it throws if the database is already closed or if you call it from inside a user function, authorizer or query subscriber), or rely on using, which calls database[Symbol.dispose]() (a no-op when already closed). The explicit resource management syntax (using) needs a V8 that supports it: it parsed on Node.js 24 and 26 and was a SyntaxError on 22.23.3 (observed). Statements gained close() and Symbol.dispose only in v26.8.0 (on 24 a using statement fails with Symbol(Symbol.dispose) is not a function, observed); earlier lines finalise a statement when it is garbage collected.

Closing resources deterministically (Node.js 24 and later for the syntax; statement disposal needs 26.8)
import { DatabaseSync } from 'node:sqlite';

{
  using db = new DatabaseSync(':memory:');
  using stmt = db.prepare('SELECT 1 AS one');   // statement disposal: Node.js 26.8 and later
  console.log(stmt.get());                      // { one: 1 }
}                                               // stmt, then db, are closed here

Source: Node.js API: SQLite (database[Symbol.dispose](), statement.close())

User-defined functions, aggregates and the authorizer

SQLite can call back into JavaScript. database.function(name[, options], fn) registers a scalar SQL function; database.aggregate(name, options) registers an aggregate (and, when you also give inverse, a window function) with start, step and an optional result. Options such as deterministic (the function always returns the same output for the same input, which lets SQLite optimise), directOnly (not callable from triggers or views), useBigIntArguments and varargs map to SQLite function flags. The return value must be a valid SQLite value (see the type table), and undefined becomes NULL.

database.setAuthorizer(callback) (since v24.10.0) installs a callback that SQLite invokes whenever a statement is compiled and wants to read, write or change the schema; it returns SQLITE_OK, SQLITE_DENY (error) or SQLITE_IGNORE. It is a defence-in-depth tool for running partly trusted SQL. The authorizer must not touch the connection that invoked it (calling prepare() or exec() from inside it throws ERR_INVALID_STATE).

A scalar function, an aggregate and an authorizer that forbids DELETE
import { DatabaseSync, constants } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec("CREATE TABLE books (title TEXT, author TEXT); INSERT INTO books VALUES ('Dune','Frank Herbert'),('Ubik','Philip K. Dick')");

db.function('slug', { deterministic: true }, (s) => s.toLowerCase().replace(/[^a-z0-9]+/g, '-'));
console.log(db.prepare('SELECT slug(title) AS slug FROM books').all());   // dune, ubik

db.aggregate('longest', {
  start: '',
  step: (acc, value) => (value.length > acc.length ? value : acc),
});
console.log(db.prepare('SELECT longest(author) AS a FROM books').get());  // { a: 'Philip K. Dick' }

db.setAuthorizer((action) =>
  action === constants.SQLITE_DELETE ? constants.SQLITE_DENY : constants.SQLITE_OK);
try {
  db.exec('DELETE FROM books');
} catch (e) {
  console.log(e.code, e.message);                                          // ERR_SQLITE_ERROR not authorized
}

Source: Node.js API: SQLite (database.function(), database.aggregate(), database.setAuthorizer())

On Node.js 22 the scalar function example works (the function() method was added in v22.13.0, aggregate() in v22.16.0), but setAuthorizer() does not exist before v24.10.0.

Sessions and changesets

The SQLite session extension records the changes made to a database so they can be replayed on another one. database.createSession([options]) (since v23.3.0 and v22.12.0) returns a Session that tracks all tables, or only options.table. session.changeset() returns a Uint8Array with every change since the session was created (it can be called repeatedly); session.patchset() returns a more compact form that omits the old values. target.applyChangeset(changeset[, options]) replays it and returns true when it was applied without being aborted. Two options control it: filter(table) decides which tables to apply, and onConflict(type) must return SQLITE_CHANGESET_OMIT (skip), SQLITE_CHANGESET_REPLACE (overwrite; only valid for data and primary-key conflicts) or SQLITE_CHANGESET_ABORT (roll back, the default). Typical uses are shipping a delta to a replica, offline-first synchronisation and auditing what a migration changed.

Record changes on a primary and replay them on a replica
import { DatabaseSync, constants } from 'node:sqlite';

const primary = new DatabaseSync(':memory:');
const replica = new DatabaseSync(':memory:');
for (const db of [primary, replica]) {
  db.exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)');
}

const session = primary.createSession({ table: 'books' });
const insert = primary.prepare('INSERT INTO books (title) VALUES (?)');
insert.run('Dune');
insert.run('Emma');

const changeset = session.changeset();                       // Uint8Array
const ok = replica.applyChangeset(changeset, {
  filter: (table) => table === 'books',
  onConflict: () => constants.SQLITE_CHANGESET_OMIT,
});
console.log(ok, replica.prepare('SELECT * FROM books').all());   // true, both rows

// Applying the same changeset again conflicts on the primary keys; OMIT skips them
console.log(replica.applyChangeset(changeset, { onConflict: () => constants.SQLITE_CHANGESET_OMIT }));  // true
session.close();

Source: Node.js API: SQLite (database.createSession(), database.applyChangeset()), SQLite: Session Extension

Both databases need the table to exist with the same schema; the changeset carries row changes, not DDL. session.close() releases the session (the session is also disposable since v24.9.0).

Backup, serialize and deserialize

sqlite.backup(sourceDb, path[, options]) (since v23.8.0 and v22.16.0, a function exported from the module, not a method) copies a live database to a file. It is the one asynchronous API of the module: it returns a promise that fulfils with the number of pages copied, so the event loop gets a turn between batches. options.rate is the number of pages per step (default 100), options.progress is called after each step with { totalPages, remainingPages }, and options.source and options.target name the attached databases (default 'main'). The source stays usable while the backup runs: writes from the same connection are reflected right away, whereas writes from another connection restart the backup. An existing destination file is overwritten.

Back up a WAL-mode database and open the copy read-only
import { DatabaseSync, backup } from 'node:sqlite';
import { mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const dir = mkdtempSync(join(tmpdir(), 'bookshelf-'));
try {
  const db = new DatabaseSync(join(dir, 'bookshelf.db'));
  db.exec('PRAGMA journal_mode = WAL');
  db.exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)');
  const ins = db.prepare('INSERT INTO books (title) VALUES (?)');
  db.exec('BEGIN');
  for (let i = 0; i < 50000; i++) ins.run(`Book ${i}`);
  db.exec('COMMIT');

  let steps = 0;
  const pages = await backup(db, join(dir, 'copy.db'), {
    rate: 5,                                   // pages per step: small, so the progress callback fires
    progress: ({ totalPages, remainingPages }) => {
      steps++;
    },
  });
  console.log('pages copied:', pages > 0, 'progress calls:', steps > 0);

  const copy = new DatabaseSync(join(dir, 'copy.db'), { readOnly: true });
  console.log(copy.prepare('SELECT count(*) AS n FROM books').get());     // { n: 50000 }
  try {
    copy.exec('DELETE FROM books');
  } catch (e) {
    console.log(e.code, e.message);              // ERR_SQLITE_ERROR attempt to write a readonly database
  }
  db.close();
  copy.close();
} finally {
  rmSync(dir, { recursive: true, force: true });
}

Source: Node.js API: SQLite (sqlite.backup()), Node.js API: File system (mkdtempSync, rmSync)

On all three lines the run printed pages copied: true progress calls: true. The progress callback is called after each backup step, so a small database that fits in one step may produce no progress calls at all (observed with 2000 rows and rate: 50); do not rely on an exact count. For an in-memory snapshot, database.serialize([dbName]) returns the whole database as a Uint8Array and database.deserialize(buffer[, options]) loads it into another connection (both since v26.1.0; neither exists on Node.js 24 or 22). Serialising a database that is in WAL mode and loading it into an in-memory connection failed with unable to open database file in this page’s trial (observed), so serialise databases that use the default journal mode, or use backup() for file databases.

Serialize and deserialize an in-memory database (Node.js 26.1 and later)
import { DatabaseSync } from 'node:sqlite';

const mem = new DatabaseSync(':memory:');
mem.exec("CREATE TABLE t (v TEXT); INSERT INTO t VALUES ('hello')");
const bytes = mem.serialize();
const clone = new DatabaseSync(':memory:');
clone.deserialize(bytes);
console.log(bytes instanceof Uint8Array, clone.prepare('SELECT v FROM t').get());   // true { v: 'hello' }

Source: Node.js API: SQLite (database.serialize(), database.deserialize())

Observing and limiting what SQLite does

Several smaller APIs help in production:

  • Tracing. The documentation says SQL trace events can be observed through the diagnostics_channel module on the sqlite.db.query channel. In a trial on Node.js 26 a subscriber received messages with sql, database and duration fields (observed; read the diagnostics_channel page for the contract), and it received nothing on 24.

  • Statement counters (Node.js 26.8 and later). statement.stat(counter) reads SQLite’s per-statement counters: 'fullscanStep', 'sort', 'autoindex', 'vmStep', 'reprepare', 'run', 'filterMiss', 'filterHit' and 'memused'; statement.resetStats() zeroes them. Asserting stat('fullscanStep') === 0 in a test is a cheap guard against a query that silently stopped using its index.

  • Limits and extensions. database.limits and the limits constructor option cap resource use. allowExtension: true enables loadExtension(path), which loads a shared library into the process and runs its native code with your privileges: enable it only for libraries you trust, and call enableLoadExtension(false) once you have loaded what you need (when the constructor option is false, extension loading cannot be enabled later).

  • Column metadata. statement.columns() returns name, column, table, database and type for each result column, useful to build generic table viewers or typed mappers.

One security observation: the Node.js permission model does not appear to gate SQLite’s own file access. In a trial on Node.js 26 with --permission --allow-fs-read set to the working directory and no --allow-fs-write, new DatabaseSync('./perm.db') still created the file and a table (observed; the JavaScript node:fs APIs are what --allow-fs-write governs, because SQLite opens the file itself in C). Do not treat the permission flags as a sandbox for database writes; restrict the process with operating-system or container permissions as well. See Permission Model for what the flags do cover.

Testing with SQLite

An in-memory database is the cheapest test fixture the platform offers: each test opens new DatabaseSync(':memory:'), runs the same migration function as production and is isolated from every other test, with no cleanup. The Bookshelf openBookshelf(':memory:') above is exactly that. The page on the Node.js test runner shows the node:test structure around it. In-memory databases do not support WAL and cannot be shared between connections, so tests that exercise concurrent access should use a temporary file instead.

When SQLite is the right tool, and when to move to a server database

SQLite is not a toy. Its own documentation lists embedded applications, devices, local data stores, small to medium websites and data-analysis files as good fits, and says client/server databases fit better for several situations. Use these criteria for a Node.js service:

Table 5. Choosing between node:sqlite and a server database
Question Stay on SQLite when Move to PostgreSQL (or another server database) when

How many processes write?

One process owns the file (a single Node.js process or a single worker thread doing the writes). WAL gives concurrent readers.

Several machines or many processes need to write at once; SQLite allows one writer at a time per database file.

Where is the data?

On a local disk of one machine (a CLI, a desktop tool, a single container with a volume).

The database must be reachable over the network, or the application scales horizontally across hosts; SQLite on a network filesystem is unsafe.

How big and how busy?

Up to modest sizes and request rates; reads dominate.

Heavy concurrent write load, multi-terabyte data, or workloads needing replication, fine-grained roles and online failover.

Does the loop stay free?

Queries are short and indexed; the blocking call is microseconds.

Slow analytical queries or large result sets would block the event loop; run them in a worker thread or on a server database.

What features are needed?

Plain SQL, JSON functions, transactions, a single-file backup.

Rich types, full-text and geospatial extensions, row-level security, logical replication, a managed service.

A path that works well is to start the Bookshelf on node:sqlite (prototypes, tests, CLI tools, single-instance deployments) and keep the data access behind a small module, so that swapping to PostgreSQL later changes one file. The SQL dialect differences are small for the simple queries used here, but not zero (type names, AUTOINCREMENT versus identity columns, ? versus $1 placeholders). The next page, Databases from Node.js, covers PostgreSQL, MongoDB and Redis clients, connection pools and the ORM landscape; for SQL itself see the SQL reference and Choosing the Right Database.

Pitfalls summary

Table 6. Common mistakes with node:sqlite
Mistake Consequence and fix

Building SQL with template-literal interpolation

SQL injection; bind parameters (?, :name, or a tag store).

Running a slow query on the main thread

Every other request waits; index the query, page the results with iterate() or LIMIT, or move it to a worker thread.

One transaction per row for bulk inserts

A disk sync per row; wrap the batch in BEGIN and COMMIT.

An await inside a BEGIN …​ COMMIT section

Other tasks run while the transaction is open and a failure leaves it dangling; keep the section synchronous.

Reading an integer above 2 to the power 53 without readBigInts

ERR_OUT_OF_RANGE; enable BigInt reads for that statement or store such ids as text.

Assuming serialize(), tagged statements or using on statements exist everywhere

Missing on Node.js 22 (and 24 for some); check the version map above.

Opening the same file from several processes without timeout

database is locked errors; set a busy timeout and use WAL mode.

Treating the module as Stable

The API is a release candidate: pin your Node.js version and read the release notes when upgrading.

What changed recently

Table 7. Notable node:sqlite changes by version
Version Change

v22.5.0

Module added behind --experimental-sqlite.

v23.4.0, v22.13.0

No longer behind the flag; iterate() added.

v24.0.0, v22.16.0

aggregate(), isTransaction, location(), setReturnArrays() and the timeout option.

v24.2.0

database[Symbol.dispose]() no longer experimental.

v24.9.0

createTagStore() and SQLTagStore; sessions become disposable.

v24.10.0

setAuthorizer().

v25.1.0, v24.12.0 and v25.5.0, v24.14.0

defensive option added, then enabled by default.

v25.7.0

The module becomes a release candidate.

v25.8.0

database.limits.

v26.1.0

serialize() and deserialize().

v26.8.0

statement.close(), Symbol.dispose on statements, stat(), resetStats(), the persistent option of prepare(), boolean and ArrayBuffer parameters; prepare() throws ERR_INVALID_ARG_VALUE for SQL with no statements; accessing the invoking connection from an authorizer now throws.

v26.10.0

undefined parameters bind to NULL.

Where the books are outdated

Node.js Projects (Jonathan Wexler) uses SQLite through Sequelize in its library-API project and again in its AI-assistant project, which means installing a third-party package and an ORM; for a prototype, a small service or a test fixture the built-in module removes both, at the price of writing SQL yourself and of a release-candidate API. Neither book covers node:sqlite, which was added in v22.5.0 and only lost its flag in v23.4.0 and v22.13.0, so treat any recommendation in them to install a separate SQLite package as dated for simple cases. Third-party drivers such as better-sqlite3 remain valid choices: they are stable, support additional features and run on older Node.js lines, which can matter if you cannot yet depend on a release-candidate core module.

References