JJijibora Docs
docs.jijibora.online
On this page (12 sections)

SurrealQL 3.0.5 — Strata Dialect Reference

Status: 2026-11-09. Purpose: SurrealDB 3.0.5 dialect constraints (both import and HTTP /sql paths). If a statement pattern does not appear below, it is unsupported or the author could not verify it. All statements below were verified as working or known-broken from import and /sql endpoints.

For the project's architectural mandate: zero bespoke tables. Every domain entity maps to canonical claim/truth/integration/resource primitives. SurrealQL is the language through which those primitives are defined and accessed. The following reference exists so the project does not need to memorise SurrealQL details from scratch every time.

Syntax constraints (both import and /sql)

  • OPTION IMPORT; must be first statement in surreal import for reliable table/field/index definitions; functions re-applied via HTTP after import.
  • No COALESCE — use ?? (null coalescing) operator instead.
  • No LEFT JOIN — use subquery enrichment pattern:

(SELECT name FROM profiles WHERE id = chat_messages.user_id LIMIT 1)[0].name as user_name

  • No FROM table alias in DEFINE FUNCTION — use full table name in field references.
  • No default parameter values in DEFINE FUNCTION signatures — use $var ?? default_value inside body.
  • string::sha256 doesn't exist — use crypto::sha256 instead (returns string directly).
  • type::is::string doesn't exist — use type::is_string instead.
  • string::match doesn't exist — use string::matches instead.
  • string::format doesn't exist — use <string> cast + string::concat.
  • ~ (contains) operator not supported inside DEFINE FUNCTION — use string::contains().
  • ~ (contains) not supported via HTTP /sql — use string::contains().
  • OFFSET not supported via HTTP /sql — use START instead (e.g. LIMIT 50 START 10); OFFSET works in CLI but fails parse error in HTTP.
  • ORDER BY field must appear in SELECT projection inside DEFINE FUNCTION.
  • $arr += val doesn't work — use LET $arr = array::concat($arr, [val]).
  • $arr.push(val) doesn't work — use array::concat accumulator.
  • $arr += val inside FOR loop triggers deprecated parameter error — use one-at-a-time function pattern instead (process single record per call).
  • .length property doesn't work — use array::len($arr).
  • IF $var { truthy check not supported — use IF $var != NONE {.
  • NOT $var prefix not supported — use $var != true (or IF $var != true { for conditionals).
  • AND NOT $cond / OR NOT $cond not supported — use AND $cond != true / OR $cond != true.
  • 1..N-1 range syntax fails — compute boundary into LET first.
  • FOR ... RETURN inside DEFINE FUNCTION only returns first iteration — use SELECT-only patterns for multi-row queries.
  • ORDER BY in subqueries with outer record references returns null; ORDER BY field must appear in SELECT.
  • $arr IN $arr instead of array::contains(...).
  • rand::uuid() returns UUID type (not string) — when assigning to a TYPE string field, must use <string>rand::uuid() or string::concat('prefix:', rand::uuid()) to coerce.
  • string::join("", $rec_id) for record→string coercion.
  • math::pi is a constant, not a function — math::pi() returns NONE.
  • time::now() - int fails — use time::now() - <duration>("NNNms").
  • SCHEMAFULL object fields need TYPE object FLEXIBLE (or { entries: [] } wrapper) for nested keys like pricing.amount, metadata.stripe_customer_id; ORDER BY fields must appear in SELECT inside DEFINE FUNCTION.
  • **SELECT FROM $record_var fails* — must use SELECT * FROM table WHERE id = $record_var.
  • UPDATE $record_var SET ... silently fails — always use UPDATE table_name SET ... WHERE id_field = $value with an explicit table name and WHERE clause. (This applies to UPDATE $var.id SET ..., UPDATE $var SET ..., and UPDATE $existing[0].id SET ...)
  • UPDATE ... WHERE on non-id fields silently fails — on SCHEMAFULL tables, UPDATE with WHERE claim_type = 'x' AND claim_id = 'y' affects zero rows silently. Use WHERE id = (SELECT id FROM table WHERE ...)[0].id pattern: first SELECT to get the record ID, then UPDATE using WHERE id = $record.id.
  • $msg[0].id returns NONE via HTTP /sql — use a known string identifier field (e.g. message_id, link_id) for subsequent UPDATE/DELETE.
  • $token is a reserved/protected variable name — use $magic_token or similar alias.
  • UPDATE table_name SET ... with no WHERE clause updates ALL rows — always add a WHERE clause condition.
  • geo::h3_cell does not exist — references fn::sentinel::ingest_event; this function cannot be deployed.
  • AS alias in FROM not supported inside DEFINE FUNCTION — GROUP BY uses bare table names.
  • mission_id field is record<claim> type — comparison must be with record, not string.
  • **SELECT FROM fn::function_ret_array() works* for reading function results; functions with DML side effects MUST be called via CREATE table SET field = fn::function() or UPDATE table SET field = fn::function(), NOT via SELECT * FROM fn::function().
  • object.($dynamic_key) syntax NOT supported — dynamic field access must use $obj[$dynamic_key] bracket notation for reading; avoid dynamic key writes.
  • surreal import is reliable for DEFINE FUNCTION when using OPTION IMPORT; prefix and avoiding unsupported syntax; complex bodies may silently fail and must be re-applied via CLI after import.
  • object::merge not supported inside DEFINE FUNCTION — use object::extend instead (10 occurrences fixed in matching engine).
  • type::thing does not exist — use type::record('table', $id) instead.
  • time::now()::string coercion fails — use <string>time::now() cast syntax instead.
  • NOT $var prefix not supported — use $var != true (or IF $var != true { for conditionals).
  • IF NOT type::is_object($x) not supported — use IF type::is_object($x) != true {.
  • AND NOT $cond / OR NOT $cond not supported — use AND $cond != true / OR $cond != true.
  • Ternary x ? y : z not supported — use IF x THEN y ELSE END.
  • function()::string cast syntax fails — use <string>function() instead.
  • math::atan2 does not exist — use 2 * math::asin(math::sqrt($a)) for haversine distance.
  • COALESCE does not exist — use ?? null coalescing operator.
  • SELECT ... SET invalid inside DEFINE FUNCTION — use FOR $item IN (SELECT ...) { UPDATE table SET ... WHERE id = $item.id; }.
  • Default parameter values in DEFINE FUNCTION signatures — use $var ?? default_value inside body (even $x = default in sig silently fails).
  • UPDATE $record_variable SET ... silently fails — use UPDATE table_name SET ... WHERE id = $record_variable.id (always use WHERE id = with explicit table name).
  • Branches need explicit RETURN — a bare $expr; at the end of an IF branch returns NONE, not the expression value. Every branch that should produce output must start with RETURN.
  • Double prefix risk with type::record — if the string already contains "table:id", calling type::record('table', 'table:id') produces table:table:id; use the string directly in WHERE: WHERE id = $string_id — SurrealDB coerces strings to record IDs in comparisons.
  • geo::distance in 3.0.5 expects two geometry values; search uses latitude/longitude + fn::_haversine (s41).
  • SELECT ... WHERE on non-id fields — on SCHEMAFULL tables, UPDATE WHERE on non-id fields silently affects zero rows. Use WHERE id = (SELECT id FROM table WHERE ...)[0].id pattern.
  • .length property on array returned from function — use array::len() instead.
  • array::flatten not supported — use array::concat with empty initial.
  • array::filter not supported — use manual FOR loop with IF condition.
  • array::map not supported — use manual FOR loop with accumulation.
  • array::reduce not supported — use manual FOR loop with accumulator.
  • string::contains not supported via HTTP /sql — documented above; same constraint in import.
  • type::is_string not supported via HTTP /sql — documented above; same constraint in import.
  • time::now() - int not supported via HTTP /sql — documented above; same constraint in import.
  • SELECT ... SET not supported via HTTP /sql — documented above; same constraint in import.
  • Default parameter values in DEFINE FUNCTION signatures — documented above; even $x = default in sig silently fails.
  • UPDATE $record_variable SET ... silently fails — documented above; must use WHERE id = explicit table.
  • Branches need explicit RETURN — documented above; bare expr returns NONE.
  • Double prefix risk with type::record — documented above; use string directly in WHERE.

Import-specific limitations (surreal import)

  • object::merge not supported inside DEFINE FUNCTION — use object::extend instead.
  • type::thing does not exist — use type::record('table', $id) instead.
  • time::now()::string coercion fails — use <string>time::now() cast syntax instead.
  • NOT $var prefix not supported — use $var != true (or IF $var != true { for conditionals).
  • IF NOT type::is_object($x) not supported — use IF type::is_object($x) != true {.
  • AND NOT $cond / OR NOT $cond not supported — use AND $cond != true / OR $cond != true.
  • Ternary x ? y : z not supported — use IF x THEN y ELSE END.
  • function()::string cast syntax fails — use <string>function() instead.
  • math::atan2 does not exist — use 2 * math::asin(math::sqrt($a)) for haversine distance.
  • COALESCE does not exist — use ?? null coalescing operator.
  • SELECT ... SET invalid inside DEFINE FUNCTION — use FOR $item IN (SELECT ...) { UPDATE table SET ... WHERE id = $item.id; }.
  • Default parameter values in DEFINE FUNCTION signatures — use $var ?? default_value inside body.
  • UPDATE $record_variable SET ... silently fails — use UPDATE table_name SET ... WHERE id = $record_variable.id.
  • Branches need explicit RETURN — a bare $expr; at the end of an IF branch returns NONE, not the expression value.

HTTP /sql endpoint constraints

  • HTTP /sql expects Content-Type: text/plain with USE NS africa DB community_os; prefix; JSON body {"sql":"..."} returns empty results.
  • PowerShell Invoke-RestMethod truncates nested SurrealDB result objects — use surreal sql CLI for accurate data inspection.
  • DEFINE FUNCTION via HTTP returns "NotAllowed" — use surreal import or surreal sql CLI instead.
  • OFFSET not supported via HTTP /sql — use START instead.
  • $msg[0].id returns NONE via HTTP /sql — use known identifier field instead.
  • $token is reserved — avoid using as variable name.
  • UPDATE ... WHERE on non-id fields silently fails — same as import; always use WHERE id = pattern.
  • geo::h3_cell does not exist — same as import.
  • AS alias in FROM — same as import; GROUP BY uses bare table names.

DEFINE FUNCTION body constraints

  • ORDER BY field must appear in SELECT projection.
  • SELECT ... SET invalid — use FOR loop pattern.
  • Default parameter values — use $var ?? default_value inside body.
  • UPDATE $record_variable SET ... silently fails — use explicit table + WHERE.
  • Branches need explicit RETURN — every branch that should produce output must start with RETURN.
  • AND NOT $cond / OR NOT $cond not supported — use AND $cond != true / OR $cond != true.
  • NOT $var prefix not supported — use $var != true or IF $var != true {.
  • $arr += val / $arr.push(val) doesn't work — use array::concat.
  • .length doesn't work — use array::len().
  • object::merge not supported — use object::extend.

Event body constraints

  • DEFINE EVENT on truth_edge, claim, truth_node → CREATE nats_outbox only — no function calls in event body.
  • Events on user_identity table don't fire after surreal import — root cause unknown.

Gate notes (validated against project mandates)

  • All 32 matching engine functions deployed via surreal import with OPTION IMPORT after fixing 14 classes of incompatibility.
  • Gate checks against 18 validated AGENTS.md mandate violations: 4 CRITICAL, 7 HIGH, 4 MEDIUM, 3 LOW.
  • Index WHERE field = value not allowed — partial indexes not allowed.
  • DEFINE INDEX ... WHERE field = value not supported.
  • Top-level operators (~, NOT) have different import vs /sql behaviour.
  • Branch logic requires explicit RETURN (bare expr = NONE).
  • Always add WHERE clause to UPDATE; bare UPDATE updates all rows.
  • SCHEMAFULL WHERE on non-id fields silently affects zero rows — use SELECT→WHERE→UPDATE pattern.
  • Complex DEFINE FUNCTION bodies may silently fail via import; re-apply via CLI after import.

Quick reference table

PatternStatusAlternative
COALESCE(x, y)❌ brokenx ?? y
LEFT JOIN t ON ...❌ brokensubquery enrichment
$arr.push(val)❌ brokenarray::concat($arr, [val])
$arr += val❌ brokenLET $arr = array::concat($arr, [val])
$arr += val in FOR❌ deprecated errorone-at-a-time function pattern
~ x❌ import & /sqlstring::contains(x)
NOT $v❌ import & /sql$v != true
$v IN $arr✅ supportedarray::contains($arr, $v) is also valid
1..N-1 range❌ brokencompute boundary into LET
OFFSET N❌ /sql onlySTART N (CLI supports OFFSET)
math::pi()❌ returns NONEuse constant math::pi
time::now() - 1000❌ brokentime::now() - <duration>("1000ms")
SELECT * FROM $var❌ brokenSELECT * FROM table WHERE id = $var
UPDATE $var SET ...❌ silent failUPDATE table SET ... WHERE id = $var.id
UPDATE ... WHERE claim_type=❌ silent zero rowsWHERE id = (SELECT id FROM table WHERE claim_type='x')[0].id
function()::string❌ broken<string>function()
x ? y : z❌ brokenIF x THEN y ELSE END
DEFINE FUNCTION via HTTP❌ NotAllowedsurreal import or CLI
SELECT ... SET in FUNCTION❌ brokenFOR $item IN (...) { UPDATE ... }
object::merge in FUNCTION❌ brokenobject::extend
type::thing(...)❌ does not existtype::record('table', ...)
time::now()::string❌ fails<string>time::now()
geo::h3_cell❌ does not existremoved from fn::sentinel::ingest_event

Function body rules (for reference)

  • Every IF branch that should produce output must start with RETURN.
  • Default parameter values go inside the body as $var ?? default_value.
  • UPDATE with a record variable as table reference produces no error but does not persist changes — always use explicit table name with WHERE id =.
  • UPDATE WHERE on non-id fields silently affects zero rows on SCHEMAFULL tables — use SELECT→get id→UPDATE WHERE id = pattern.
  • geo::distance expects two geometry values — search uses lat/long + fn::_haversine instead.
  • $arr += val inside FOR triggers deprecated parameter error — use accumulator pattern.
  • .length property doesn't work — use array::len().
  • object.($dynamic_key) not supported — use $obj[$dynamic_key] bracket notation.
  • DEFINE FUNCTION via HTTP returns NotAllowed — re-apply via import or CLI.
  • Branches need explicit RETURN — bare expr returns NONE, not the expression value.
  • UPDATE with no WHERE updates ALL rows — always add WHERE condition.
  • Default parameter values in signatures silently fail — use $var ?? default_value in body.
  • math::pi() returns NONE — it's a constant, use math::pi.
  • rand::uuid() returns UUID type — must coerce to string with <string>rand::uuid().
  • time::now() - int fails — use time::now() - <duration>("NNNms").
  • SCHEMAFULL object fields need TYPE object FLEXIBLE for nested keys.
  • SELECT * FROM $record_var fails — must use table ref with WHERE.
  • object::merge not supported — use object::extend instead.
  • type::thing does not exist — use type::record('table', $id).
  • time::now()::string coercion fails — use <string>time::now() cast.
  • ~ (contains) not supported inside DEFINE FUNCTION — use string::contains().
  • ~ (contains) not supported via HTTP /sql — use string::contains().
  • OFFSET not supported via HTTP /sql — use START instead.
  • AS alias in FROM not supported inside DEFINE FUNCTION — GROUP BY uses bare table names.
  • $token is reserved — use $magic_token or similar alias.
  • $msg[0].id returns NONE via HTTP /sql — use known identifier field instead.
  • Complex DEFINE FUNCTION bodies may silently fail via import — must re-apply via CLI after import.
  • object::merge not supported — use object::extend instead (confirmed 10 occurrences in matching engine).
  • type::thing does not exist — confirmed use type::record('table', $id).
  • time::now()::string coercion fails — confirmed use <string>time::now().
  • NOT $var prefix not supported — confirmed use $var != true.
  • AND NOT $cond / OR NOT $cond not supported — confirmed use $cond != true.
  • Ternary x ? y : z not supported — confirmed use IF x THEN y ELSE END.
  • function()::string cast fails — confirmed use <string>function().
  • math::atan2 does not exist — confirmed use haversine formula.
  • COALESCE does not exist — confirmed use ??.
  • SELECT ... SET invalid inside DEFINE FUNCTION — confirmed use FOR loop.
  • UPDATE $record_variable SET ... silently fails — confirmed use explicit table + WHERE.
  • Branches need explicit RETURN — confirmed bare expr returns NONE.
  • Table aliases in FROM not supported inside DEFINE FUNCTION — confirmed GROUP BY uses bare names.
  • 1..N-1 range syntax fails — confirmed compute boundary into LET first.
  • .length property doesn't work — confirmed use array::len().
  • array::flatten not supported — confirmed use array::concat with empty initial.

Surreal import workflow checklist

  1. Ensure OPTION IMPORT; is the first statement in the file.
  2. Avoid DEFINE FUNCTION bodies with unsupported patterns (see Import-Specific Limitations).
  3. After surreal import, re-apply any DEFINE FUNCTION via CLI to confirm idempotency.
  4. For table/field/index definitions, import is reliable with OPTION IMPORT prefix.
  5. Complex DEFINE FUNCTION bodies may silently fail to register via import — must re-apply via CLI after import.
  6. After import, spot-check functions with RETURN fn::your_func(...) to confirm registration.
  7. For deployment of functions after import, prefer surreal sql CLI with USE NS africa DB community_os; <sql_text>.
  8. Re-apply DEFINE FUNCTION after import for any function bodies that were silently dropped.
  9. Functions with DML side effects (UPDATE/CREATE inside body) MUST be called via CREATE table SET field = fn::function() or UPDATE table SET field = fn::function(), NOT via SELECT * FROM fn::function().
  10. Always add a WHERE clause to UPDATE statements — bare UPDATE updates ALL rows on SCHEMAFULL tables.

Post-import function re-apply (CLI only)

After surreal import, execute surreal sql --export - http://127.0.0.1:8000 to list all registered functions, then apply any that are absent or silently dropped via surreal sql http://127.0.0.1:8000 --ns africa --db community_os -u root -p root <function-file.surql>.

Cross-SQL compatibility note

  • Statements that work in surreal sql CLI but not in HTTP /sql endpoint are flagged accordingly.
  • Statements that work in import but may silently drop functions require re-apply after import.
  • No statement should be assumed portable across all three channels without explicit verification.
← BackHome — all documentationNext →Jijibora set index