duckdb

What is duckdb?

The duckdb npm package provides a fast, in-process SQL OLAP database management system. It is designed for analytical query workloads and can be embedded in applications to perform complex queries on large datasets efficiently.

What are duckdb's main functionalities?

In-Memory Database

This feature allows you to create and manipulate an in-memory database. The code sample demonstrates creating a table, inserting data, and querying the data using SQL.

const duckdb = require('duckdb');
const db = new duckdb.Database(':memory:');
const connection = db.connect();
connection.run('CREATE TABLE test (id INTEGER, name STRING);');
connection.run("INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob');");
connection.all('SELECT * FROM test;', (err, res) => {
  if (err) throw err;
  console.log(res);
});

File-Based Database

This feature allows you to create and manipulate a file-based database. The code sample shows how to create a database file, create a table, insert data, and query the data.

const duckdb = require('duckdb');
const db = new duckdb.Database('mydb.duckdb');
const connection = db.connect();
connection.run('CREATE TABLE test (id INTEGER, name STRING);');
connection.run("INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob');");
connection.all('SELECT * FROM test;', (err, res) => {
  if (err) throw err;
  console.log(res);
});

Efficient Query Execution

DuckDB is optimized for analytical queries. This code sample demonstrates executing an aggregate query to calculate the average value from a table.

const duckdb = require('duckdb');
const db = new duckdb.Database(':memory:');
const connection = db.connect();
connection.run('CREATE TABLE test (id INTEGER, value DOUBLE);');
connection.run('INSERT INTO test VALUES (1, 10.5), (2, 20.5), (3, 30.5);');
connection.all('SELECT AVG(value) AS average_value FROM test;', (err, res) => {
  if (err) throw err;
  console.log(res);
});

Other packages similar to duckdb

sqlite3

The sqlite3 package provides a self-contained, serverless, and zero-configuration SQL database engine. It is similar to DuckDB in that it can be embedded in applications and used for local data storage. However, DuckDB is optimized for analytical queries and large datasets, whereas SQLite is more suited for smaller, transactional workloads.

better-sqlite3

better-sqlite3 is an alternative to the sqlite3 package that offers a simpler API and better performance for synchronous operations. Like DuckDB, it is an embedded database, but it is primarily designed for transactional workloads rather than analytical processing.

alasql

AlaSQL is a JavaScript SQL database library that runs in the browser and Node.js. It is designed for in-memory data processing and can handle complex queries. While it offers similar in-memory capabilities as DuckDB, it is not as optimized for large-scale analytical queries.

README

[!WARNING] The original DuckDB <> Node.js bindings in this package are deprecated in favor of the new and shiny @duckdb/node-api package. Currently, the plan is to release this package duckdb-node for the last time for the DuckDB 1.4.x (~Fall 2025) series but not for the DuckDB 1.5.x series (~Early 2026) any more. Please contact DuckDB Labs if you absolutely require updates after that.

DuckDB Node Bindings

This package provides a Node.js API for DuckDB, the "SQLite for Analytics". The API for this client is somewhat compliant to the SQLite Node.js client for easier transition (and transition you must eventually).

Load the package and create a database object:

var duckdb = require('duckdb');

var db = new duckdb.Database(':memory:'); // or a file name for a persistent DB

Then you can run a query:

db.all('SELECT 42 AS fortytwo', function(err, res) {
  if (err) {
    console.warn(err);
  }
  console.log(res[0].fortytwo)
});

Other available methods are each, where the callback is invoked for each row, run to execute a single statement without results and exec, which can execute several SQL commands at once but also does not return results. All those commands can work with prepared statements, taking the values for the parameters as additional arguments. For example like so:

db.all('SELECT ?::INTEGER AS fortytwo, ?::STRING as hello', 42, 'Hello, World', function(err, res) {
  if (err) {
    console.warn(err);
  }
  console.log(res[0].fortytwo)
  console.log(res[0].hello)
});

However, these are all shorthands for something much more elegant. A database can have multiple Connections, those are created using db.connect().

var con = db.connect();

You can create multiple connections, each with their own transaction context.

Connection objects also contain shorthands to directly call run(), all() and each() with parameters and callbacks, respectively, for example:

con.all('SELECT 42 AS fortytwo', function(err, res) {
  if (err) {
    console.warn(err);
  }
  console.log(res[0].fortytwo)
});

From connections, you can create prepared statements (and only that) using con.prepare():

var stmt = con.prepare('select ?::INTEGER as fortytwo');

To execute this statement, you can call for example all() on the stmt object:

stmt.all(42, function(err, res) {
  if (err) {
    console.warn(err);
  }
  console.log(res[0].fortytwo)
});

You can also execute the prepared statement multiple times. This is for example useful to fill a table with data:

con.run('CREATE TABLE a (i INTEGER)');
var stmt = con.prepare('INSERT INTO a VALUES (?)');
for (var i = 0; i < 10; i++) {
  stmt.run(i);
}
stmt.finalize();
con.all('SELECT * FROM a', function(err, res) {
  if (err) {
    console.warn(err);
  }
  console.log(res)
});

prepare() can also take a callback which gets the prepared statement as an argument:

var stmt = con.prepare('select ?::INTEGER as fortytwo', function(err, stmt) {
  stmt.all(42, function(err, res) {
    if (err) {
      console.warn(err);
    }
    console.log(res[0].fortytwo)
  });
});

Supported Node versions

We actively support only LTS and In-Support Node versions, as per July 2023, they are: Node 18, Node 20 and Node 21. For OSX and Linux targets, also Node 22 is available. Release schedule for Node.js can be checked here: https://github.com/nodejs/release#release-schedule.

We currently bundle and test DuckDB also for Node 12, 14, 16, 17 and 19. We plan of going so going forward as long as the tooling supports it. As per July 2023, Node 15 has been removed from the supported versions.

Development

First install:

To install all the dev dependencies of the project, run npm install (this uses package.json) You might want to add the --ignore-scripts option if you don't care about building the package for now and just want to install the dependencies.

Tests:

Tests are located in test and can be run with npm test To run a single test, you can use npm test -- --grep "name of test as given in describe"

Additional notes:

To build the NodeJS package from source, when on Windows, requires the following extra steps:

• Set OPENSSL_ROOT_DIR to the root directory of an OpenSSL installation
• Supply the STATIC_OPENSSL=1 option when executing make, or set -DOPENSSL_USE_STATIC_LIBS=1 manually when calling cmake