# 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:

```sh
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.

```sql
-- 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.

```sql
-- 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:

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

Drop a table and its data:

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

## INSERT

Insert one row, or several rows in one statement:

```sql
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

```sql
-- 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.

```sql
-- 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`:

```sql
-- 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`.

```sql
-- 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`:

```sql
-- 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

```sql
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.

```sql
-- 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

```sql
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

```sql
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`.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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`:

```sql
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.

```sql
-- 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.

```sql
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.

```sql
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.

```sql
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:

```sql server
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.

```sql
-- 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.

```sql
-- 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.

```sql server
WATCH accounts WHERE balance > 100;
UNWATCH accounts;
```

## Databases

A database holds its own tables and collections.

```sql
-- 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:

```sql server
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.

```sql 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.

```sql server
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.

```sql 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.

```sql server
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:

```sql server
ALTER COLLECTION documents SET POLICY '[]';
```

## Settings and cluster operations

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

```sql 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](./run.md).
- `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.