Database Module

The database module provides secure access to execute SQL queries against databases configured in the system. It supports parameterized queries to prevent SQL injection and enforces permission-based access control.

Functions

database.query(id_or_handle, sql_string, bindings)

Executes a SQL query against the specified database with parameter binding support.

Parameters:

• id_or_handle (string): Database UUID or handle identifier
• sql_string (string): The SQL query to execute (supports placeholders)
• bindings (table): Array of parameter bindings for the SQL query

Returns:

• [success, result] where:
  • success (boolean): true if query succeeded, false otherwise
  • result (mixed): Query results on success (array of rows for SELECT), error message on failure

Basic Examples:

-- Simple SELECT query
local success, users = database.query(
    "my-database",
    "SELECT * FROM users WHERE active = ?",
    {true}
)

if success then
    print("Found " .. #users .. " active users")
    for _, user in ipairs(users) do
        print("User: " .. user.name .. " (" .. user.email .. ")")
    end
else
    print("Query failed: " .. users)
end

-- INSERT with returning clause
local success, result = database.query(
    "my-database",
    "INSERT INTO orders (customer_id, total, status) VALUES (?, ?, ?) RETURNING id",
    {customer_id, 99.99, "pending"}
)

if success and #result > 0 then
    print("Created order with ID: " .. result[1].id)
end

-- UPDATE with multiple bindings
local success, result = database.query(
    "my-database",
    "UPDATE inventory SET quantity = quantity - ? WHERE product_id = ? AND quantity >= ?",
    {order_quantity, product_id, order_quantity}
)

if success then
    print("Inventory updated successfully")
end

Query Types and Return Values

SELECT Queries:

-- Returns array of row objects
local success, rows = database.query(
    "analytics-db",
    "SELECT id, name, created_at FROM products WHERE category_id = ? ORDER BY name",
    {category_id}
)

if success then
    -- rows is an array of tables
    -- Each table has keys matching column names
    for i, row in ipairs(rows) do
        print(string.format("Product %d: %s (ID: %d)", i, row.name, row.id))
    end
end

INSERT Queries:

-- Standard INSERT (returns empty result on success)
local success, result = database.query(
    "main-db",
    "INSERT INTO logs (level, message, created_at) VALUES (?, ?, NOW())",
    {"INFO", "User logged in"}
)

-- INSERT with RETURNING clause
local success, result = database.query(
    "main-db",
    "INSERT INTO users (email, name) VALUES (?, ?) RETURNING id, created_at",
    {"john@example.com", "John Doe"}
)

if success and #result > 0 then
    local new_user = result[1]
    print("Created user ID: " .. new_user.id)
    print("Created at: " .. new_user.created_at)
end

UPDATE/DELETE Queries:

-- UPDATE returns empty result on success
local success, result = database.query(
    "main-db",
    "UPDATE users SET last_login = NOW() WHERE id = ?",
    {user_id}
)

-- DELETE with affected rows check (database-specific)
local success, result = database.query(
    "main-db",
    "DELETE FROM sessions WHERE expires_at < NOW()",
    {}
)

Advanced Features

Transactions (Within Single Query)

-- Use database-specific transaction syntax
local success, result = database.query(
    "main-db",
    [[
        BEGIN;
        UPDATE accounts SET balance = balance - ? WHERE id = ?;
        UPDATE accounts SET balance = balance + ? WHERE id = ?;
        COMMIT;
    ]],
    {amount, from_account, amount, to_account}
)

Batch Operations

-- Insert multiple records efficiently
function batch_insert_users(users)
    local placeholders = {}
    local bindings = {}

    for i, user in ipairs(users) do
        table.insert(placeholders, "(?, ?, ?)")
        table.insert(bindings, user.name)
        table.insert(bindings, user.email)
        table.insert(bindings, user.role)
    end

    local sql = "INSERT INTO users (name, email, role) VALUES " ..
                table.concat(placeholders, ", ")

    return database.query("main-db", sql, bindings)
end

-- Usage
local users = {
    {name = "Alice", email = "alice@example.com", role = "admin"},
    {name = "Bob", email = "bob@example.com", role = "user"},
    {name = "Charlie", email = "charlie@example.com", role = "user"}
}

local success, result = batch_insert_users(users)

Complex Queries

-- Join queries with multiple parameters
local success, report = database.query(
    "analytics-db",
    [[
        SELECT
            o.id as order_id,
            o.total,
            c.name as customer_name,
            COUNT(oi.id) as item_count
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.created_at >= ?
          AND o.created_at < ?
          AND o.status IN (?, ?, ?)
        GROUP BY o.id, o.total, c.name
        ORDER BY o.created_at DESC
        LIMIT ?
    ]],
    {start_date, end_date, "completed", "shipped", "delivered", limit}
)

if success then
    for _, row in ipairs(report) do
        print(string.format("Order %d: %s - $%.2f (%d items)",
            row.order_id, row.customer_name, row.total, row.item_count))
    end
end

Error Handling

function safe_query(db_handle, sql, bindings)
    local success, result = database.query(db_handle, sql, bindings or {})

    if not success then
        -- Log error for debugging
        print("Database error: " .. result)

        -- Check for specific error types
        if string.match(result, "constraint") then
            return false, "Data validation failed"
        elseif string.match(result, "connection") then
            return false, "Database connection error"
        elseif string.match(result, "permission") then
            return false, "Access denied"
        else
            return false, "Database operation failed"
        end
    end

    return true, result
end

-- Usage with error handling
local success, result = safe_query(
    "main-db",
    "INSERT INTO users (email, name) VALUES (?, ?)",
    {email, name}
)

if not success then
    -- Handle specific error
    if result == "Data validation failed" then
        print("Email may already exist")
    else
        print("Error: " .. result)
    end
end

Best Practices

1. Always use parameter bindings: Never concatenate user input into SQL strings

   -- BAD: SQL injection vulnerability
   local sql = "SELECT * FROM users WHERE email = '" .. user_email .. "'"
   
   -- GOOD: Safe parameterized query
   local sql = "SELECT * FROM users WHERE email = ?"
   database.query("db", sql, {user_email})

2. Handle NULL values properly:

   -- Check for NULL values in results
   local success, result = database.query("db", "SELECT * FROM users WHERE id = ?", {id})
   if success and #result > 0 then
       local user = result[1]
       local phone = user.phone or "No phone"  -- Handle NULL
   end

3. Use appropriate data types:

   -- Ensure correct type conversion
   local user_id = tonumber(user_id_string)
   local is_active = (status == "active")  -- Convert to boolean
   
   database.query("db",
       "UPDATE users SET active = ?, updated_at = NOW() WHERE id = ?",
       {is_active, user_id}
   )

4. Implement connection pooling patterns:

   -- Cache database handles for reuse
   local db_handles = {}
   
   function get_db_handle(name)
       if not db_handles[name] then
           db_handles[name] = name  -- Store handle
       end
       return db_handles[name]
   end
   
   -- Use cached handle
   local db = get_db_handle("main-db")
   database.query(db, "SELECT * FROM users", {})

Integration Examples

Data Migration

function migrate_user_data(source_db, target_db)
    -- Read from source
    local success, users = database.query(
        source_db,
        "SELECT id, email, name, created_at FROM legacy_users",
        {}
    )

    if not success then
        print("Failed to read source data: " .. users)
        return false
    end

    -- Transform and insert into target
    for _, user in ipairs(users) do
        local insert_success, _ = database.query(
            target_db,
            "INSERT INTO users (legacy_id, email, name, migrated_at) VALUES (?, ?, ?, NOW())",
            {user.id, user.email, user.name}
        )

        if not insert_success then
            print("Failed to migrate user: " .. user.email)
        end
    end

    return true
end

Report Generation

function generate_sales_report(db_handle, start_date, end_date)
    local report = {
        period = {start = start_date, end_date = end_date},
        metrics = {}
    }

    -- Get total sales
    local success, totals = database.query(db_handle,
        "SELECT COUNT(*) as count, SUM(total) as revenue FROM orders WHERE created_at BETWEEN ? AND ?",
        {start_date, end_date}
    )

    if success and #totals > 0 then
        report.metrics.order_count = totals[1].count
        report.metrics.total_revenue = totals[1].revenue
    end

    -- Get top products
    local success, products = database.query(db_handle,
        [[
            SELECT p.name, SUM(oi.quantity) as units_sold
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id
            WHERE o.created_at BETWEEN ? AND ?
            GROUP BY p.id, p.name
            ORDER BY units_sold DESC
            LIMIT 10
        ]],
        {start_date, end_date}
    )

    if success then
        report.top_products = products
    end

    return report
end

Security and Performance

Security:

• Requires actor with database access permissions
• Database must exist and be accessible to the actor
• SQL injection protection via mandatory parameter bindings
• Query execution timeout limits prevent long-running queries
• Result set size limits prevent memory exhaustion

Performance:

• Connection pooling is handled automatically
• Query results are streamed for large datasets
• Consider pagination for large result sets
• Use database indexes for frequently queried columns