Rhai (evalexpr_rhai)

What you can do with one query

The magic moment: store the rule as text, evaluate it per row, no redeploy.

CREATE TABLE eligibility(name TEXT, rule TEXT);
INSERT INTO eligibility VALUES
  ('high_earner',  'context.salary > 100000'),
  ('senior',       'context.salary > 80000 && context.years >= 5'),
  ('manager_path', 'context.title.contains("Lead") || context.salary > 120000');

SELECT e.name AS bucket, emp.id, emp.name
FROM employees emp
CROSS JOIN eligibility e
WHERE evalexpr_rhai(e.rule,
        { 'salary': emp.salary,
          'years':  emp.years,
          'title':  emp.title }).ok = TRUE;

evalexpr_rhai parses each distinct rule string once, caches the AST, and runs the Rhai interpreter per row against the row’s context. Admins edit the eligibility table; the next query picks up the change. No code path between “rule edited” and “rule evaluated.”

Result shape — UNION(ok JSON, error VARCHAR)

Every call returns a DuckDB UNION with two arms. On success, read .ok; on failure, read .error. The query keeps running either way.

SELECT evalexpr_rhai('5 + 6').ok;          -- 11

SELECT evalexpr_rhai('1 / 0').error;       -- 'Runtime error: ...'

This is the entire error-handling story — there’s no evalexpr_rhai_safe / evalexpr_rhai_strict split. A script that throws produces the error arm for that row; rows whose scripts succeed continue to produce .ok. Failures inside the extension itself, such as a context that can’t be serialized or a string with an embedded NUL byte, also come back as the error arm rather than aborting the query.

An empty script evaluates to JSON null in the ok arm, and a NULL script returns NULL.

Per-row context as JSON

The second argument is a JSON object DuckDB builds from row columns. Inside the script it appears as context.<field>. Anything DuckDB can encode to JSON crosses the boundary — numbers, strings, booleans, arrays, nested objects:

SELECT id,
       evalexpr_rhai('context.qty * context.price * 1.0875',
                     { 'qty': qty, 'price': price }).ok::DECIMAL AS taxed_total
FROM orders;

For a multi-line script with a defined function, pass the whole thing as a string literal:

