SQL

sql(text, tables) runs one SELECT statement over your tables and returns a lazy query. The statement is compiled to the same plan the table methods build, so SQL gets every optimization: fused filters, parallel group-bys, joins, top-k, and chunk skipping on datasets bigger than memory.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  cust:   [10, 20, 10, 30, 40, 20, 10, 30],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU', 'US', 'APAC'],
  price:  [120, 80, 45, 300, 99, 15, 60, 210],
  qty:    [2, 1, 4, 1, 3, 10, 2, 1],
  ts: ['2024-01-03', '2024-01-15', '2024-02-01', '2024-02-10',
       '2024-02-20', '2024-03-05', '2024-03-06', '2024-03-30'].map((d) => new Date(d)),
});
const customers = engine.table({ cust: [10, 20, 30, 40], tier: [1, 2, 1, 2] });

console.log(sql(`
  SELECT c.tier, COUNT(*) AS n, SUM(o.price * o.qty) AS revenue
  FROM orders o JOIN customers c ON o.cust = c.cust
  WHERE o.region <> 'US' AND o.ts >= DATE '2024-01-01'
  GROUP BY c.tier HAVING COUNT(*) > 1
  ORDER BY revenue DESC LIMIT 5`, { orders, customers }).toRows());
await engine.close();

The sql() function

sql(text, tables, options) // -> Query

tables is an object of names to tables or datasets: { orders, customers }, or { o: ordersTable } to call it o. Names are matched without regard to case. A name may also contain a schema ('sales.orders'); public.orders finds orders.

The result is a lazy query, so nothing runs until you ask:

Method	Returns
toRows(limit)	plain objects; dates as Date objects
collect()	a table
count()	the number of rows
agg(spec)	totals over the rows, as on any lazy query
explain()	the plan

Each has an ...Async version that runs on the worker threads. Errors in the SQL throw a SyntaxError whose message starts with vectro SQL: and says what to change.

Options:

Option	Does
types: true	adds .columns to the query: [{ name, type }] with type 'int', 'float', 'bool', 'text', 'date' or 'day' (dates without a time), worked out from the tables’ columns
foldCase: true	reads unquoted names in lower case, as PostgreSQL does, and names unnamed columns as PostgreSQL would (count, sum)
functions	more functions: { name: { out: 'text' or 'number', f(...values) } }, run once per distinct set of values
session	values for functions such as version() and names such as current_user
database	a schema name that means the tables themselves, as public does

The servers use types, foldCase and session to speak to BI tools; functions is the one you’ll most often want yourself.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const users = engine.table({ email: ['[email protected]', '[email protected]', '[email protected]'], visits: [3, 5, 2] });

const q = sql(`
  SELECT domain(email) AS domain, SUM(visits) AS visits
  FROM users GROUP BY 1 ORDER BY visits DESC`,
  { users },
  { types: true, functions: { domain: { out: 'text', f: (e) => e.split('@')[1] } } });

console.log(q.columns);
console.log(q.toRows());
await engine.close();

Statement shape

[WITH name [(columns)] AS (query), ...]
SELECT [DISTINCT] items
  [FROM table [AS] alias | (query) [AS] alias
    [[INNER | LEFT | RIGHT | FULL [OUTER] | CROSS] JOIN table | (query) alias
       [ON a.x = b.y [AND ...] | USING (x, ...)]]...]
  [WHERE condition]
  [GROUP BY expressions]
  [HAVING condition]
