🧮

Rhai (evalexpr_rhai)

Run [Rhai](https://rhai.rs/) scripts inside DuckDB SQL. Rhai is a small, sandboxed, Rust-native scripting language with dynamic typing — best when SQL alone can't express the logic but a full UDF would be overkill. Constant scripts compile once and cache; per-row context goes in via a JSON object.

Browse Contents

Install

-- Install the extension
INSTALL evalexpr_rhai FROM community;

-- Load it into your session
LOAD evalexpr_rhai;

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

-- Evaluate per-row with a context object
SELECT name,
       evalexpr_rhai('context.salary > 100000', { 'salary': salary }).ok AS high_earner
FROM employees;

-- Full script with a defined function
SELECT evalexpr_rhai('fn sq(x) { x * x }  sq(context.n)', { 'n': 7 }).ok;

Technical Overview

Why Embed Rhai in DuckDB?

Run row-by-row scripted transforms when SQL alone can't express the logic, with bounded execution. Rhai is a small, Rust-native scripting language (source, book) — sandboxed by default, no host filesystem / process / network access from inside scripts. This extension exposes it as a single SQL function: evalexpr_rhai.

🧮 What this extension is for

Use Rhai when SQL is too constrained for the expression you need to write, but a real DuckDB UDF or a Python pipeline is too heavy. The script is text — store it in a column, edit it without redeploying, evaluate it per row.

  • • Dynamic rules from a column: Membership rules, scoring rules, eligibility predicates — store them as Rhai source in a config table and run them with evalexpr_rhai at query time. Admins edit a row, the next query picks up the new rule.
  • • Branching that's awkward in CASE: Multi-step conditional logic with intermediate variables, early returns, or computed thresholds — clearer in a few lines of Rhai than a tower of nested CASE WHEN expressions.
  • • Custom math with named helpers: Define a function with fn, then call it. Useful for one-off calculations that don't deserve their own DuckDB scalar function but that you want to read top-to-bottom in source.
  • • Sandboxed by construction: Rhai scripts cannot open files, spawn processes, or make network calls. The host program decides what's exposed — and this extension exposes only the context JSON object. Safe to run text supplied by users or stored in tables.

⚙️ How it works

evalexpr_rhai parses the script string, evaluates it inside an embedded Rhai interpreter, and serializes the result back to DuckDB as a UNION(ok JSON, "error" VARCHAR). Constant script strings are parsed once and the resulting AST is cached, so the same script in a WHERE clause doesn't re-parse per row.

  • • Single function surface: evalexpr_rhai(expression) and evalexpr_rhai(expression, context) — one name, two overloads. Read .ok on success, .error on failure. There is no separate evalexpr_rhai_safe / evalexpr_rhai_strict split; the union return type is the only error-handling shape.
  • • Per-row context as JSON: The second argument is a JSON object DuckDB builds from row columns ({ 'salary': salary }). Inside the script it appears as context.<field>. Anything DuckDB can encode to JSON crosses the boundary — numbers, strings, booleans, arrays, nested objects.
  • • Compile-and-cache for constant scripts: When the script string is a literal (or otherwise constant within a query), the parser runs once. Per-row cost after warmup is interpreter execution only. When the script comes from a column (the rules-from-a-column pattern), each distinct script string parses on first encounter.
  • • Errors don't kill the query: A script that throws — runtime error, type error, divide-by-zero — produces the error arm of the union for that row. The query keeps running. Read the failure arm with .error if you need to surface it.

🛡️ Performance and limits

Rhai is an interpreter running per row. Be honest about what that means.

  • • Slower than vectorized SQL: Row-by-row interpretation is fundamentally slower than DuckDB's vectorized execution. Expect interpreter execution to dominate at high row counts — for hot OLAP paths over millions of rows, prefer native SQL or a real DuckDB extension function.
  • • Best fit: moderate volumes: Where flexibility wins over peak throughput — config-driven rules, ad-hoc transforms, audits, exploratory analysis, batches in the thousands-to-low-millions of rows. Beyond that, profile.
  • • Sandbox is intentional: Rhai has no module loading, no file I/O, no networking. That's a feature here — you can evaluate untrusted script text from a column without granting it access to anything outside the context. If you need those capabilities, this is the wrong tool.
  • • Language is constrained: Rhai is smaller than full JavaScript. No package ecosystem, no async / await, no regex unless you reach for Rhai-specific helpers. The full feature set is documented in the Rhai Book.

↔️ Rhai vs. QuickJS — pick by need

evalexpr_rhai and the sibling quickjs extension occupy the same slot — embedded scripting in DuckDB SQL — but make different trade-offs. They can coexist; pick per use case.

  • • Pick Rhai when safety matters more than expressiveness: Sandboxed by default, no module loading, no host I/O, smaller language surface. The right choice for evaluating script text supplied by users or stored in a config table — there's nothing inside Rhai to reach the host with.
  • • Pick QuickJS when you need full JavaScript: Full ECMAScript semantics, regex, JSON ergonomics, the JavaScript idioms developers already know. The right choice when you're porting expressions from a JavaScript codebase or need a richer standard library — at the cost of a larger attack surface and a heavier interpreter.
  • • Both are row-by-row: Neither vectorizes. The choice is about language and sandboxing, not throughput. If raw throughput dominates, push the logic into SQL or a compiled UDF instead.

🎯 Common Use Cases

Rules stored as data

Membership / eligibility / scoring rules live in a table as Rhai source. Each query evaluates the rule against the row's columns via context. Admins edit text; nothing redeploys.

Branching too tangled for CASE

When CASE WHEN ... THEN ... WHEN ... THEN ... becomes hard to read or shares intermediate variables, a few lines of Rhai with named locals reads better and is easier to evolve.

Custom math helpers

Define fn helpers inside the script, call them on row data. Cheap one-off calculations that you don't want to register as a permanent DuckDB function.

Safe evaluation of user input

Because Rhai is sandboxed, evaluating script text submitted by users (with the row context they're allowed to see) is straightforward. The sibling quickjs extension is more permissive; choose Rhai when that matters.

Deep Dive

Technical Details

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.”

Best fit, scoped honestly

Rhai runs row by row — it does not vectorize. Per-call cost after parse-cache warmup is interpreter execution only, but it’s still slower than native DuckDB SQL on the hot path. The right slot is config-driven rules, branching that’s awkward in CASE, custom math, and ad-hoc transforms — moderate volumes where flexibility wins over peak throughput.

For sustained OLAP scans over millions of rows, push the logic into SQL or a compiled DuckDB extension function. For full JavaScript expressiveness instead of Rhai’s smaller surface, see the sibling quickjs extension.

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.

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.

API Reference

Function Documentation

Detailed documentation for each function including signatures, parameters, and examples.

evalexpr_rhai

Scalar Function Execute
Signature
evalexpr_rhai(expression: VARCHAR) → UNION(ok JSON, "error" VARCHAR)
Parameters (Positional)
Parameter Type Mode Description
expression VARCHAR Positional
Returns
Description

Evaluate a Rhai expression or full script and return the result as a DuckDB UNION of ok (the JSON-encoded result on success) or error (the error message on failure). With one argument, the script runs with no per-row context. With two arguments, the second is a JSON object exposed inside the script as context.<field> — the standard pattern for per-row evaluation. Constant expressions are parsed and cached the first time they're seen, so repeat evaluations skip the parse step.

Examples
1

Simple expression

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

Per-row evaluation with context

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

Full function definition inside the script

SELECT evalexpr_rhai('fn sq(x) { x * x }  sq(context.n)',
                     { 'n': 7 }).ok;
-- 49
4

Read the error arm on failure (script keeps the row)

SELECT evalexpr_rhai('1 / 0').error;
-- 'Runtime error: ...'
Back to Extension Contents

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

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.

Back to Top

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Software License MIT
Pricing Free
Written In Rust
Source Available Yes
View on GitHub
Usage
13,603+
loads in last 30 days

Platforms

Linux
Linux (musl)
macOS
Windows
WASM
Supported platform architectures
Linux: x86_64, aarch64
Linux (musl): x86_64
macOS: Apple Silicon, Intel
Windows: x86_64
WASM: eh, mvp, threads
Compiled binary sizes
Platform Architecture Size
Linux aarch64 5.26 MB
Linux x86_64 5.76 MB
Linux (musl) x86_64 5.00 MB
macOS Apple Silicon 3.47 MB
macOS Intel 3.91 MB
Windows x86_64 9.21 MB
WASM eh 704.8 KB
WASM mvp 701.1 KB
WASM threads 701.5 KB

Gzipped download size from the DuckDB community-extensions registry.

DuckDB Versions

Release calendar
Supported
v1.4.4 v1.5.2

Rhai scripting in SQL

Install evalexpr_rhai to evaluate Rhai expressions and full scripts from inside SQL — column-driven branching, custom math, dynamic rules pulled from the database, all in a sandboxed interpreter.

Install Now View Other Extensions