ADBC Driver

The @sluice/adbc package implements a high-performance ADBC driver. To use it import from the built-in @sluice/adbc module.

import { Database } from '@sluice/adbc';

const db = new Database(':memory:');
const query = db.query("select 'Hello world' as message;");
query.get(); // => { message: "Hello world" }

The API is simple, synchronous, and fast. Credit to bun:sqlite , better-sqlite3 and their contributors for inspiring the API of @sluice/adbc.

Features include:

Transactions
Parameters (named & positional)
Prepared statements
Datatype conversions (BLOB becomes Uint8Array)
The fastest performance of any DuckDB or ADBC driver for JavaScript

The @sluice/adbc module is roughly TBDx faster than TBD for read queries. Each driver was benchmarked against the Northwind Traders dataset. View and run the benchmark source.

Installation (with DuckDB driver)

Download the libduckdb from the DuckDB download page.

Place the libduckdb.so (linux) or libduckdb.dylib (mac) in your project’s bin folder.

Load the driver using the driver and entrypoint options.

import { Database } from '@sluice/adbc';

const adbc = new Database(':memory:', {
  driver: new URL(`../bin/libduckdb`, import.meta.url).pathname,
  entrypoint: 'duckdb_adbc_init',
});

Installation (with SQLite driver)

Install the sqlite adbc driver with mamba

mamba install libadbc-driver-sqlite

Copy libadbc_driver_sqlite.so (linux) or libadbc_driver_sqlite.dylib to your project’s bin folder.

Load the driver using the driver and blank entrypoint options.

import { Database } from '@sluice/adbc';

const adbc = new Database('file:adbc_driver_sqlite?mode=memory&cache=shared', {
  driver: new URL(`../bin/libadbc_driver_sqlite`, import.meta.url).pathname,
  entrypoint: 'AdbcDriverInit',
  pathkey: 'uri',
});

Database

To open or create a DuckDb database:

import { Database } from '@sluice/adbc';

const db = new Database('mydb.duckdb');

To open an in-memory database:

import { Database } from '@sluice/adbc';

// all of these do the same thing
const db = new Database(':memory:');
const db = new Database();
const db = new Database('');

To open in readonly mode:

import { Database } from '@sluice/adbc';
const db = new Database('mydb.duckdb', { readonly: true });

To create the database if the file doesn’t exist:

import { Database } from '@sluice/adbc';
const db = new Database('mydb.duckdb', { create: true });

`.close()`

To close a database:

const db = new Database();
db.close();

Note: close() is called automatically when the database is garbage collected. It is safe to call multiple times but has no effect after the first.

`.serialize()`

const olddb = new Database('mydb.duckdb');
const contents = olddb.serialize(); // => Uint8Array
const newdb = Database.deserialize(contents);

`.query()`

Use the db.query() method on your Database instance to prepare a SQL query. The result is a Statement instance that will be cached on the Database instance. The query will not be executed.

const query = db.query(`select "Hello world" as message`);

Note — Use the .prepare() method to prepare a query without caching it on the Database instance.

// compile the prepared statement
const query = db.prepare('SELECT * FROM foo WHERE bar = ?');

Statements

A Statement is a prepared query, which means it’s been parsed and compiled into an efficient binary form. It can be executed multiple times in a performant way.

Create a statement with the .query method on your Database instance.

const query = db.query(`select "Hello world" as message`);

Queries can contain parameters. These can be numerical (?1) or named ($param or :param or @param).

const query = db.query(`SELECT ?1, ?2;`);
const query = db.query(`SELECT $param1, $param2;`);

Values are bound to these parameters when the query is executed. A Statement can be executed with several different methods, each returning the results in a different form.

`.all()`

Use .all() to run a query and get back the results as an array of objects.

const query = db.query(`select $message;`);
query.all({ $message: 'Hello world' });
// => [{ message: "Hello world" }]

Internally, this calls sqlite3_reset and repeatedly calls sqlite3_step until it returns SQLITE_DONE.

`.get()`

Use .get() to run a query and get back the first result as an object.

const query = db.query(`select $message;`);
query.get({ $message: 'Hello world' });
// => { $message: "Hello world" }

Internally, this calls sqlite3_reset followed by sqlite3_step until it no longer returns SQLITE_ROW. If the query returns no rows, undefined is returned.

`.run()`

Use .run() to run a query and get back undefined. This is useful for queries schema-modifying queries (e.g. CREATE TABLE) or bulk write operations.

const query = db.query(`create table foo;`);
query.run();
// => undefined

Internally, this calls sqlite3_reset and calls sqlite3_step once. Stepping through all the rows is not necessary when you don’t care about the results.

`.values()`

Use values() to run a query and get back all results as an array of arrays.

const query = db.query(`select $message;`);
query.values({ $message: 'Hello world' });

query.values(2);
// [
//   [ "Iron Man", 2008 ],
//   [ "The Avengers", 2012 ],
//   [ "Ant-Man: Quantumania", 2023 ],
// ]

`.chunks()`

Use chunks() to run a query and get back all results a streaming iterator of chunks; each chunk can then be introspected as .rows(), .values(), or .df() depending on whether you want it as an array of rows, as an array of arrays or as a dataframe.

const query = db.query(`select $message;`);
query.values({ $message: 'Hello world' });

query.chunks().next().value.rows();
// [{ message: "Hello world" }]

`.finalize()`

Use .finalize() to destroy a Statement and free any resources associated with it. Once finalized, a Statement cannot be executed again. Typically, the garbage collector will do this for you, but explicit finalization may be useful in performance-sensitive applications.

const query = db.query('SELECT title, year FROM movies');
const movies = query.all();
query.finalize();

`.toString()`

Calling toString() on a Statement instance prints the expanded SQL query. This is useful for debugging.

