HTTP, OData, PostgreSQL, Flight SQL

Vectro can answer SQL for other programs: spreadsheets, BI tools, notebooks, other services. One command serves a folder of data files; the same servers are a function call away in your own app. Every server is read only.

npx vectro serve --root ~/data                          # HTTP on 127.0.0.1:8787
npx vectro serve --root ~/data --pg 5433 --user me --password secret   # and PostgreSQL
npx vectro serve --root ~/data --flight 31337           # and Arrow Flight SQL

The servers are in beta: they are checked with the drivers BI tools use, not yet with every application. For step-by-step setups in Power BI, Tableau, Metabase, Grafana and Excel, see BI tools.

vectro serve

Every CSV, TSV, JSON-lines (.jsonl, .ndjson), Arrow / Feather (.arrow, .feather, .ipc) and .vectro file directly in the folder becomes a table named after its file (daily-sales.csv is daily_sales). Datasets are opened on disk, not loaded, so a folder can hold tables of any size; other files are loaded into memory and must be under 2 GB. Text columns load as categories, so tools can group, filter and join on them (free text in files over 5M rows stays as it is).

Option	Meaning
--root <folder>	the data folder (default: the current folder)
--host 127.0.0.1	the address to listen on; 0.0.0.0 for every network
--port 8787	the HTTP port; --no-http for none (PORT also works)
--token <secret>	HTTP requests need this token (or VECTRO_TOKEN)
--cors	allow pages on other sites to call the HTTP server
--pg 5433	also speak the PostgreSQL protocol on this port
--user <name>, --password <secret>	PostgreSQL and Flight sign-in (or VECTRO_PG_PASSWORD); a password is required with a --host other than this machine
--auth scram | md5 | password	how PostgreSQL checks the password (default scram)
--tls-cert <file>, --tls-key <file>	encrypted PostgreSQL and Flight connections
--database <name>	the database name reported (default vectro)
--flight 31337	also serve Arrow Flight SQL on this port
--log-queries	print every statement clients send
--quiet	no log lines

SQL over HTTP

Endpoint	Gives
GET /sql?q=… or POST /sql (the SQL as the body, or JSON { "sql": … })	the answer; format=json (default), csv, ndjson or arrow; limit=n
GET /tables, GET /tables/<name>	table names, rows and column types
GET /tables/<name>/rows?format=csv	a whole table (limit=n for the first rows)
POST /reload	reads the data folder again; new files become tables
GET /odata, /odata/$metadata, /odata/<table>?$filter=…	an OData v4 feed (below)

JSON answers are { columns, rows, row_count, ms }, with each row an array. The SQL is Vectro’s SQL; statements that change data (INSERT, DELETE, CREATE…) get a 400 error.

In your own app, serve() from vectrojs/serve takes tables you already have:

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

const engine = new Engine();
const orders = engine.table({
  order_id: [1, 2, 3, 4, 5, 6],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price: [120, 80, 45, 300, 99, 15],
  qty: [2, 1, 4, 1, 3, 10],
});

const server = await serve({ tables: { orders }, port: 0, token: 'secret' });  // port 0: any free port
const auth = { authorization: 'Bearer secret' };
const q = encodeURIComponent('SELECT region, SUM(price * qty) AS revenue FROM orders GROUP BY region ORDER BY revenue DESC');

let r = await fetch(`${server.url}/sql?q=${q}`);
console.log(r.status, (await r.json()).error);              // 401: no token

r = await fetch(`${server.url}/sql?q=${q}`, { headers: auth });
console.log(await r.json());

r = await fetch(`${server.url}/sql?q=${q}&format=csv&token=secret`);  // for tools that can't send headers
console.log(await r.text());

r = await fetch(`${server.url}/sql?format=ndjson`, { method: 'POST', headers: auth, body: 'SELECT COUNT(*) AS n FROM orders' });
console.log(await r.text());

