kd: the scripting language

kd is the scripting language of kinedb. A kd script runs in three places:

• A file: kinedb --data-dir ./data -f script.kd runs the script in the process, with no server.
• A stored function: CREATE FUNCTION ... LANGUAGE kd stores a function in the database, and CALL runs it on the server.
• A ticker: CREATE TICKER ... CALL <function> runs a stored function on a schedule.

Every kd value is a JSON value: a number, a string, a boolean, null, an array or an object. Every example on this page ran on the current build with -f, in the order of this page, in one database. A // expect: comment shows a part of the output.

Run a script

kinedb --data-dir ./data -f script.kd

print(...) writes its arguments on one line, with a space between them. After the last statement, kinedb prints the value of that statement: a string as it is, any other value as JSON, and nothing for null.

// expect: hello kd 42
print("hello", "kd", 42);

Values

typeof gives the type of a value. A string takes double or single quotes, and the escapes \n, \t, \r, \\, \' and \".

// expect: number string bool null array object
print(typeof(1), typeof("a"), typeof(true), typeof(null), typeof([1]), typeof({a: 1}));

Variables

let declares a variable. =, += and -= assign to it.

// expect: 35
let x = 10;
x = 30;
x += 10;
x -= 5;
print(x);

Operators

The arithmetic operators are +, -, *, / and %. *, / and % bind stronger than + and -, and parentheses group.

// expect: 17 2 1 2.5 25
print(2 + 3 * 5, 17 % 5, 7 - 6, 5 / 2, (2 + 3) * 5);

+ also joins strings and arrays:

// expect: hello world [1,2,3,4]
print("hello" + " " + "world", [1, 2] + [3, 4]);

The comparison operators are ==, !=, <, <=, > and >=. The logical operators are &&, || and !.

// expect: true true false true
print(1 == 1.0, "a" != "b", true && false, !false || false);

In a condition, null, false, 0 and "" are false. Every other value is true, empty arrays and empty objects too.

// expect: no no no no yes yes
let out = "";
for v in [null, false, 0, "", [], {}] {
  if v { out = out + "yes "; } else { out = out + "no "; }
}
print(out);

Arrays and objects

An index reads an array element (from 0) or an object field. A dot also reads a field. An assignment to an index or a field changes the value.

// expect: 2 Alice
let a = [1, 2, 3];
let p = {name: "Alice", age: 30};
print(a[1], p.name);

// expect: [1,99,3] 31
let a = [1, 2, 3];
a[1] = 99;
let p = {name: "Alice", age: 30};
p.age = 31;
print(a, p["age"]);

Control flow

if, else if and else take a condition without parentheses and a block in braces.

// expect: medium
let n = 15;
if n > 20 { print("large"); } else if n > 10 { print("medium"); } else { print("small"); }

for <name> in <value> walks the elements of an array, or the keys of an object. continue goes to the next element, and break leaves the loop. In a function body, a break or a continue outside a loop is an error: "break outside a loop".

// expect: 7
let sum = 0;
for x in [1, 2, 3, 4, 5, 6] {
  if x == 3 { continue; }
  if x == 5 { break; }
  sum += x;
}
print(sum);

// expect: a=1 b=2
let o = {a: 1, b: 2};
let s = "";
for k in o { s = s + k + "=" + to_string(o[k]) + " "; }
print(s);

Functions

fn defines a function. return gives its result; a function without return gives null. A function can call itself.

// expect: 120
fn fact(n) {
  if n <= 1 { return 1; }
  return n * fact(n - 1);
}
print(fact(5));

Comments

// expect: 3
// a line comment
/* a block
   comment */
print(1 + 2);

Built-in functions

Function	What it gives
print(a, b, ...)	Writes the values on one line. Gives null.
typeof(v)	"number", "string", "bool", "null", "array" or "object".
length(v)	The number of characters of a string, or of elements of an array or an object.
keys(o), values(o)	The keys, or the values, of an object, as an array.
to_string(v)	The value as a string. See to_string.
to_number(s)	The string as a number, or null when it is not a number.
to_int(n)	The number without its fraction.
floor(n), ceil(n), round(n)	The number rounded down, up, or to the nearest integer.

// expect: 3 5 ["a","b"] [1,2]
print(length([1, 2, 3]), length("hello"), keys({a: 1, b: 2}), values({a: 1, b: 2}));

// expect: 42 42 3 3 -4 4 4
print(to_number("42"), to_string(42), to_int(3.7), floor(3.9), floor(-3.2), ceil(3.1), round(3.5));

to_string

to_string(v) gives:

