Simple data analysis core (SDA-core)

SDA-core is a lightweight DuckDB-powered TypeScript library for tabular, SQL, CSV, Parquet, and geospatial data analysis on Deno, Node.js, and Bun. It has one runtime dependency: DuckDB.

Choose this package for core data loading, cleaning, joining, statistics, and geospatial operations. For AI, vector search, Google Sheets, and data visualization features, use the full simple-data-analysis library.

The library is available on JSR with its documentation.

AI coding assistants and agents can start with the concise llms.txt index. The complete generated API reference is available in llm.md.

The library is maintained by Nael Shiab, computational journalist and senior data producer for CBC News.

The library is available on JSR and NPM.

# Deno
deno add jsr:@nshiab/simple-data-analysis-core

# Node.js
npm i @nshiab/simple-data-analysis-core

# Bun
bun add @nshiab/simple-data-analysis-core

To quickly set up a data project with essential folders, configurations, and documentation for AI agents, you can use @nshiab/setup-data-project.

# Deno
deno run -A jsr:@nshiab/setup-data-project

# Node
npx @nshiab/setup-data-project

# Bun
bunx @nshiab/setup-data-project

new SimpleDB() works in memory. To work directly in a DuckDB file, pass { file: "./analysis.duckdb" }: SDA opens an existing file or creates a new one on first use. Await getTable() to access a saved table, or start() to restore all saved table handles before using the synchronous getTables() method. { readOnly: true } opens an existing file without allowing changes.

const sdb = new SimpleDB({ file: "./analysis.duckdb" });
const observations = await sdb.getTable("observations");
await observations.filter("value IS NOT NULL").log();
await sdb.close();

loadDB(file) imports a copy into the current database, including into a persistent database. It opens the source read-only, rejects existing table-name conflicts, and rolls back failed copies. writeDB(file) exports a snapshot after executing pending work; subsequent changes still affect the working database.

When migrating from the previous interface:

• Replace loadDB(file, { detach: false }) with new SimpleDB({ file }) for DuckDB files. The detach and name import options have been removed. SQLite files are supported for import/export, not as the persistent working database.
• The constructor now opens existing files by default. Explicit overwrite: true still replaces a file on first use.
• Existing export destinations require writeDB(file, { overwrite: true }). Exports finish in a temporary file before publishing the output. Open database files attached to that instance, symbolic links, and directories cannot be replaced.
• DuckDB files support both .db and .duckdb extensions. SDA index definitions are stored inside the reserved __sda schema. Writable persistent databases save metadata on close().
• SQLite exports contain main-schema tables and views materialized as tables. They do not preserve DuckDB schemas, indexes, constraints, or SDA metadata. SQLite conversion may lose type information; unsupported conversions fail without replacing the destination.
• close() executes pending work, saves metadata, and releases resources. It no longer compacts or replaces persistent database files.

These benchmarks compare SDA-core with raw DuckDB and popular Python and R libraries, measuring duration and peak memory.

They were run on a MacBook Pro with an Apple M4 Max and 64 GB of memory.

Using 22,051,025 temperature records (ahccd.csv, 1.77 GB, in benchmarks/data/), we remove missing temperatures, convert dates and numbers, save the cleaned data, then calculate average temperatures by station and decade and export the sorted results.

Using 335,024 Montreal public trees (arbres-publics.csv, 135.5 MB) and 91 neighbourhood boundaries (quartierreferencehabitation.geojson, 1.14 MB), both in benchmarks/data/, we remove missing coordinates, create points, join trees to neighbourhoods, then count trees per neighbourhood and export the sorted results.

The full simple-data-analysis library is itself an extension of SDA-core. It subclasses SimpleTable to add AI, Google Sheets and charting methods, then subclasses SimpleDB so every table created by the database uses that extended table class.

Follow the same pattern when building an extension. To make new table methods chainable, define them on a SimpleTable subclass, give them a return type of this and return this after queuing their work. Then extend SimpleDB<YourTable> and set its tableClass to your subclass. Methods that create tables should always use this.sdb.newTable() so they also return your extended table type.

import {
  SimpleDB as CoreDB,
  SimpleTable as CoreTable,
} from "@nshiab/simple-data-analysis-core";

class MyTable extends CoreTable {
  selectForPublication(columns: string[]): this {
    this.selectColumns(columns);
    return this;
  }
}

class MyDB extends CoreDB<MyTable> {
  constructor() {
    super();
    this.tableClass = MyTable;
  }
}

const sdb = new MyDB();
await sdb
  .newTable("articles")
  .loadData("articles.csv")
  .selectForPublication(["title", "author"])
  .log();

If an extension must perform asynchronous work before queuing table builders, use queueAsyncBarrier(). The callback runs at the barrier's position in database-wide program order, and builders it queues run before later chained operations:

import { queueAsyncBarrier } from "@nshiab/simple-data-analysis-core/helpers";

class RemoteTable extends CoreTable {
  loadRemote(url: string): this {
    queueAsyncBarrier(this, {
      method: "loadRemote()",
      parameters: { url },
      execute: async () => {
        const rows = await fetch(url).then((response) => response.json()) as {
          [key: string]: unknown;
        }[];
        this.loadArray(rows);
      },
    });
    return this;
  }
}

The callback must await all asynchronous work that can queue builders. If it rejects, captured builders that have not already run are discarded. Builders already drained by an observer inside the callback remain applied; queueAsyncBarrier() does not provide database rollback.