First queries

Run these statements from nyxsql or the web console. Every CREATE TABLE names a storage policy because that setting is required by the current engine. The examples use memory_data: data stays memory-resident for serving and is journaled when the server runs with --data-dir.

1. Append history

Append tables retain each accepted row and are the natural source for history, streams, and transforms.

CREATE TABLE trades (
  id UInt64 NOT NULL,
  market String NOT NULL,
  amount UInt64 NOT NULL,
  PRIMARY KEY (id)
) SETTINGS mode = 'append', storage_policy = 'memory_data';

INSERT INTO trades (id, market, amount) VALUES
  (1, 'BTC-USD', 4120),
  (2, 'ETH-USD', 2880);

SELECT id, market, amount
FROM trades
WHERE amount > 1000
ORDER BY id;

Expected rows:

id  market   amount
1   BTC-USD  4120
2   ETH-USD  2880

2. Latest-per-key state

A keyed table replaces the current value for an existing primary key. The engine maintains its key count instead of deriving it from a full history scan.

CREATE TABLE balances (
  address String NOT NULL,
  balance UInt256,
  PRIMARY KEY (address)
) SETTINGS mode = 'keyed', storage_policy = 'memory_data';

INSERT INTO balances (address, balance) VALUES ('alice', 100);
INSERT INTO balances (address, balance) VALUES ('alice', 125);
INSERT INTO balances (address, balance) VALUES ('bob', 50);

SELECT address, balance FROM balances ORDER BY address;
SELECT count() FROM balances;

The first query returns alice = 125 and bob = 50; the count is 2.

3. Typed attributes

Attribute tables store independently versioned attributes and reconstruct the current entity projection. The entity key is explicit.

CREATE TABLE accounts (
  id UInt64,
  ATTRIBUTE (balance UInt64, tier String)
) SETTINGS kind = 'attribute',
           storage_policy = 'memory_data',
           entity = (id);

INSERT INTO accounts (id, balance, tier) VALUES
  (1, 100, 'gold'),
  (2, 200, 'silver');

SELECT id, balance, tier FROM accounts ORDER BY id;

Attribute projection maintenance drains through PSI asynchronously, but a default latest read overlays the exact unreflected suffix. The first read after the acknowledged insert must therefore return both entities immediately; assert that behavior directly. Poll a projection watermark or maintenance metric only when the test is specifically about background convergence.

4. Follow changes live

STREAM SELECT is a statement-prefix form. It emits an initial snapshot and then remains open for committed changes.

STREAM SELECT id, market, amount
FROM trades
WHERE amount > 1000;

Open a second nyxsql pane with Ctrl+N and insert another row:

INSERT INTO trades (id, market, amount)
VALUES (3, 'SOL-USD', 3600);

The stream receives the new matching result. A stream ends when the client cancels, the connection fails, the server shuts down, the source is dropped, or the engine returns a terminal error. Clients must treat termination as final for that subscription and create a new subscription when their retry policy allows; the current release does not promise resumable cross-connection cursors.

5. Maintain a target with a transform

Create a target whose schema includes the projected key, then register the transform:

CREATE TABLE trade_copy (
  id UInt64 NOT NULL,
  market String NOT NULL,
  amount UInt64 NOT NULL,
  PRIMARY KEY (id)
) SETTINGS mode = 'keyed', storage_policy = 'memory_data';

CREATE TRANSFORM copy_trades INTO trade_copy AS
SELECT id, market, amount FROM trades;

SHOW TRANSFORMS;
SELECT id, market, amount FROM trade_copy ORDER BY id;

ALTER TRANSFORM copy_trades PAUSE, ALTER TRANSFORM copy_trades RESUME, and DROP TRANSFORM copy_trades control its lifecycle. Continuous transforms are experimental and currently maintained inside the all-in-one process. Registration, the initial state derivation, and updates drain asynchronously; poll the target for the expected condition with a deadline instead of treating the CREATE TRANSFORM response as a catch-up barrier.

Clean up

DROP TRANSFORM copy_trades;
DROP TABLE trade_copy;
DROP TABLE accounts;
DROP TABLE balances;
DROP TABLE trades;

Next

• Core concepts — understand tables, PSI, streaming, and storage.
• SQL reference — statement syntax and data types.
• Connection behavior — retry, termination, and compatibility rules for application clients.