@fileshed/kysely-node-sqlite

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

It asks SQLite whether a statement returns rows, through StatementSync.columns(), instead of guessing from the SQL text or the compiled query node. Raw sql templates and RETURNING clauses therefore behave here 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. Use this form when the handle needs setting up first, whether that is loading an extension, opening a session, or applying settings the dialect does not manage:

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 are separate config shapes. 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 therefore has to run everything the Postgres path runs: the same migrations, the same recursive CTEs, the same RETURNING clauses, the same raw escape hatches.

So 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 is SQLite's own answer, and the only one that stays correct for shapes with no SELECT keyword at the front: 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, so an application that already owns a handle (for pragmas, extensions, sessions) keeps owning it.
• A prepared statement cache that is transparent: identical results whether it is on or off.
• Bind parameters validated before they reach SQLite, so an unsupported value raises an error naming its position instead of binding as something else.
• 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.

A differential test suite pins every one of those. Each shape runs through this dialect and through the better-sqlite3 dialect that ships with Kysely, asserted 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, because changing them would cost you information.

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 the small queries most applications run it disappears into the noise. Note that with rawRows on, a column your Database type declares as Buffer arrives as a Uint8Array — the declared types no longer describe what you get.

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 therefore compile their own statements and never take one from the prepared-statement cache, so a query running alongside a stream cannot disturb it.

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 that is the safer default, so it stays even though other drivers differ. 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, and a loud failure beats that. 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. That is Node telling you something true about your runtime, and the dialect has no business hiding 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, exactly as they are on a whole-result read.

One caveat applies to every single-connection SQLite dialect, this one and Kysely's official one alike: 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, and the script never publishes anything itself.

License

MIT © Christopher S. Case