---
title: "HeliosDB Stored Procedures - Function Development Guide"
---

# HeliosDB Stored Procedures - Function Development Guide

:::caution[Planned design: not yet available in the server]
The current `heliosdb-full` server does not run WASM or polyglot (JavaScript,
Python, Rust) stored procedures: the WASM runtime is not wired into the server.
This page describes the design for this upcoming feature, and its SQL (for
example `LANGUAGE wasm`, security profiles and syscall filters) is not accepted
by the current server. Only the
[Go SDK](/docs/full/guides/features/wasm_procedures_language_sdks/#go-sdk) is
published. See the
[WASM Procedures Quick Start](/docs/full/guides/features/wasm_procedures_quick_start/)
for what works today.
:::

## Table of Contents

1. [Introduction](#introduction)
2. [Getting Started](#getting-started)
3. [Supported Languages](#supported-languages)
4. [Creating Functions](#creating-functions)
5. [Type System](#type-system)
6. [Security and Sandboxing](#security-and-sandboxing)
7. [Performance Optimization](#performance-optimization)
8. [Best Practices](#best-practices)
9. [Debugging](#debugging)
10. [Examples](#examples)

## Introduction

HeliosDB's polyglot stored procedure system supports these programming languages:
- **JavaScript** (via QuickJS)
- **Python** (via RustPython)
- **Rust** (compiled to WebAssembly)
- **WebAssembly** (any language that compiles to WASM)

All functions run in a secure, sandboxed environment with resource limits and capability-based access control.

## Getting Started

### Basic Function Creation

Create a simple JavaScript function:

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE javascript
AS $$
    function add_numbers(a, b) {
        return a + b;
    }
$$;
```

Call the function:

```sql
SELECT add_numbers(10, 32); -- Returns 42
```

### Python Function

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION calculate_discount(price REAL, rate REAL)
RETURNS REAL
LANGUAGE python
AS $$
    def calculate_discount(price, rate):
        return price * (1 - rate)
$$;
```

### Rust/WASM Function

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION fibonacci(n INTEGER)
RETURNS INTEGER
LANGUAGE rust
AS $$
    -- Binary WASM module content (base64 encoded or binary)
$$;
```

## Supported Languages

### JavaScript

**Features:**
- ES6+ syntax support
- Async/await operations
- JSON manipulation
- Array and object methods
- Math operations

**Runtime:** QuickJS with 16MB memory limit by default

**Example:**
```javascript
function process_data(items) {
    return items
        .filter(x => x > 0)
        .map(x => x * 2)
        .reduce((sum, x) => sum + x, 0);
}
```

### Python

**Features:**
- Python 3.x syntax
- Standard library support
- List comprehensions
- Dictionary operations
- String manipulation

**Runtime:** RustPython with 32MB memory limit by default

**Example:**
```python
def process_users(users):
    return [
        {
            'id': user['id'],
            'name': user['name'].upper(),
            'active': user.get('active', False)
        }
        for user in users
        if user.get('age', 0) >= 18
    ]
```

### Rust (WebAssembly)

**Features:**
- Zero-cost abstractions
- Memory safety
- High performance
- Direct WASM compilation

**Example:**
```rust
#[no_mangle]
pub extern "C" fn factorial(n: i32) -> i32 {
    if n <= 1 {
        1
    } else {
        n * factorial(n - 1)
    }
}
```

## Creating Functions

### SQL Syntax

```sql
CREATE FUNCTION function_name(param1 type1, param2 type2, ...)
RETURNS return_type
LANGUAGE language_name
[VOLATILE | STABLE | IMMUTABLE]
[STRICT]
AS $$
    function_body
$$;
```

### Function Attributes

- **VOLATILE**: Function has side effects or returns different values with same inputs (default)
- **STABLE**: Function returns same value for same inputs within a transaction
- **IMMUTABLE**: Function always returns same value for same inputs (pure function)
- **STRICT**: Function returns NULL if any input is NULL

### Example with Attributes

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION calculate_hash(data TEXT)
RETURNS TEXT
LANGUAGE javascript
IMMUTABLE
STRICT
AS $$
    function calculate_hash(data) {
        // Deterministic hash function
        let hash = 0;
        for (let i = 0; i < data.length; i++) {
            hash = ((hash << 5) - hash) + data.charCodeAt(i);
            hash = hash & hash;
        }
        return hash.toString(16);
    }
$$;
```

## Type System

### Supported SQL Types

| SQL Type | JavaScript | Python | Rust/WASM |
|----------|-----------|---------|-----------|
| INTEGER | number | int | i32 |
| BIGINT | number | int | i64 |
| REAL | number | float | f32 |
| DOUBLE | number | float | f64 |
| BOOLEAN | boolean | bool | i32 (0/1) |
| TEXT | string | str | String |
| JSON | object | dict | - |
| ARRAY[] | Array | list | Vec<T> |

### Type Conversion

Values are automatically converted between languages:

```javascript
// JavaScript function receiving JSON
function process_order(order) {
    return {
        order_id: order.id,
        total: order.items.reduce((sum, item) => sum + item.price, 0),
        item_count: order.items.length
    };
}
```

```python
# Python function with array input
def sum_array(numbers):
    return sum(numbers)
```

## Security and Sandboxing

### Capability-Based Security

Functions run in a sandboxed environment with explicit permissions:

```sql
-- Grant database read permission
ALTER FUNCTION my_function
GRANT CAPABILITY DATABASE_READ('users');

-- Grant database write permission
ALTER FUNCTION my_function
GRANT CAPABILITY DATABASE_WRITE('audit_log');

-- Grant network access
ALTER FUNCTION my_function
GRANT CAPABILITY NETWORK_ACCESS('api.example.com');
```

### Available Capabilities

- `DATABASE_READ(pattern)` - Read from tables matching pattern
- `DATABASE_WRITE(pattern)` - Write to tables matching pattern
- `DATABASE_EXECUTE` - Execute DDL/admin operations
- `FILE_READ(pattern)` - Read files matching pattern
- `FILE_WRITE(pattern)` - Write files matching pattern
- `NETWORK_ACCESS(pattern)` - Access network hosts matching pattern
- `CALL_FUNCTION(pattern)` - Call other functions
- `ENV_ACCESS` - Access environment variables
- `TIME_ACCESS` - Access system time
- `RANDOM_ACCESS` - Generate random numbers
- `CRYPTO_ACCESS` - Cryptographic operations
- `HTTP_CLIENT` - HTTP client access

### Resource Limits

Default limits per function execution:

- **Memory**: 16MB (JavaScript), 32MB (Python)
- **CPU Time**: 5 seconds
- **Wall Time**: 10 seconds
- **Instructions**: 10 billion
- **Stack Size**: 2MB

Customize limits:

```sql
ALTER FUNCTION expensive_calculation
SET max_memory_bytes = 67108864;  -- 64MB

ALTER FUNCTION expensive_calculation
SET max_cpu_time_ms = 30000;  -- 30 seconds
```

### Strict Security Example

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION secure_function(input TEXT)
RETURNS TEXT
LANGUAGE javascript
AS $$
    function secure_function(input) {
        // Runs with minimal permissions
        // No file, network, or database access
        return input.toUpperCase();
    }
$$;

-- Apply strict security policy
ALTER FUNCTION secure_function
SET SECURITY POLICY STRICT;
```

## Performance Optimization

### Function Result Caching

Enable caching for expensive, deterministic functions:

```sql
ALTER FUNCTION expensive_calculation
SET CACHE_TTL = 300;  -- Cache results for 5 minutes

ALTER FUNCTION expensive_calculation
SET CACHE_SIZE = 1000;  -- Cache up to 1000 results
```

### JIT Optimization

Configure JIT compilation settings:

```sql
-- Fast JIT (lower latency, less optimization)
ALTER FUNCTION quick_function
SET JIT_MODE = 'low_latency';

-- Aggressive optimization (higher latency, better performance)
ALTER FUNCTION heavy_computation
SET JIT_MODE = 'fast';
```

### Parallel Execution

Execute multiple function calls in parallel:

```javascript
// Batch execution in application code
const results = await db.executeParallel([
    { function: 'process_user', args: [user1] },
    { function: 'process_user', args: [user2] },
    { function: 'process_user', args: [user3] }
]);
```

### Performance Tips

1. **Use IMMUTABLE for pure functions** - Enables better caching
2. **Minimize memory allocations** - Reuse arrays/objects
3. **Batch database operations** - Use array inputs instead of loops
4. **Enable caching for read-heavy workloads**
5. **Use appropriate data types** - INTEGER faster than TEXT for numbers
6. **Avoid deep nesting** - Flatten data structures when possible

## Best Practices

### 1. Error Handling

```javascript
function safe_divide(a, b) {
    if (b === 0) {
        throw new Error('Division by zero');
    }
    return a / b;
}
```

```python
def safe_parse_json(text):
    try:
        return heliosdb.json.parse(text)
    except Exception as e:
        heliosdb.log(f'JSON parse error: {e}')
        return None
```

### 2. Input Validation

```javascript
function process_email(email) {
    if (typeof email !== 'string' || !email.includes('@')) {
        throw new Error('Invalid email address');
    }
    return email.toLowerCase().trim();
}
```

### 3. Logging

```javascript
function debug_calculation(x, y) {
    heliosdb.log('Calculating with inputs:', x, y);
    const result = x * y + x / y;
    heliosdb.log('Result:', result);
    return result;
}
```

### 4. Transaction Handling

```javascript
function transfer_funds(from_id, to_id, amount) {
    heliosdb.transaction(function(tx) {
        const from_balance = heliosdb.query(
            'SELECT balance FROM accounts WHERE id = ?',
            [from_id]
        ).rows[0].balance;

        if (from_balance < amount) {
            tx.rollback();
            throw new Error('Insufficient funds');
        }

        heliosdb.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, from_id]
        );

        heliosdb.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, to_id]
        );

        tx.commit();
    });
}
```

### 5. Testing Functions

```sql
-- Test basic functionality
SELECT add_numbers(5, 3); -- Expected: 8

-- Test edge cases
SELECT add_numbers(0, 0); -- Expected: 0
SELECT add_numbers(-5, 5); -- Expected: 0

-- Test NULL handling (if STRICT)
SELECT add_numbers(NULL, 5); -- Expected: NULL

-- Test error handling
SELECT safe_divide(10, 0); -- Should throw error
```

## Debugging

### Debug Mode

Enable debug logging:

```sql
ALTER FUNCTION my_function
SET DEBUG = true;
```

### Stack Traces

Errors include stack traces:

```
ExecutionError: Division by zero
  at safe_divide (line 3, column 15)
  at calculate_average (line 12, column 20)
```

### Performance Profiling

```sql
SELECT * FROM heliosdb.function_stats('my_function');
```

Returns:
- Total calls
- Average execution time
- Cache hit rate
- Memory usage
- Error count

## Examples

### Example 1: Data Validation

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION validate_user_data(user JSON)
RETURNS JSON
LANGUAGE javascript
AS $$
    function validate_user_data(user) {
        const errors = [];

        if (!user.email || !user.email.includes('@')) {
            errors.push('Invalid email');
        }

        if (!user.name || user.name.length < 2) {
            errors.push('Name must be at least 2 characters');
        }

        if (user.age && (user.age < 0 || user.age > 150)) {
            errors.push('Invalid age');
        }

        return {
            valid: errors.length === 0,
            errors: errors
        };
    }
$$;
```

### Example 2: Data Transformation

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION transform_orders(orders JSON)
RETURNS JSON
LANGUAGE javascript
AS $$
    function transform_orders(orders) {
        return orders.map(order => ({
            id: order.id,
            customer: order.customer_name,
            total: order.items.reduce((sum, item) =>
                sum + item.quantity * item.price, 0
            ),
            items: order.items.length,
            status: order.shipped ? 'shipped' : 'pending'
        }));
    }
$$;
```

### Example 3: Complex Calculation

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
CREATE FUNCTION calculate_stats(numbers DOUBLE[])
RETURNS JSON
LANGUAGE python
AS $$
    def calculate_stats(numbers):
        if not numbers:
            return None

        n = len(numbers)
        mean = sum(numbers) / n
        variance = sum((x - mean) ** 2 for x in numbers) / n
        std_dev = variance ** 0.5

        sorted_nums = sorted(numbers)
        median = sorted_nums[n // 2] if n % 2 == 1 else \
                 (sorted_nums[n // 2 - 1] + sorted_nums[n // 2]) / 2

        return {
            'count': n,
            'mean': mean,
            'median': median,
            'std_dev': std_dev,
            'min': min(numbers),
            'max': max(numbers)
        }
$$;
```

### Example 4: Cross-Language Interoperability

```sql
-- Planned syntax for the upcoming WASM procedures feature; the current server does not accept it.
-- JavaScript function
CREATE FUNCTION format_currency(amount REAL)
RETURNS TEXT
LANGUAGE javascript
AS $$
    function format_currency(amount) {
        return '$' + amount.toFixed(2);
    }
$$;

-- Python function that calls JavaScript function
CREATE FUNCTION generate_invoice(items JSON)
RETURNS TEXT
LANGUAGE python
AS $$
    def generate_invoice(items):
        total = sum(item['price'] * item['quantity'] for item in items)
        formatted_total = heliosdb.call_function('format_currency', [total])

        return f"Invoice Total: {formatted_total}"
$$;
```

## Conclusion

HeliosDB's polyglot stored procedure system runs business logic close to your data in a sandbox. Follow these guidelines to keep database functions maintainable as your application grows.

For more information:
- [API Reference](https://www.heliosdb.com/docs/full/reference/api_reference/)