SELECT range AS n,
       evalexpr_rhai('
         fn collatz(n) {
           let count = 0;
           while n > 1 {
             count += 1;
             n = if n % 2 == 0 { n / 2 } else { n * 3 + 1 };
           }
           count
         }
         collatz(context.n)
       ', { 'n': range }).ok::INTEGER AS length
FROM range(1000, 1005);

The Rhai language itself — operators, control flow, type system, the standard string / array / map methods — is documented in the Rhai Book. The source and issue tracker live at rhaiscript/rhai.

Compile-and-cache for constant scripts

When the script string is a literal in the SQL — or otherwise constant within the query — the Rhai parser runs once. The resulting AST is cached and reused for every row. Per-row cost after warmup is interpreter execution only.

When the script string comes from a column (the rules-from-a-column pattern in the lead snippet), each distinct script value parses on first encounter and caches by content. A handful of distinct rules is fine; thousands of distinct rules per query is the point at which parse cost starts to matter.

The sandbox

Rhai has no host I/O. Scripts cannot:

• Open files, read environment variables, or touch the filesystem.
• Spawn processes or invoke shell commands.
• Make network connections.
• Load Rhai modules from disk (module loading is disabled in this extension).

The only thing a script can see is the context object you pass in. That makes it safe to evaluate script text supplied by users or stored in a config table — there’s no host surface to reach. The full sandbox model is described in the Rhai Book — Safety chapter.

Rhai vs. QuickJS — pick by need

The sibling quickjs extension occupies the same slot. They can coexist in one DuckDB session; pick per use case:

• Rhai is more constrained and safer. Sandboxed by default, no module loading, no host I/O, smaller language. The right choice when you’re evaluating script text from users or a config table, or when you want a small, predictable surface.
• QuickJS is more expressive. Full ECMAScript semantics, regex, the JavaScript standard library, the idioms developers already know. The right choice when you’re porting expressions from a JavaScript codebase or need a richer standard library.
• Neither vectorizes. Both run row by row. If raw throughput dominates, push the logic into native SQL or a compiled UDF instead — the choice between Rhai and QuickJS is about language and sandboxing, not speed.

One-shot expression

The simplest call — no context, no setup. Returns a UNION(ok JSON, error VARCHAR):

SELECT evalexpr_rhai('5 + 6').ok;            -- 11

SELECT evalexpr_rhai('[1, 2, 3].map(|x| x * 2)').ok;
-- [2, 4, 6]

See evalexpr_rhai for the full signature.

Per-row evaluation with context

The second argument is a JSON object exposed inside the script as context.<field>. Build it from row columns:

SELECT name,
       evalexpr_rhai('context.salary > 100000',
                     { 'salary': salary }).ok AS high_earner
FROM employees;

SELECT id,
       evalexpr_rhai('context.qty * context.price * 1.0875',
                     { 'qty': qty, 'price': price }).ok::DECIMAL AS taxed_total
FROM orders;

Anything DuckDB can serialize to JSON crosses the boundary — numbers, strings, booleans, arrays, nested objects.

Rules stored in a column

The pattern that makes this extension worth the install. Store the rule as text, evaluate per row, edit the table to change the rule:

CREATE TABLE group_membership(group_name TEXT, logic TEXT);
INSERT INTO group_membership VALUES
  ('managers',    'context.name == "George" || context.name == "Rusty"'),
  ('shift_leads', 'context.name == "John"'),
  ('employees',   'context.name == "Alex"');

SELECT DISTINCT group_name
FROM group_membership
WHERE evalexpr_rhai(logic, { 'name': 'John' }).ok = TRUE;
-- 'shift_leads'

Each distinct logic string parses on first use and caches by content.

Multi-line scripts and fn definitions

For anything beyond a one-liner, pass a multi-line script — define helpers with fn, call them at the bottom:

CREATE MACRO collatz_length(n) AS
evalexpr_rhai('
  fn collatz(n) {
    let count = 0;
    while n > 1 {
      count += 1;
      n = if n % 2 == 0 { n / 2 } else { n * 3 + 1 };
    }
    count
  }
  collatz(context.n)
', { 'n': n });

SELECT range AS n,
       collatz_length(range).ok::INTEGER AS length
FROM range(1000, 1005);

The full language reference lives in the Rhai Book.

Branching that’s awkward in CASE

When CASE WHEN chains share intermediate values or thresholds, Rhai with named locals reads better:

SELECT id,
       evalexpr_rhai('
         let base   = context.qty * context.price;
         let disc   = if context.tier == "gold" { 0.20 }
                      else if context.tier == "silver" { 0.10 }
                      else { 0.0 };
         let taxed  = base * (1.0 - disc) * 1.0875;
         taxed
       ', { 'qty': qty, 'price': price, 'tier': tier }).ok::DECIMAL AS final_total
FROM orders;

Reading the error arm

A script that throws produces the error arm of the union. The query keeps running:

SELECT evalexpr_rhai('1 / 0').error;          -- 'Runtime error: ...'

-- Surface failures alongside successes
SELECT id,
       evalexpr_rhai(rule, { 'x': x }).ok    AS result,
       evalexpr_rhai(rule, { 'x': x }).error AS failure
FROM jobs;

For a single column that’s “result on success, NULL on failure,” use .ok directly — it’s NULL for the rows whose script threw.

Filtering on a script result

Booleans returned from .ok work in WHERE directly:

SELECT *
FROM employees
WHERE evalexpr_rhai('context.salary > 100000 && context.years >= 5',
                    { 'salary': salary, 'years': years }).ok = TRUE;

Constant script strings parse once and cache, so the rule isn’t re-parsed per row.

Diagnostics

-- Smoke-test that the extension is loaded and the interpreter runs
SELECT evalexpr_rhai('1 + 1').ok;             -- 2

-- Check arrays / closures land intact
SELECT evalexpr_rhai('[1, 2, 3].filter(|x| x > 1)').ok;
-- [2, 3]

If a call returns the error arm where you expected ok, the message in .error comes from Rhai itself — the Rhai Book covers the language’s error and exception model in the throw chapter.