• a string as it is, with no quotes;
• a number in its shortest form, and an integer with no fraction (4001364731, not 4001364731.0);
• true, false or null;
• an array or an object as compact JSON, with no spaces. The keys of an object come in sorted order. Every string inside gets the JSON escapes: \", \\, \n, \t, \r, and \u00XX for the other control characters. A non-ASCII letter stays as it is.

So a quote in a value cannot end a JSON string of the result. The result is not a SQL string literal: a ' in a value stays ', and a string alone gets no escapes. Do not join a value into SQL text. Give it to SQL as a parameter, with query(sql, [a, …]) (parameters); an object parameter becomes a JSON document.

// expect: {"a":4001364731,"b":"q\"}{","c":"a\\b\tc","d":"æøå"} 2.5 it's
print(to_string({b: "q\"}{", a: 4001364731, c: "a\\b\tc", d: "æøå"}), to_string(2.5), to_string("it's"));

The database

A script reads and writes the database with these functions. The same functions work in a file (-f) and in a stored function.

Function	What it does
query(sql)	Runs the SQL. A SELECT gives an array of objects, one per row.
query(sql, [a, b, ...])	Runs the SQL with parameters: $1 gets a, $2 gets b. See parameters.
find(name), find(name, where)	SELECT * FROM <name>, with an optional WHERE <where>.
insert(name, object)	INSERT INTO <name>. A table: the keys of the object are the columns, and a key that exists replaces the row. A document collection: the object is the document, and an _id that exists replaces that document. See documents.
update(name, object, where)	UPDATE <name> SET <key> = <value>, ... WHERE <where>.
delete(name, where), remove(name, where)	DELETE FROM <name>, with an optional WHERE <where>.
log(a, b, ...)	Writes the values to the log output.

The examples use this table:

CREATE TABLE players (id INT PRIMARY KEY, name TEXT, score INT);
INSERT INTO players (id, name, score) VALUES (1, 'ann', 10), (2, 'bob', 20);

// expect: 2 ann 20
let rows = query("SELECT name, score FROM players ORDER BY id");
print(length(rows), rows[0].name, rows[1].score);

// expect: [{"id":2,"name":"bob","score":20},{"id":3,"name":"cid","score":30}]
insert("players", {id: 3, name: "cid", score: 30});
print(find("players", "score > 15"));

// expect: 99 2
update("players", {score: 99}, "id = 1");
delete("players", "id = 2");
print(query("SELECT score FROM players WHERE id = 1")[0].score, length(find("players")));

The last statement of this script is a value, so kinedb prints it:

// expect: 129
let total = 0;
for r in find("players") { total += r.score; }
total

insert and update quote each value of their object, so a string value with a ' lands as it is. A where string and the SQL of query are SQL text, and the server runs them as they are. Do not join a string from a caller into them. Give the value as a parameter.

// expect: it's'); DELETE FROM players; --
insert("players", {id: 4, name: "it's'); DELETE FROM players; --", score: 0});
print(find("players", "id = 4")[0].name);
delete("players", "id = 4");

Parameters

query(sql, [a, b, ...]) puts each value of the array into the SQL as one literal: $1 gets the first value, $2 the second. The value never becomes SQL text, so a string from a caller cannot change the statement. A $1 inside a string, a $$ body or a comment is text, not a parameter.

// expect: 1 it's'); DELETE FROM players; --
query("INSERT INTO players (id, name, score) VALUES ($1, $2, $3)", [5, "it's'); DELETE FROM players; --", 0]);
let rows = query("SELECT id, name FROM players WHERE name = $1", ["it's'); DELETE FROM players; --"]);
print(length(rows), rows[0].name);
delete("players", "id = 5");

A value becomes the literal of its type:

Value	Literal
null	NULL
a bool, a number	the bool, the number
a string	a string, with each ' doubled
an object	a JSON document, for INSERT INTO <collection> $1 and UPSERT INTO <collection> ON (<field>) $1
an array of numbers	a vector

The call refuses another array, a $0, a $n without a value and a value that no $n uses. A statement with a $n and no parameters is refused: "unbound parameter $1: pass its value with sql(text, params), or in kd as query(sql, [args])". A client sends its parameters with sql(text, params); kd and the server use the same binder.

An object parameter writes a document. UPSERT replaces the document with the same field value. A row of a document collection holds _id and the document as JSON text in document:

// expect: "k":"a","text":"it's'} { \"x"}
query("CREATE COLLECTION notes TYPE DOCUMENT");
query("UPSERT INTO notes ON (k) $1", [{k: "a", text: "first"}]);
query("UPSERT INTO notes ON (k) $1", [{k: "a", text: "it's'} { \"x"}]);
let docs = query("SELECT * FROM notes");
print(docs[0].document);
print(length(docs));
query("DROP COLLECTION notes");

