@molecule/api-database-sqlite

SQLite database provider for molecule.dev.

Provides DatabasePool and DataStore implementations backed by better-sqlite3. Ideal for lightweight services, status pages, and single-instance deployments.

Auto-generated, AI-first package reference for the molecule.dev ecosystem. It is written to be read by coding agents as much as by people, and is generated from this package's source — edit src/index.ts JSDoc, not this file.

Quick Start

import { setPool, setStore } from '@molecule/api-database'
import { pool, store } from '@molecule/api-database-sqlite'

setPool(pool)
setStore(store)

Type

provider

Installation

npm install @molecule/api-database-sqlite @molecule/api-bond @molecule/api-database @molecule/api-secrets better-sqlite3
npm install -D @types/better-sqlite3

API

Interfaces

SqliteConfig

Configuration for the SQLite database provider.

interface SqliteConfig {
  /** Path to the SQLite database file. Defaults to SQLITE_PATH env var or './data/app.db'. */
  path?: string
  /** Enable WAL mode for better concurrent read performance. Defaults to true. */
  walMode?: boolean
  /** Enable foreign key constraints. Defaults to true. */
  foreignKeys?: boolean
}

ProcessEnv

Environment variables consumed by the SQLite database provider.

interface ProcessEnv {
  SQLITE_PATH?: string
}

SqliteColumnMeta

Minimal shape of better-sqlite3's Statement.columns() entries we use.

interface SqliteColumnMeta {
  name: string
  type: string | null
}

Functions

createMigrator(migrationsDir)

Returns a runMigrations() bound to a migrations directory.

function createMigrator(migrationsDir: string): () => Promise<void>

• migrationsDir — Absolute path to the directory of ordered *.sql migration files. Resolve via join(new URL('.', import.meta.url).pathname, '../../migrations') from the app's scripts/migrate.ts.

Returns: A no-arg runMigrations() that opens the SQLite file (creating it and its parent directory if missing) and applies every migration file in lexical order using multi-statement exec(). Reads the file path from the SQLITE_PATH env var (default ./data/app.db), matching the pool.

createPool(config)

Creates a SQLite DatabasePool backed by better-sqlite3.

function createPool(config?: SqliteConfig): DatabasePool

• config — SQLite configuration options.

Returns: A DatabasePool wrapping a synchronous better-sqlite3 connection.

createStore(pool)

Creates a DataStore backed by a SQLite DatabasePool.

function createStore(pool: DatabasePool): DataStore

• pool — The SQLite DatabasePool to use for queries.

Returns: A DataStore that translates CRUD operations to SQLite-compatible SQL.

convertPlaceholders(text, values)

Convert PostgreSQL-style positional placeholders ($1, $2, ...) to SQLite-style (?) placeholders. Also reorders values array to match the placeholder order.

