SqlDataSource

Abstract base class for SQL-backed data sources that adds data loading on top of query execution.

SqlDataSource is an abstract class that builds on top of DataSource. It defines a common abstraction to connect to WASM-based SQL-supported data sources in the browser (like DuckDB WASM or PGLite) and run SQL against them by fixing execute(sql: string) to string.

Outline

abstract class SqlDataSource implements DataSource<string> {
  table: string;
  protected refCount: number;
  abstract execute(sql: string): Promise<Record<string, any>[]>;

  loadData(config: {
    table?: string;
    schema: DataSchema[];
    data: any[][];
    replace?: Map<string, Map<string, string>>;
  }): Promise<void>;

  loadDataFromURL(config: {
    url: string;
    type: "json" | "csv";
    preprocess?: (data: unknown) => unknown;
    schema?: DataSchema[];
    replace?: Map<string, Map<string, string>>;
    table?: string;
  }): Promise<ColumnMetadata[]>;

  protected abstract insertArrowTable(table: arrow.Table, name: string): Promise<void>;
  addRef(): void;
  abstract release(): Promise<void>;
}

interface DataSource {
  execute(req: T): Promise<Record<string, any>[]>;
  addRef(): void;
  release(): Promise<void>;
}

interface DataSchema extends Schema {
  name: string;
  displayName?: string;
}

interface Schema {
  type: "measure" | "dimension";
  subtype?: SchemaSubtype;
  datetimeFormat?: string;
  aggregateFn?: AggregateFn;
  cardinality?: "low" | "high";
}

type SchemaSubtype = "temporal" | "nominal" | "integer" | "decimal";

type AggregateFn = "sum" | "avg" | "count" | "min" | "max";

type ColumnMetadata = DataSchema & {
  normColName: string;
  originalColName: string;
};

On top of query execution, it adds two data loading methods: loadData() for in-memory arrays and loadDataFromURL() for fetching JSON or CSV from a URL and loading to WASM-based sources in the browser.

Recommendation

We recommend using DuckDBWasmDataSource (an implementation of SqlDataSource) to load in-memory CSV or JSON data before feeding it to the grid. This gives the data model full SQL semantics, making complex visualizations like pivots, grouping, and aggregation easy to build.

What it adds over DataSource

Inherited from DataSource	Added by SqlDataSource
`execute(sql)` - run a SQL query	`loadData(config)` - load column-major arrays into a SQL table
`addRef()` / `release()` - ref-counted lifecycle	`loadDataFromURL(config)` - fetch JSON/CSV, infer schema, then calls `loadData()` internally

Loading data with `loadData()`

loadData() takes data already in JS memory as column-major arrays and a DataSchema array describing each column, then loads it into the in-memory WASM SQL engine running in the browser. The schema tells the engine how to cast and treat each column; it carries type, subtype, and other options.

Tip

For loading data from a JSON or CSV file, use loadDataFromURL() instead - it handles fetching and parsing for you. If you need to fetch data from a custom API, call the API yourself, prepare the data into column-major arrays, then pass it to loadData().

Column-major means each array holds all values for one column, not one row:

const schema = [
  { name: "city", type: "dimension" },
  { name: "revenue", type: "measure", subtype: "integer" },
];

// column-major: data[0] = all cities, data[1] = all revenues
const data = [
  ["NYC", "LA", "Chicago"],
  [100, 200, 150],
];

await ds.loadData({ schema, data });

Under the hood, loadData() does this:

Batch-inserts the data using Apache Arrow in a table
Casts each column to its target type (e.g. INTEGER, DOUBLE, TIMESTAMP) based on the schema

See the full API Reference for all loadData() options.

Loading data from a URL with `loadDataFromURL()`

loadDataFromURL() is a convenience method that fetches JSON or CSV from a URL, parses it, optionally infers the schema, and loads it via loadData(). Everything it does you can do yourself - fetch, parse, prepare column-major arrays, and call loadData() directly.

JSON

const columns = await ds.loadDataFromURL({
  url: "https://example.com/sales.json",
  type: "json",
});

The JSON must be an array of objects:

[
  { "city": "NYC", "revenue": 100 },
  { "city": "LA", "revenue": 200 }
]

CSV

const columns = await ds.loadDataFromURL({
  url: "https://example.com/sales.csv",
  type: "csv",
});

Providing a schema

If you omit schema, types are inferred automatically by sampling up to 20 non-empty values per column:

