@flowblade/source-duckdb

Duckdb datasource adapter based on @duckdb/node-api.

Duckdb datasource adapter based on @duckdb/node-api

Install

npm install @flowblade/source-duckdb @duckdb/node-api

pnpm add @flowblade/source-duckdb @duckdb/node-api

yarn add @flowblade/source-duckdb @duckdb/node-api

bun add @flowblade/source-duckdb @duckdb/node-api

nub add @flowblade/source-duckdb @duckdb/node-api

aube add @flowblade/source-duckdb @duckdb/node-api

Quick start

Create a duckdb instance

import os from "node:os";

import { type DuckDBConnection, DuckDBInstance } from "@duckdb/node-api";
import { DuckdbDatasource } from "@flowblade/source-duckdb";

// Create a connection to a DuckDB instance
const createConnection = async (maxThreads = 4): Promise<DuckDBConnection> => {
    const availableThreads = os.availableParallelism();
    const maxParallelism = Math.min(maxThreads, availableThreads - 1);
    const threads = availableThreads > 1 ? maxParallelism : undefined;

    const instance = await DuckDBInstance.create(":memory:", {
        // Choose between READ_ONLY or READ_WRITE
        // Note that in READ_WRITE mode concurrency is limited to 1
        // See: https://duckdb.org/docs/connect/concurrency.html
        access_mode: "READ_WRITE",
        max_memory: "64MB",
        // Using more threads may require additional memory
        ...(threads ? { threads: threads.toString(10) } : {}),
    });
    return await instance.connect();
};

const duckdb = await createConnection();

// Create a duckdb datasource
export const ds = new DuckdbDatasource({ connection: duckdb });

Query the database

import { DuckdbDatasource, sql } from "@flowblade/source-duckdb";

import { ds } from "./config.ts";

const params = {
    min: 10,
    max: 99,
    name: "test",
    createdAt: new Date().toISOString(),
};

type Row = { id: number; name: "test"; createdAt: Date };

const rawSql = sql<Row>`

      WITH products(productId, createdAt)
          AS MATERIALIZED (
               FROM RANGE(1,100) SELECT
               range::INT,
               TIMESTAMPTZ '2025-01-01 12:30:00.123456789+01:00'
          )

      SELECT productId,
             ${params.name} as name,
             createdAt

      FROM products
      WHERE productId BETWEEN ${params.min}::INTEGER AND ${params.max}::INTEGER
      AND createdAt < ${params.createdAt}::TIMESTAMPTZ
    `;

const result = await ds.query(rawSql);

// Option 1: The QResult object contains the data, metadata and error
//  - data:  the result rows (TData or undefined if error)
//  - error: the error (QError or undefined if success)
//  - meta:  the metadata (always present)

const { data, meta, error } = result;

// Option 2: You operate over the result, ie: mapping the data

const { data: data2 } = result.map((row) => {
    return {
        id: row.productId,
        key: `key-${row.productId}`,
    };
});

if (data) {
    console.log(data);
}

Compatibility