npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2026 – Pkg Stats / Ryan Hefner

@molecule/api-database-duckdb

v1.1.3

Published

DuckDB database provider for molecule.dev

Readme

@molecule/api-database-duckdb

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 ({@link 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 {@link 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 ({@link 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 {@link 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 {@link UnsupportedSqlFeatureError}, naming the construct — see {@link 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).