Stored Procedures

The SQL engine (ADR-0014) runs procedures in two languages. Both are registered with CREATE PROCEDURE, invoked with CALL, run inside the caller's transaction, and replicate under Raft. Pick SQL text for plain statements, Cobra when you need real control flow.

SQL-text procedures

The zero-toolchain path — the body is a block of SQL statements, re-parsed on every CALL. Parameters are referenced by name.

CREATE PROCEDURE give_raise(pct INT) AS BEGIN
  UPDATE users SET salary = salary + salary * pct / 100;
  INSERT INTO audit(event) VALUES ('raise applied');
END;

CALL give_raise(5);

Managing procedures

CREATE OR ALTER PROCEDURE give_raise(pct INT) AS BEGIN
  UPDATE users SET salary = salary + salary * pct / 100;
END;                          -- redefine in place

SHOW PROCEDURES;              -- name, params, language, definition
DROP PROCEDURE give_raise;

Procedures run in the caller's transaction

Every statement a procedure runs is part of the surrounding transaction, so a failure rolls the whole thing back — the procedure is atomic with the work around it.

BEGIN;
CALL give_raise(5);          -- its UPDATE + INSERT join this transaction
UPDATE settings SET last_raise = NOW();
COMMIT;                       -- all-or-nothing

Cobra — compiled procedures

Cobra is the compiled procedure language. You author a small program, compile it to portable .cobrac bytecode, and the server executes it on a built-in Rust VM — no toolchain on the server, no cgo, no sidecar process. The program defines run(db, …params); the db handle goes through the same executor as ordinary SQL.

Cobra is a full general-purpose language with its own docs, tooling, and playground at cobralang.baltavista.com.

Write the procedure

db.execute(sql[, params]) runs a DML statement and returns the affected-row count; db.query(sql[, params]) runs a SELECT and returns a list of row dicts. Both join the CALL's transaction. Anything printed is returned to the client as notices.

# transfer.cobra — real logic, compiled to bytecode
def run(db, from_id, to_id, amount):
    if amount <= 0:
        raise "amount must be positive"       # aborts the CALL + its transaction

    db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from_id])
    db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to_id])

    rows = db.query("SELECT balance FROM accounts WHERE id = ?", [from_id])
    print("remaining:", rows[0]["balance"])       # -> a notice
    return rows                                    # list of dicts -> a result set

Compile & register

# compile to portable bytecode, then base64 it
cobra build --portable transfer.cobra transfer.cobrac
B64=$(base64 -i transfer.cobrac)

-- register the compiled procedure (param types declared in SQL)
CREATE PROCEDURE transfer(from_id INT, to_id INT, amount DECIMAL)
  LANGUAGE COBRA AS '<base64 of transfer.cobrac>';

CALL transfer(1, 2, 100);      -- both UPDATEs + the SELECT run in ONE transaction
SHOW PROCEDURES;               -- the language column reads COBRA

Return shaping

Everything printed comes back as notices attached to the result, so a procedure can report progress without polluting its return set.

Deterministic & replication-safe

Cobra procedures are deterministic by construction. At CREATE time the bytecode is validated — async, imports, and every form of I/O (network, files, clocks, randomness, channels) are rejected. Each CALL runs under a 100M-instruction fuel cap, so a runaway procedure cannot stall the server. Because a procedure can only touch the database through db and has no other side effects, it produces identical results on every Raft node — CREATE, CALL, and DROP all replicate safely.

For the rest of the SQL surface — joins, CTEs, window functions, transactions — see the SQL Reference.