Documents

insert(name, object) writes the object as one document into a document collection. A nested object stays nested, and a string keeps its '. An object without _id gets a new _id. An object with an _id that exists replaces that document, as INSERT INTO <collection> {...} does. To replace the document with the same value of another field, use UPSERT (parameters).

// expect: 1 n1 {"_id":"n1","detail":{"x":"it's"},"k":"second"}
query("CREATE COLLECTION notes TYPE DOCUMENT");
insert("notes", {_id: "n1", k: "first", detail: {x: "it's"}});
insert("notes", {_id: "n1", k: "second", detail: {x: "it's"}});
let docs = query("SELECT * FROM notes");
print(length(docs), docs[0]._id, docs[0].document);
query("DROP COLLECTION notes");

A table keeps the row form: each key of the object is a column. A table refuses an object value of a column ("a JSON object is not a column value").

Stored functions and tickers

CREATE FUNCTION <name>(<name> <type>, …) RETURNS <type> LANGUAGE kd AS $$ <body> $$ stores a function, and CALL <name>(<arguments>) runs it. Every parameter and the result have a type, for example INT, TEXT or ROW (an object). The arguments bind to the parameter names in order. CALL converts each argument to the type of its parameter before the body runs, with the rule of a write to a column of that type (see Column types at a write). An extra argument, an argument of the wrong type and a missing argument for a NOT NULL parameter are errors, and the body does not run.

A parameter is NOT NULL unless you write NULL after its type. Only a NULL parameter can get null, from a NULL argument or from a missing argument. The array args also holds all the arguments. The body uses the built-in functions and the database functions above. Stored functions, CALL and tickers need a server connection.

CREATE FUNCTION add_points(player INT, points INT) RETURNS INT LANGUAGE kd AS $$ update("players", {score: points}, "id = " + to_string(player)); return points; $$;
CALL add_points(1, 50);
CREATE FUNCTION decay() RETURNS INT NULL LANGUAGE kd AS $$ query("UPDATE players SET score = score - 1"); return null; $$;
CREATE TICKER decay_every_minute EVERY 60s CALL decay OVERLAP SKIP;

Call a function from a client with call(name, args). Every SDK has it: the JS and Python clients and the JS and Python embedded databases. The SDK reads the signature of the function one time (SHOW FUNCTIONS) and keeps it for the connection. It converts each argument to the type of its parameter. Then it sends CALL name($1, …) with the values as typed parameters, never as SQL text, so an argument cannot inject SQL. args is an array in the order of the parameters, or an object with the parameter names. An argument of the wrong type is refused in the client, with the name of the parameter, and nothing is sent. When the signature changed on the server (CREATE OR REPLACE), the server refuses the call with the refusal kind signature. The SDK then reads the signature again one time and sends the call again. A RETURNS <type> function gives its value; a RETURNS TABLE function gives the rows as objects.

const points = await db.call('add_points', [1, 50]);       // 50
const same = await db.call('add_points', { player: 1, points: 50 });

In an embedded database, call(name, args, { offline }) goes to the server while the database has a server connection. Offline, offline: 'refuse' fails at once with "needs the server". The local run ('preview', the default) and the queued call ('queue') are not built yet (B2175); offline, they also fail with a clear error.

The server checks the result before the writes commit. A result of the wrong type, or null for a result that is not NULL, fails the CALL, and no write of the body commits. The types, the conversion rule, RETURNS TABLE and the exact errors are in the SQL reference.

A read in a stored function is routed like a client read. Each SELECT of the body (query, find) goes to a node that serves the table's shard right now, as the same SELECT sent by a client does. A node that is not current on the shard (it has no read lease from its leader, or it lost contact with the leader) passes the read on and never answers from its own copy. When no current node is reachable, the CALL fails with the temporary answer ... retry shortly ..., and nothing of the body was written. The writes of the body commit together after the body ends. A body cannot run CALL or VACUUM.

A body runs with the rights of its caller. A CALL applies the permissions of the user on the session, so a stored function gives nobody more rights than they have. The caller also needs the INVOKE right on the function (GRANT INVOKE ON FUNCTION <name> TO …); a function with no grant is for root only. The server checks the right before the body runs. A ticker calls its function as the user who created the ticker, and the server resolves that user again on every tick. When the creator no longer exists, the server skips the tick instead of running the body: it never runs the body with no permission check.

A function created with RUN AS <user> runs with the rights of that user. Every read and every write of the body then uses the permissions of that user, for every caller and for a ticker too. The server checks the INVOKE right of the caller first, and only then runs the body as that user. The server resolves the user on every call. When the user no longer exists, the CALL fails before the body runs. A function that anyone may call must check the range of its arguments and write only what it must: see a small API with a function.