[UNION [ALL] | INTERSECT | EXCEPT  SELECT ...]...
[ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[LIMIT n | FETCH FIRST n ROWS ONLY] [OFFSET m]

• GROUP BY takes columns, expressions, SELECT aliases or positions (GROUP BY 1).
• ORDER BY takes the same, and aggregates (ORDER BY SUM(qty) DESC).
• SELECT without FROM works: SELECT 1 + 2 AS x.
• SELECT * and SELECT o.* expand to the columns; a USING key appears once.
• An unnamed result column is named after what it computes: sum_price, count, avg_qty, or col3 for other expressions.
• Names in double quotes ("Order Date") or backticks may hold spaces and other characters; -- and /* */ comments are skipped.

Expressions

Kind	Supported
Literals	numbers, 'text' ('' for a quote), E'...' with backslash escapes, NULL, TRUE, FALSE, DATE '2024-01-01', TIMESTAMP '2024-01-01 12:00:00', INTERVAL '7 days'
Arithmetic	+ - * /, % (sign of the dividend, as in SQL)
Comparison	= <> != < <= > >=, also on text and on dates written as text (ts >= '2024-03-01')
Logic	AND OR NOT
Tests	BETWEEN, IN (...), LIKE, ILIKE, IS [NOT] NULL, IS [NOT] TRUE / FALSE, IS [NOT] DISTINCT FROM, regular expressions ~ ~* !~ !~*
Conditionals	CASE WHEN ... THEN ... ELSE ... END, CASE x WHEN ..., COALESCE, IFNULL, NVL, NULLIF
Math	ABS, SQRT, FLOOR, CEIL / CEILING, ROUND(x [, digits]), LEAST, GREATEST
Casts	CAST(x AS type) and x::type (see CAST)

INTERVAL takes milliseconds, seconds, minutes, hours, days or weeks. Months and years vary in length, so they are refused; use DATE_TRUNC instead.

Aggregates

Function	Notes
COUNT(*), COUNT(x)	rows; rows where x is present
COUNT(DISTINCT x)	exact, see distinct counts
APPROX_COUNT_DISTINCT(x)	HyperLogLog on split tables and streams, exact on tables
SUM, AVG (or MEAN), MIN, MAX	skip missing values; numbers only
MEDIAN(x)
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY x)	interpolated
PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY x)	an actual value
QUANTILE_CONT(x, p), QUANTILE_DISC(x, p), QUANTILE(x, p)	DuckDB’s spellings

Aggregates can be used anywhere in SELECT, HAVING and ORDER BY, inside expressions (SUM(price * qty) / COUNT(*), SUM(CASE WHEN ... END)), and with or without GROUP BY.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU', 'US', 'APAC'],
  price:  [120, 80, 45, 300, 99, 15, 60, NaN],
});

console.log(sql(`
  SELECT region,
         COUNT(*) AS n, COUNT(price) AS priced,
         MEDIAN(price) AS median,
         PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY price) AS p90,
         SUM(CASE WHEN price >= 100 THEN 1 ELSE 0 END) AS big
  FROM orders GROUP BY region ORDER BY region`, { orders }).toRows());
await engine.close();

Percentiles of a few partitions count values in buckets on every thread and select only within the buckets they need. MEDIAN per region over 10M rows takes 33.6 ms (DuckDB 71.7).

Joins

[INNER] JOIN, LEFT, RIGHT and FULL [OUTER] JOIN with ON (equalities joined by AND) or USING, and CROSS JOIN. Either side may be a subquery with an alias. Commas between tables are refused. Joins explains how they run, including how RIGHT and FULL joins find unmatched rows in the same pass.

Subqueries

• In FROM and JOIN: FROM (SELECT ...) t (the alias is required).
• x IN (SELECT ...) and x NOT IN (SELECT ...).
• EXISTS (SELECT ...) and NOT EXISTS (SELECT ...).
• (SELECT one value) anywhere a value goes.
• Correlated subqueries, which refer to the outer query, when the link is an equality: WHERE o.cust = c.cust.

Every subquery becomes a join: IN and EXISTS join the subquery’s distinct keys, and a single-value subquery joins its one row, or its rows grouped by the correlation keys. The whole statement stays one plan with no row-by-row loops.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  cust:   [10, 20, 10, 30, 40, 20, 10],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU', 'US'],
  price:  [120, 80, 45, 300, 99, 15, 60],
});
const customers = engine.table({ cust: [10, 20, 30, 50], tier: [1, 2, 1, 3] });
const t = { orders, customers };

// orders from tier-1 customers
console.log(sql('SELECT COUNT(*) AS n FROM orders WHERE cust IN (SELECT cust FROM customers WHERE tier = 1)', t).toRows());

