SqlStandardTableDataModel

SqlStandardTableDataModel is the SQL implementation of StandardTableDataModel. It takes a DataSchema[], a SqlDataSource, and an optional StandardTableConfig, then translates each StandardDataFetchAndTransformIR into a single SQL SELECT query.

This implementation targets in-browser WASM SQL engines (like DuckDB WASM) where the grid runs queries locally in the browser. If your data lives behind an API that connects to a full SQL server (Postgres, BigQuery, Redshift, Snowflake etc.), you can use this class as a reference for how to translate the IR into SQL and build your own StandardTableDataModel subclass that generates server-compatible queries.

class SqlStandardTableDataModel extends StandardTableDataModel {
  protected table: string;
  protected dataSource: SqlDataSource;
  constructor(dataSchema: DataSchema[], dataSource: SqlDataSource, config: Partial<StandardTableConfig>, metadataPlumber?: StandardMetadataPlumber);
  protected buildResolverInput(ir: StandardDataFetchAndTransformIR): StandardMetadataResolverInput;
  getData(ir: StandardDataFetchAndTransformIR, metadataResolver?: StandardMetadataResolver<unknown>): Promise<GetRowsResponse>;
  getRangeOfColumn(field: string): Promise<ColumnRangeValues>;
}

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";

interface StandardTableConfig {
  pageSize: number;
  maxNumPageBeforeEviction: number;
}

interface StandardDataFetchAndTransformIR {
  startRow: number;
  endRow: number;
  groupPath: string[];
  groupBy: string[];
  project: string[];
  sort: SortEntry[];
  filter: ScalarFilter[];
  metadata?: unknown;
}

interface SortEntry {
  field: string;
  direction: SortDirection;
  by?: string;
}

type SortDirection = "asc" | "desc" | "noop";

interface ScalarFilter {
  type: "scalar";
  field: string;
  subtype?: "date";

  op: "eq" | "neq" | "in" | "not_in"
    | "gt" | "lt" | "gte" | "lte"
    | "between"
    | "contains" | "doesNotContain" | "startsWith" | "endsWith"
    | "before" | "after"
    | "empty" | "notEmpty";

  value: string | string[] | number | number[] | null;
}

interface StandardMetadataResolverInput {
  ir: StandardDataFetchAndTransformIR;
  schema: DataSchema[];
  dataSource?: DataSource<any>;
}

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

interface StandardMetadataResolver {
  resolve(input: StandardMetadataResolverInput): T[];
}

interface GetRowsResponse {
  rowData: any[][];
  totalRowCount: number;
  metadata?: Record<string, any[]>;
}

type ColumnRangeValues = | { type: "categorical"; values: string[] }
  | { type: "range"; min: number; max: number }
  | { type: "temporal"; min: string; max: string };

Constructor

import { SqlStandardTableDataModel } from "grid";

const model = new SqlStandardTableDataModel(schema, dataSource);
// or with config overrides:
const model = new SqlStandardTableDataModel(schema, dataSource, {
  pageSize: 5000,
  maxNumPageBeforeEviction: 100,
});

• schema - DataSchema[] describing each column's name, type, and subtype
• dataSource - a SqlDataSource instance (e.g. DuckDBWasmDataSource) with data already loaded
• config - optional Partial<StandardTableConfig>. Defaults to { pageSize: 10000, maxNumPageBeforeEviction: 300 } if omitted. See Configuration

The constructor reads the table name from dataSource.table and stores it for query generation.

getData implementation

getData receives a StandardDataFetchAndTransformIR and builds a single SQL query. The query shape depends on whether the IR has grouping active.

The code snippets below show user-facing getViewModelData() calls - the parent class handles page caching and orchestration, then calls getData() internally with the same IR shape. The SQL shown is what getData generates via its internal buildSQL method.

The IR has two fields that control grouping: groupBy is an ordered list of columns that define the nesting hierarchy (e.g. ["country", "state", "city"]), and groupPath is the path the user has drilled into (e.g. ["USA"] means "show what's inside USA"). When groupPath is shorter than groupBy, the model is at a group level and emits a GROUP BY query. When they are equal length (or groupBy is empty), the model is at the leaf level and emits a flat SELECT. See Row grouping for how expand/collapse drives these values.

Flat mode (no grouping)

When groupBy is empty or groupPath has reached the leaf level (groupPath.length >= groupBy.length), the model generates a plain SELECT with pagination:

