sql()

sql() parses a SELECT statement, resolves its table names against the object you pass, and compiles it to the same lazy Query the JavaScript API builds, so SQL gets every optimization of the planner, on tables in memory and on datasets bigger than memory. Subqueries become joins, so a whole statement is one plan. The grammar and functions are listed in the SQL guide.

import { sql } from 'vectrojs';             // Node
import { sql } from 'vectrojs/browser';     // browsers
import { sql } from 'vectrojs/portable';    // edge runtimes

sql(text, tables?, options?)

Returns a Query; run it with toRows(), collect(), agg(), count() or their …Async twins, or inspect it with explain(). Parsing and compiling happen in this call, so a syntax error, an unknown table or column, or something not supported throws here, as a SyntaxError whose message starts with vectro SQL:.

tables maps names to Tables or Datasets: { orders, customers }. Table names are found ignoring case; a name may also be schema-qualified ("pg_catalog.pg_class"), and an unqualified name also finds a pg_catalog. one. Names in the SQL that aren’t plain identifiers go in double quotes.

Name	Type	Default	Meaning
types	boolean	false	Gives the query a columns property: [{ name, type }], type one of 'int', 'float', 'bool', 'text', 'date', 'day' (dates without a time). Worked out from the tables’ columns before anything runs.
foldCase	boolean	false	Unquoted names in lower case, as PostgreSQL reads them.
functions	object	none	More scalar functions: { name: { out, f } }, out 'text' or 'number', f(...values) a JavaScript function. It runs once per distinct combination of argument values, not per row; missing text arrives as null.
session	object	none	Values for session functions such as version() and current_user.
database	string	none	A schema name that refers to the tables themselves, as public does.

The servers in vectrojs/serve use types, foldCase, session and database to answer as PostgreSQL would; most programs need none of them.

SQL here only reads: there are no INSERT, UPDATE or DDL statements, and no ? or $1 parameters (the PostgreSQL server binds those for its clients). To use a value from your program, put it in the text with care or filter with the JavaScript API, whose params are bound safely.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  cust: [1, 2, 1, 3, 2, 1],
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price: [120, 80, 45, 300, 99, 15],
  ts: ['2024-01-05', '2024-01-20', '2024-02-02', '2024-02-10', '2024-03-01', '2024-03-15'],
}, { dates: ['ts'] });
const customers = engine.table({ cust: [1, 2, 3], tier: ['gold', 'basic', 'gold'] });

const q = sql(`
  SELECT c.tier, DATE_TRUNC('month', o.ts) AS month, COUNT(*) AS n, SUM(o.price) AS revenue
  FROM orders o JOIN customers c ON o.cust = c.cust
  WHERE o.ts >= DATE '2024-01-01'
  GROUP BY 1, 2 ORDER BY month, revenue DESC`, { orders, customers });

console.log(q.toRows());
console.log(await sql('SELECT region FROM orders WHERE price > 100', { orders }).countAsync());
await engine.close();

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const sales = engine.table({ region: ['EU', 'US', 'EU', 'US', 'EU'], amount: [10, 20, 30, 40, 50] });

const ranked = sql(`
  SELECT region, amount,
         RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS r,
         SUM(amount) OVER (PARTITION BY region) AS region_total
  FROM sales ORDER BY region, r`, { sales }, { types: true });
console.log(ranked.columns);
console.log(ranked.toRows());

const shout = { out: 'text', f: (s) => (s === null ? null : `${s}!`) };
console.log(sql('SELECT shout(region) AS loud, MEDIAN(amount) AS mid FROM sales GROUP BY 1', { sales }, { functions: { shout } }).toRows());
await engine.close();