r = await fetch(`${server.url}/sql?q=${encodeURIComponent('DELETE FROM orders')}`, { headers: auth });
console.log(r.status, (await r.json()).error);              // read only

await server.close();
await engine.close();   // the server doesn't close an engine it was given

serve(options) takes tables ({ name: table or dataset }), root (a data folder), port (8787), host ('127.0.0.1'), token, cors, odataPage (50,000) and log. It returns { url, port, server, engine, tables, catalog, reload(), close() }.

Safety. The servers listen on 127.0.0.1 unless given another host. With a token, HTTP requests need Authorization: Bearer <token>, Basic with any user name and the token as the password, or token= in the URL. The HTTP server doesn’t require a token on other hosts, so set one whenever it listens beyond this machine. Files outside the data folder are never read.

Speed. On 10M orders, a group by region answers in 9 ms over HTTP; 1M rows export in 0.6 s as CSV (42 MB) or JSON and in 0.07 s as Arrow (28 MB), and all 10M rows as Arrow in 0.45 s. Arrow is the fastest way to move rows into pandas or Polars.

OData

The HTTP server also serves OData v4 at /odata. Power BI and Excel (Get Data > OData feed) and Tableau (its OData connector) see every table with its column types, and their filters, column choices, sorting and paging run in Vectro:

Query option	Example
$filter	region eq 'EU' and qty gt 2
$select	order_id,price
$orderby	price desc
$top, $skip	$top=100&$skip=200
$count	$count=true, or /odata/orders/$count
$apply	groupby((region),aggregate(qty with sum as total,$count as n))

Answers come 50,000 rows a page (or Prefer: odata.maxpagesize=n), with @odata.nextLink to the next page. A table’s key is its first whole-number column whose name ends in id; requests for one row by key aren’t served.

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

const engine = new Engine();
const orders = engine.table({
  order_id: [1, 2, 3, 4, 5, 6],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price: [120, 80, 45, 300, 99, 15],
  qty: [2, 1, 4, 1, 3, 10],
});
const server = await serve({ tables: { orders }, port: 0 });
const odata = async (query) => (await fetch(`${server.url}/odata/orders?${query}`)).json();

console.log((await odata(`$filter=${encodeURIComponent("region eq 'EU' and qty gt 2")}&$select=order_id,price&$orderby=price desc`)).value);
console.log((await odata(`$apply=${encodeURIComponent('groupby((region),aggregate(qty with sum as total,$count as n))')}`)).value);
console.log((await odata('$top=2&$count=true'))['@odata.count']);

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

With a token, OData clients sign in with Basic: any user name, the token as the password. On 10M orders, a filtered page answers in 44 ms, a 50,000-row page in 0.13 s and a group-by in 28 ms. Pages far into a big table cost more, so for importing all of a very large table use the PostgreSQL protocol.

PostgreSQL protocol

With --pg, Vectro answers as a PostgreSQL 16 database, read only, so anything with a Postgres connector connects live: Power BI, Tableau, Metabase, Grafana, DBeaver, Excel through ODBC, psql and every driver. Queries arrive in each tool’s own SQL and run on Vectro’s engine.

npx vectro serve --root ~/data --pg 5433 --user me --password secret
psql -h 127.0.0.1 -p 5433 -U me vectro

