@molecule/api-database-duckdb
Provider bond · database · API (Node) · v1.1.3 · Apache-2.0
DuckDB database provider for molecule.dev — embedded analytical SQL engine that queries Parquet, CSV and JSON files in-process
npm install @molecule/api-database-duckdbnpm · Source on GitHub · Implements @molecule/api-database
How it works
@molecule/api-database-duckdb is a provider bond on the API (Node) side: it implements the database core interface (@molecule/api-database) with a concrete vendor or library behind it.
Your code calls the core; you wire this provider once at startup. Swapping vendors later is one line in that wiring, not a rewrite.
import { setPool, setStore } from '@molecule/api-database'
import { pool, store } from '@molecule/api-database-duckdb'
setPool(pool)
setStore(store)Works with: @molecule/api-bond, @molecule/api-database, @molecule/api-secrets
Secrets: DUCKDB_PATH (optional)
Reference
DuckDB database provider for molecule.dev — an embedded analytical SQL engine that queries Parquet, CSV and JSON files in-process.
Provides DatabasePool and DataStore implementations backed by
@duckdb/node-api (DuckDB 2.0). DuckDB is the SQLite shape, but
columnar/OLAP: it is the right bond for analytics over files
(SELECT * FROM read_parquet('events.parquet')), aggregations and ad-hoc
slices — not for a high-write-rate OLTP app (that is the postgresql bond's
job).
DuckDB cannot reproduce a foreign key's ON DELETE/ON UPDATE CASCADE,
SET NULL or SET DEFAULT action, and this bond refuses to silently drop
it: a resource whose setup SQL needs one is refused by the generator's
compatibility preflight (sqlCapabilities) before an app is built.
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.tsJSDoc, not this file.
Quick Start
import { setPool, setStore } from '@molecule/api-database'
import { pool, store } from '@molecule/api-database-duckdb'
setPool(pool)
setStore(store)
Type
provider
Installation
npm install @molecule/api-database-duckdb @duckdb/node-api @molecule/api-bond @molecule/api-database @molecule/api-secrets
API
Interfaces
DuckdbInstanceHandle
One opened database: the DuckDB instance, the lock over its single pooled connection, and how many pools currently use it.
interface DuckdbInstanceHandle {
/** The DuckDB instance. */
instance: DuckDBInstance
/** FIFO lock over the pool's single connection. */
lock: ConnectionLock
/** The resolved path (`':memory:'` for in-memory). */
path: string
/** Pools currently using this instance. */
refs: number
}
MigrationOptions
Options for the migration runner.
interface MigrationOptions {
/** Prefix for ledger keys when one database applies SQL from more than one directory. */
namespace?: string
}
DuckdbConfig
Configuration for the DuckDB database provider.
interface DuckdbConfig {
/**
* Path of the DuckDB database file — created on first open — or `':memory:'`
* for a database that lives only for the process's lifetime. Defaults to the
* `DUCKDB_PATH` env var, then `'./data/app.duckdb'`.
*/
path?: string
/**
* DuckDB `memory_limit` for the instance. DuckDB's own default is 80% of
* system RAM, which on a shared VM lets one query evict everything else, so
* this bond caps it at `'1GB'` by default. Accepts DuckDB size strings
* (`'512MB'`, `'2GB'`) or a plain byte count as a string. Also settable via
* the `DUCKDB_MEMORY_LIMIT` env var.
*/
memoryLimit?: string
/**
* DuckDB `threads` for the instance. DuckDB's own default is every CPU core,
* which oversubscribes a shared host, so this bond caps it at `4` by default.
* Also settable via the `DUCKDB_THREADS` env var.
*/
threads?: number
/**
* Open the database read-only (`access_mode = 'READ_ONLY'`). Multiple
* processes may read one file concurrently; only read-write mode is
* exclusive, so a read-only pool never fights an app holding the write lock.
*/
readOnly?: boolean
/**
* Extra DuckDB settings handed to `DuckDBInstance.fromCache(path, options)`
* as-is (a flat `Record<string, string>` of setting name → value). Entries
* here win over the `memoryLimit`/`threads`/`readOnly` fields.
*/
config?: Record<string, string>
/**
* How long (ms) a query, `connect()` or `transaction()` waits for the single
* connection before failing with an error that says who is holding it.
* Defaults to 30 000.
*/
acquireTimeoutMillis?: number
}
ProcessEnv
Environment variables consumed by the DuckDB database provider.
interface ProcessEnv {
DUCKDB_PATH?: string
DUCKDB_MEMORY_LIMIT?: string
DUCKDB_THREADS?: string
}
TranslateDdlOptions
Options for translateDdlToDuckdb.
interface TranslateDdlOptions {
/** File name the SQL came from; copied into an unsupported-construct error. */
source?: string
}
Types
MigratablePool
A pool that can run migrations — DatabasePool plus the interactive
transaction every bond in the database category implements (the interface
marks it optional; no bond ships without it, and the runner needs it).
type MigratablePool = DatabasePool & {
/** Begins a transaction that holds the pool's connection until it ends. */
transaction(): Promise<DatabaseTransaction>
}
Classes
ConnectionLock
Serializes every use of ONE DuckDB connection.
All connections from one instance share one database, and DuckDB detects
conflicting writes across connections of one instance as transaction
conflicts — an interactive BEGIN … COMMIT interleaved with writes from
another connection fails the transaction. So this pool uses ONE connection
(the sqlite bond's model): plain query() calls hold it for one statement;
connect() and transaction() hold it until they are released, so a
transaction's BEGIN → … → COMMIT/ROLLBACK runs alone.
Waiting is bounded: a waiter that cannot get the lock within its timeout is removed from the queue and rejected with an error naming the likely cause (code holding a connection while calling the pool), instead of hanging forever.
class ConnectionLock {
private locked = false
private holder: string | null = null
private waiters: Array<{ grant: () => void }> = []
/**
* Number of callers currently waiting for the lock.
*
* @returns The queue length.
*/
get waiting(): number {
return this.waiters.length
}
/**
* Whether the lock is currently held.
*
* @returns `true` when held.
*/
get isLocked(): boolean {
return this.locked
}
/**
* Waits for the lock in FIFO order. The returned release function MUST be
* called exactly once; extra calls are ignored.
*
* @param purpose - What the caller will do with the lock (`'query'`,
* `'connect()'`, `'transaction()'`), used in the timeout message.
* @param timeoutMillis - Maximum wait; `0` or less waits forever.
* @returns A release function.
*/
acquire(purpose: string, timeoutMillis: number): Promise<() => void> {
let released = false
const release = (): void => {
if (released) return
released = true
this.holder = null
const next = this.waiters.shift()
if (next) {
next.grant()
} else {
this.locked = false
}
}
if (!this.locked) {
this.locked = true
this.holder = purpose
return Promise.resolve(release)
}
return new Promise<() => void>((resolve, reject) => {
let timer: ReturnType<typeof setTimeout> | undefined
const waiter = {
grant: (): void => {
if (timer !== undefined) clearTimeout(timer)
this.locked = true
this.holder = purpose
resolve(release)
},
}
this.waiters.push(waiter)
if (timeoutMillis > 0) {
timer = setTimeout(() => {
const index = this.waiters.indexOf(waiter)
if (index === -1) return
this.waiters.splice(index, 1)
reject(
new Error(
`DuckDB: a ${purpose} waited ${timeoutMillis} ms for the pool's single ` +
`connection, which is held by an open ${this.holder ?? 'operation'}. This pool ` +
`serializes everything on one connection: release() a connect()ed connection and ` +
`commit()/rollback() a transaction before calling the pool or the store again, and ` +
`run every statement of a transaction on its own query().`,
),
)
}, timeoutMillis)
}
})
}
}
UnsupportedSqlFeatureError
The error this bond's translator throws when a migration needs a construct DuckDB cannot honour. Named and typed so a caller can tell a genuine dialect limit apart from malformed SQL, and report it without a migration run.
The diagnostic itself is described by the core TYPE contract
(SqlCompatibilityIssue); this class is the bond's own error
construction, next to the translation that raises it.
class UnsupportedSqlFeatureError extends Error {
/** The construct's feature. */
readonly feature: SqlFeature
/** The exact construct found in the migration. */
readonly detail: string
/** Where the statement came from, when known. */
readonly source?: string
/**
* Build the error from a compatibility diagnostic, copying its message and fields.
*
* @param issue - The diagnostic being refused.
*/
constructor(issue: SqlCompatibilityIssue) {
super(issue.message)
this.name = 'UnsupportedSqlFeatureError'
this.feature = issue.feature
this.detail = issue.detail
this.source = issue.source
}
}
Functions
defaultPoolCreated()
Whether the lazy default pool has been created.
Readiness and shutdown wiring must not FORCE a connection.
function defaultPoolCreated(): boolean
Returns: true once the default pool exists.
closeDefaultPool()
Close the lazy default pool, if it was created.
function closeDefaultPool(): Promise<void>
Returns: Resolves once the pool has ended.
instanceOptionsFor(config)
Resolve the effective instance options for a config: sane memory_limit and
threads defaults (DuckDB's own defaults are 80% of RAM and every core,
which let one in-process engine eat a shared VM whole), access_mode for
readOnly, env overrides, then the caller's raw config map on top.
function instanceOptionsFor(config?: DuckdbConfig): Record<string, string>
config— Pool configuration.
Returns: The options for DuckDBInstance.fromCache(path, options).
choosePath(config)
Pick the database path from config, then DUCKDB_PATH, then the default.
function choosePath(config?: DuckdbConfig): string
config— Pool configuration.
Returns: The path as given (not yet resolved).
acquireInstance(config)
Open (or join) the instance for a pool.
A FILE path goes through DuckDBInstance.fromCache: the official answer to
"two instances must never open the same file" — every pool in this process
on one file shares one instance (and one write lock). In-memory databases
are always PRIVATE (a shared :memory: cache entry would hand two test
pools one database), so they use a fresh create and are closed when their
last pool ends. File-backed cached instances are NEVER closed by this
module: the client's cache has no eviction, and closing a cached instance
would hand the next fromCache caller a dead one — they live for the
process, which is also why a file path must not be reused across processes
in read-write mode.
function acquireInstance(config?: DuckdbConfig): Promise<DuckdbInstanceHandle>
config— Pool configuration.
Returns: The instance, with a reference taken for the caller.
releaseInstance(handle)
Drop a pool's reference; close the instance when no pool uses it and it is one of this module's private in-memory instances.
function releaseInstance(handle: DuckdbInstanceHandle): void
handle— The instance to release.
registerLifecycleIntegration(resources)
Register the database readiness check and shutdown hook with the bonded process-lifecycle provider.
function registerLifecycleIntegration(resources: {
ping: () => Promise<void>
close: () => Promise<void>
created: () => boolean
}): void
resources— The ping and close functions this bond owns.
isIdempotencyError(err)
Returns true when err looks like an "already exists" / duplicate-object
idempotency error — the class of error an IF NOT EXISTS migration is
EXPECTED to hit on a re-run. DuckDB errors carry no code, so this matches
the message text (the same approach the sqlite bond takes).
function isIdempotencyError(err: unknown): boolean
err— The error thrown while applying a migration.
Returns: True if it is safe to warn and continue.
applyMigrations(pool, migrationsDir, options)
Apply every migration file in migrationsDir to pool, in lexical order.
Exported so a sibling bond (motherduck) can run the same ledger logic on
its own pool.
function applyMigrations(
pool: MigratablePool,
migrationsDir: string,
options?: MigrationOptions,
): Promise<void>
pool— The pool to migrate (its database is created on first open).migrationsDir— Absolute path to the directory of ordered*.sqlfiles.options— Ledger namespace for multi-directory apps.
Returns: Resolves when every file is applied or recorded.
createMigrator(migrationsDir, options)
Returns a runMigrations() bound to a migrations directory.
function createMigrator(migrationsDir: string, options?: MigrationOptions): () => Promise<void>
migrationsDir— Absolute path to the directory of ordered*.sqlmigration files, resolved from the API package directory.options— A stable namespace for a second SQL directory.
Returns: A no-arg runMigrations() that opens the DuckDB file (creating it and its parent directory if missing) and applies every migration file in lexical order. Reads the file path from the DUCKDB_PATH env var (default ./data/app.duckdb), matching the pool.
runDuckdbQuery(connection, text, values)
Runs one statement on a connection and normalizes its result into the core
QueryResult shape. $1-style and ? placeholders pass through to
DuckDB untranslated — both are native. With VALUES the client prepares the
statement (one statement only); without values a multi-statement string
runs in full and the LAST statement's result is returned (the pglite bond's
simple-query behavior).
rowCount follows node-postgres: rows returned for a statement that
produced rows (a SELECT, or INSERT/UPDATE/DELETE … RETURNING — DuckDB
reports 0 changed rows for a RETURNING statement, so the returned rows ARE
the affected count), rowsChanged for plain DML, null for DDL.
function runDuckdbQuery<T = Record<string, unknown>>(
connection: DuckDBConnection,
text: string,
values?: DuckDBValue[],
): Promise<QueryResult<T>>
connection— The connection to run on.text— SQL with$1, $2, …or?placeholders.values— Positional parameter values (already coerced).
Returns: The normalized result.
createPool(config)
Creates a DatabasePool backed by DuckDB.
The database opens on first use, not here, so creating a pool is free and
synchronous. See DuckdbConfig for where the file lives and the
resource defaults the instance gets.
function createPool(config?: DuckdbConfig): DatabasePool
config— Database path, resource limits and wait timeout.
Returns: A pool over one DuckDB connection.
createStore(pool)
Creates a DataStore backed by a DuckDB DatabasePool.
function createStore(pool: DatabasePool): DataStore
pool— The DuckDB DatabasePool to use for queries.
Returns: A DataStore that translates CRUD operations to DuckDB-compatible SQL.
translateDdlToDuckdb(sql, options)
Translate PostgreSQL-dialect DDL to DuckDB-compatible DDL at migration time.
Resource/template setup migrations are authored in PostgreSQL dialect. DuckDB
2.0 already speaks most of it natively — uuid/timestamptz/bytea/text[]
type names, gen_random_uuid(), now(), CURRENT_TIMESTAMP, :: casts,
ADD COLUMN IF NOT EXISTS, DROP COLUMN IF EXISTS, ON CONFLICT, ILIKE —
all verified against 2.0.0-r.1. The constructs it rejects are rewritten here,
and only where the rewrite preserves behaviour:
jsonb→json, in both the column-type position and the::jsonbcast (DuckDB has aJSONtype, stored as VARCHAR; there is nojsonb).USING <method>on aCREATE INDEX(gin/btree/hash/gist) → removed: DuckDB indexes have no access method.- A partial index gets no
WHEREsupport in DuckDB. A non-unique partial index becomes a full index on the same columns — the predicate only narrows which rows the index covers, never a query's result. A UNIQUE partial index becomes a UNIQUE index overCASE WHEN <predicate> THEN <column> ENDper column: rows outside the predicate get NULL keys (distinct in DuckDB), so uniqueness applies to exactly the rows PostgreSQL would constrain.
What it does NOT rewrite is an ON DELETE/ON UPDATE referential action
(CASCADE, SET NULL, SET DEFAULT): DuckDB rejects the clause at parse
time and has no triggers, so dropping it would turn a cascading delete into a
refusing one. Such SQL throws UnsupportedSqlFeatureError, naming the
construct — see sqlCapabilities. The compatibility preflight in the
generator rejects those resource/provider combinations before an app exists;
this throw is the last line of defence for SQL that reaches the runner another
way.
Everything else passes through untouched — unlike the sqlite bond, no placeholder conversion or timestamp-default rewriting happens here.
function translateDdlToDuckdb(sql: string, options?: TranslateDdlOptions): string
sql— Raw migration SQL, possibly in Postgres dialect.options— The source file name used in an unsupported-construct error.
Returns: DuckDB-compatible SQL.
coerceDuckdbParam(value)
Coerce a JS value into something @duckdb/node-api can bind. The client
infers a parameter's type from the VALUE and only understands null,
booleans, numbers, bigints and strings — anything else fails at bind time
with Cannot create values of type ANY. Specify a specific type., which
would crash every create()/update() that passes a Date, a boolean-ish
object or a jsonb value. Strings cast on the way in: DuckDB implicitly casts
a bound VARCHAR to UUID/TIMESTAMPTZ/DECIMAL/JSON/LIST parameters, verified
against 2.0.0-r.1.
function coerceDuckdbParam(value: unknown): DuckDBValue
value— The value to bind.
Returns: A @duckdb/node-api-bindable value.
normalizeDuckdbRows(rows)
Normalize DuckDB result rows to the JS shapes the other database bonds hand
back. DuckDB's JS conversion returns a bigint for every 64+-bit integer —
COUNT(*), BIGINT and HUGEINT columns — and JSON.stringify THROWS on a
bigint, so a handler that serializes a row with COUNT(*) in it would 500.
A bigint within Number.MAX_SAFE_INTEGER becomes a JS number (matching the
sqlite bond); a larger one becomes its decimal string (matching node-postgres
int8AsString). Mutates rows in place for speed.
function normalizeDuckdbRows<T>(rows: Record<string, unknown>[]): T[]
rows— Row objects fromresult.getRowObjectsJS().
Returns: The normalized rows.
parseJsonColumns(rows, jsonColumns)
Parse the JSON-typed columns of a table's rows into values, matching what
the postgresql/sqlite bonds hand back from a json/jsonb column. DuckDB's
JSON type is an alias over VARCHAR, so a JSON column always READS as its
JSON text — the column's type is only visible in
information_schema.columns.data_type, never in the result metadata, which
is why this runs in the DataStore (it knows the table) and not in the pool.
A value that is not valid JSON text is left as the raw string.
function parseJsonColumns<T>(rows: Record<string, unknown>[], jsonColumns: Set<string>): T[]
rows— Row objects.jsonColumns— The table's JSON-typed column names, lower-cased.
Returns: The rows with JSON columns parsed.
Constants
sqlCapabilities
What the DuckDB bond supports from PostgreSQL-dialect migrations.
fk-referential-action is deliberately unsupported: applying the setup
library with the action dropped would make a parent delete fail while child
rows exist, where PostgreSQL cascades — a silently changed delete, which this
bond refuses rather than reproduces inexactly.
const sqlCapabilities: SqlCapabilities
pool
The DuckDB connection pool instance.
const pool: DatabasePool
store
Default DataStore proxy backed by the lazily-initialized default pool.
const store: DataStore
MEMORY_PATH
The in-memory database path.
const MEMORY_PATH: ':memory:'
DEFAULT_DUCKDB_PATH
Default on-disk database path.
const DEFAULT_DUCKDB_PATH: './data/app.duckdb'
DEFAULT_MEMORY_LIMIT
Default memory_limit: DuckDB's own default is 80% of RAM — far too much on a shared host.
const DEFAULT_MEMORY_LIMIT: '1GB'
DEFAULT_THREADS
Default threads: DuckDB's own default is every core — oversubscribes a shared host.
const DEFAULT_THREADS: 4
MIGRATION_LEDGER
The table that records which migration files have run.
const MIGRATION_LEDGER: '_molecule_migrations'
databaseDuckdbSecretDefinitions
Secret definitions required by the DuckDB database bond.
const databaseDuckdbSecretDefinitions: 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-duckdb'
export function setupDatabaseDuckdb(): void {
setPool(pool)
setStore(store)
}
Injection Notes
Requirements
Peer dependencies:
@molecule/api-bond^1.0.1@molecule/api-database^1.2.0@molecule/api-secrets^1.0.1
Environment Variables
DUCKDB_PATH(optional) — DuckDB database path — default:./data/app.duckdb- Setup: Filesystem path of the DuckDB database file (created on first run), or :memory:.
- Example:
./data/app.duckdb
Runtime Dependencies
@duckdb/node-api@molecule/api-bond@molecule/api-database@molecule/api-secrets
Configure via the DUCKDB_PATH environment variable (default:
./data/app.duckdb; :memory: for a database that lives only for the
process's lifetime).
pool.query(text, values)takes Postgres-style$1, $2placeholders — or?— natively, with no translation. With VALUES a string must be ONE statement (the client prepares it); without values a multi-statement string runs in full and the LAST statement's result is returned.rowCountfollows node-postgres: rows returned for a SELECT or…RETURNINGstatement, rows changed for plain INSERT/UPDATE/DELETE,nullfor DDL.- One process per file, in read-write mode. DuckDB holds a file lock:
the dev server and a CLI must never open the same
.duckdbfile read-write at the same time (Could not set lock on file). This pool shares one instance per file inside the process (fromCache) and serializes every statement on ONE connection behind a FIFO queue, the sqlite bond's model — atransaction()holds the queue for its wholeBEGIN→ … →COMMIT/ROLLBACKlifetime. A second PROCESS wanting concurrent reads should openreadOnly: true. - Resource defaults differ from DuckDB's own. DuckDB defaults to 80% of
RAM for
memory_limitand one thread per core — enough to eat a shared host. This bond defaults tomemory_limit: '1GB'andthreads: 4, overridable viaDUCKDB_MEMORY_LIMIT/DUCKDB_THREADSor thememoryLimit/threadsconfig fields. - Files are the point:
read_parquet,read_csv,read_jsonand the'file.parquet'shorthand all work throughpool.query()as plain SQL. They read relative to the API process's working directory. - JSON columns read as JSON TEXT through raw
query()— DuckDB'sJSONtype is an alias over VARCHAR, so the type is invisible in result metadata. The DataStore CRUD helpers (create,findMany, …) parse JSON-typed columns for you; in raw SQL usedata -> 'key'/json_extractor accept the string. COUNT(*)and BIGINT come back as JS numbers when they fitNumber.MAX_SAFE_INTEGER(raw DuckDB returnsbigint, whichJSON.stringifythrows on); a larger value becomes its decimal string, matching node-postgres.- Schema changes are
*.sqlfiles in the app'sapi/migrations/, applied in lexical order on startup bycreateMigrator. Postgres-dialect DDL applies after a translation that keeps behaviour:jsonb→json, indexUSING <method>dropped, and a partial index applied as a full index or (UNIQUE) as aCASE WHENexpression index.gen_random_uuid(),now(),ADD/DROP COLUMN IF [NOT] EXISTS,::casts anduuid/timestamptz/bytea/text[]types are native. AnON DELETE/ON UPDATEreferential action throwsUnsupportedSqlFeatureError— DuckDB cannot reproduce it; each file runs once (recorded in_molecule_migrations) and in one transaction. - 2.0 file format is a one-way break: versions before DuckDB 2.0 cannot read files this bond creates (2.0 still reads 1.x files). The engine is OLAP — bulk scans and aggregations are its home; an app doing many small row transactions belongs on the postgresql bond.
pool.end()closes the pool's connection; a FILE-backed instance is process-lifetime by design (the client's instance cache has no eviction).