Files on a server

vastgrid-duckdb opens table files with DuckDB and answers the grid’s requests: windows of rows, sorted and filtered views, column statistics, value lists, selection summaries, find and export. In the browser, a RemoteSource sends those requests to it.

npm install vastgrid vastgrid-duckdb

The engine and the handler

import path from "node:path";
import { createTableHandler, TableEngine, type TableHandlerOptions } from "vastgrid-duckdb";

const root = "/srv/data";
const engine = TableEngine.create({ allowedDirectories: [root], tempDirectory: "/tmp/vastgrid" });

export const options: TableHandlerOptions = {
  engine: () => engine,
  resolve: (ref) => {
    const file = path.resolve(root, ref);
    if (!file.startsWith(`${root}${path.sep}`)) throw new Error("Not allowed");
    return file;
  },
};

export const handler = createTableHandler(options);

TableEngine.create returns a promise; the handler waits for it. Create one engine per process and share it between requests: it keeps the open tables, their views and a pool of DuckDB connections.

createTableHandler returns a function from a Fetch API Request to a Response, for Next.js route handlers, Hono, Bun, Deno and anything else that has them. Pass requests with their signal, so that requests the browser gives up (the grid scrolled away) stop their queries.

import { DataGrid, RemoteSource } from "vastgrid";

const source = new RemoteSource({ endpoint: "/api/table", ref: "sales/2024.parquet" });
<DataGrid source={source} />;

ref is whatever your resolve understands: a path relative to a folder, an id in your database, a key of an upload.

Frameworks without Fetch API requests

handleTableRequest(body, options, signal) answers one parsed request and errorBody(error) turns an error into the status and body to send. With Express:

import express from "express";
import { errorBody, handleTableRequest } from "vastgrid-duckdb";
import { options } from "./table.ts"; // the options above

const app = express();
app.post("/api/table", express.json({ limit: "1mb" }), async (request, response) => {
  const controller = new AbortController();
  response.on("close", () => controller.abort());
  try {
    const result = await handleTableRequest(request.body, options, controller.signal);
    response.json({ result });
  } catch (error) {
    const { status, body } = errorBody(error, console.error);
    response.status(status).json(body);
  }
});

Options

TableEngine.create(options)

Option	Default	Description
allowedDirectories	(required)	Directories DuckDB may read and export into. All other file access is disabled for good.
tempDirectory	(required)	Where the engine keeps its database file, data spilled from memory and converted files. Each engine uses its own folder inside and deletes it on dispose(); folders left by crashed processes are deleted on the next start.
memoryLimit	a fifth of the memory, 1–4 GB	DuckDB’s memory limit, for example "4GB".
threads	CPUs − 1	DuckDB’s worker threads.
maxTables	4	Tables kept open; the least recently used ones are closed beyond this.
maxViews	2	Sorted or filtered views kept per table.
materializeRows	20000000	Tables with more rows get views that hold only the order of the rows instead of a sorted copy (see below).
previewRows	1000	Rows readable while a delimited or JSON file is still loading.
textLimit	2000	Longest text sent for a cell by default.
log		Receives problems that are not caused by a request, for your logs.

createTableHandler(options)

Option	Description
engine	The engine, or a function that returns it or a promise of it.
resolve(ref)	Turns the ref of a request into an absolute file path. Throw to refuse. This is where you decide what a client may open.
resolveTarget(target)	Turns the target of an export (source.exportView(…), called by your app) into an absolute path for a new file. Without it, exports are refused and capabilities.export is false.
onError(error)	Receives errors that are not caused by the request, for logging.

Security

• DuckDB can read only the allowedDirectories and its own temporary folder. The engine sets this when it starts and then disables changing it, so no request can reach other files, also not through SQL.
• Files are allowed by their real location, with links followed: a link inside an allowed folder to a file outside it is refused with not_allowed.
• SQL is generated by the engine and every value is a parameter. Clients send column indexes, never names or SQL.
• Error messages sent to clients name files without the server’s folders and leave out the SQL that DuckDB quotes.
• resolve runs for every table that is opened. Check there that the user may open the file, as you would for a download.
• A rows request reads at most 1,000,000 rows and 1,000,000 cells. Limit the size of request bodies in your framework too.

Files

The format is chosen by the file’s extension:

Format	Extensions
CSV	.csv, .csv.gz, .csv.zst
TSV	.tsv, .tab, .tsv.gz, .tsv.zst
Parquet	.parquet, .pq
JSON Lines	.jsonl, .ndjson, .jsonl.gz, .ndjson.gz
JSON	.json, .json.gz

• Delimited and JSON files become DuckDB tables in the engine’s database file. The first rows (previewRows) are readable at once; the grid shows them while the rest loads and enables sorting, filters and statistics when the load is done. A 735 MB CSV of 10 million rows shows its first rows in 0.15 s and is fully loaded in 5.3 s.
• Parquet files are read in place by row position, with no load: a 1.4 GB file of 100 million rows opens in 20 ms.
• Encodings that DuckDB cannot read, Shift_JIS and EUC-JP, are detected and converted to UTF-8 first. The table’s notes then say encoding:shift_jis, and the grid’s status bar shows it. Columns whose values do not fit one type are read as text (all_varchar), and invalid UTF-8 is replaced (invalid_utf8_replaced).
• When a file changes on disk (its size or modification time), the next info opens it again with a new revision. Change the grid’s reloadKey to read it again, for example after your app saved it.

Sorted and filtered views

Sorting and filtering create a view. For tables up to materializeRows rows (20 million by default) the view is a sorted copy of the table, so it scrolls as fast as the table: about 2 ms per block of rows. For larger tables the view holds only the order of the rows; it is built an order of magnitude faster and needs a fraction of the temporary space, and reading each block of rows from it takes 0.1–0.2 s for a 100M-row Parquet file. See Performance.

Deployment

• @duckdb/node-api is a native module. Keep it out of server bundles and install it with your server. For Next.js:

  next.config.ts

  export default {
    serverExternalPackages: ["@duckdb/node-api", "@duckdb/node-bindings"],
  };

• Ship the platform package, @duckdb/node-bindings-<platform> (about 120 MB on macOS). Serverless platforms with small bundle limits may not fit it.

• Give tempDirectory room: loaded files, sorted views and data spilled from memory are kept there.

• The engine keeps tables between requests, so run it in a long-lived process. Call dispose() when the process shuts down to close DuckDB and delete the temporary files.