@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-duckdb

npm · 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.ts JSDoc, 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 *.sql files.
  • 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 *.sql migration 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 ::jsonb cast (DuckDB has a JSON type, stored as VARCHAR; there is no jsonb).
  • USING <method> on a CREATE INDEX (gin/btree/hash/gist) → removed: DuckDB indexes have no access method.
  • A partial index gets no WHERE support 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 over CASE WHEN <predicate> THEN <column> END per 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 from result.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, $2 placeholders — 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. rowCount follows node-postgres: rows returned for a SELECT or …RETURNING statement, rows changed for plain INSERT/UPDATE/DELETE, null for 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 .duckdb file 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 — a transaction() holds the queue for its whole BEGIN → … → COMMIT/ROLLBACK lifetime. A second PROCESS wanting concurrent reads should open readOnly: true.
  • Resource defaults differ from DuckDB's own. DuckDB defaults to 80% of RAM for memory_limit and one thread per core — enough to eat a shared host. This bond defaults to memory_limit: '1GB' and threads: 4, overridable via DUCKDB_MEMORY_LIMIT / DUCKDB_THREADS or the memoryLimit/threads config fields.
  • Files are the point: read_parquet, read_csv, read_json and the 'file.parquet' shorthand all work through pool.query() as plain SQL. They read relative to the API process's working directory.
  • JSON columns read as JSON TEXT through raw query() — DuckDB's JSON type 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 use data -> 'key' / json_extract or accept the string.
  • COUNT(*) and BIGINT come back as JS numbers when they fit Number.MAX_SAFE_INTEGER (raw DuckDB returns bigint, which JSON.stringify throws on); a larger value becomes its decimal string, matching node-postgres.
  • Schema changes are *.sql files in the app's api/migrations/, applied in lexical order on startup by createMigrator. Postgres-dialect DDL applies after a translation that keeps behaviour: jsonb → json, index USING <method> dropped, and a partial index applied as a full index or (UNIQUE) as a CASE WHEN expression index. gen_random_uuid(), now(), ADD/DROP COLUMN IF [NOT] EXISTS, :: casts and uuid/ timestamptz/bytea/text[] types are native. An ON DELETE/ ON UPDATE referential action throws UnsupportedSqlFeatureError — 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).