@fileshed/kysely-node-sqlite

A Kysely dialect for Node's built-in node:sqlite module, with no native dependency to compile or ship.

Statement routing is decided by StatementSync.columns(), so raw sql templates and RETURNING clauses behave the way they do on Kysely's other dialects.

npm install @fileshed/kysely-node-sqlite kysely

Usage

Let the dialect open the database:

import { Kysely } from 'kysely';
import { NodeSqliteDialect } from '@fileshed/kysely-node-sqlite';

const db = new Kysely<Database>({
    dialect: new NodeSqliteDialect({
        location: './app.db',
        pragmas: { journal_mode: 'WAL', foreign_keys: true },
    }),
});

Or hand it one you already have:

import { DatabaseSync } from 'node:sqlite';

const database = new DatabaseSync('./app.db', { timeout: 5000 });

database.loadExtension('./sqlite-vec.dylib');
database.exec('PRAGMA mmap_size = 268435456');

const db = new Kysely<Database>({
    dialect: new NodeSqliteDialect({ database }),
});

The two configs are mutually exclusive. location opens the database and accepts the open-time options below; database takes a DatabaseSync (or a function returning one, possibly async) and leaves construction to you. Either way, db.destroy() closes the database, matching Kysely's own SQLite dialect.

Options

Option	Applies to	Default	Meaning
location	managed	—	Path, or :memory:.
timeout	managed	5000	Busy timeout in milliseconds.
readOnly	managed	false	Open the database read-only.
enableForeignKeyConstraints	managed	true (node's default)	Foreign key enforcement at open time.
database	injected	—	A DatabaseSync, or a function returning one.
pragmas	both	{}	Applied in declaration order immediately after open.
transactionMode	both	'deferred'	deferred, immediate, or exclusive.
statementCache	both	true (256)	false disables it; a number sets the cap.
readBigInts	both	false	Read INTEGER columns as bigint.
rawRows	both	false	Skip row normalization and return node:sqlite's own row objects.
onCreateConnection	both	—	Runs once, before the first query.

Why this package exists

It was written for FileShed, a self-hosted file host that supports SQLite as a single-file deployment option alongside Postgres. The SQLite path has to run everything the Postgres path runs: migrations, recursive CTEs, RETURNING, raw SQL.

The other reason is what a native SQLite addon costs to ship. A compiled driver needs a binary for every platform and architecture you deploy to, matched to the ABI of the runtime that loads it. A multi-arch Docker image needs the right binary for each architecture at build time, or a compiler toolchain in the image to build one. Electron has its own ABI, so every Electron upgrade means rebuilding. Even when prebuilds exist, npm ci has to be able to reach the host serving them. Since node:sqlite is part of the runtime, there is no binary to worry about.

The dialect implements the whole driver contract, including the corners a query builder rarely reaches:

• Every row-returning statement returns its rows, decided by StatementSync.columns(). That covers whole-query raw templates (sql`SELECT ...`, sql`PRAGMA ...`), INSERT/UPDATE/DELETE ... RETURNING, EXPLAIN QUERY PLAN, VALUES, and CTEs that end in either a read or a write.
• Non-row-returning statements report insertId and numAffectedRows as bigint.
• Savepoints, so startTransaction() and nested transaction scopes work. Names compile as quoted identifiers.
• Streaming over iterate(), so rows arrive as they are read and the cursor is released when a consumer stops early.
• An injectable DatabaseSync, for applications that already own a handle.
• A prepared statement cache, on by default. Results are identical with it off.
• Bind parameters validated before they reach SQLite, so an unsupported value raises an error naming its position.
• Rows shaped like every other dialect's: ordinary objects, with blob columns as Buffer. Code that reads them does not care which database it hit.

All of this is tested by running every kind of query through both this dialect and the better-sqlite3 dialect that ships with Kysely, then checking the results against a hand-written expectation and against each other. Kysely's own dialect is the reference; where this one differs, the bug is here.

Working with node:sqlite

node:sqlite is a thin binding, and a few of its defaults surprise code written against other SQLite drivers. The dialect smooths some of those over and passes the rest through untouched.

What this dialect corrects

Rows arrive as null-prototype objects → you get ordinary objects. node:sqlite builds rows with Object.create(null), so row.hasOwnProperty(...) is not a function, row instanceof Object is false, and string interpolation throws. Every row is copied into a normal object on the way out. Set rawRows: true to skip the copy. On a result set of tens of thousands of rows that copy costs roughly 33% of the read time; on small queries it disappears into the noise. Note that with rawRows on, a column your Database type declares as Buffer arrives as a Uint8Array.

Blob columns arrive as Uint8Array → you get Buffer. The wrap shares the same memory rather than copying bytes, and it happens in the same pass as the row copy, so rawRows: true turns off both.

Binding a value SQLite cannot store fails silently → it throws, naming the position. node:sqlite reads a leading object argument as a bag of named parameters, so a Date in the first position does not error: it consumes that slot, shifts every later parameter down one, and leaves the final placeholder NULL. The dialect checks parameters before they reach SQLite, so this surfaces at the call site. Bindable values are null, numbers, bigints, strings, and any ArrayBufferView (which includes Buffer); a bare ArrayBuffer is rejected because it is not a view and would bind as NULL. Convert dates to an ISO string or epoch number, and booleans to 0/1.

The busy timeout defaults to 0 → it defaults to 5000ms. With node's default, any lock contention fails immediately with SQLITE_BUSY instead of waiting. Set timeout to choose your own.

Re-running a statement mid-iteration silently rewinds it → streams are isolated. A node:sqlite statement carries a single cursor, and running it again while an iterator is open restarts that iterator without raising. Streams compile their own statements and never take one from the prepared-statement cache.

An abandoned iterator blocks DDL → cursors are released. A statement left mid-iteration stays active, and an active statement makes DROP TABLE fail with SQLITE_LOCKED. Breaking out of a stream, or throwing from it, closes the cursor.

What it deliberately leaves alone

Foreign keys are enforced by default. node:sqlite opens with them on and the dialect keeps it that way. Set enableForeignKeyConstraints: false, or pragmas: { foreign_keys: false }, to turn them off.

Integers larger than 2^53 throw when read (ERR_OUT_OF_RANGE). Rounding them into a float would lose the value silently. Set readBigInts: true to read INTEGER columns as bigint instead; that applies to every integer column, count(*) included.

Errors are passed through as raised, with errcode intact. Wrapping them in a dialect-specific error class would trade a precise result code for a message string. The predicates below read the original.

Node's experimental-status warning is left to print. node:sqlite is still flagged experimental and warns on first use; the dialect does not suppress it.

insertId and numAffectedRows are bigint, matching Kysely's own SQLite dialect.

Errors

Errors are passed through exactly as node:sqlite raised them, so errcode survives. They are plain Error objects with code (always 'ERR_SQLITE_ERROR'), errcode (the extended result code), and errstr. Most failures differ only in their message text, so this package exports predicates that read the code instead:

import { isUniqueViolation, isForeignKeyViolation } from '@fileshed/kysely-node-sqlite';

try { await db.insertInto('blob').values(row).execute(); }
catch(error)
{
    if(isUniqueViolation(error)) { /* already stored */ }
    else if(isForeignKeyViolation(error)) { /* parent is gone */ }
    else { throw error; }
}

isNodeSqliteError, isConstraintViolation, isUniqueViolation, isPrimaryKeyViolation, isForeignKeyViolation, isNotNullViolation, isCheckViolation, isBusy, isLocked, and isReadOnly are available, along with primaryResultCode() and the sqliteResultCodes table. isUniqueViolation covers both a unique index collision (2067) and a primary key collision (1555), which SQLite reports as different codes.

Transactions

Transactions open with BEGIN DEFERRED unless transactionMode says otherwise. immediate takes the write lock at BEGIN, which is worth setting for transactions that read and then write: a deferred transaction has to upgrade its lock mid-flight, and that upgrade is where SQLITE_BUSY appears under concurrency.

Savepoints are implemented, so Kysely's startTransaction() and its savepoint / rollbackToSavepoint / releaseSavepoint commands all work. Savepoint names are compiled as quoted identifiers.

Of Kysely's TransactionSettings, accessMode: 'read only' is honoured: it sets PRAGMA query_only for the duration of the transaction, so a write inside one fails with SQLITE_READONLY. It is cleared when the transaction ends, including when it fails. A read-only transaction always begins deferred regardless of transactionMode, since IMMEDIATE and EXCLUSIVE exist to take a write lock that query_only forbids.

isolationLevel is accepted and has no effect: a SQLite transaction is serializable, which is at least as strong as every level Kysely can name, and SQLite offers no way to weaken it.

Access to the single connection is serialized by a mutex, so overlapping transactions queue instead of interleaving their statements.

Streaming

streamQuery uses iterate(), so rows come back as they are read and the result set is never materialized. It accepts any row-returning statement, whether it came from the query builder or from a raw sql template. Statements that return no rows are rejected. Rows are normalized as they stream, same as a whole-result read.

One caveat applies to any single-connection SQLite dialect: Kysely holds the connection for the whole life of a stream, so issuing another query on the same Kysely instance before the stream finishes will deadlock. Consume the stream, or break out of it, first.

Statement cache

Prepared statements are cached by SQL text in a 256-entry LRU, which you can resize with a number or turn off with false. Cached statements survive schema changes: SQLite re-prepares them, so a SELECT * cached before an ALTER TABLE ... ADD COLUMN returns the new column afterward.

Compatibility

• Node ^22.16.0 || >=23.11.0. The floor is StatementSync.columns(), added in 22.16.0 and 23.11.0; versions 23.0 through 23.10 are excluded because they lack it.
• Kysely >=0.28.0 <0.30.0, as a peer dependency. The driver interfaces this package implements are unchanged across 0.28 and 0.29; the suite is run against both.

Releasing

Releases are changelog-driven and run from a clean tree with npm run release -- <major|minor|patch|prerelease>.

1. The script fills CHANGELOG.md's [Unreleased] section from the commits it hasn't seen yet, then stops and shows you the notes to edit or approve.
2. On approval it type-checks, runs the tests, builds, runs npm pack --dry-run, and bumps the version.
3. It stamps the changelog, commits vX.Y.Z, tags, pushes, and opens the GitHub release (marked pre-release when the version carries a hyphen).
4. Publishing that release fires .github/workflows/publish.yml, which publishes to npm over OIDC trusted publishing with provenance. No npm token is involved.

License

MIT © Christopher S. Case