SQL & Postgres wire protocol (v2, Beta)

Aito v2 speaks a small, PostgreSQL-compatible subset of SQL, two ways:

• Over REST — POST /api/v2/_sql with a SQL statement.
• Over the Postgres wire protocol — point psql, a JDBC/ODBC tool, or psycopg at Aito and query it like any Postgres database.

Both run 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. The point is parity by construction: the same statement is valid Postgres and valid here.

The SQL subset

SELECT  <select_list>
FROM    <table>
[[INNER] JOIN <table> ON <base>.<fk> = <joined>.<pk>]
[WHERE  <condition>]
[GROUP BY <column>]
[ORDER BY <column> [ASC|DESC]]
[LIMIT  <int>]
[OFFSET <int>]

• select list — *, columns, or an aggregate: count(*), sum(col), avg(col), min(col), max(col). Columns may be qualified (orders.id). Any item may be renamed with AS alias.
• where — =, <>/!=, <, <=, >, >=, IS NULL, IS NOT NULL, IN (…), NOT IN (…), LIKE, combined with AND / OR / NOT and parens.
• GROUP BY — a single column: SELECT color, count(*), avg(price) FROM products GROUP BY color. count(*) is the per-group row count; sum/avg aggregate per group; WHERE filters before grouping; ORDER BY <agg alias> and LIMIT rank/paginate the groups.
• literals — integers, decimals, single-quoted strings ('' escapes a quote), TRUE/FALSE, NULL. Keywords are case-insensitive.

LIKE supports a trailing-% prefix ('ap%') or an exact string only — Aito has no substring/suffix operator, so '%ap' / '%ap%' / _ are rejected.

JOIN along a link

A SQL JOIN maps onto an Aito link — a foreign-key column addressed by a dotted path (product.name). An INNER JOIN along an existing link is therefore not a row-combining cartesian product but a thin skin over that link-path projection, with no content copied (it's one of the four spellings of the same join):

SELECT orders.id, products.name, orders.qty
FROM orders JOIN products ON orders.product = products.id
WHERE products.category = 'dairy' ORDER BY orders.id

The base-table side of the ON names the link column (orders.product); references to the joined table (products.name) follow that link. WHERE, ORDER BY, and aggregates may reference joined columns. It runs the identical query as the JSON form {"from":"orders","select":["id","product.name","qty"], "where":{"product.category":"dairy"},"orderBy":"id"}. Only an INNER JOIN whose ON is a declared link is supported — outer/cross/multi-table joins are rejected.

Not supported yet (rejected with a clear error, never silently ignored): multiple ORDER BY keys, outer/cross/multi-table JOIN (and joins whose ON is not a declared link), multi-column GROUP BY, per-group min/max, HAVING, UNION, DISTINCT, BETWEEN, substring/suffix LIKE and ILIKE, subqueries, and any write/DDL (INSERT/UPDATE/DELETE/CREATE). = NULL / <> NULL are rejected with a hint to use IS NULL / IS NOT NULL (matching Postgres's three-valued logic).

Inference & learned ranking are JSON-only (by design)

SQL ORDER BY is a plain column sort and SELECT projects columns/aggregates — by design. Aito's inference features have no standard SQL syntax and are not part of the SQL surface: learned-ranking blends (e.g. rank by similarity × contextual probability, orderBy: {"$multiply": [{"$similarity": …}, {"$p": {"$context": …}}]}), predictive / contextual $p columns, and the predict / recommend / relate query types. Reach for the JSON _query / _search API for these — that is where Aito's ML ranking lives; SQL is the relational surface over the same engine.

REST endpoint

POST /api/v2/_sql. The request body is the raw SQL statement (text/plain, or application/json with the statement as the body). The response is the standard v2 result JSON — identical to the equivalent _query response.

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"

Postgres wire protocol (pgwire)

Existing Postgres clients connect and run the SQL subset with no code change.

Enabling it

The listener is off by default (it's a query-executing port). Enable it with environment variables on the Aito instance:

PGWIRE_ENABLED=true      # turn the listener on
PGWIRE_PORT=5432         # default 5432

It binds to the same address as the HTTP server.

Connecting

The password is your Aito API key; the user is ignored; the database selects the environment (postgres / aito / empty → the master env, otherwise the env of that name).

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

# JDBC
jdbc:postgresql://$AITO_HOST:5432/aito     # user=aito, password=<API key>

# psycopg
psycopg.connect("host=$AITO_HOST port=5432 dbname=aito user=aito password=$AITO_API_KEY")

Both the simple and extended query protocols work, so plain statements and parameterized prepared statements (? / $1) run.

Authentication & TLS

• SCRAM-SHA-256 — the API key is proven by challenge-response and never sent in the clear. (When API-key auth is disabled on the instance, the connection is open — same posture as the REST API.)

• TLS — off by default; configure a keystore to enable it, then clients can connect with sslmode=require:

  PGWIRE_TLS_ENABLED=true
  PGWIRE_TLS_KEYSTORE=/path/server.jks
  PGWIRE_TLS_KEYSTORE_PASSWORD=…
  

  TLS is optional (like stock Postgres): with a keystore configured the server offers SSL but still accepts plaintext.

Schema browsing & relationships

A minimal synthesized pg_catalog answers the introspection queries tools run, so schema browsing works: listing tables and their columns/types (getTables/getColumns), and — usefully — links between collections surface as SQL foreign keys (getImportedKeys/getExportedKeys), with a link target reported as the referenced table's primary key. So tools like DBeaver or Metabase can show how your collections relate.

Limitations

• JOINs follow existing links only: an INNER JOIN along a declared link works (it projects through the link — see above), so a tool that sees the foreign key can run the join. Arbitrary joins on non-link columns, and outer/cross/multi-table joins, are still rejected.
• Read-only — SELECT only; no writes/DDL over SQL (use the data and schema APIs).
• Metadata depth — no composite/unique/check constraints, sequences, indexes, or functions; unrecognised catalog queries return empty rather than erroring, so deep tool features may show nothing.
• Wire protocol — no SCRAM channel binding (-PLUS) or client-certificate auth; TLS is optional, not enforced; no COPY or cursors.

Related

• Query Reference — the JSON query language _sql lowers to.
• Relationships, Links & Joins — how a SQL JOIN maps to an Aito link.
• Environments & Auth — API keys and environments.