Use DuckDB with DaloyJS

DuckDB is an in-process OLAP database. Use it when your DaloyJS API needs to query local analytical data, Parquet files, CSV exports, or a small embedded reporting database without running a separate database server. It is not a replacement for Postgres or MySQL as the primary transactional database behind a multi-writer API.

1. Install

Use the current Node client, @duckdb/node-api. The older duckdb package is deprecated: DuckDB Labs planned its last release for the DuckDB 1.4 series and does not publish it for 1.5.

pnpm add @duckdb/node-api

npm install @duckdb/node-api

yarn add @duckdb/node-api

bun add @duckdb/node-api

2. Create a hardened DuckDB plugin

Create one instance per process and connect during app startup. Use an in-memory database for transient analytics, or point DuckDB at a persisted file when the platform gives you durable disk.

Apply security settings once, at startup, before any request runs. These settings are global to the instance, and lock_configuration freezes them, so running SET per request would fail on the second request (and would race between concurrent requests on the shared connection).

import { DuckDBInstance, type DuckDBConnection } from "@duckdb/node-api";
import type { App } from "@daloyjs/core";

declare module "@daloyjs/core" {
  interface AppState {
    duckdb: DuckDBConnection;
  }
}

// The only directory the API may read files from. Keep it absolute and
// end it with a slash so "/srv/data/sales-old" does not match.
const DATA_DIR = "/srv/data/sales/";

// Call with the root app, not through app.register(): a decoration made
// inside a registered plugin is visible only to that plugin's own routes.
export async function duckDbPlugin(app: App) {
  const path = process.env.DUCKDB_PATH ?? ":memory:";
  // fromCache() returns the same instance for the same path within this
  // process, so every caller shares these settings (and the lock below).
  const instance = await DuckDBInstance.fromCache(path);
  const connection = await instance.connect();

  // Order matters: allow the data directory, then turn external access off.
  await connection.run(`set allowed_directories = ['${DATA_DIR}']`);
  await connection.run("set enable_external_access = false");
  await connection.run("set autoinstall_known_extensions = false");
  await connection.run("set allow_community_extensions = false");
  await connection.run("set lock_configuration = true");

  app.decorate("duckdb", connection);
  // Runs on the Node, Bun, and Deno adapters after in-flight requests drain.
  app.onClose(() => {
    connection.closeSync();
  });
}

With enable_external_access = false, read_parquet, read_csv, COPY, and ATTACH refuse files and URLs, except under allowed_directories (or individual files listed in allowed_paths), which DuckDB documents as always allowed. That keeps the route below working while blocking every other path. Keep the declare module block in this module, not in a separate .d.ts file: declaration files are exempt from type-checking when skipLibCheck is on (the scaffolded default), so state.duckdb would silently degrade to any.

3. Query from a route

Keep SQL structure owned by the server and pass request values as parameters. Give nullable parameters an explicit type (the third argument), so a null binds as a typed VARCHAR instead of relying on type inference from a value that carries no type. Convert results to JSON-safe objects before returning them through a response schema.

import { z } from "zod";
import { VARCHAR } from "@duckdb/node-api";
import { App, secureHeaders } from "@daloyjs/core";
import { duckDbPlugin } from "./db/duckdb.ts";

const app = new App();
app.use(secureHeaders());
await duckDbPlugin(app);

const SalesSummary = z.object({
  region: z.string(),
  revenue: z.number(),
});

app.get(
  "/analytics/sales",
  {
    operationId: "salesSummary",
    request: { query: z.object({ region: z.string().min(1).optional() }) },
    responses: {
      200: { description: "ok", body: z.array(SalesSummary) },
    },
  },
  async ({ query, state }) => {
    const reader = await state.duckdb.runAndReadAll(
      `select region, sum(revenue)::double as revenue
       from read_parquet('/srv/data/sales/*.parquet')
       where $region is null or region = $region
       group by region
       order by revenue desc`,
      { region: query.region ?? null },
      { region: VARCHAR },
    );

    return {
      status: 200,
      body: SalesSummary.array().parse(reader.getRowObjectsJson()),
    };
  },
);

Runtime support

Use the right storage mode

Security notes

• Do not execute SQL text from users. DuckDB SQL can read files, access networks through extensions, and consume significant CPU or memory.
• Use parameterized values when request data belongs in a query, as in the route above.
• Harden the instance at startup (section 2), not per request. If you need a second, differently configured database in the same process, create it with DuckDBInstance.create() on its own path instead of fromCache(), which would hand you the already-locked instance.
• Never pass a user-supplied path directly into read_csv, read_parquet, COPY, or ATTACH, even inside an allowed directory. Map request values to known file names on the server.
• Consider set autoload_known_extensions = false too, once you have confirmed the extensions you rely on (such as Parquet) are built into your DuckDB build.
• For user-authored SQL, run DuckDB out of process with OS/container sandboxing and strict timeouts. DuckDB's own docs say these settings are not a complete sandbox. A DaloyJS route should treat user-authored SQL like code execution, not like a search filter.

When to choose DuckDB

• You need analytics over Parquet, CSV, JSON, or local snapshots.
• You want an embedded reporting endpoint in a Node service without operating a warehouse.
• You are building import/export, admin analytics, billing summaries, or offline data tools.

When not to choose it

• You need many API replicas to write the same primary application data.
• You deploy only to Cloudflare Workers. Use Turso, Cloudflare D1, or Neon instead.
• You want row-level authorization enforced inside the database. Keep that in Postgres/Supabase or enforce it explicitly in application code.

See also the database hosting overview, Node adapter, DuckDB Node.js client docs, and Securing DuckDB.