Skip to content
Vectro
GitHub ↗

Docs · Getting started

Quick start (Node)

Make a table, filter it, add up columns, group it, query it with SQL and read a CSV file, in Node.

This page goes through the main calls once, in Node. Every example runs as it is: copy it into a .mjs file and run it with node.

Make an engine and a table

An Engine owns the memory and the worker threads. Tables are made from it, from plain arrays, typed arrays, CSV, Arrow or JSON lines.

import { Engine } from 'vectrojs';

const engine = new Engine();                 // one thread per core
const orders = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU', 'APAC', 'US'],
  price:  [120, 80, 45, 300, 99, 15, 60, 210],
  qty:    [2, 1, 4, 1, 3, 10, 2, 1],
});

console.log(orders.length, orders.columnNames);   // 8 [ 'region', 'price', 'qty' ]
console.log(orders.toRows(2));

await engine.close();

Text columns such as region are stored as categories: each distinct label gets a number, so region == 'EU' is compared as a number inside the query. See Tables for typed arrays, dates and memory.

Filter and aggregate

filter takes an expression and returns a view of the same columns with the condition attached; nothing is copied. agg computes several results in one pass over the rows.

import { Engine } from 'vectrojs';

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

const eu = orders.filter("region == 'EU' && price > 20");
console.log(eu.agg({
  orders: 'count()',
  revenue: 'sum(price * qty)',
  avgPrice: 'mean(price)',
}));
// { orders: 2, revenue: 420, avgPrice: 82.5 }

console.log(orders.sum('price * qty'), orders.max('price'));

await engine.close();

The filter and the three aggregations compile into one WebAssembly loop, run on every core. On 10M rows this kind of query takes about 3 ms. The expression language is on its own page.

Group by

import { Engine } from 'vectrojs';

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

const byRegion = orders.groupBy('region').agg({
  n: 'count()',
  revenue: 'sum(price * qty)',
});
console.log(byRegion.toRows());

// the result is a table: sort it, filter it, group it again
console.log(byRegion.top(1, '-revenue').toRows());

await engine.close();

groupBy takes one key or a list (groupBy(['region', 'day'])). See Group by.

New columns

derive(name, expr) returns a new table with one more column, computed for every row in one pass. The other columns are shared with the original, not copied.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ price: [120, 80, 45], qty: [2, 1, 4] });

const withTotal = orders.derive('total', 'price * qty');
console.log(withTotal.toRows());

await engine.close();

SQL

sql(text, tables) runs a SELECT over the tables you name in the second argument. It returns a lazy query; toRows() runs it.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  cust:  [1, 2, 1, 3, 2, 1],
  price: [120, 80, 45, 300, 99, 15],
});
const customers = engine.table({ cust: [1, 2, 3], tier: ['gold', 'silver', 'gold'] });

console.log(sql(`
  SELECT c.tier, COUNT(*) AS n, SUM(o.price) AS spend
  FROM orders o JOIN customers c ON o.cust = c.cust
  GROUP BY c.tier ORDER BY spend DESC`, { orders, customers }).toRows());

await engine.close();

SQL compiles to the same optimized plans as lazy queries. What it supports is listed on the SQL page.

Read a CSV file

engine.readCSV(path) reads the file straight into engine memory and parses it on every thread. Column types are guessed from the first 1,000 rows: numbers, categories, ISO dates.

import { Engine, sql } from 'vectrojs';
import { writeFileSync, mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const dir = mkdtempSync(join(tmpdir(), 'vectro-'));
const path = join(dir, 'orders.csv');
writeFileSync(path, [
  'ts,region,price,qty',
  '2024-03-01,EU,120,2',
  '2024-03-01,US,80,1',
  '2024-03-02,EU,45,4',
  '2024-03-03,APAC,300,1',
].join('\n'));

const engine = new Engine();
const orders = engine.readCSV(path);
console.log(orders.filter("ts >= '2024-03-02'").toRows());
console.log(sql('SELECT region, SUM(price * qty) AS revenue FROM orders GROUP BY region', { orders }).toRows());

await engine.close();
rmSync(dir, { recursive: true });

parseCSV(text) does the same for a string or bytes you already have. Options, JSON lines and Arrow are on CSV, JSON lines, Arrow. For files bigger than memory, convert them to a dataset.

Inside a server: the async calls

The calls above block the calling thread until the answer is ready, which is fine in a script. In a server, use the …Async version of each one: the work runs on the worker threads while the event loop keeps serving other requests.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const n = 2_000_000;
const orders = engine.table({
  region: Array.from({ length: n }, (_, i) => ['EU', 'US', 'APAC'][i % 3]),
  price: Float64Array.from({ length: n }, (_, i) => (i * 37) % 500),
});

const [summary, groups, rows] = await Promise.all([
  orders.filter('price > 100').aggAsync({ n: 'count()', revenue: 'sum(price)' }),
  orders.groupBy('region').aggAsync({ avg: 'mean(price)' }),
  sql('SELECT region, MAX(price) AS top FROM orders GROUP BY region', { orders }).toRowsAsync(),
]);
console.log(summary, groups.toRows(), rows);

await engine.close();

Async queries on one engine run one after another, in the order they were called, so it is safe to start several. Calling a blocking method while an async query is running on the same engine throws an error rather than corrupting it.

Next