serve, servePg, serveFlight

vectrojs/serve puts tables in front of other programs. serve() answers SQL over HTTP in JSON, CSV, JSON lines or Arrow, plus an OData v4 feed (Excel, Power BI’s Web and OData connectors, Google Sheets, pandas, curl). servePg() speaks the PostgreSQL wire protocol as a read-only PostgreSQL 16 (Power BI, Tableau, Metabase, Grafana, DBeaver, psql, every driver). serveFlight() speaks Arrow Flight SQL over gRPC (pandas, Polars and DuckDB through ADBC). All three only read, listen on 127.0.0.1 unless told otherwise, and can share one set of tables made by openTables(). The vectro serve command is these functions with flags. Node only. See Serving and BI tools.

import { serve, servePg, serveFlight, openTables } from 'vectrojs/serve';

Tables to serve

Each server takes its tables one of three ways: tables (tables and datasets from your code), root (a data folder), or catalog (the result of openTables(), shared between servers). With tables or root the server opens its own catalog and closes it on close().

openTables(options?)

Returns a promise of a catalog: the tables given in code plus one table per loadable file directly in root.

Name	Type	Default	Meaning
tables	object	{}	{ name: table or dataset }. These win over files of the same name.
root	string	none	A data folder (~ is expanded).
engine	Engine	the tables’ engine, else a new one	The engine files are loaded into.
log	function	none	Called with a message for each file skipped.

Files load by extension: .csv and .tsv (text that rarely repeats is loaded as categories up to 5,000,000 rows, so it can be filtered and grouped), .jsonl and .ndjson (one flat object per line; column kinds from the first 1,000 lines), .arrow, .feather and .ipc, and .vectro datasets, which are opened on disk rather than loaded. A table is named after its file: daily sales.csv becomes daily_sales. Files over 2 GB other than .vectro are refused, and only regular files directly in the folder are read (no subfolders, no symbolic links).

The catalog has:

Name	Meaning
engine	The engine.
entries	A Map of name to { table, source, owned }; source is 'given' or the file path.
root	The folder’s real path, or null.
version	Goes up by one on each reload.
named()	{ name: table }, as sql() takes it.
reload()	Reads the folder again (file tables are released and reloaded; new files become tables). Returns { loaded, failed }, failed as [{ file, error }]. Throws without a root.
close()	Releases the file tables, and closes the engine if the catalog made it.

HTTP and OData

serve(options?)

Starts an HTTP server and returns a promise of it once it is listening.

Name	Type	Default	Meaning
tables, root, catalog, engine			The tables, as above.
port	number	8787	0 picks a free port.
host	string	'127.0.0.1'	The address to listen on.
token	string	none	Requests need Authorization: Bearer <token>, Basic with the token as the password (any user name), or ?token= in the URL.
cors	boolean	false	Allow pages from other sites (Access-Control-Allow-Origin: *).
odataPage	number	50000	Rows per OData page; further pages come through @odata.nextLink.
log	function	none	Called with a line per query and per error.

Endpoint	Gives
GET /sql?q=...	The answer to one SELECT. format is json (default), csv, ndjson or arrow; limit=n caps the rows.
POST /sql	The same, with the SQL as the body, or JSON { "sql": "...", "format": "csv", "limit": 100 }. Bodies are limited to 1 MB.
GET /tables	{ tables: [{ name, rows, columns, file?, on_disk? }] }.
GET /tables/<name>	One table’s description.
GET /tables/<name>/rows	A whole table, with format and limit.
POST /reload	Reads the data folder again; returns { loaded, failed }.
GET /odata, /odata/$metadata, /odata/<table>	An OData v4 feed: $filter, $select, $orderby, $top, $skip, $count, $apply=groupby(...)/aggregate(...) run in Vectro.
GET /	The endpoints and table names.

JSON answers are { columns: [{ name, type }], rows: [[...], ...], row_count, ms }, type being number, text or date (ISO text). Errors are JSON { error } with status 400 (bad SQL or request), 401 (token), 404, 405 or 413.

The returned server has url, port, server (the Node http.Server), engine, tables (the catalog’s entries), catalog, reload() and close(), which closes open connections and, unless the catalog was passed in, the catalog.

