SQL

Wiki / Tables and SQL

On this page

• Where SQL runs
• Names and parameters
• SELECT
• Expressions and functions
• INSERT, UPDATE and DELETE
• Reading by key and index
• SQL as text: the sql. procedures
• Errors

SQL in Galactus follows SQLite: the same results for the same data and query, checked against SQLite on thousands of generated queries. From version 2.1.0. Two choices differ: LIKE is case-sensitive (ILIKE ignores case), and CAST of text that is not a number fails instead of reading as 0.

Where SQL runs

A braced block marks where SQL starts and ends:

// Each friend's latest three orders
MATCH (:Customer {id: $id})-[:KNOWS]->(f:Customer)
CALL (f) SQL { SELECT sku, placed_at FROM f.orders ORDER BY placed_at DESC LIMIT 3 }
RETURN f.name, sku, placed_at;

// A summary back onto the node, in the same transaction
MATCH (c:Customer {id: $id})
SET c.lifetime_spend = SQL { SELECT coalesce(sum(amount), 0) FROM c.orders };

INSERT, UPDATE and DELETE run only in CALL (…) SQL { }. A block is parsed with the statement, so a syntax error fails before anything runs, and its compiled plan is kept while the tables' schemas stay the same.

Names and parameters

• In FROM, c.orders names a table of a variable in scope. CALL (c, d) SQL sees c and d, CALL (*) SQL every variable, and CALL SQL none. The value forms see every variable.
• $name is a query parameter or a variable in scope, and $name.key reads a property; a name that is both is an error. A list of maps also reads as a table: FROM $rows AS r.
• Parameters are values, never SQL: $sku holding 'x'; DROP … is the string it holds. A table name cannot come from a parameter's text.
• Names compare without regard to case where unique; "x", `x` and [x] quote a name. A keyword can name a table after FROM (FROM Order).

SELECT

Nulls sort first ascending, as in SQLite. sum over no rows is null; total is 0.0. Integer division truncates. Result columns need distinct names.

Expressions and functions

Comparison (= == <> != < <= > >=), AND OR NOT, IS [NOT] NULL|TRUE|FALSE, IS [NOT] DISTINCT FROM, [NOT] BETWEEN, [NOT] IN, [NOT] LIKE … [ESCAPE], ILIKE, GLOB, REGEXP, arithmetic, ||, CASE, CAST/::, typed literals (DATE '2026-10-24') and CURRENT_DATE/TIME/TIMESTAMP.

Functions: coalesce, ifnull, nullif, iif, upper, lower, length, octet_length, substr/substring (SQLite's rules, FROM … FOR), trim, ltrim, rtrim, replace, instr/strpos/position, lpad, rpad, repeat, reverse, left, right, concat, concat_ws, split_part, initcap, chr/char, ascii/unicode, hex, typeof, abs, round, ceil, floor, trunc, sign, sqrt, cbrt, power, exp, ln, log, log10, log2, mod, pi, trigonometry, degrees, radians, greatest, least, EXTRACT/date_part, now(). Any other name calls the Cypher function of that name.

INSERT, UPDATE and DELETE

MATCH (c:Customer {id: $id})
CALL (c) SQL {
  INSERT INTO c.orders (id, placed_at, sku, qty, amount)
  VALUES ($orderId, localdatetime(), $sku, $qty, $amount)
  ON CONFLICT (id) DO UPDATE SET qty = orders.qty + excluded.qty
  RETURNING id, qty
}
RETURN id, qty;

• Every value is computed before the first write, so SET a = b, b = a swaps. Types, NOT NULL, the primary key, unique indexes and CHECK constraints are checked for every row.
• A failed statement changes nothing, graph changes in it included; in an explicit transaction only that statement is undone.
• Without RETURNING a change keeps the outer row; with it, the outer row repeats once per changed row (none when nothing changed).
• MATCH (c:Customer) CALL (c) SQL { UPDATE c.orders … } changes every customer's table. A node without the table is skipped; INSERT into one is an error naming it.

Reading by key and index

A select over one table whose WHERE bounds the primary key or an index (=, IN, IN $list, <, <=, >, >=, BETWEEN, joined by AND, on the leading columns) reads that range instead of the table: on 20,000 rows a key lookup is more than ten times faster than a scan. The WHERE still filters, so a bound of another type is simply left to it. sql.explain shows the choice.

SQL as text: the sql. procedures

The text sees only params: sql.value('SELECT count(*) FROM $t WHERE qty > $min', {t: c.orders, min: 3}). These procedures read; changes run in CALL (c) SQL { }. sql.page in key order continues after the last key returned, so rows written between pages neither repeat nor go missing; in another order it continues by position.

Errors

These are the Bolt codes drivers receive, from version 2.1.2; a statement that fails changes nothing, graph changes in it included. Node and relationship constraints report ConstraintValidationFailed too. Messages name the problem in SQL terms: CHECK constraint positive_qty failed: qty > 0, a row with this primary key (id) already exists, no such column: colour.