Using DuckDB in Node.js: A Practical Guide

Why run DuckDB in your app process

DuckDB is embedded: no server, no port, no credentials. You get a columnar analytical engine as a library dependency, which is why it is such a natural fit for self-hosted tools where "run another service" is a real cost for every user.

Installing and opening a database

npm install @duckdb/node-api

import { DuckDBInstance } from '@duckdb/node-api';

const instance = await DuckDBInstance.create('./analytics.duckdb', {
    threads: '4',
    memory_limit: '2GB',
});
const connection = await instance.connect();

Passing a file path persists the database; passing ':memory:' keeps it ephemeral. For an analytics read layer that is rebuilt from Postgres, in-memory is a legitimate option — but startup then costs a full re-sync.

Connection model: one instance, pooled connections

The rule that saves the most pain: one `DuckDBInstance` per process, per file. Opening the same file twice from the same process — or from two processes — invites lock errors and, in the worst case, corruption.

Connections are cheap and are the unit of concurrency:

class Db {
    #instance;
    async init(path) {
        this.#instance = await DuckDBInstance.create(path);
    }
    async query(sql, params = []) {
        const conn = await this.#instance.connect();
        try {
            const reader = await conn.runAndReadAll(sql, params);
            return reader.getRowObjects();
        } finally {
            conn.closeSync();
        }
    }
}
export const db = new Db();

The finally block matters. A connection leaked on an error path holds resources for the life of the process, and under load you will hit the limit during exactly the incident you are trying to debug.

Parameterized queries, always

DuckDB uses positional ? placeholders:

const rows = await db.query(
    `SELECT country, count(*) AS views
       FROM events
      WHERE site_id = ? AND timestamp >= ? AND timestamp < ?
      GROUP BY country
      ORDER BY views DESC
      LIMIT 20`,
    [siteId, from, to]
);

Never build SQL with template literals or concatenation, even for values you believe are safe. Interpolating a "trusted" internal ID is how injection bugs get introduced two refactors later, when that ID starts coming from a query string.

Identifiers cannot be parameterized. If a column or table name must be dynamic, validate it against an allowlist:

const SORTABLE = new Set(['views', 'visitors', 'bounce_rate']);
if (!SORTABLE.has(sortColumn)) throw new Error('Invalid sort column');
const sql = `SELECT … ORDER BY ${sortColumn} DESC`;  // safe: allowlisted

Bulk inserts: batch or suffer

Inserting rows one at a time is the single most common DuckDB performance mistake. Each statement pays fixed overhead, and a column store is optimized for batch appends.

async function insertBatch(conn, rows) {
    if (!rows.length) return;
    const cols = 6;
    const placeholders = rows.map(() => `(${Array(cols).fill('?').join(',')})`).join(',');
    const values = rows.flatMap(r => [r.id, r.siteId, r.ts, r.path, r.country, r.browser]);
    await conn.run(
        `INSERT INTO events (id, site_id, timestamp, path, country, browser) VALUES ${placeholders}`,
        values
    );
}

The placeholder string is generated from the row count, not from row content — no user data reaches the SQL text. Batches of 1,000–5,000 rows are a good default.

For very large loads, skip the driver entirely and let DuckDB read the file:

INSERT INTO events SELECT * FROM read_parquet('dump.parquet');
INSERT INTO events SELECT * FROM read_csv_auto('dump.csv');

Type conversions that surprise people

• BIGINT arrives as a JavaScript `BigInt`. count(*) returns 42n, and JSON.stringify throws on it. Cast in SQL (count(*)::INTEGER) or convert explicitly (Number(v)) before serializing.
• TIMESTAMP comes back as a DuckDB timestamp object; call its conversion method rather than assuming a JS Date.
• DECIMAL is not a JS number. Cast to DOUBLE in the query if you want floats.

Normalizing these at the data-access boundary — one toPlain(row) helper — keeps BigInt surprises out of your route handlers.

Reading Parquet and CSV in place

No import step needed:

await db.query(`SELECT count(*) FROM read_parquet('archive/*.parquet')`);
await db.query(`SELECT * FROM read_csv_auto('report.csv') LIMIT 10`);

This is what makes hot/cold tiering practical: cold data stays as files and is still queryable.

Frequently asked questions

Should I run one DuckDB instance or several?

One instance per process, with pooled connections from it. Opening the same database file from multiple instances risks corruption, and it is the single most common way people get into trouble.

Do I need parameterized queries if the input is internal?

Yes. Beyond injection, parameter binding avoids per-query parse and plan work and prevents subtle type-coercion bugs from string concatenation. Treat it as unconditional.

Why are my bulk inserts so slow?

Almost certainly one statement per row. Batch inserts into multi-row statements or an appender, wrap them in a transaction, and throughput improves by orders of magnitude.

Does DuckDB block the Node event loop?

Long queries run on DuckDB's own threads, but result materialization crosses into JS and can stall the loop if a query returns a very large result set. Aggregate in SQL and return small results.

How do BIGINT values come back in JavaScript?

As BigInt, not Number, because they can exceed Number.MAX_SAFE_INTEGER. Convert explicitly before arithmetic or JSON serialization — this surprises people at the API boundary more than anywhere else.

A checklist

1. One DuckDBInstance per file per process.

2. Always close connections in finally.

3. Parameterize values; allowlist identifiers.

4. Batch inserts; never one row per statement.

5. Normalize BigInt and timestamps before they reach JSON.

6. Set memory_limit explicitly — the default may exceed your container's limit.

7. Wrap multi-statement work in BEGIN / COMMIT so a crash cannot leave a half-written table.