User-Defined Functions

OxiBase supports user-defined functions written in multiple scripting languages through pluggable backends. By default, functions can be written in Rhai (a lightweight, fast scripting language), with optional support for JavaScript/TypeScript (via Boa) and Python (via RustPython). This allows you to extend the database with custom logic while choosing the right tool for each use case.

Overview

User-defined functions (UDFs) enable you to create custom scalar functions that can be called from SQL queries. Functions run in secure, isolated environments with controlled access to system resources.

Scripting Backends

OxiBase supports multiple scripting backends, each optimized for different use cases:

Rhai Backend (Default)

  • Language: LANGUAGE RHAI
  • Description: Lightweight, fast scripting language written in Rust
  • Performance: Excellent performance for simple calculations and logic
  • Availability: Always enabled
  • Use Case: General-purpose scripting, high-performance requirements

Boa Backend (Optional)

  • Language: LANGUAGE BOA or LANGUAGE JAVASCRIPT
  • Description: Full JavaScript/TypeScript runtime with modern ES features
  • Performance: Good performance with rich ecosystem support
  • Availability: Enable with --features js
  • Use Case: Complex logic, JSON processing, date manipulation

Python Backend (Optional)

  • Language: LANGUAGE PYTHON
  • Description: Python scripting with access to standard library
  • Performance: Good performance with extensive libraries
  • Availability: Enable with --features python
  • Use Case: Scientific computing, data processing, ML/AI integration

Enabling Optional Backends

To use JavaScript/TypeScript or Python functions, enable the corresponding feature flags:

# Enable JavaScript/TypeScript support
cargo build --features js

# Enable Python support
cargo build --features python

# Enable both
cargo build --features js,python

Functions vs Stored Procedures

OxiBase currently supports user-defined functions but not stored procedures. Understanding the difference is important for choosing the right tool for your database logic.

Comparison at a Glance

Feature Function (CREATE FUNCTION) Procedure (CREATE PROCEDURE)
Return Value Must return exactly one value (scalar). Can return zero, one, or multiple values.
Usage in SQL Can be used in SELECT, WHERE, and JOIN. Must be called using EXECUTE or CALL.
Data Modification Cannot perform DML (Insert, Update, Delete). Can perform any DML operations.
Transactions No transaction control allowed. Supports COMMIT, ROLLBACK, and SAVEPOINT.
Parameters Generally only Input parameters. Supports Input, Output, and In-Out.
Error Handling Limited (JavaScript exceptions only). Full support for error handling constructs.

Key Differences

Integration with Queries

Functions are “pluggable” into your SQL statements and can be used just like built-in functions:

-- Function usage in queries
SELECT calculate_tax(price) FROM products;
SELECT * FROM users WHERE is_adult(age);

Procedures cannot be used directly in queries and must be called separately:

-- Procedure usage (when implemented)
EXECUTE update_inventory;
CALL process_monthly_report;

Side Effects and DML

Functions are restricted to be “side-effect free” and cannot change database state:

-- ✅ Valid function - read-only calculation
CREATE FUNCTION calculate_tax(price INTEGER) RETURNS INTEGER
LANGUAGE BOA AS 'return price * 0.08;';

Procedures are designed for actions that modify data:

-- ❌ Invalid in functions - would be valid in procedures (when implemented)
-- CREATE PROCEDURE update_prices()
-- AS BEGIN
--     UPDATE products SET price = price * 1.1;
-- END;

When to Use Functions vs Procedures

Use Functions when:

  • You need to perform calculations and use results in queries
  • The logic is simple and read-only
  • You want to encapsulate reusable business logic
  • Examples: Currency conversion, string formatting, age calculation

Use Procedures when:

  • You need to perform write operations (INSERT/UPDATE/DELETE)
  • You need complex multi-step logic with error handling
  • You need transaction control
  • You need to return multiple result sets
  • Examples: Monthly payroll processing, customer registration, data cleanup

Note: Stored procedures are planned for future implementation in OxiBase but are not currently available.

Creating User-Defined Functions

Use the CREATE FUNCTION statement to define a user-defined function:

CREATE FUNCTION function_name(param1 TYPE1, param2 TYPE2, ...)
RETURNS return_type
LANGUAGE BOA AS 'JavaScript code here';

