Chapter 15 · Embedding SQLite in Applications
Node.js and JavaScript: Built-In/Driver Options and Async Boundaries
Use the current built-in node:sqlite API to build a small parameterized data layer, understand its synchronous execution model, manage transactions and resources, and separate JavaScript async architecture from SQLite’s single-writer engine behavior.
Learning outcomes
Node.js now has an official built-in SQLite module, node:sqlite. As of the current Node 26 documentation it is a release-candidate API (Stability 1.2), not yet a fully stable Stability-2 API. Its DatabaseSync and StatementSync operations execute synchronously. This lesson uses that official surface so the code has one authoritative owner and a clear stability label.
Distinguish current built-in node:sqlite from third-party SQLite packages and record the Node/API stability requirement.
Open DatabaseSync with explicit options, prepare statements, bind values, and map object rows.
Own transactions explicitly with BEGIN/COMMIT/ROLLBACK and classify errors before retrying.
Explain why Promise/async scheduling does not create simultaneous SQLite writers.
Recognize event-loop blocking risks of synchronous database work and choose an architecture accordingly.
Build a small local CLI-style FieldNotes data layer with no web framework dependency.
Current Node.js SQLite situation
node:sqlite was added in Node 22.5.0. It no longer requires the historical --experimental-sqlite flag in supported later releases, but current Node 26 documentation labels the module release candidate. Node 24's current LTS-line documentation also labels it release candidate. Pin a Node version range in production and test the exact API surface you depend on.
| Choice | Status / model | Course position |
|---|---|---|
node:sqlite | Official Node module; current docs: release candidate; synchronous DatabaseSync/StatementSync APIs | Primary lesson approach because ownership/documentation are official. |
| Third-party synchronous binding | Project-specific native package/API | May be mature, but evaluate maintenance, bundled SQLite version, native packaging, and security separately. |
| Third-party async binding / worker wrapper | Schedules work asynchronously or on worker threads | Can protect event-loop responsiveness; does not change SQLite locking or single-writer semantics. |
| ORM/query builder | Higher abstraction over a driver | Still inherits the selected driver and SQLite engine behavior. |
Inspect the runtime before using features
import process from 'node:process';import { DatabaseSync } from 'node:sqlite';console.log('Node:', process.version);const db = new DatabaseSync(':memory:');console.log('SQLite:', db.prepare('SELECT sqlite_version() AS v').get().v);db.close();The generation environment has Node 22.16.0 and node:sqlite available; that local line still documents the module as active development rather than the newer release-candidate status. This reinforces the rule: record the actual Node runtime and test its API, not just current website docs.
Open with deliberate connection options
import { DatabaseSync } from 'node:sqlite';export function openDb(path) { const db = new DatabaseSync(path, { open: true, readOnly: false, enableForeignKeyConstraints: true, timeout: 5000, }); const fk = db.prepare('PRAGMA foreign_keys').get().foreign_keys; if (fk !== 1) { db.close(); throw new Error('foreign key enforcement is not enabled'); } return db;}Current Node documentation exposes a busy timeout option in milliseconds and enables foreign keys by default in current releases. The explicit check makes your application contract visible and protects against runtime/version/configuration drift.
Prepared statements return objects and bind host values
export function createDevice(db, code, status) { const stmt = db.prepare(` INSERT INTO device(device_code, status) VALUES (?, ?) `); const result = stmt.run(code, status); return result.lastInsertRowid;}export function getDevice(db, id) { const stmt = db.prepare(` SELECT device_id, device_code, status, last_service_at FROM device WHERE device_id = ? `); return stmt.get(id); // object, or undefined when no row matches}StatementSync.run() reports changes and lastInsertRowid. get() returns the first row as an object or undefined; all() returns an array of row objects. SQLite INTEGER values can exceed JavaScript's safe-number range, so current APIs expose BigInt-related options—do not silently coerce identifiers that may exceed Number.MAX_SAFE_INTEGER.
Transaction helper: synchronous control flow can still be disciplined
export function inTransaction(db, work) { db.exec('BEGIN IMMEDIATE'); try { const result = work(); db.exec('COMMIT'); return result; } catch (err) { try { db.exec('ROLLBACK'); } catch { /* preserve original error */ } throw err; }}BEGIN IMMEDIATE is a policy choice, not a universal recommendation. It is useful here because this service operation is definitely a write and we want write contention to surface at the transaction boundary. For read-mostly or different contention patterns, choose transaction mode based on Chapter 8/9 evidence.
Atomic FieldNotes service operation
export function recordService(db, deviceId, when, text) { const insert = db.prepare(` INSERT INTO maintenance_note(device_id, noted_at, note_text) VALUES (?, ?, ?) RETURNING note_id `); const update = db.prepare(` UPDATE device SET last_service_at = ? WHERE device_id = ? `); return inTransaction(db, () => { const note = insert.get(deviceId, when, text); const changed = update.run(when, deviceId).changes; if (changed !== 1) throw new Error(`device ${deviceId} not found`); return note.note_id; });}A small local CLI data layer
import { openDb } from './db.js';import { recordService, getDevice } from './repository.js';const db = openDb('fieldnotes.db');try { const deviceId = Number(process.argv[2]); const text = process.argv.slice(3).join(' '); if (!Number.isSafeInteger(deviceId) || !text) { throw new Error('usage: node service.js DEVICE_ID NOTE_TEXT'); } const id = recordService(db, deviceId, new Date().toISOString(), text); console.log({ noteId: id, device: getDevice(db, deviceId) });} finally { db.close();}This is intentionally local and framework-free. A web API would add request concurrency, cancellation, authentication, logging, and backpressure concerns, but the database contract should remain the same.
Async JavaScript does not make synchronous SQLite non-blocking
Putting a synchronous database call inside an async function does not move the work off the event loop. The function blocks until DatabaseSync returns. For a small desktop/CLI workload this can be perfectly acceptable. For latency-sensitive servers, consider a worker-thread/database-worker architecture or a carefully evaluated asynchronous driver.
async function handler() { // This is still synchronous work on the current thread. const rows = db.prepare('SELECT ...').all(); return rows;}Worker threads can move blocking CPU/I/O work away from the main event loop. They still share the same SQLite file-level concurrency rules if they open connections to the same database. One writer remains one writer.
Long transactions are an architecture problem, not a Promise problem
| Pattern | Event-loop effect | SQLite effect |
|---|---|---|
| One short SELECT via DatabaseSync | Brief synchronous block | Read transaction/snapshot as needed. |
| Large unbounded scan + JSON serialization | Potentially long event-loop block | Long read may also hold a snapshot/checkpoint boundary. |
| Transaction waits on network HTTP call | Terrible design if transaction remains open | Extends lock/snapshot lifetime and contention. |
| Worker owns DB; main thread sends messages | Main thread stays responsive | SQLite concurrency unchanged; ownership becomes clearer. |
| Many async requests each open a writer | Scheduling looks concurrent | Write transactions still serialize and can return BUSY. |
Error handling and retries
Node exceptions may carry driver-specific fields in addition to the message. Preserve enough structured information to distinguish constraints, read-only errors, and busy/locked cases. Retry only at a transaction boundary where the operation is idempotent or otherwise retry-safe. Do not loop forever on every database exception.
A 5-second timeout and three safe retries can still be wrong if a request holds a transaction for 30 seconds. Diagnose transaction duration and ownership before increasing wait settings.
Checkpoint
Node integration check
Focus on runtime and architecture boundaries.
- What is the current stability label of node:sqlite in Node 26 documentation?
- Does DatabaseSync execute asynchronously because Node is an async platform?
- What do StatementSync.get(), all(), and run() return conceptually?
- Why can a worker-thread design improve responsiveness without improving SQLite write parallelism?
- Why should a Node application query sqlite_version() at runtime?
- When should a transaction be rolled back in the service example?
Review the answers
Current Node 26 docs label node:sqlite release candidate (Stability 1.2). DatabaseSync/StatementSync are synchronous. get returns one row object/undefined, all returns row objects, and run returns write metadata such as changes/lastInsertRowid. Worker threads change scheduling, not SQLite’s single-writer rule. Runtime SQLite can differ by Node line/build. Roll back whenever any step of the business unit fails so partial state is not committed.
Bridge to .NET and Java
Lesson 4 switches syntax dramatically but keeps the same database contract: open, initialize, prepare/bind, execute, map rows, own transactions, inspect runtime version, and dispose resources. That sameness is the transferable skill.