Inferred type	Condition
`INTEGER`	All sampled values are whole numbers
`DOUBLE`	All sampled values are numbers, at least one has a decimal
`TIMESTAMP`	All sampled values are ISO 8601 date strings
`VARCHAR`	Everything else

This works for simple cases but can misclassify columns - e.g. a zip code column like "10001" would be inferred as INTEGER instead of VARCHAR.

You can provide your own DataSchema array for precise control. With an explicit schema, you decide how each column is stored, aggregated, and filtered - for example marking a column as "temporal" enables date-range filtering, and setting aggregateFn controls how measures are rolled up in pivot views:

const columns = await ds.loadDataFromURL({
  url: "https://example.com/sales.json",
  type: "json",
  schema: [
    { name: "city", type: "dimension" },
    { name: "date", type: "dimension", subtype: "temporal", datetimeFormat: "%Y-%m-%d" },
    { name: "revenue", type: "measure", subtype: "decimal" },
  ],
});

Dimensions

A column is a dimension if you would group by it, pivot on it, or use it as a label. These are typically categorical values like names, categories, regions, or IDs.

{ name: "city", type: "dimension" }
{ name: "category", type: "dimension", cardinality: "low" }

Temporal dimensions

A dimension is temporal if it represents a point in time - dates, timestamps, or datetimes. Mark it with subtype: "temporal" and provide a datetimeFormat so the engine can parse the raw string values.

{ name: "order_date", type: "dimension", subtype: "temporal", datetimeFormat: "%Y-%m-%d" }

This enables date-range filtering (min/max) instead of listing every distinct date value.

Measures

A column is a measure if it holds numeric values you want to aggregate - totals, averages, counts, etc. These are the numbers that appear in the cells of a pivot table or get summed in grouped views.

{ name: "revenue", type: "measure", subtype: "decimal" }
{ name: "quantity", type: "measure", subtype: "integer", aggregateFn: "sum" }

String replacements

If your source data has formatting issues or requires data cleaning before inserting into the in-memory database (e.g. "$1,000" instead of 1000), use the replace option to clean values before type casting:

await ds.loadData({
  schema,
  data,
  // replaces "$" and "," with empty string in revenue column
  // e.g. "$1,000" becomes "1000"
  replace: new Map([
    ["revenue", new Map([["$", ""], [",", ""]])],
  ]),
});

Preprocessing

Use preprocess to transform raw data before it gets loaded. For example, to unwrap a nested response:

// API returns: { status: "ok", data: { rows: [{ city: "NYC", revenue: 100 }, ...] } }
// loadDataFromURL expects a flat array of objects
// preprocess extracts the nested array
const columns = await ds.loadDataFromURL({
  url: "https://api.example.com/report",
  type: "json",
  preprocess: (raw) => (raw as any).data.rows,
});

Concrete implementations

SqlDataSource is abstract. Use one of the built-in implementations:

Class	Environment	Backend
`DuckDBDataSource`	Node.js	Native DuckDB
`DuckDBWasmDataSource`	Browser	DuckDB WASM

import { DuckDBWasmDataSource } from "grid";

const ds = await DuckDBWasmDataSource.create();
await ds.loadDataFromURL({
  url: "/data/sales.json",
  type: "json",
});

// now query it
const rows = await ds.execute("SELECT city, SUM(revenue) FROM sales GROUP BY city");

Implementing your own `SqlDataSource`

If the built-in DuckDB implementations don't fit your use case, you can extend SqlDataSource with any WASM-based SQL engine. You need to implement three methods: execute(), insertArrowTable(), and release().

Here's an example using PGLite (Postgres compiled to WASM):

import { PGlite } from "@electric-sql/pglite";
import * as arrow from "apache-arrow";
import { SqlDataSource } from "grid";

class PGLiteDataSource extends SqlDataSource {
  private db: PGlite;

  private constructor(db: PGlite) {
    super();
    this.db = db;
  }

  // create a new in-memory PGLite instance
  static async create(): Promise<PGLiteDataSource> {
    const db = new PGlite();
    return new PGLiteDataSource(db);
  }

  // run a SQL query and return rows as key-value records
  async execute(sql: string): Promise<Record<string, any>[]> {
    const result = await this.db.query(sql);
    return result.rows;
  }

  // batch-insert Arrow data into the target table
  // PGLite doesn't have native Arrow support, so we fall back to
  // row-by-row INSERT using the Arrow table's JS accessors
  protected async insertArrowTable(table: arrow.Table, name: string): Promise<void> {
    const numRows = table.numRows;
    const fields = table.schema.fields;
    if (numRows === 0) return;

    for (let r = 0; r < numRows; r++) {
      const values = fields.map((_, c) => {
        const val = table.getChildAt(c)?.get(r);
        return val === null || val === undefined ? "NULL" : `'${String(val)}'`;
      });
      await this.db.exec(
        `INSERT INTO "${name}" VALUES (${values.join(", ")})`
      );
    }
  }