A statement whose placeholders and values disagree is REFUSED, with the same contract PostgreSQL enforces at bind time (bind message supplies 3 parameters, but prepared statement "" requires 2). This converter is the only place that sees both the SQL text and the values, and without the check a $N numbering slip — a list numbered from the loop index that restarts at $1 and collides with a $1 already in the statement, leaving a supplied value with no placeholder — returns WRONG ROWS with no error at all. Postgres rejects the same call outright, so an app developed on this bond (every scaffold's default) otherwise carries a class of bug only its production database would name.

function convertPlaceholders(text: string, values?: unknown[]): { text: string; values: unknown[] }

• text — SQL query text with $N placeholders.
• values — Parameter values ordered by $N index.

Returns: The query text with ? placeholders and reordered values.

translateDdlToSqlite(sql)

Translate PostgreSQL-dialect DDL to SQLite-compatible DDL at migration time.

Resource/template setup migrations are authored in PostgreSQL dialect, but a SQLite project applies them raw via the migrator's db.exec(). SQLite tolerates Postgres type names through affinity (uuid, timestamptz, jsonb, boolean), but it rejects Postgres function/cast/index SYNTAX, which raises near "(": syntax error and aborts the whole migration. This rewrites the handful of constructs that actually appear across the resource library so those migrations apply on SQLite:

• DEFAULT gen_random_uuid() → DEFAULT (lower(hex(randomblob(16)))), the SQLite equivalent random-id default. Resource handlers set id = id || uuid() so they don't need it, but a custom handler using the bare create('t', {…}) (no explicit id — the documented pattern) DOES rely on the column default; stripping it left id … NOT NULL with no default, so every such insert hit NOT NULL constraint failed: t.id. Translating (not stripping) keeps both paths working: resource handlers pass an id that overrides the default, custom handlers get one generated.
• now() / NOW(), and a literal DEFAULT CURRENT_TIMESTAMP (either case — the form 831 of the template migrations use), → DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')): ISO-8601, UTC, milliseconds. Those defaults previously became SQLite's current_timestamp, whose

(description truncated — read the full text in node_modules/@molecule/api-database-sqlite/dist/utilities.d.ts.)

function translateDdlToSqlite(sql: string): string

• sql — Raw migration SQL, possibly in Postgres dialect.

Returns: SQLite-compatible SQL.

coerceSqliteParam(value)

Coerce a JS value into something better-sqlite3 can bind. better-sqlite3 only accepts numbers, strings, bigints, buffers, and null — a JS boolean, plain object, array, undefined, or Date throws at bind time, which would crash every create()/update() that passes a boolean or jsonb value (e.g. users.twoFactorEnabled, projects.packages). Mirrors what the Postgres driver accepts: booleans → 0/1, objects/arrays → JSON text, Date → ISO string, undefined → null.

function coerceSqliteParam(value: unknown): unknown

• value — The value to bind.

Returns: A better-sqlite3-bindable primitive.

normalizeSqliteRows(rows, columns)

Normalize a SQLite result set back to JS types using the declared column types, so values round-trip like the Postgres bond instead of leaking SQLite's storage form: a BOOLEAN column's 0/1 → boolean, a JSON/JSONB column's text → parsed value. Columns with an unknown/expression type (null) and values that aren't in storage form pass through untouched. Mutates rows in place for speed.

function normalizeSqliteRows(rows: Record<string, unknown>[], columns: SqliteColumnMeta[]): T[]

• rows — Raw rows from stmt.all().
• columns — stmt.columns() metadata for the result set.

Returns: The normalized rows.

Constants

pool

The SQLite connection pool instance.

const pool: DatabasePool

store

Default DataStore proxy backed by the lazily-initialized default pool.

const store: DataStore

MIGRATION_LEDGER

The table that records which migration files have run.

const MIGRATION_LEDGER: '_molecule_migrations'

databaseSqliteSecretDefinitions

Secret definitions required by the SQLite database bond.

const databaseSqliteSecretDefinitions: SecretDefinition[]

Core Interface

Implements @molecule/api-database interface.

Bond Wiring

Setup function to register this provider with the core interface:

import { setPool, setStore } from '@molecule/api-database'
import { pool, store } from '@molecule/api-database-sqlite'

export function setupDatabaseSqlite(): void {
  setPool(pool)
  setStore(store)
}

Injection Notes

Requirements

Peer dependencies:

• @molecule/api-bond ^1.0.1
• @molecule/api-database ^1.1.0
• @molecule/api-secrets ^1.0.1

Environment Variables

• SQLITE_PATH (optional) — SQLite database path — default: ./data/app.db
  • Setup: Filesystem path of the SQLite database file (created on first run).
  • Example: ./data/app.db

Runtime Dependencies

• @molecule/api-bond
• @molecule/api-database
• @molecule/api-secrets
• better-sqlite3

Configure via the SQLITE_PATH environment variable (default: ./data/app.db). WAL mode and foreign key constraints are enabled by default.

• Schema changes are *.sql files in the app's api/migrations/. A scaffolded API applies new files in lexical order each time it starts (server.ts runs createMigrator), so a new file applies on the next restart; write CREATE TABLE IF NOT EXISTS. Each file runs ONCE: the _molecule_migrations table records it, and an edit to a recorded file does not run again — except that a column added to its CREATE TABLE is still added. Every other change to an applied schema goes in a NEW file. Postgres-dialect DDL is translated (gen_random_uuid(), now() / CURRENT_TIMESTAMP, :: casts, USING <method>, ADD COLUMN IF NOT EXISTS), so the same file works on the postgres bond.
• A @molecule/api-resource-* package's src/__setup__/*.sql is Postgres dialect and applies here as it is — copy the file into api/migrations/ unchanged. The migrator translates it on the way in (above), and a resource package's uuid/timestamptz/jsonb type names need no translation at all: SQLite takes them by affinity, and the store reads a JSON/JSONB column back as a parsed value. Never hand-write a SQLite version of a package's schema: a transcribed copy drifts from the schema that package's own queries expect, and the drift surfaces as no such column at runtime rather than at the migration.
• A DEFAULT now() / DEFAULT CURRENT_TIMESTAMP column yields ISO-8601 UTC with milliseconds (2026-10-16T01:22:21.416Z) — the format the other bonds and new Date().toISOString() produce. SQLite's own current_timestamp is space-separated, Z-less and whole-second, which new Date() reparses as LOCAL time and which ties for two rows written in the same second; the migrator translates those defaults so stored values are ISO and compare as strings in time order. A column a caller writes explicitly is left alone.
• pool.query(text, values) takes Postgres-style $1, $2 placeholders (converted to ? and reordered) or plain ? placeholders.
• pool.transaction() calls and plain pool.query() calls are serialized behind an internal FIFO queue (better-sqlite3 has only ONE shared connection — there is no per-transaction isolation). A transaction() holds the queue for its whole BEGIN → … → COMMIT/ROLLBACK lifetime, so a concurrent transaction() or query() call waits its turn instead of racing BEGIN or silently running inside — and being discarded by — someone else's rollback. Don't hold a transaction open across an await on unrelated work; every other query on this pool queues behind it until it resolves.
• All-or-nothing writes: withTransaction(async () => { … }) from @molecule/api-database. Every call inside it — the CRUD helpers (create, findMany, updateById, …) and query() — joins the one transaction; it commits when the callback resolves, rolls back when it throws. Never call the CRUD helpers between a manual pool.transaction() and its commit(): they queue behind the transaction they belong to, and fail after 30 s with an error naming withTransaction().
• like is case-insensitive and does NOT escape the value — the caller's own %/_ are honored as wildcards, identical to the postgresql/mysql bonds. For human-typed search input, use ilike instead (escapes + auto- wraps %…%) — see WhereCondition['operator'] in @molecule/api-database.
• A migration file with a genuine error (not an idempotent "already exists"/"duplicate column name") now FAILS the boot with every broken file named, instead of warn-logging and booting with a partial schema.