// customers with at least one order over 100
console.log(sql(`SELECT cust FROM customers c
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust = c.cust AND o.price > 100)`, t).toRows());

// orders above their region's average (correlated)
console.log(sql(`SELECT cust, region, price FROM orders o
  WHERE price > (SELECT AVG(p.price) FROM orders p WHERE p.region = o.region)
  ORDER BY price DESC`, t).toRows());

// a value per customer (correlated, in SELECT)
console.log(sql(`SELECT c.cust, (SELECT COUNT(*) FROM orders o WHERE o.cust = c.cust) AS orders
  FROM customers c ORDER BY c.cust`, t).toRows());
await engine.close();

CTEs and set operations

WITH name AS (...) names a query for the rest of the statement; WITH name (a, b) AS (...) also names its columns. Later CTEs can use earlier ones.

UNION (distinct rows), UNION ALL, INTERSECT and EXCEPT combine queries with the same number of columns, matched by position. A final ORDER BY and LIMIT apply to the combined result.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ cust: [10, 20, 10, 30, 40, 20], qty: [2, 1, 4, 1, 3, 10] });
const customers = engine.table({ cust: [10, 20, 30, 50] });
const t = { orders, customers };

console.log(sql(`
  WITH spend (cust, units) AS (SELECT cust, SUM(qty) FROM orders GROUP BY cust),
       big AS (SELECT * FROM spend WHERE units >= 5)
  SELECT * FROM big ORDER BY units DESC`, t).toRows());

console.log(sql('SELECT cust FROM orders UNION SELECT cust FROM customers ORDER BY cust', t).toRows());
console.log(sql('SELECT cust FROM customers EXCEPT SELECT cust FROM orders', t).toRows());
console.log(sql('SELECT cust FROM customers INTERSECT SELECT cust FROM orders ORDER BY 1', t).toRows());
await engine.close();

Window functions

f(...) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS | RANGE BETWEEN start AND end])

Kind	Functions
Ranking	ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE(n)
Offsets	LAG(x [, n [, default]]), LEAD(x [, n [, default]]), FIRST_VALUE, LAST_VALUE, NTH_VALUE(x, n)
Aggregates	SUM, AVG, MIN, MAX, COUNT

Frame bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING. ROWS counts rows; RANGE measures by the order value, so RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW is a 7-day window on a date column. Without a frame, a window with ORDER BY runs from the partition’s start to the current row and its ties; without ORDER BY, it covers the whole partition, as in standard SQL.

Windows work over grouped results too: RANK() OVER (PARTITION BY region ORDER BY SUM(qty) DESC). Filter on a window’s result in an outer query, since WHERE runs before windows.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const sales = engine.table({
  day:    ['2024-03-01', '2024-03-02', '2024-03-03', '2024-03-04', '2024-03-05', '2024-03-08']
            .flatMap((d) => [new Date(d), new Date(d)]),
  region: Array.from({ length: 12 }, (_, i) => (i % 2 ? 'US' : 'EU')),
  units:  [5, 3, 7, 4, 2, 6, 8, 1, 4, 9, 3, 5],
});

// running total, change from the day before, and a 3-day moving average, per region
console.log(sql(`
  SELECT region, day, units,
         SUM(units) OVER (PARTITION BY region ORDER BY day) AS running,
         units - LAG(units) OVER (PARTITION BY region ORDER BY day) AS change,
         AVG(units) OVER (PARTITION BY region ORDER BY day
                          RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS avg3
  FROM sales WHERE region = 'EU' ORDER BY day`, { sales }).toRows());

// the top 2 days per region
console.log(sql(`
  SELECT region, day, units FROM (
    SELECT region, day, units,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY units DESC) AS rn
    FROM sales) t
  WHERE rn <= 2 ORDER BY region, units DESC`, { sales }).toRows());
await engine.close();