Parameters

  • function_name: The name of the function (must be unique)
  • param1, param2, ...: Parameter names and their data types
  • return_type: The data type of the return value
  • LANGUAGE RHAI|DENO|PYTHON: Specifies the scripting backend to use
  • AS 'code': The JavaScript/TypeScript code that implements the function

Supported Return Data Types

User-defined functions can return values of these scalar data types:

Data Type Description JavaScript Example
INTEGER 64-bit signed integers return 42;
FLOAT 64-bit floating-point numbers return 3.14159;
TEXT UTF-8 text strings return "Hello World";
BOOLEAN True/false values return arguments[0] > 10;
TIMESTAMP Date and time values return new Date().toISOString();
JSON JSON documents and objects return {name: "John", age: 30};

Functions must return exactly one value and declare their return type in the CREATE FUNCTION statement. The JavaScript runtime automatically converts return values to the appropriate OxiBase type.

Note: OxiBase currently only supports scalar user-defined functions. Table-valued functions and stored procedures are planned for future releases.

Function Implementation

Argument Access

All backends now support named parameters. Arguments are accessed by their parameter names:

Rhai Backend

Parameters are bound to variables with their declared names:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'a + b';

Boa Backend

Parameters are set as global variables with their declared names:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE BOA AS 'return a + b;';

Python Backend

Parameters are set as local variables with their declared names:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return a + b';

Python functions support the same argument and return types as JavaScript:

  • INTEGER: Python int
  • FLOAT: Python float
  • TEXT: Python str
  • BOOLEAN: Python bool
  • JSON: Python objects (dict/list parsed from JSON string)

Example functions:

-- String manipulation
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS 'return f"Hello, {name}!"';