CREATE OR REPLACE FUNCTION changes the signature and the body in one step. The function exists during the whole change, so a CALL or a ticker never finds it missing. The grants of the function stay. A function that an older release stored has no types, and the server refuses its CALL until you replace it (a function without types).

Trigger functions

A trigger calls a stored function for each row of a write (Triggers). The function has the signature (old_row ROW NULL, new_row ROW NULL) RETURNS ROW NULL. old_row is the stored row (null for an INSERT), and new_row is the row that the write will store (null for a DELETE). Return an object to set cells of the new row, or null to keep it. refuse("<reason>") refuses the write, and nothing of the write and of the body commits. The writes of the body commit in the same batch as the write.

CREATE FUNCTION no_empty_name(old_row ROW NULL, new_row ROW NULL) RETURNS ROW NULL LANGUAGE kd AS $$
  if new_row == null { return null; }
  if new_row.name == "" { refuse("name may not be empty"); }
  return null;
$$;

Limits

A kd script runs inside the kinedb process, so the interpreter limits each run. A run that goes past a limit stops with an error, and the process continues. A stored function that stops commits none of its writes.

Limit	Value	The error
Instructions	1 000 000	instruction limit exceeded (1000000 instructions)
Wall time	30 s; a stored function sets its own, see below	script execution timed out (30000 ms limit)
Call depth	64 (the evaluation depth comes first, see below)	call stack overflow (max 64 levels)
Memory	see below	kd function memory limit exceeded (<n> bytes)
Nesting depth	64	script nests deeper than 64 levels
Evaluation depth	128	script evaluation nests deeper than 128 levels
Deferred SQL of a stored function	the write limit of one transaction, see below	the batch of this function body of <n> MiB exceeds this node's write-pipeline budget, or … exceeds the Raft entry ceiling

A stored function can set its own wall time. Add WITH (timeout_ms = <n>) after the body, and after RUN AS when the statement has it. The value is in milliseconds, from 1 to 300000. A function without the clause has 30000 ms. SHOW CREATE FUNCTION shows the clause when the value is not 30000. The interpreter checks the time before each native call (for example query) and every 1024 instructions. One long native call is not stopped.

CREATE FUNCTION nightly() RETURNS INT LANGUAGE kd AS $$ return 1; $$ WITH (timeout_ms = 120000);

A stored function commits its writes as one transaction, so one transaction limit applies to them. The function keeps the text of each write until it returns, and also the text of each row read that goes with the writes. The total size of these texts has the same limit as a transaction([...]) call: the write-pipeline budget of the node, and never more than the Raft entry ceiling (about 63.9 MiB). The statement that goes past the limit stops the function, and the function commits none of its writes. The same call cannot succeed again: write fewer or smaller rows in one call.

The nesting limit applies when kinedb parses the script, before the script runs. CREATE FUNCTION refuses a body above the limit. Each block, (, [, {, index, call, ! and - is one level. A chain of operators adds one level for each link, so a + b + c uses two levels. To join many values, put them in an array and use a for loop.

The evaluation limit applies while the script runs. It counts the statements and the expressions that are not complete at one time, through all the active calls. Each statement is one level, and each expression is one level. A ( is not a level. A call of your own function uses at least two levels: the statement and the call. The levels of its body add to these two. Thus a recursion stops at this limit before it gets to the call depth of 64. The limit keeps the stack of the interpreter in 2 MiB.

The table shows the deepest recursion f(C) that runs. A "before" value of 63 is the call limit: a larger C got the call-depth error. A smaller "before" value is the stack: a larger C stopped the process. In the "now" column, a larger C gets the evaluation error.

The recursive step in fn f(n)	Levels for each call	Largest C, before	Largest C, now
let a = f(n - 1);	2	63	61
return f(n - 1) + 0;	3	63	41
let a = [[f(n - 1)]];	4	63	30
let a = [[[[[f(n - 1)]]]]];	7	51	17
10 levels of [ around f(n - 1)	12	31	10
20 levels of [ around f(n - 1)	22	17	5

The "before" values are for a server built for development, which uses more stack. For a deeper recursion, use a loop: keep the pending values in an array.

The memory limit counts the values that the run holds at one time: the variables, the print output and the values of an expression that is not complete. A value that the script replaces gives its bytes back. The count is an estimate of the heap bytes. A server started with --memory gives each call a quarter of its transient reserve, at most 256 MiB. A server without --memory and a -f script get 256 MiB. Each call has its own limit; the limit does not divide between calls that run at the same time.

See the SQL reference for CREATE OR REPLACE FUNCTION, SHOW FUNCTIONS, DROP FUNCTION and the ticker statements.