SQL reference

This page lists the SQL that kinedb runs today. Every example in a plain sql block ran on the current build, in the order of this page, in one database. So a later example can use a table that an earlier example created. A -- expect: comment shows a part of the output.

The examples use the embedded mode of the binary, which starts no server:

kinedb --data-dir ./data -e "SHOW TABLES"

Some statements need a running server (kinedb --connect host:port, POST /sql, or a client library). The text before such an example says so. The embedded mode refuses them with requires a running server.

Data types

• INT: a 64-bit signed integer.
• FLOAT: a 64-bit floating-point number (IEEE 754).
• BOOL: true or false.
• TEXT: a UTF-8 string.
• BLOB: any bytes.
• TIMESTAMP: microseconds since the Unix epoch.
• VECTOR: a list of numbers.

-- expect: OK
CREATE TABLE demo (
  id INT,
  amount FLOAT,
  is_active BOOL,
  name TEXT,
  data BLOB,
  ts TIMESTAMP,
  embedding VECTOR,
  PRIMARY KEY (id)
);

Tables

A table has typed columns and a primary key.

-- expect: OK
CREATE TABLE accounts (
  account_id INT,
  owner TEXT,
  balance FLOAT,
  created_at TIMESTAMP,
  PRIMARY KEY (account_id)
);

List the tables, and show the columns of one table:

-- expect: 2 rows
SHOW TABLES;
-- expect: account_id
DESCRIBE accounts;
-- expect: account_id
SHOW COLUMNS FROM accounts;

Drop a table and its data:

CREATE TABLE temp_table (id INT, PRIMARY KEY (id));
DROP TABLE temp_table;

INSERT

Insert one row, or several rows in one statement:

INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (1, 'Alice', 1000.0, 1000000);
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES
  (2, 'Bob', 2000.0, 2000000),
  (3, 'Charlie', 3000.0, 3000000),
  (4, 'Diana', 4000.0, 4000000);
-- expect: 4
SELECT COUNT(*) FROM accounts;

SELECT

-- expect: Alice
SELECT * FROM accounts WHERE account_id = 1;
-- expect: Bob
SELECT owner, balance FROM accounts WHERE owner = 'Bob';

WHERE

The comparison operators are =, !=, <, <=, > and >=. AND, OR, NOT and parentheses combine conditions.

-- expect: 1 rows
SELECT * FROM accounts WHERE balance > 2000.0 AND owner != 'Diana';
-- expect: 3 rows
SELECT owner FROM accounts WHERE NOT account_id = 1;
-- expect: Bob
SELECT owner FROM accounts WHERE (account_id = 1 OR account_id = 2) AND balance > 1500.0;

IN, BETWEEN, LIKE, ILIKE (not case-sensitive) and IS NULL:

-- expect: 2 rows
SELECT owner FROM accounts WHERE account_id IN (1, 2);
-- expect: 2 rows
SELECT owner FROM accounts WHERE balance BETWEEN 1000.0 AND 2000.0;
-- expect: Alice
SELECT owner FROM accounts WHERE owner LIKE 'Al%';
-- expect: Alice
SELECT owner FROM accounts WHERE owner ILIKE 'al%';
-- expect: 0 rows
SELECT owner FROM accounts WHERE owner IS NULL;

ORDER BY, LIMIT and OFFSET

ORDER BY sorts by one or more columns, ASC (the default) or DESC. OFFSET comes after LIMIT.

-- expect: Diana
SELECT owner FROM accounts ORDER BY balance DESC LIMIT 1;
-- expect: 2 rows
SELECT * FROM accounts LIMIT 2;
-- expect: Charlie
SELECT owner FROM accounts ORDER BY account_id LIMIT 1 OFFSET 2;

Aggregates

COUNT(*), SUM, AVG, MIN and MAX:

-- expect: 4
SELECT COUNT(*) FROM accounts;
-- expect: 10000
SELECT SUM(balance) FROM accounts;
-- expect: 2500
SELECT AVG(balance) FROM accounts;
-- expect: 1000
SELECT MIN(balance) FROM accounts;
-- expect: 4000
SELECT MAX(balance) FROM accounts;

GROUP BY and HAVING

CREATE TABLE orders (
  order_id INT,
  account_id INT,
  amount FLOAT,
  PRIMARY KEY (order_id)
);
INSERT INTO orders (order_id, account_id, amount)
VALUES
  (101, 1, 100.0),
  (102, 1, 200.0),
  (103, 2, 150.0),
  (104, 3, 300.0),
  (105, 3, 250.0);