-- Mathematical operations
CREATE FUNCTION power(base INTEGER, exp INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return base ** exp';

-- JSON processing
CREATE FUNCTION extract_field(json_data JSON, field TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS '''
import json
data = json.loads(json_data)
return data.get(field, "")
''';

Return Values

Functions return values using backend-specific syntax:

Rhai Backend 

-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE RHAI AS '"Hello, " + name + "!"';

-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'x * x';

-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE RHAI AS 'n % 2 == 0';

Boa Backend 

-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE BOA AS 'return `Hello, ${name}!`;';

-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE BOA AS 'return x * x;';

-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE BOA AS 'return n % 2 === 0;';

-- Return JSON
CREATE FUNCTION create_person(name TEXT, age INTEGER)
RETURNS JSON
LANGUAGE BOA AS 'return { name: name, age: age };';

Python Backend 

-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS 'return f"Hello, {name}!"';

-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return x * x';

-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE PYTHON AS 'return n % 2 == 0';

-- Return JSON
CREATE FUNCTION create_person(name TEXT, age INTEGER)
RETURNS JSON
LANGUAGE PYTHON AS '''
import json
return json.dumps({"name": name, "age": age})
''';

Note: Python functions support both return statements (recommended) and direct result = assignments for backward compatibility. If no value is returned, the function returns NULL.

JavaScript Features

User-defined functions have access to standard JavaScript features:

  • All ECMAScript built-ins (Math, Date, etc.)
  • Arrow functions and modern syntax
  • Template literals
  • Array and object methods
  • JSON parsing and serialization
CREATE FUNCTION format_currency(amount INTEGER, currency TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
    const formatted = new Intl.NumberFormat("en-US", {
        style: "currency",
        currency: currency
    }).format(amount / 100);
    return formatted;
';

Using User-Defined Functions

Once created, user-defined functions can be used in any SQL context where scalar functions are allowed:

-- Simple usage
SELECT greet('World') as greeting;

-- In expressions
SELECT id, square(price) as price_squared
FROM products;

-- In WHERE clauses
SELECT * FROM users
WHERE is_even(age);

-- In complex queries
SELECT
    name,
    format_currency(salary, 'USD') as formatted_salary
FROM employees;

Security Considerations

All backends execute in secure sandboxes with controlled access:

Rhai Backend

  • No file system access - Pure computation only
  • No network access - Cannot make HTTP requests
  • Memory isolation - Each call runs in isolated context
  • Limited runtime - Execution time limits prevent abuse

Boa Backend

  • No file system access - Cannot read or write files
  • No network access - Cannot make HTTP requests or open sockets
  • No system access - Cannot execute system commands or access environment variables
  • Limited runtime - Functions have execution time limits to prevent abuse
  • Memory isolation - Each function call runs in its own JavaScript context

Python Backend

  • No file system access - Cannot read or write files
  • No network access - Cannot make HTTP requests
  • Limited system access - Cannot execute system commands
  • Memory isolation - Each call runs in isolated context
  • Limited runtime - Execution time limits prevent abuse

Performance Characteristics

Performance varies by backend:

Rhai Backend

  • Runtime Creation: Minimal overhead (microseconds)
  • Execution Speed: Fastest for simple calculations
  • Memory Usage: Low memory footprint
  • Best For: High-frequency calls, simple logic

Boa Backend

  • Runtime Creation: Moderate overhead (~milliseconds)
  • Execution Speed: Good for complex logic
  • Memory Usage: Higher memory usage
  • Best For: Complex algorithms, JSON processing

Python Backend

  • Runtime Creation: Moderate overhead (~milliseconds)
  • Execution Speed: Good for numerical computing
  • Memory Usage: Moderate memory usage
  • Best For: Scientific computing, data processing

General Guidelines

  • Rhai is recommended for most use cases due to its speed and low overhead
  • Each function call creates a new runtime instance for isolation
  • Consider caching results at the application level for expensive operations
  • Runtime creation overhead is typically acceptable for OLTP workloads

Examples

Basic Arithmetic (Rhai) 

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'a + b';

CREATE FUNCTION calculate_area(width INTEGER, height INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'width * height';

SELECT add_numbers(5, 3) as sum, calculate_area(10, 20) as area;
-- Result: sum = 8, area = 200

String Processing (Rhai) 

CREATE FUNCTION slugify(text TEXT)
RETURNS TEXT
LANGUAGE RHAI AS '
    text
        .to_lower()
        .replace(re("[^a-z0-9]+"), "-")
        .replace(re("^-+|-+$"), "")
';

SELECT slugify('Hello, World! How are you?') as slug;
-- Result: "hello-world-how-are-you"

String Processing (Boa) 

CREATE FUNCTION slugify(text TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
    return text
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, "-")
        .replace(/^-+|-+$/g, "");
';

SELECT slugify('Hello, World! How are you?') as slug;
-- Result: "hello-world-how-are-you"

Date Calculations (Rhai) 

CREATE FUNCTION days_until(date TEXT)
RETURNS INTEGER
LANGUAGE RHAI AS '
    // Simple date difference calculation
    // Note: Rhai has limited date support, consider Boa for complex date operations
    30  // Placeholder - use Boa for real date calculations
';

// For complex date operations, use Boa:
CREATE FUNCTION days_until(date TIMESTAMP)
RETURNS INTEGER
LANGUAGE BOA AS '
    const target = new Date(date);
    const now = new Date();
    const diff = target - now;
    return Math.ceil(diff / (1000 * 60 * 60 * 24));
';

SELECT days_until('2024-12-31') as days_remaining;

JSON Processing (Boa) 

CREATE FUNCTION extract_field(json_doc JSON, field TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
    const doc = JSON.parse(json_doc);
    return doc[field] || null;
';

SELECT extract_field(metadata, 'version') as version
FROM documents;

Mathematical Functions (Rhai) 

CREATE FUNCTION fibonacci(n INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS '
    if n <= 1 { n }
    else {
        let a = 0;
        let b = 1;
        for i in 2..=n {
            let temp = a + b;
            a = b;
            b = temp;
        }
        b
    }
';

CREATE FUNCTION factorial(n INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS '
    if n <= 1 { 1 }
    else { n * factorial(n - 1) }
';

SELECT fibonacci(10) as fib, factorial(5) as fact;
-- Result: fib = 55, fact = 120

Data Validation (Boa) 

CREATE FUNCTION validate_email(email TEXT)
RETURNS BOOLEAN
LANGUAGE BOA AS '
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return emailRegex.test(email);
';

CREATE FUNCTION is_strong_password(password TEXT)
RETURNS BOOLEAN
LANGUAGE BOA AS '
    // At least 8 characters, 1 uppercase, 1 lowercase, 1 number
    const strongRegex = /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)[a-zA-Z\d]{8,}$/;
    return strongRegex.test(password);
';

SELECT validate_email('user@example.com') as valid_email,
       is_strong_password('MyPass123') as strong_password;

Error Handling

Functions that throw exceptions will cause the query to fail:

-- Rhai
CREATE FUNCTION safe_divide(a INTEGER, b INTEGER)
RETURNS FLOAT
LANGUAGE RHAI AS '
    if b == 0 {
        throw "Division by zero";
    }
    a / b
';

-- Boa
CREATE FUNCTION safe_divide(a INTEGER, b INTEGER)
RETURNS FLOAT
LANGUAGE BOA AS '
    if (b === 0) {
        throw new Error("Division by zero");
    }
    return a / b;
';

Backend-Specific Considerations

Rhai Backend

  • Syntax: Simple, Rust-like syntax with automatic type inference
  • Features: Basic arithmetic, string operations, conditionals, loops
  • Limitations: Limited standard library, no built-in JSON parsing
  • Performance: Excellent for numerical computations and simple logic

Boa Backend

  • Syntax: Full JavaScript/TypeScript with modern ES features
  • Features: Rich standard library, JSON support, date/time operations
  • Limitations: Higher memory usage and startup time
  • Performance: Good for complex algorithms and data processing

Python Backend

  • Syntax: Standard Python syntax
  • Features: Extensive standard library, good for numerical computing
  • Limitations: Moderate startup time, higher memory usage
  • Performance: Good for algorithms requiring complex data structures

Limitations

  • Only scalar functions are supported (not aggregate or window functions)
  • Functions cannot access database state directly
  • Maximum execution time per function call is limited
  • Memory usage per function is bounded
  • No access to external modules or packages (backend-specific limitations apply)
  • Rhai has a smaller standard library compared to JavaScript/Python

Best Practices

  1. Choose the right backend:
    • Use Rhai for simple, high-performance calculations
    • Use Boa for complex logic, JSON processing, or date operations
    • Use Python for scientific computing or when you need extensive libraries

  2. Keep functions simple - Complex logic is better handled in application code

  3. Validate inputs - Functions should handle edge cases and invalid inputs gracefully

  4. Use appropriate return types - Match the function’s purpose with the correct data type

  5. Test thoroughly - User-defined functions should be well-tested across all backends

  6. Consider performance - Rhai is fastest, but choose based on your specific needs

  7. Document your functions - Use meaningful names and consider adding comments

  8. Handle errors appropriately - Different backends have different error handling patterns

UDF Testing Strategy

User-defined functions (UDFs) require comprehensive testing to ensure reliability across different backends and environments. The testing strategy covers multiple layers and scenarios.

Test Categories

1. Unit Tests per Backend

Each backend has dedicated test suites that validate:

  • Function Creation: Syntax validation and compilation
  • Argument Handling: Type conversion and parameter passing
  • Return Values: Correct output types and values
  • Error Handling: Runtime errors and exception propagation
  • Type Safety: Mixed-type operations and conversions

Examples:

// Rhai backend tests
#[test]
fn test_rhai_string_manipulation() {
    let db = Database::open("memory://test").unwrap();
    db.execute(r#"
        CREATE FUNCTION greet(name TEXT) RETURNS TEXT
        LANGUAGE RHAI AS '`Hello, ${name}!`'
    "#, ()).unwrap();
    // ... validation
}

// Python backend tests
#[test]
fn test_python_runtime_error() {
    let db = Database::open("memory://test").unwrap();
    db.execute(r#"
        CREATE FUNCTION error_func() RETURNS INTEGER
        LANGUAGE PYTHON AS 'return 1 / 0'
    "#, ()).unwrap();
    // ... error validation
}

2. Cross-Backend Integration Tests

Tests that verify consistent behavior across all enabled backends:

  • Function Registration: Multi-backend function loading
  • Execution Consistency: Same inputs produce equivalent outputs
  • Error Message Alignment: Standardized error formats
  • Performance Validation: Backend-specific performance expectations

3. End-to-End SQL Tests

Complete SQL workflow testing including:

  • DDL Operations: CREATE/DROP FUNCTION statements
  • Query Integration: UDFs in SELECT, WHERE, and aggregate contexts
  • Transaction Safety: UDF behavior within transactions
  • Concurrency: Multi-user function execution

4. Environment Consistency Tests

Validates UDF reliability across different environments:

  • Feature Flags: Tests with different cargo feature combinations
  • Platform Compatibility: Cross-platform execution consistency
  • Dependency Isolation: Sandboxing and security boundary validation

Backend-Specific Considerations

Rhai Backend

  • String Concatenation: Requires explicit .to_string() for non-string types
  • Type Coercion: Limited implicit conversions
  • Performance: Fastest for simple operations
  • Error Format: “Rhai execution error: {message}”

Python Backend

  • Library Access: Extensive standard library and scientific computing
  • Type Flexibility: Dynamic typing with runtime conversion
  • Error Format: “Python execution error: {exception_string}”
  • Security: Isolated execution environment

Boa Backend

  • JavaScript/TypeScript: Modern JS runtime with ES modules
  • JSON Processing: Native JSON support
  • Date Operations: Full Date API
  • Error Format: “Function execution failed: {error}”

Testing Infrastructure

Test Organization 

tests/
├── functions/
│   ├── backends_test.rs      # Backend registration and loading
│   └── ...
├── rhai_scripting_test.rs    # Rhai-specific functionality
├── python_scripting_test.rs  # Python-specific functionality
├── multi_backend_integration_test.rs  # Cross-backend scenarios
└── ddl_function_test.rs      # DDL operations

CI/CD Integration

  • Matrix Testing: All backends tested with --all-features
  • Feature Isolation: Individual backend testing with specific features
  • Performance Regression: Execution time monitoring
  • Memory Safety: Leak detection and resource cleanup

Test Execution 

# Run all tests with all backends
make test-all

# Run specific backend tests
cargo test --test rhai_scripting_test --features rhai
cargo test --test python_scripting_test --features python

# Run integration tests
cargo test --test multi_backend_integration_test --all-features

Best Practices for UDF Testing

  1. Test All Backends: Ensure UDFs work across Rhai, Python, and Boa
  2. Validate Error Messages: Check error format consistency
  3. Performance Benchmarking: Compare execution times across backends
  4. Type Edge Cases: Test with null values, type mismatches, and boundaries
  5. Concurrency Testing: Multiple UDF executions in parallel
  6. Memory Leak Detection: Ensure proper resource cleanup
  7. Cross-Platform Validation: Test on different operating systems
  8. Security Boundary Testing: Validate sandbox isolation

Common Testing Patterns

Error Testing 

let result: Result<i64, _> = db.query_one("SELECT error_func()", ());
assert!(result.is_err());
let err_msg = result.unwrap_err().to_string();
// Check for backend-specific error patterns
assert!(err_msg.contains("execution error"));

Type Conversion Testing 

// Test string concatenation with mixed types
db.execute(r#"
    CREATE FUNCTION format_person(name TEXT, age INTEGER)
    RETURNS TEXT LANGUAGE RHAI AS 'name + " is " + age.to_string() + " years old"'
"#, ()).unwrap();

Performance Validation 

let start = std::time::Instant::now();
// Execute UDF multiple times
for _ in 0..1000 {
    db.query_one("SELECT fast_udf(42)", ()).unwrap();
}
let duration = start.elapsed();
// Assert performance expectations
assert!(duration < std::time::Duration::from_millis(100));

Dropping Functions

User-defined functions can be dropped using the DROP FUNCTION statement:

DROP FUNCTION function_name;

Parameters

  • function_name: The name of the function to drop
  • IF EXISTS: Optional clause that prevents an error if the function doesn’t exist

Examples 

-- Drop a function
DROP FUNCTION calculate_total;

-- Drop a function only if it exists
DROP FUNCTION IF EXISTS old_function;

-- Drop a schema-qualified function
DROP FUNCTION myschema.add_numbers;

Behavior

  • Dropping a function removes it from the database permanently
  • The function becomes unavailable for new queries immediately
  • Existing queries using the function may fail if the function is dropped during execution
  • Functions are dropped from both the system catalog and the runtime registry

Copyright © 2025-2026 Oxibase Contributors. Gabriel Maeztu.