Each window costs one pass after its sort: running and moving sums use prefix sums, and moving MIN / MAX use a monotonic queue. A filter such as rn <= 3 on ROW_NUMBER or RANK keeps only the rows that can rank that high before sorting, picked on every thread. On a dataset, window functions and percentiles gather all their rows, so the columns they use must fit in memory.

Text functions

Function	Does
UPPER / UCASE, LOWER / LCASE	change case
TRIM, LTRIM, RTRIM (optional set of characters), TRIM(LEADING / TRAILING / BOTH 'x' FROM s)	strip characters
SUBSTRING / SUBSTR(s, start [, length]), SUBSTRING(s FROM a FOR b)	part of a string; positions from 1
LEFT(s, n), RIGHT(s, n)	the first or last n characters
LENGTH / LEN / CHAR_LENGTH, OCTET_LENGTH / STRLEN	characters; UTF-8 bytes
REPLACE, REVERSE, REPEAT, LPAD, RPAD
SPLIT_PART(s, delimiter, n)	the nth piece (negative counts from the end)
STRPOS / INSTR(s, sub), POSITION(sub IN s)	position of sub, 0 if absent
STARTS_WITH / PREFIX, ENDS_WITH / SUFFIX, CONTAINS	tests
CONCAT, CONCAT_WS(sep, ...)	join strings, skipping missing values
ASCII, CHR	code point and back
REGEXP_MATCHES / REGEXP_LIKE, REGEXP_REPLACE(s, pattern, replacement [, flags]), REGEXP_EXTRACT(s, pattern [, group])	regular expressions (JavaScript syntax)
STRFTIME(ts, format)	a date as text: %Y %m %d %H %M %S %j %a %A %b %B %F %T and more
FORMAT(fmt, ...)	PostgreSQL’s format with %s, %I, %L

The || operator joins strings too, numbers included: city || ' #' || id. CASE, COALESCE and NULLIF work on text, and text compares with < and >. Positions count characters (not bytes) from 1, as in DuckDB.

Each text function runs once per distinct value, not once per row: a category column has few labels, and a million rows of region are a handful of calls. The answer is a category column again, so it groups, sorts and joins like any other. On 10M rows: GROUP BY UPPER(region) 30 ms (DuckDB 42), region || '-' || qty as a key 85 ms (92), STRFTIME(ts, '%Y-%m') 48 ms (61), ILIKE on a column 29 ms (46). A CASE whose results are all quoted labels is worked out as label codes inside the fused pass (CASE text + group: 5.7 ms vs 21).

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const people = engine.table({
  id:   [1, 2, 3, 4],
  name: ['  Ada Lovelace', 'bo chen', 'Cy Young ', 'Di'],
  city: ['Seattle', 'Austin', 'San José', 'Paris'],
});

console.log(sql(`
  SELECT id,
         TRIM(name) AS name,
         UPPER(SPLIT_PART(TRIM(name), ' ', 1)) AS first,
         LENGTH(city) AS len,
         LPAD(CAST(id AS VARCHAR), 3, '0') AS code,
         city || ' #' || id AS label,
         REGEXP_REPLACE(city, '[aeiou]', '*', 'g') AS masked
  FROM people
  WHERE name ILIKE '%o%' OR city ~ '^P'
  ORDER BY id`, { people }).toRows());
await engine.close();

Date functions

Date columns hold milliseconds since 1970, in UTC.

Function	Gives
YEAR(x), MONTH(x), DAY(x) / DAYOFMONTH, HOUR(x), MINUTE(x), SECOND(x)	parts as numbers
DOW(x) / DAYOFWEEK(x)	day of the week, 0 = Sunday
ISODOW(x) / WEEKDAY(x)	day of the week, 1 = Monday
EXTRACT(part FROM x)	any part above, or EPOCH (seconds since 1970)
DATE_PART('part', x)	the same, part as text
DATE_TRUNC('year' / 'month' / 'week' / 'day' / 'hour', x)	the start of that period, as a date
STRFTIME(x, format)	text, see above
NOW(), CURRENT_TIMESTAMP	the time the query was compiled

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const events = engine.table({
  ts: ['2024-01-03T09:30:00Z', '2024-01-15T18:05:00Z', '2024-02-01T07:00:00Z',
       '2024-02-10T12:45:00Z', '2024-03-05T23:10:00Z'].map((s) => new Date(s)),
  ms: [120, 340, 95, 210, 180],
});