• The protocol: SCRAM-SHA-256 (or --auth md5 | password), TLS with --tls-cert / --tls-key, the simple and the extended query protocol (prepared statements, $1 parameters, text and binary values, portals read a part at a time), BEGIN / COMMIT, SET / SHOW, DECLARE / FETCH cursors, cancel requests and PostgreSQL error codes.
• Types: whole-number columns are bigint, other numbers double precision, text text, dates timestamp (date when every value is at midnight). Answers are typed from the query before any row is read: COUNT is bigint, AVG double precision, comparisons boolean.
• The catalog: information_schema and pg_catalog list the served tables and columns, the metadata queries drivers send (Npgsql’s type loading, pgJDBC’s getTables / getColumns) are answered as PostgreSQL would, and questions about keys, indexes and comments get empty answers. psql’s \dt and \dn work.
• Checked with: pgJDBC 42.7.4 (used by Tableau, DBeaver and Metabase) and Npgsql 4.0.17 (the driver in Power BI’s PostgreSQL connector), including Power BI’s own catalog queries. Power BI and Tableau themselves haven’t been tried yet.
• Safety: without a password only this machine may connect; with --host 0.0.0.0 a password is required. INSERT, CREATE and other writes are refused.
• Speed (10M orders, against a local PostgreSQL 16 with the same rows): a group by region answers in 20 to 80 ms through psql, against PostgreSQL’s 480 to 720 ms; 1M rows reach psql in 1.0 s (PostgreSQL 0.7 s) and a Java program through pgJDBC in 0.9 s (PostgreSQL 0.6 to 1.0 s).

In code: servePg({ tables, root, port: 5433, host, user, password, auth, tls: { certFile, keyFile }, database, logQueries }).

Arrow Flight SQL

Flight SQL answers in Arrow, column by column, so nothing is turned into rows on either side. It is the fastest way to move results into Python or any Arrow tool.

npx vectro serve --root ~/data --flight 31337     # grpc://localhost:31337

import adbc_driver_flightsql.dbapi as flight      # pip install adbc-driver-flightsql
with flight.connect('grpc://localhost:31337') as conn:
    df = conn.cursor().execute('SELECT region, SUM(qty) FROM orders GROUP BY 1').fetch_df()   # pandas

Served: statements and prepared statements, the catalog (catalogs, schemas, tables with their Arrow schemas, table types; adbc_get_objects works), and sign-in with a user name and password (a handshake, then a bearer token). Columns are typed: whole numbers int64, other numbers float64, text utf8, dates timestamp[ms, UTC] (or date32), booleans bool. Checked with the ADBC Flight SQL driver, pandas, Polars, DuckDB and pyarrow.flight. gRPC and protobuf are implemented in Vectro itself, with no dependencies.

On 10M orders a group-by answers in 97 ms, and all 10M rows (9 columns, 790 MB as Arrow) arrive in 2.9 s, against 19.3 s for the same rows through the PostgreSQL protocol with pgJDBC. Not yet: parameters for prepared statements (DoPut) and writes.

In code: serveFlight({ tables, root, port: 31337, host, user, password, tls, database }), which returns { url, port, host, close() }.

One folder, three servers

openTables opens a folder (or your tables) once, so the servers share one engine and one set of tables. This is what vectro serve does.

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

const dir = mkdtempSync(join(tmpdir(), 'vectro-'));
writeFileSync(join(dir, 'daily-sales.csv'), 'day,store,sales\n2024-01-01,north,10\n2024-01-01,south,7\n2024-01-02,north,12\n');

const catalog = await openTables({ root: dir });
const http = await serve({ catalog, port: 0 });
const pg = await servePg({ catalog, port: 0 });
const flight = await serveFlight({ catalog, port: 0 });
console.log(`PostgreSQL on port ${pg.port}, Flight SQL at ${flight.url}`);

console.log((await (await fetch(`${http.url}/tables`)).json()).tables.map((t) => [t.name, t.rows]));

// a new file appears: reload the folder
writeFileSync(join(dir, 'stores.csv'), 'store,city\nnorth,Leeds\nsouth,Bristol\n');
console.log(await (await fetch(`${http.url}/reload`, { method: 'POST' })).json());

// an Arrow answer, read back into a table
const engine = new Engine();
const q = encodeURIComponent('SELECT s.city, SUM(d.sales) AS sales FROM daily_sales d JOIN stores s ON d.store = s.store GROUP BY s.city');
const bytes = new Uint8Array(await (await fetch(`${http.url}/sql?q=${q}&format=arrow`)).arrayBuffer());
console.log(engine.fromArrow(bytes).toRows());

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

The full options are in the serve reference and the CLI reference.