const vm = await model.getViewModelData({
  startRow: 0,
  endRow: 50,
  groupBy: [],
  groupPath: [],
  project: ["city", "revenue", "quarter"],
  sort: [{ field: "revenue", direction: "desc" }],
  filter: [{ type: "scalar", field: "quarter", op: "in", value: ["Q1", "Q2"] }],
});

The equivalent SQL (formatted for readability; actual output is a single line):

SELECT "city", "revenue", "quarter", COUNT(*) OVER() AS __total__
FROM "sales"
WHERE "quarter" IN ('Q1', 'Q2')
ORDER BY "revenue" DESC
LIMIT 50 OFFSET 0

COUNT(*) OVER() is a window function that returns the total row count across the full filtered result, without requiring a separate count query. The model reads it from the first row (rows[0].__total__) and strips it from the output data.

Grouped mode

When groupPath.length < groupBy.length, the model generates a GROUP BY query. The groupPath values become WHERE equality conditions that drill into the hierarchy, and the current level's group field becomes the GROUP BY column:

const vm = await model.getViewModelData({
  startRow: 0,
  endRow: 50,
  groupBy: ["country", "state", "city"],
  groupPath: ["USA"],
  project: ["country", "state", "city", "revenue"],
  sort: [],
  filter: [],
});

groupPath: ["USA"] means "fetch states inside USA". The depth is 1, so groupBy[1] ("state") is the group field. The equivalent SQL:

SELECT "state", COUNT(*) AS __count__, SUM("revenue") AS "revenue",
       COUNT(*) OVER() AS __total__
FROM "sales"
WHERE "country" = 'USA'
GROUP BY "state"
ORDER BY "state"
LIMIT 50 OFFSET 0

Step by step:

1. "country" = 'USA' comes from groupPath[0] matching groupBy[0]
2. "state" is the GROUP BY field (the current level being fetched)
3. COUNT(*) gives the number of rows in each group (used by the renderer for expand/collapse)
4. SUM("revenue") aggregates measures - the aggregation function comes from the column's aggregateFn in the schema
5. COUNT(*) OVER() gives the total number of groups (for pagination)

Only columns whose schema has an aggregateFn are included as aggregated measures. The group field itself and any dimension columns without aggregateFn are excluded from the output in grouped mode.

getRangeOfColumn implementation

getRangeOfColumn returns the domain of a column - used by filter UIs to show available values or numeric/date ranges. The query depends on the column's schema type:

Measures - returns the numeric min and max:

SELECT MIN("revenue") AS min_val, MAX("revenue") AS max_val
FROM "sales"

Returns { type: "range", min: number, max: number }.

Temporal dimensions - returns the date range as strings:

SELECT MIN("order_date") AS min_val, MAX("order_date") AS max_val
FROM "sales"

Returns { type: "temporal", min: string, max: string }.

Categorical dimensions - returns all distinct values:

SELECT DISTINCT "region" FROM "sales" ORDER BY "region"

Returns { type: "categorical", values: string[] }.

How filters are translated

Each ScalarFilter in the IR's filter array becomes a WHERE clause. Multiple filters are combined with AND. The supported operators:

String values are escaped (single quotes doubled) to prevent SQL injection within the WASM engine.

Date-part filters

When a filter has subtype: "date", the model wraps the column with EXTRACT before comparing. A DatePartScalarFilter has an additional part field ("year", "month", "quarter", etc.) that specifies which component to extract:

{ type: "scalar", subtype: "date", field: "order_date", part: "year", op: "eq", value: 2024 }

Produces:

EXTRACT(year FROM "order_date") = 2024

This enables queries like "orders in Q1" or "year between 2020 and 2024" that operate on date parts rather than raw timestamp values.

How sort is translated

Each SortEntry in the IR's sort array becomes an ORDER BY clause. Entries with direction: "noop" are skipped. The translation depends on the query mode:

Flat mode - sort by the column value directly:

ORDER BY "revenue" DESC, "city" ASC

Grouped mode - the sort target depends on what is being sorted:

• Sorting by the group field itself: ORDER BY "state" ASC
• Sorting by a measure (via the by field on SortEntry): ORDER BY SUM("revenue") DESC - uses the column's aggregateFn from the schema
• Sorting by another aggregated field (a column with aggregateFn in its schema): ORDER BY SUM("quantity") ASC. Fields without aggregateFn are silently skipped in grouped mode

If no sort entries are provided and a group field exists, the model defaults to ORDER BY "groupField" to ensure consistent ordering.

API Reference

Extends StandardTableDataModel.