-- expect: 3 rows
SELECT account_id, COUNT(*) as order_count, SUM(amount) as total
FROM orders
GROUP BY account_id;
-- expect: 2 rows
SELECT account_id, SUM(amount) as total
FROM orders
GROUP BY account_id
HAVING SUM(amount) > 150.0;

JOIN

JOIN (inner), LEFT JOIN and RIGHT JOIN join two tables on a condition.

-- expect: Alice
SELECT accounts.owner, orders.order_id
FROM accounts
JOIN orders ON accounts.account_id = orders.account_id
WHERE accounts.account_id = 1;
-- expect: 6 rows
SELECT accounts.owner, orders.order_id
FROM accounts
LEFT JOIN orders ON accounts.account_id = orders.account_id;
-- expect: 5 rows
SELECT accounts.owner, orders.order_id
FROM accounts
RIGHT JOIN orders ON accounts.account_id = orders.account_id;

UPDATE

UPDATE accounts SET balance = 1500.0 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100.0;
-- expect: 1600
SELECT balance FROM accounts WHERE account_id = 1;

An UPDATE without WHERE changes every row.

DELETE

DELETE FROM accounts WHERE account_id = 4;
-- expect: 3
SELECT COUNT(*) FROM accounts;

Document collections

A document collection holds JSON documents. Each document has a key, _id.

-- expect: OK
CREATE COLLECTION documents TYPE DOCUMENT;

INSERT without an _id makes one. PUT '<id>' {...} writes the document at that key, and it replaces a document that is there. GET reads one document by its key.

-- expect: OK
INSERT INTO documents {"name": "Alice", "age": 30};
-- expect: 1 rows
SELECT * FROM documents;
-- expect: OK
PUT 'doc1' {"name": "Bob", "title": "Engineer"} INTO documents;
-- expect: doc1
GET 'doc1' FROM documents;

A WHERE reads nested fields with dots. On an array field, = is true when one element of the array is equal to the value.

-- expect: OK
INSERT INTO documents {"_id": "doc2", "name": "Carol", "address": {"city": "Oslo"}, "tags": ["red", "blue"]};
-- expect: Carol
SELECT name FROM documents WHERE address.city = 'Oslo';
-- expect: Carol
SELECT name FROM documents WHERE tags = 'red';

UPDATE and DELETE work on documents with any WHERE:

UPDATE documents SET age = 31 WHERE name = 'Alice';
-- expect: 31
SELECT age FROM documents WHERE name = 'Alice';
DELETE FROM documents WHERE _id = 'doc2';
-- expect: 2
SELECT COUNT(*) FROM documents;

Key-value collections

A key-value collection holds one value per key.

-- expect: OK
CREATE COLLECTION cache TYPE KV;
-- expect: OK
PUT 'key1' 1000 INTO cache;
-- expect: OK
PUT 'key2' 2000 INTO cache;
-- expect: key1
GET 'key1' FROM cache;
-- expect: OK
DELETE 'key1' FROM cache;

Indexes

A secondary index speeds up a WHERE on its column. DESCRIBE SELECT shows the plan of a query.

CREATE INDEX idx_owner ON accounts (owner);
-- expect: 1 rows
SHOW INDEXES;
-- expect: 1 rows
SHOW INDEXES FROM accounts;
-- expect: index scan
DESCRIBE SELECT * FROM accounts WHERE owner = 'Alice';
DROP INDEX idx_owner;

Transactions

BEGIN and COMMIT group statements.

BEGIN;
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (5, 'Eve', 500.0, 5000000);
COMMIT;
-- expect: 4
SELECT COUNT(*) FROM accounts;

A COMMIT can carry an idempotency token. A client that lost its connection asks GET TRANSACTION STATUS whether the commit happened, and it retries only when it did not.

BEGIN;
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (6, 'Frank', 600.0, 6000000);
COMMIT WITH TOKEN 'token-unique-001';
-- expect: OK
GET TRANSACTION STATUS 'token-unique-001';

TRANSFER moves an amount from one row to another in one atomic step. It needs a server connection:

TRANSFER 10 OF balance ON accounts FROM 1 TO 2;

Time travel and history

Every commit keeps the old data readable. AS OF reads a table at a commit (HEAD is the newest), and SHOW COMMITS lists the commits. SHOW COMMIT <hash> shows one commit.

-- expect: 5
SELECT COUNT(*) FROM accounts AS OF HEAD;
-- expect: hash
SHOW COMMITS;

