EvalExpr_Rhai DuckDB Extension

The EvalExpr_Rhai extension, developed by Query.Farm, enables DuckDB to evaluate expressions written in the Rhai scripting language directly within SQL queries. Rhai is a lightweight, embedded scripting language designed for safety, speed, and ease of use—making it ideal for custom logic, transformations, and dynamic expression evaluation.

DuckDB extensions are plugins that expand the core DuckDB engine with new capabilities.

Key Features

• Inline expression evaluation: Use Rhai scripts within SQL queries to compute values dynamically.
• Custom logic and transformations: Perform conditional logic, mathematical operations, string manipulation, and more using familiar scripting syntax.
• Secure and sandboxed: Execute scripts in a safe, isolated environment with no filesystem or network access by default.
• High performance: Rhai’s fast runtime ensures minimal overhead even when evaluating complex expressions.

Use Cases

• Creating derived columns using logic too complex for standard SQL expressions.
• Implementing dynamic filters, rules, or scoring systems within queries.
• Embedding custom business logic without needing to write a DuckDB extension in a lower-level language.

Getting Started

EvalExpr_Rhai is a DuckDB community extension maintained and supported by Query.Farm.

Install EvalExpr_Rhai in DuckDB by running:

INSTALL evalexpr_rhai FROM community;

Then load it with:

LOAD evalexpr_rhai;

API

The extension provides a single function:

evalexpr_rhai(VARCHAR, JSON) -> UNION['ok': JSON, 'error': VARCHAR]

The arguments in order are:

1. The Rhai expression to evaluate.
2. Any context values that will be available to the Rhai expression by accessing a variable called context.

The return value is a union type. The union type is very similar to the Result type from Rust.

If the Rhai expression was successfully evaluated the JSON result of the expression will be returned in the ok element of the union. If there was an error evaluating the expression it will be returned in the error element of the expression.

Love ❤️ this DuckDB extension? You’ll Love This.

Get the best from Query.Farm — smart tips, powerful tools, and project updates sent directly to your inbox, but only when we’ve got something great to share.

Examples

-- Calculate the value of an expression
SELECT evalexpr_rhai('40+2');

┌───────────────────────────────┐
│     evalexpr_rhai('40+2')     │
│ union(ok json, error varchar) │
├───────────────────────────────┤
│ 42                            │
└───────────────────────────────┘

-- Expression's return type is a union of
--
-- ok JSON - the result of the expression as a JSON value
-- error VARCHAR - the error if any from evaluating the expression

-- Demonstrate returning a JSON object from Rhai
SELECT evalexpr_rhai('#{"apple": 5, "price": 3.52}').ok;

┌────────────────────────────────────────────────────┐
│ (evalexpr_rhai('#{"apple": 5, "price": 3.52}')).ok │
│                        json                        │
├────────────────────────────────────────────────────┤
│ {"apple":5,"price":3.52}                           │
└────────────────────────────────────────────────────┘

-- Demonstrate what happens when the expression
-- cannot be parsed, an error is returned.
SELECT evalexpr_rhai('#{"apple: 5}').error;

┌────────────────────────────────────────────────────┐
│       (evalexpr_rhai('#{"apple: 5}')).error        │
│                      varchar                       │
├────────────────────────────────────────────────────┤
│ Open string is not terminated (line 1, position 3) │
└────────────────────────────────────────────────────┘

-- Either .ok or .error will be populated but never both.

-- When evaluating and expression you can also pass in
-- variables via context.
CREATE TABLE employees (name text, state text, zip integer);
INSERT INTO employees values
  ('Jane', 'FL', 33139),
  ('John', 'NJ', 08520);

-- Pass the row from the employees table in as "context.row"
SELECT evalexpr_rhai(
  '
  context.row.name + " is in " + context.row.state
  ',
  {
    row: employees
  }) AS result from employees;

┌───────────────────────────────┐
│            result             │
│ union(ok json, error varchar) │
├───────────────────────────────┤
│ "Jane is in FL"               │
│ "John is in NJ"               │
└───────────────────────────────┘

-- To demonstrate how Rhai can be used to implement
-- a function in DuckDB, the next example creates
-- a macro function that calls a Rhai function
-- to calculate the Collatz sequence length.

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

-- Use the previously defined macro.
SELECT
  collatz_series_length(range).ok::bigint AS sequence_length,
  range AS starting_value
FROM
  range(10000, 20000)
ORDER BY 1 DESC limit 10;

┌─────────────────┬────────────────┐
│ sequence_length │ starting_value │
│      int64      │     int64      │
├─────────────────┼────────────────┤
│             278 │          17647 │
│             278 │          17673 │
│             275 │          13255 │
│             273 │          19593 │
│             273 │          19883 │
│             270 │          14695 │
│             270 │          15039 │
│             267 │          10971 │
│             265 │          16457 │
│             265 │          16777 │
├─────────────────┴────────────────┤
│ 10 rows                2 columns │
└──────────────────────────────────┘