import { Engine } from 'vectrojs';
import { serve } from 'vectrojs/serve';

const engine = new Engine();
const orders = engine.table({ region: ['EU', 'US', 'EU', 'APAC'], price: [120, 80, 45, 300] });
const server = await serve({ tables: { orders }, port: 0, token: 'secret' });

const q = encodeURIComponent('SELECT region, SUM(price) AS total FROM orders GROUP BY region ORDER BY total DESC');
const headers = { authorization: 'Bearer secret' };
console.log(await (await fetch(`${server.url}/sql?q=${q}`, { headers })).json());
console.log(await (await fetch(`${server.url}/sql?q=${q}&format=csv&token=secret`)).text());
console.log((await fetch(`${server.url}/tables`)).status);    // 401 without the token

const odata = await fetch(`${server.url}/odata/orders?$filter=${encodeURIComponent('price gt 100')}&$select=region`, { headers });
console.log((await odata.json()).value);

await server.close();
await engine.close();

PostgreSQL

servePg(options?)

Starts a PostgreSQL protocol server and returns a promise of it. It answers as PostgreSQL 16, read only: the simple and the extended query protocol (prepared statements, $1 parameters, text and binary values, portals), BEGIN and COMMIT, SET and SHOW, DECLARE and FETCH cursors, cancel requests, and the information_schema and pg_catalog queries drivers and BI tools send. Writes are refused.

Name	Type	Default	Meaning
tables, root, catalog, engine			The tables, as above.
port	number	5433	0 picks a free port.
host	string	'127.0.0.1'	Any address other than 127.0.0.1, ::1 or localhost needs a password.
user	string	none	The only user name accepted. Without it any name is.
password	string	none	Without it, anyone on this machine may connect.
auth	'scram', 'md5' or 'password'	'scram'	How the password is checked (SCRAM-SHA-256 by default).
tls	object	none	{ cert, key } (PEM text or bytes) or { certFile, keyFile }, for encrypted connections.
database	string	'vectro'	The database name reported.
log	function	none	Called with connection messages.
logQueries	boolean	false	Also log every statement clients send.

Column types: whole-number columns are bigint, other numbers double precision, text text, dates timestamp (date when every value is at midnight). The returned server has port, host, server, catalog, url (postgresql://user@host:port/database) and close().

Arrow Flight SQL

serveFlight(options?)

Starts an Arrow Flight SQL server over gRPC (HTTP/2) and returns a promise of it. Served: statements and prepared statements (without parameters), the catalog (catalogs, schemas, tables with their Arrow schemas, table types), and sign-in with a user name and password (Handshake or Basic, then a bearer token). Answers are Arrow, column by column: whole numbers int64, other numbers float64, text utf8, dates timestamp (ms, UTC) or date32.

Options: tables, root, catalog, engine, port (default 31337), host, user, password, tls, database, log and logQueries, as for servePg (no auth). The returned server has port, host, server, catalog, url (grpc://host:port, or grpc+tls:// with tls) and close().

import { mkdtempSync, writeFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { serve, servePg, serveFlight, openTables } from 'vectrojs/serve';

const dir = mkdtempSync(join(tmpdir(), 'vectro-'));
writeFileSync(join(dir, 'daily sales.csv'), 'region,amount\nEU,10\nUS,20\nEU,5\n');

const catalog = await openTables({ root: dir });
const http = await serve({ catalog, port: 0 });
const pg = await servePg({ catalog, port: 0, user: 'me', password: 'secret' });
const flight = await serveFlight({ catalog, port: 0 });
console.log([...catalog.entries.keys()], pg.url, flight.url);

writeFileSync(join(dir, 'regions.tsv'), 'region\tname\nEU\tEurope\nUS\tUnited States\n');
console.log(await (await fetch(`${http.url}/reload`, { method: 'POST' })).json());
const q = encodeURIComponent('SELECT r.name, SUM(s.amount) AS total FROM daily_sales s JOIN regions r USING (region) GROUP BY 1');
console.log((await (await fetch(`${http.url}/sql?q=${q}`)).json()).rows);

await Promise.all([http.close(), pg.close(), flight.close()]);
await catalog.close();
rmSync(dir, { recursive: true, force: true });