Branches

A branch is a named root of the whole database. It costs no copy.

-- expect: OK
CREATE BRANCH dev_branch;
-- expect: 2 rows
SHOW BRANCHES;
-- expect: OK
USE BRANCH dev_branch;
-- expect: OK
USE BRANCH main;
-- expect: OK
DROP BRANCH dev_branch;

WATCH: live queries

WATCH <table> [FAST] [VERBOSE] [WHERE <column> <operator> <value>] streams the changes of a table to the client, over the WebSocket of a server connection, until UNWATCH. The embedded mode answers OK, but it has no connection to stream on.

WATCH accounts WHERE balance > 100;
UNWATCH accounts;

Databases

A database holds its own tables and collections.

-- expect: OK
CREATE DATABASE mydb;
-- expect: 2 rows
SHOW DATABASES;
DROP DATABASE mydb;

USE <database> switches the database of a session. It needs a server connection:

USE mydb;

Functions and tickers

A function is a script in kd, the scripting language of kinedb. Its parameters are variables in the body, and return gives the result. Functions and tickers need a server.

CREATE FUNCTION add_one(x) RETURNS VALUE LANGUAGE kd AS $$ return x + 1; $$;
CALL add_one(41);
SHOW FUNCTIONS;
DROP FUNCTION add_one;

A ticker calls a function on a schedule. OVERLAP SKIP (the default) skips a run while the last run still works; OVERLAP WAIT waits for it.

CREATE FUNCTION ping() RETURNS VALUE LANGUAGE kd AS $$ return 1; $$;
CREATE TICKER ping_every_minute EVERY 60s CALL ping OVERLAP SKIP;
SHOW TICKERS;
DROP TICKER ping_every_minute;

Users, roles and permissions

These statements need a server.

CREATE USER 'alice' PASSWORD 'secret123';
ALTER USER 'alice' SET PASSWORD 'newsecret';
ALTER USER 'alice' REVOKE SESSIONS;
DROP USER 'alice';

A permission goes to a target: ANYONE, AUTHENTICATED, ROLE <name>, or MATCH <field> (a rule that matches the user against a field of the row). A WHERE limits the permission to the matching rows. A user gets permissions through roles.

CREATE ROLE analysts;
GRANT ROLE analysts TO 'alice';
GRANT SELECT ON documents TO ROLE analysts;
GRANT SELECT ON documents TO ANYONE WHERE _type = 'public';
GRANT ALL ON orders TO ROLE analysts;
REVOKE SELECT ON documents FROM ROLE analysts;
REVOKE ROLE analysts FROM 'alice';
DROP ROLE analysts;

ALTER COLLECTION <name> SET POLICY '<json array>' replaces the whole policy list of a collection:

ALTER COLLECTION documents SET POLICY '[]';

Settings and cluster operations

These statements are for operators, and most of them need a server.

SHOW SETTINGS;
SET CLUSTER cluster.slow_query_threshold_ms = 100;
SHOW CLUSTER STATUS;
SHOW CLUSTER QUORUM;
SET CLUSTER QUORUM = 1;
SHOW NODES;
SHOW PEERS;
SHOW RAFTGROUPS;
SHOW RAFTGROUP 0;
SHOW SHARDS;
SHOW PLACEMENT;
SHOW TABLE STATS;
SHOW METRICS;
SHOW SLOW QUERIES;
SHOW MEMORY;
SHOW DISK STATUS;
SHOW SYNC STATUS;
SHOW S3 STATUS;
SHOW ROOTS;
SHOW LEAVES OF accounts LIMIT 100;
CLUSTER VERIFY;
SPLIT SHARD 0 OF accounts AT 'm';
MERGE SHARDS 0 1 OF accounts;
ALTER CLUSTER EVICT NODE 'abc123';
ALTER CLUSTER READMIT NODE 'abc123';
VACUUM;

• SHOW SETTINGS lists every setting with its value; SET CLUSTER <setting> = <value> changes a cluster setting on every node. See Run kinedb.
• SHOW NODES, SHOW PEERS, SHOW RAFTGROUPS and SHOW SHARDS show the members, the Raft groups and the shards. CLUSTER VERIFY checks that the replicas agree.
• SPLIT SHARD and MERGE SHARDS split and merge shards by hand. The cluster also splits a shard by itself when it grows.
• ALTER CLUSTER EVICT NODE removes a node from the cluster, and READMIT lets it back.
• VACUUM reclaims the space of dead blocks.