HeliosDB Stored Procedures - Function Development Guide

HeliosDB Stored Procedures - Function Development Guide

Table of Contents

1. Introduction
2. Getting Started
3. Supported Languages
4. Creating Functions
5. Type System
6. Security and Sandboxing
7. Performance Optimization
8. Best Practices
9. Debugging
10. Examples

Introduction

HeliosDB provides a powerful polyglot stored procedure system that supports multiple 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:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE javascript
AS $$
    function add_numbers(a, b) {
        return a + b;
    }
$$;

Call the function:

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

Python Function

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

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:

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:

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:

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

Creating Functions

SQL Syntax

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

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

Type Conversion

Values are automatically converted between languages:

// 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 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:

-- 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:

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

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:

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:

-- 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:

// 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

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

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

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

3. Logging

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

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

-- 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:

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

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

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

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

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

-- 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 provides a powerful, secure, and performant way to implement business logic close to your data. By following these guidelines and best practices, you can build robust, maintainable database functions that scale with your application.

For more information:

• Security Model Documentation
• Performance Tuning Guide
• API Reference