import { Database } from '@sluice/adbc';

// setup
const query = db.query('SELECT $param;');

console.log(query.toString()); // => "SELECT NULL"

query.run(42);
console.log(query.toString()); // => "SELECT 42"

query.run(365);
console.log(query.toString()); // => "SELECT 365"

Parameters

Queries can contain parameters. These can be numerical (?1) or named ($param or :param or @param). Bind values to these parameters when executing the query:

Query
Results

const query = db.query("SELECT * FROM foo WHERE bar = $bar");
const results = query.all({
  $bar: "bar",
});

[
{ "$bar": "bar" }
]

Numbered (positional) parameters work too:

Query
Results

const query = db.query("SELECT ?1, ?2");
const results = query.all("hello", "goodbye");

[
  {
    "?1": "hello",
    "?2": "goodbye"
  }
]

Transactions

Transactions are a mechanism for executing multiple queries in an atomic way; that is, either all of the queries succeed or none of them do. Create a transaction with the db.transaction() method:

const insertCat = db.prepare('INSERT INTO cats (name) VALUES ($name)');
const insertCats = db.transaction((cats) => {
  for (const cat of cats) insertCat.run(cat);
});

At this stage, we haven’t inserted any cats! The call to db.transaction() returns a new function (insertCats) that wraps the function that executes the queries.

To execute the transaction, call this function. All arguments will be passed through to the wrapped function; the return value of the wrapped function will be returned by the transaction function. The wrapped function also has access to the this context as defined where the transaction is executed.

const insert = db.prepare('INSERT INTO cats (name) VALUES ($name)');
const insertCats = db.transaction((cats) => {
  for (const cat of cats) insert.run(cat);
  return cats.length;
});

const count = insertCats([{ $name: 'Keanu' }, { $name: 'Salem' }, { $name: 'Crookshanks' }]);

console.log(`Inserted ${count} cats`);

The driver will automatically begin a transaction when insertCats is called and commit it when the wrapped function returns. If an exception is thrown, the transaction will be rolled back. The exception will propagate as usual; it is not caught.

Nested transactions — Transaction functions can be called from inside other transaction functions. When doing so, the inner transaction becomes a savepoint.

View nested transaction sample

// setup
import { Database } from "@sluice/adbc";
const db = Database.open(":memory:");
db.run(
  "CREATE TABLE expenses (id INTEGER PRIMARY KEY AUTOINCREMENT, note TEXT, dollars INTEGER);",
);
db.run(
  "CREATE TABLE cats (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, age INTEGER)",
);
const insertExpense = db.prepare(
  "INSERT INTO expenses (note, dollars) VALUES (?, ?)",
);
const insert = db.prepare("INSERT INTO cats (name, age) VALUES ($name, $age)");
const insertCats = db.transaction(cats => {
  for (const cat of cats) insert.run(cat);
});

const adopt = db.transaction(cats => {
insertExpense.run("adoption fees", 20);
insertCats(cats); // nested transaction
});

adopt([
{ $name: "Joey", $age: 2 },
{ $name: "Sally", $age: 4 },
{ $name: "Junior", $age: 1 },
]);

Transactions also come with deferred, immediate, and exclusive versions.

insertCats(cats); // uses "BEGIN"
insertCats.deferred(cats); // uses "BEGIN DEFERRED"
insertCats.immediate(cats); // uses "BEGIN IMMEDIATE"
insertCats.exclusive(cats); // uses "BEGIN EXCLUSIVE"

Reference

declare class Database {
  constructor(pathname?: string | Uint8Array, options?: DbConfig);
  static open(pathname: string, options?: DbConfig): Database;
  static deserialize(serialized: Uint8Array, isReadOnly?: boolean): Database;

  info(): Record<string, any>;
  getObjects(depth: number, tableName?: string): Record<string, any>;
  query<ReturnType extends Record<string, any>>(query: string): Statement<ReturnType>;
  batchInsert(tableName: string, array: ArrowArrayJs): number;
  prepare<ReturnType extends Record<string, any>>(
    query: string,
    isPrepared: boolean,
    params?: ParamsType | undefined,
    flags?: any,
  ): Statement<ReturnType>;
  run(query: string, params?: ParamsType): void;
  exec(query: string, params?: ParamsType): void;
  close(): void;
  transaction(fn: (...args: any) => void): CallableFunction & {
    deferred: (...args: any) => void;
    immediate: (...args: any) => void;
    exclusive: (...args: any) => void;
  };
  serialize(name?: string): Buffer;

  readonly handle: number;
  readonly inTransaction: boolean;
  readonly filename: string;
  readonly url: string;
}

interface Statement<ReturnType extends Record<string, any>> {
  all(params?: ParamsType): ReturnType[];
  get(params?: ParamsType): ReturnType | null;
  run(params?: ParamsType): void;
  values(params?: ParamsType): Array<Array<string | bigint | number | boolean | Uint8Array>>;
  df(params?: ParamsType): Record<string, Array<string | bigint | number | boolean | Uint8Array>> | null;
  finalize(...args: any): void;
  toJSON(): { sql: string; isFinalized: boolean; paramsCount: number; columnNames: string[] };

  readonly schema: ArrowSchemaJs | null;
  readonly columnNames: string[];
  readonly paramsCount: number;
  readonly native: any;
  readonly isFinalized: boolean;
}

type TypedArray =
  | Int8Array
  | Uint8Array
  | Int16Array
  | Uint16Array
  | Int32Array
  | Uint32Array
  | Float32Array
  | Float64Array;

type SQLQueryBindings =
  | string
  | bigint
  | TypedArray
  | number
  | boolean
  | null
  | Record<string, string | bigint | TypedArray | number | boolean | null>;

type ParamsType = SQLQueryBindings | SQLQueryBindings[];