  // clean up when all consumers have released
  async release(): Promise<void> {
    this.refCount--;
    if (this.refCount <= 0) {
      await this.db.close();
    }
  }
}

Performance note

This example uses row-by-row inserts since PGLite doesn't support Arrow ingestion natively. For large datasets this will be slower than DuckDB WASM which accepts Arrow tables directly. Consider DuckDB WASM unless you specifically need Postgres compatibility.

API Reference

Extends DataSource.

Abstract base class for SQL-backed data sources.

Narrows DataSource<T> to DataSource<string> - the command to execute is always a SQL string. On top of query execution, it provides data ingestion via loadData() and loadDataFromURL(). Data must be provided in column-major format and is batched into the underlying SQL engine using Apache Arrow for efficient bulk inserts.

Subclasses must implement execute(), insertArrowTable(), and release().

Prop

Type

tablestring

The SQL table name where loaded data is stored. Auto-generated by loadData() if not provided.

Type

string

executefunction

Abstract. Execute a SQL string against the underlying WASM-based SQL engine and return the result rows. Implemented by concrete subclasses like DuckDBWasmDataSource.

Type

(sql: string) => Promise<Record<string, any>[]>

Parameters

sql -

A SQL query string (e.g. "SELECT city, SUM(revenue) FROM sales GROUP BY city").

Returns

Result rows as key-value records.

loadDatafunction

Load column-major data from JS memory into the WASM-based SQL engine in the browser.

Creates a table, inserts data using Apache Arrow, then casts each column to its target type based on the provided schema.

data is column-major: data[i] is the array of values for schema[i].

schema: [{ name: "city" }, { name: "revenue" }]
data:   [["NYC", "LA"],     [100, 200]]
           ^ data[0]          ^ data[1]

Type

(config: { table?: string; schema: DataSchema[]; data: any[][]; replace?: Map<string, Map<string, string>>; }) => Promise<void>

Parameters

config.schema -

Array of DataSchema column definitions describing name, type, and subtype.

config.data -

Column-major array of values. data[i] corresponds to schema[i], where each item in data[i] is a value of that column.

config.table -

Optional table name. Auto-generated if omitted.

config.replace -

Optional per-column string replacements applied before type casting. A Map<columnName, Map<searchString, replaceString>> - for each column, every occurrence of searchString in the cell value is replaced with replaceString. Useful for cleaning formatting like currency symbols or thousand separators (e.g. "$1,000" → "1000").

loadDataFromURLfunction

Fetch data from a URL, parse it as JSON or CSV, infer the schema if not provided, and load it into the SQL engine via loadData().

Type

(config: { url: string; type: "json" | "csv"; preprocess?: (data: unknown) => unknown; schema?: DataSchema[]; replace?: Map<string, Map<string, string>>; table?: string; }) => Promise<ColumnMetadata[]>

Parameters

config.url -

URL to fetch data from. A GET request is made using the browser's fetch API.

config.type -

Format of the data: "json" (array of objects) or "csv".

config.preprocess -

Optional callback to transform the raw parsed data before loading.

config.schema -

Optional column definitions. If omitted, types are inferred from values.

config.replace -

config.table -

Optional table name. Auto-generated if omitted.

Returns

Column metadata with inferred or provided schema information.

insertArrowTablefunction

Abstract. Batch-insert an Apache Arrow table into the WASM-based SQL engine in the browser. Called internally by loadData() after converting column-major arrays into an Arrow table.

Row-by-row inserts into a WASM SQL engine are slow due to per-row overhead across the JS-to-WASM boundary. Arrow's columnar memory layout allows the entire dataset to be transferred in a single operation, making bulk loads significantly faster.

Concrete subclasses must implement this to bridge Arrow data into their specific SQL engine. The target table is already created before this method is called - the implementation only needs to insert the data, not create the table.

Type

(table: arrow.Table, name: string) => Promise<void>

Parameters

table -

An Apache Arrow Table containing the data to insert.

name -

The name of the existing SQL table to insert into.

addReffunction

Increment the reference count. Call this when a new consumer starts using this datasource.

Type

() => void

releasefunction

Abstract. Decrement the reference count and release underlying resources (connections, WASM instances) when the count reaches zero.

Type

() => Promise<void>

On this page