Pushdown Sources: JavaScript Data Grid Guide

A grid over an engine

A pushdown source sits the grid in front of a query engine and lets the two divide the work. When the user sorts, filters or scrolls, the grid turns that into one portable query, an adapter translates it into whatever the engine speaks, and only the rows asked for come back. The grid does not fetch a table and thin it in the browser; the engine does the part it is good at, over data that never has to leave it.

import { createGrid, createPushdownSource, odataAdapter } from '@toclocoinc/lattice-grid';
import * as compute from '@toclocoinc/lattice-grid';

createGrid(el, {
  columns,
  rowKey: 'OrderID',
  source: createPushdownSource({
    adapter: odataAdapter({ url: 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders' }),
    compute,        // the grid's own kernels, for anything the engine did not do
    pageSize: 100,
  }),
});

The grid stays a client library. An adapter takes a connection, a URL or a token that is already yours and imports nothing, so the bundle is byte-for-byte the same whether a page uses this or not. That is the point worth making: the grid can drive a full analytical engine without carrying a byte of one.

An adapter declares what it can do

Every engine speaks a different amount of the grid's query. Some take a full condition tree, some a single search term, some cannot sort at all. So an adapter declares its capabilities, and the grid pushes down only what the adapter claims and does the rest itself. Everything is off until declared, which is the safe direction to be wrong in: an adapter that declares nothing still works, the grid simply fetches and does the whole job.

See the split

Every grid vendor claims server-side data. What none of them show is which half of your query actually reached the server. source.lastPlan() tells you, after every query.

const plan = source.lastPlan();
plan.pushed;    // the query the adapter was given
plan.residual;  // { filters, sort, quick } the grid applied afterwards
plan.unpushed;  // ['filter'], the parts that stayed behind
plan.needsAll;  // true when the whole result had to be fetched

Turn an adapter's declared capabilities down to nothing and the same filter moves from the engine to the browser, with the plan changing to match. It is the honest version of "server-side": you can watch where the work went.

The rules that shape the split

The grid never trades a right answer for a faster one. Three rules keep a partial pushdown correct:

• A conjunction splits; a disjunction does not. An and narrows with each condition, so the supported parts go to the engine and the grid narrows the superset that comes back. An or widens with each branch, so pushing only the supported branches would return fewer rows than the filter allows, and the grid cannot recover rows that were never fetched. An unsupported or stays whole on the client.
• A sort is pushed whole or not at all. Ordering by the first column and fixing the rest locally needs every row anyway.
• Residual work needs the complete result. When a filter, a sort or a quick search cannot be pushed, the grid asks for everything rather than a window. Filtering a window on the client is not a slower route to the right answer, it is a fast route to a wrong one: the rows belonging on page one may sit on page nine.
• Grouping, totals and statistics are not residual work. By default they are computed over the rows the grid has loaded, so over a windowed source they describe the view in front of you rather than the whole result, and a dev-mode warning fires when a figure covers only part of the matching set. There are two ways to get a whole-dataset figure instead. Turn on the full-dataset pull and the grid fetches the entire matching set once and holds it, computing every group, total and statistic over all of it. Or declare an aggregate-pushdown policy and the supported statistics are computed by the engine over the whole set, ungrouped or with a GROUP BY, and only the numbers come back. Both are opt-in; without them a windowed source stays window-scoped and says so.

Rows still on their way in say so

A row waiting on a page, a live feed or a remote query never sits blank with no explanation. Every visible cell of an unloaded row shows a brief shimmer, or, for several such rows together, the middle one reads "Loading rows 200 to 350" in their place. Both wait a short moment before appearing, so a fast answer never flickers on the way past, and a row already on screen is replaced in place the instant its data lands, with no jump.

createGrid(el, {
  columns, rowKey: 'id', source,
  loading: {
    text: 'Loading…',    // shown while the first page or count is in flight
    delay: 150,           // ms before that banner appears; a fast answer skips it
    rowDelay: 150,        // ms before an unloaded row's shimmer appears
    banner: false,        // omit or true for the banner; false keeps the grid quiet instead
  },
});

A grid whose first page or first count is still in flight shows its own loading overlay, spinner and wording included, until rows land; an overlay a host has shown itself is never replaced or taken down early by it. Word the banner your own way with loading.text, tune either wait with loading.delay and loading.rowDelay, or turn the banner off entirely with loading.banner: false and let the grid stay visually quiet until the first page answers. A grid fed rows straight from memory never shows either treatment: there is nothing to wait for.

Pivoting in the engine

A pivot on a pushdown source runs where the data lives. The grouping column and the pivot columns become the query's own GROUP BY keys, the engine returns one row per group and pivot key that actually occurs, and the grid reshapes those rows into pivot columns. No leaf row crosses the wire to do it: pivoting a ten-million-row table by country and category moves the ninety occupied combinations, not the ten million rows behind them. DuckDB, ClickHouse, Elasticsearch and Splunk all answer a pivot this way.

The column set comes from the answer, not from a list you declare: the root level also asks the engine for every pivot key over the whole matching set, so the headings are whatever the engine found. A group still opens its children on demand, narrowed by the parent key, the same lazy expansion a pushdown grouping already does.

-- The level: one row per country, with its row totals, windowed.
SELECT "country" AS "__lattice_key", count(*) AS "__lattice_leaves", sum("amount") AS "amount",
       count(*) AS "transaction_id", avg("risk_score") AS "risk_score"
FROM read_parquet('transactions.parquet')
GROUP BY "country" ORDER BY "country" ASC NULLS LAST LIMIT 100 OFFSET 0

-- The axis: every category over the matching set, the column headings.
SELECT "category" AS "__lattice_p0", count(*) AS "__lattice_leaves", sum("amount") AS "amount"
FROM read_parquet('transactions.parquet')
GROUP BY "category" ORDER BY "category" ASC NULLS LAST

-- The cells: country x category for the countries in the window, keys bound.
SELECT "country" AS "__lattice_key", "category" AS "__lattice_p0", count(*) AS "__lattice_leaves",
       sum("amount") AS "amount"
FROM read_parquet('transactions.parquet')
WHERE ("country" = ? OR "country" = ? OR ...) GROUP BY "country", "category"

An adapter with no grouped aggregate to ask, the REST, OData, GraphQL and DFQL adapters among them, cannot pivot in the engine, and a remote source holds only a window rather than the leaves a browser pivot would need. Asking one to pivot is refused by name rather than shown as ordinary grouped rows with nothing pivoted: the grid's error overlay names the refusal, and lastPlan().pivotReason says why.

Grouping over an engine that cannot group

ClickHouse and Elasticsearch group in the engine, the same way DuckDB and Splunk already compute a pushed aggregate: each level of a grouping is one grouped query, its subtotals and its per-group counts come straight from the database, and no leaf row crosses the wire until a group is opened. Opening a group fetches its own rows a page at a time, narrowed by that group's own key, and a nested grouping asks one more grouped query per level opened.

An adapter with nothing to ask for a grouped total, the REST, OData, GraphQL and DFQL adapters among them, stays ungrouped instead of guessing: every row shows as a plain leaf, the row count is the source's own, and the grid names the reason it did not group (pushdown:group-unsupported) rather than building a subtotal from whichever rows happened to already be on screen.

Where a filter's value list comes from

A set filter's list of values to tick comes from the engine on a pushdown source, not from a walk of the rows in the browser. Opening the popup asks for the top 200 values by count, most frequent first; a column with more says so ("Top 200 of 8,412 values") and a search inside the popup asks the engine again with a contains condition, so a value past the first 200 is still one search away. Unticking a value from a capped list filters with notIn rather than dropping every value the list didn't show. The same route serves the header popup and the tool panel's filter section alike, and an adapter that cannot answer the question offers the ordinary text condition editor instead and says why.

Pagination pages the source

Turn on the pager over a paged or remote source and it drives the source rather than a copy of it already in the browser. Clicking Next or Previous, typing a page number and pressing return, or changing the page size each fetch exactly the rows that page needs, one request when the page size matches the source's own. A sort, a filter or a quick filter search returns the pager to page zero, because the rows a page held under the old order or the old filter are not the rows that belong there under the new one.

The row count shown in the pager is the current page: row 201 to 300 of the page you are on, not the whole matching set. The total page count and the match count above the grid still read the whole result, exactly as they do without pagination turned on, so the two figures answer different questions side by side rather than disagreeing. While the very first page or count is still in flight the pager keeps Next enabled and shows the rows arriving as loading rows, rather than guessing at a total early and having to correct it.

A source that cannot return a window at all, a streaming source among them, or a custom source with no page model, refuses pagination once by name (pagination:source-unsupported) and keeps scrolling with no pager, rather than drawing a pager that moves nothing.

Select all on a pushdown source

A pushdown source holds only the rows scrolled past, so "select all" there selects a predicate: every row the current filter matches, less anything unticked afterwards. The header checkbox shows checked, or indeterminate once a row is taken out, and the status bar reads the true count from the engine rather than the rows loaded.

grid.selection.predicate();
// { all: true, filter, except } after select-all, minus anything you untick,
// or the ordinary { all: false, include }.

await grid.selection.count();   // asks the source for the matching count
for await (const row of grid.selection.stream()) { /* every selected row, paged */ }

// export.csv, export.excel and export.clipboard with rows: 'selected'
// stream the whole selection this way and return a Promise for it.
const rows = await grid.export.csv({ rows: 'selected' });

A filter change after select-all leaves the captured selection alone rather than growing or shrinking it, and ticking a row outside it is refused. Deleting that selection hands beforeDelete the predicate and the source's own count, with no row keys to iterate, so a host deletes by that filter on its own backend rather than row by row.

The adapters

Eight adapters ship, and none of them carries an engine. Four are covered in full below: OData, REST, DuckDB and DFQL. The other four have a page of their own with the full setup: ClickHouse, Elasticsearch and OpenSearch, Splunk and GraphQL. The connect your data overview places every one of them beside the source modes.

odataAdapter

Point it at an entity-set URL and it pushes a full condition tree, multi-column sort, windowing through $top and $skip, the count, and the quick filter through $search where the service supports it. Authenticate with a header, or a custom fetch for a token that refreshes. It follows an @odata.nextLink when the whole result is needed, up to a ceiling of two hundred pages so a filter matching far more than expected fails loudly rather than fetching forever.

restAdapter

For the endpoint you already have. Rename the parameters to whatever it calls them, and declare only the operators it truly applies, because claiming more returns wrong rows while claiming less only costs speed.

restAdapter({
  url: '/api/orders',
  // Rename the parameters to whatever your endpoint already calls them.
  params: { offset: 'skip', limit: 'take', sort: 'orderBy', order: 'dir' },
  // Declare only the comparisons the endpoint genuinely applies.
  operators: ['eq', 'gt', 'lt', 'contains'],
  rows:  (body) => body.results,        // pull the rows out of your shape
  total: (body) => body.meta.totalCount, // and the match count
})

duckdbAdapter

Hand it a DuckDB connection you made, including DuckDB-Wasm running in the browser over a Parquet file, and it pushes down the filter tree, a multi-column sort and the page, with the total in the same round trip through count(*) OVER (). For grouping, totals and statistics over the whole dataset, it does two more things: it can pull the entire matching set in and compute over all of it, or it can push the aggregation down to the engine, ungrouped or with a GROUP BY, so the figures come back computed over the whole set. Values bind through prepared statements, and a BIGINT past the safe integer range is kept as a string rather than rounded into a plausible lie.

const source = createPushdownSource({
  adapter: duckdbAdapter({
    connection,   // a DuckDB connection you made; the grid imports no engine
    from: "read_parquet('data/readings.parquet')",
  }),
  compute,
});
// A hundred thousand rows, queried in the tab, no back end. The adapter pushes down
// the filter tree, a multi-column sort, and the page.

dfqlAdapter

For a DemandFlow entity, taking a personal access token and a tenant. An internal integration rather than a public one.

Writing your own

A ninth adapter is an object with a capabilities declaration and an execute(query, request) function; everything above, paging, abort, the residual filter/sort/quick pass, the group and pivot planner and the write-back bridge, is createPushdownSource itself, the same code every shipped adapter runs on top of. The adapter contract states the whole surface precisely: what you declare, the request and answer shapes, how a refusal is named, and the write-back contract for an adapter that accepts edits.

One thing to get right

The commonest mistake is total. It is the count of everything matching the filter, not the length of the page returned. The grid sizes its scrollbar from it, so returning the page length makes a large result look like a single page. If your adapter can report the real match count, declare total; if it cannot, leave it off and the grid manages without it.

Three live demos: the OData demo drives a public service and shows lastPlan() as you filter; the DuckDB demo pulls a hundred-thousand-row Parquet file into the browser and groups it, so the subtotals and grand total are over all of it; and the live DuckDB demo keeps the whole-set pull off and prints the SQL the adapter generated for each interaction. The security-log triage demo adds the whole-set aggregates: the top talkers come from a GROUP BY pushed to DuckDB over everything the filter matches. Half a million transactions in Parquet puts the whole thing on one screen: the statement, its bound values, the match count and the measured time, per interaction. Pushdown pairs with derived grids, which compose grids in the browser once the rows arrive.