console.log(sql(`
  SELECT DATE_TRUNC('month', ts) AS month, STRFTIME(ts, '%b %Y') AS label,
         COUNT(*) AS n, AVG(ms) AS avg_ms
  FROM events
  WHERE ts >= DATE '2024-01-01' AND ts < TIMESTAMP '2024-03-01 00:00:00'
  GROUP BY 1, 2 ORDER BY 1`, { events }).toRows());

console.log(sql(`
  SELECT EXTRACT(HOUR FROM ts) AS hour, ISODOW(ts) AS weekday, YEAR(ts) AS year
  FROM events WHERE ts BETWEEN '2024-01-10' AND '2024-02-05' ORDER BY ts`, { events }).toRows());
await engine.close();

CAST

CAST(x AS type) or x::type, with these type names:

To	Type names
text	VARCHAR, TEXT, STRING, CHAR, CHARACTER VARYING, …
whole numbers (truncated toward 0)	INT, INTEGER, BIGINT, SMALLINT, TINYINT, HUGEINT, INT2, INT4, INT8
decimals	DOUBLE, DOUBLE PRECISION, FLOAT, REAL, DECIMAL, NUMERIC
booleans (1 / 0)	BOOL, BOOLEAN; text 'true', 'yes', 'on', '1' and their opposites
dates	DATE (midnight), TIMESTAMP, TIMESTAMPTZ, DATETIME

Casting text to a number or a date gives a missing value where the text isn’t one. Numbers cast to text are written shortest (whole numbers without .0), dates as YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const raw = engine.table({ code: ['42', '7', 'n/a'], day: ['2024-05-01', '2024-05-02', 'soon'], price: [9.99, 12.5, -3.7] });

console.log(sql(`
  SELECT CAST(code AS INTEGER) AS code, day::DATE AS day, CAST(price AS INT) AS whole,
         CAST(price AS VARCHAR) AS text
  FROM raw`, { raw }).toRows());
await engine.close();

Missing values

NULL is Vectro’s missing value: NaN for numbers and dates, '' for text. Aggregates skip it, IS NULL finds it, and ORDER BY puts it last unless you say NULLS FIRST. Comparisons with it are unknown, as in standard SQL, so NOT, <>, NOT IN, NOT BETWEEN and NOT LIKE leave those rows out. Missing values has the details.

Speed

The same SQL text against DuckDB on 10M orders and 100K customers (compare/bench-sql.mjs, answers checked): Vectro is faster on all 20 queries. A few of them:

Query	Vectro	DuckDB
filtered totals	3.4 ms	10.1 ms
group by region	7.8 ms	30.7 ms
join + filter + group	17.3 ms	29.0 ms
top 10 customers	12.9 ms	42.5 ms
CASE inside SUM	7.6 ms	35.2 ms
correlated subquery	16.0 ms	50.6 ms
top 3 per region with RANK	36 ms	119 ms
running total by day	8.6 ms	14.6 ms

compare/check-sql.mjs runs 40 queries (CTEs, set operations, correlated subqueries, windows, percentiles, text functions on awkward strings, outer joins, missing values) in both Vectro and DuckDB on the same 200K rows, and every answer is the same.

Not supported yet

Anything outside the list above gives an error that says so. In particular:

• INTERSECT ALL and EXCEPT ALL (INTERSECT and EXCEPT work).
• Correlated subqueries linked by anything other than equalities.
• MIN, MAX, SUM and AVG of text (COUNT and COUNT(DISTINCT) of text work).
• STRING_AGG and other aggregates that build lists; arrays.
• Commas between tables in FROM; use JOIN ... ON or CROSS JOIN.
• INTERVAL in months or years.
• Statements other than SELECT (INSERT, UPDATE, CREATE…), and more than one statement at a time.
• Time zones other than UTC, and 64-bit integers beyond 2^53.