SQL & Postgres — Quickstart

⚠️ Early access. The SQL surface and the PostgreSQL wire protocol are new and deliberately minimal — a small SELECT subset, not a full Postgres. Great for evaluation and connecting existing tools; not yet production-hardened.

Aito v2 speaks a small, PostgreSQL-compatible subset of SQL. It runs the same engine as the JSON _query API — a statement is parsed and lowered to the equivalent v2 query, so a SELECT returns the same rows as its JSON equivalent. Parity by construction: the same statement is valid Postgres and valid here.

▶️ Try it now — no setup: the interactive SQL console runs read-only SQL against a live demo dataset right in your browser.

Two ways to query

Over REST — POST /api/v2/_sql with the SQL statement as the body:

curl -s https://$AITO_INSTANCE/api/v2/_sql \
  -H "x-api-key: $AITO_API_KEY" \
  -H "Content-Type: text/plain" \
  --data "SELECT id, price FROM products WHERE price > 10 ORDER BY price DESC LIMIT 5"

The response is the standard v2 result JSON — identical to the equivalent _query response, except that columns SQL names itself (an aggregate, or an ML table function) come back under their SQL names; see result column names. This endpoint is read-only.

Over the Postgres wire protocol — point psql, a JDBC/ODBC tool, psycopg, or a Postgres connector (ClickHouse, DuckDB, postgres_fdw) at Aito and query it like any Postgres database. The password is your Aito API key; the user is ignored; the database selects the environment:

psql "host=$AITO_HOST port=5432 dbname=aito user=aito password=$AITO_API_KEY"

The listener is on by default, bound to the server's own address (localhost unless configured otherwise) — see the Guide. Unlike the read-only REST endpoint, the wire protocol also accepts writes (INSERT, CREATE TABLE, COPY, …) — but only for a session authenticated with a read-write API key; a read-only key may query and nothing else.

First queries

Plain relational SQL — projection, filter, sort, group, aggregate:

SELECT name, category, price FROM products WHERE price > 2 ORDER BY price DESC LIMIT 10;

SELECT category, count(*) AS n, avg(price) AS avg_price
FROM products GROUP BY category ORDER BY n DESC;

Free-text filtering uses Postgres's full-text operator @@, which lowers to the engine's tokenising match (the smart counterpart to LIKE):

SELECT name, price FROM products WHERE name @@ 'milk';

…and Aito's inference, right in SQL — predict(col) is the argmax, predictions(col) the ranked distribution (e.g. route an invoice to a GL code):

SELECT ProductName, predict(GLCode) FROM invoices WHERE ProductName = 'Cloud Services';
SELECT predictions(GLCode)          FROM invoices WHERE ProductName = 'Cloud Services';

Two result shapes. predict(col)/predictions(col) annotate each returned row (a per-row projection). Aito's other ML verbs — recommend(...) and relate(...) — are set-returning table functions that produce a ranked relation in FROM; see the Guide and Reference.

Next

  • SQL Guide — the full surface: the SELECT subset, joins along a link, prediction, writes, auth/TLS, and the Postgres connectors (ClickHouse / DuckDB / postgres_fdw).
  • SQL Reference — the operator & function table, SQLSTATE error codes, and current limitations.
  • SQL console — run read-only SQL against the live demo.
  • Query Reference — the JSON query language _sql lowers to (and where recommend / search / learned ranking live).