First queries

A first SQL session — an append table, a versioned table with attribute groups, a read AS OF COMMIT, a streaming query and a transformation.

This walkthrough builds the two tables used throughout the docs: transfers and balances. It assumes a running node; see Install.

Sending a statement

Every statement is sent on its own, in one of two ways:

HTTP POST /sql, with a JSON body whose sql field holds the statement and whose maximum_rows field caps the rows returned. A read answers with a result document of type nyxdb.query-result/2.
A WebSocket session on /client (subprotocol nyxdb.public.v1), which sends the statement in an ExecuteSql frame. Streaming queries need this route.

Both need a session obtained from POST /auth/session. The request and frame formats are on Protocol; the console sends statements for you.

1. An append table

An append table keeps every row. It carries no primary key.

CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;

INSERT INTO transfers (
  chain_id, token, sender, recipient, amount, transferred_at, _index
)
VALUES (
  1, 'USDC', '0x4b1e7c2a', '0x9c2e4f1a', 2500000000, '2026-10-01 12:00:00.000',
  1001
);

Each INSERT carries one row. Bulk loads use the batch routes on Protocol.

2. A versioned table with attribute groups

A versioned table keeps the current row per key and every accepted version. Its attribute groups let several producers write one row.

CREATE TABLE balances (
  chain_id UINT16 NOT NULL,
  address STRING NOT NULL,
  token STRING NOT NULL,
  amount UINT256 NULL,
  updated_at DATETIME64(3, 'UTC') NULL,
  balance_index UINT128 NULL,
  balance_version UINT64 NULL,
  transfer_count UINT64 NULL,
  last_transfer_at DATETIME64(3, 'UTC') NULL,
  activity_index UINT128 NULL,
  PRIMARY KEY (chain_id, address, token),
  ATTRIBUTE GROUP holding (amount, updated_at, balance_index, balance_version)
    VALID BY (balance_index)
    REVISION BY (balance_version),
  ATTRIBUTE GROUP activity (transfer_count, last_transfer_at, activity_index)
    VALID BY (activity_index)
)
MODE VERSIONED
SHARD BY (address) SHARDS AUTO
ORDER BY (chain_id, address, token);

UPSERT INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version
)
VALUES (1, '0x9c2e4f1a', 'USDC', 2500000000, '2026-10-01 12:00:00.000', 1001, 1)
ATTRIBUTE GROUP holding;

SELECT address, token, amount, updated_at
FROM balances
WHERE chain_id = 1 AND address = '0x9c2e4f1a';

The ATTRIBUTE GROUP holding write changes only that group of the row. See INSERT and Attribute groups.

3. A read at an earlier commit

AS OF COMMIT reads a versioned table as it stood at an earlier commit:

SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;

See Versioned tables.

4. A streaming query

Over a WebSocket session, STREAM SELECT returns the current result and then keeps delivering its changes:

STREAM SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;

The stream opens with a snapshot of the totals; each new transfer then arrives as one change to its token's row. The stream runs until the client cancels it. See Streaming queries.

5. A transformation

A transformation keeps a target up to date from a query over its sources. Here balance_activity maintains the activity group of every balance row from the transfers:

CREATE TRANSFORMATION balance_activity INTO balances ATTRIBUTE GROUP activity AS
SELECT
  chain_id,
  recipient AS address,
  token,
  COUNT(*) AS transfer_count,
  MAX(transferred_at) AS last_transfer_at,
  MAX(_index) AS activity_index
FROM transfers
GROUP BY chain_id, recipient, token;

Every transfer committed from then on updates the recipient's row, while the holding group keeps the values its producer writes. See CREATE TRANSFORMATION.

Next steps

Tables for the three modes.
SELECT for the query grammar.
Transformations for derived tables.
Consistency for what a statement guarantees.