SQLite Database Module

The sqldb module provides SQLite database functionality using Rust's rusqlite crate.

Functions

• execute(path: string, sql: string) -> string Executes one SQL statement and commits it. Returns "ok" or raises a catchable runtime error on failure.

• executeArgs(path: string, sql: string, paramsJson: string) -> string Executes one parameterized SQL statement. paramsJson must be a JSON array. Returns "ok" or raises a catchable runtime error on failure.

• script(path: string, sqlScript: string) -> string Executes multiple SQL statements as one transaction. Returns "ok" or raises a catchable runtime error on failure.

• query(path: string, sql: string) -> string Query rows and return a JSON array of objects.

• queryArgs(path: string, sql: string, paramsJson: string) -> string Parameterized form of query().

Query output uses JSON-native null, numbers and strings. BLOB columns use a tagged object such as {"$bytes": "AAH/gA=="}; the tag preserves their byte identity and the base64 payload preserves arbitrary bytes. Pass the same single-key object in paramsJson to bind those bytes back as a SQLite BLOB.

• scalar(path: string, sql: string) -> string Returns the first column of the first row as a string, or "" when absent.

• scalarArgs(path: string, sql: string, paramsJson: string) -> string Parameterized form of scalar().

scalar() keeps the reference-compatible string result: NULL and no row return "", numeric values use their decimal text, text is unchanged, and BLOBs use bytes:<base64> so they cannot be mistaken for ordinary text.

• lastInsertId(path: string, sql: string, paramsJson: string) -> int Executes an insert/update and returns SQLite's lastrowid. Returns -1 on error.

• tableExists(path: string, tableName: string) -> bool Returns whether a table exists in the database.

• tables(path: string) -> string Returns table names as a JSON array.

Connection handles

Every function above names a path and opens a connection for that call only. To reuse one connection, open it once and use the *On forms with the handle:

• open(path: string) -> int Opens the database and keeps the connection open. Returns the handle, or -1 when the database cannot be opened.

• close(handle: int) -> int Releases a handle. Returns 1 when it was live, else 0.

• executeOn, executeArgsOn, scriptOn, queryOn, queryArgsOn, scalarOn, scalarArgsOn, lastInsertIdOn, tableExistsOn, tablesOn are the handle-taking forms of the functions above (same arguments, with the handle in place of the path).

Handles stay valid for the run, and a closed or unknown handle answers the usual sentinels (-1, false) for integer- and boolean-returning operations. String-returning operations raise a catchable runtime error, including for an unknown or closed handle. Two threads can share a handle: each call takes the connection for its duration.

Catch database failures with the language's try/catch statement:

try {
    global.sqldb.query("database.db", "SELECT * FROM missing_table");
} catch(str error) {
    println("Database error: ", error);
}

Example

global setup(){
    import("sqldb")
}

global main(){
    global.sqldb.execute("database.db", "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
    global.sqldb.execute("database.db", "INSERT INTO users (name) VALUES ('Alice')");
    println(global.sqldb.query("database.db", "SELECT * FROM users"));

    // Or keep one connection open across calls.
    int db = global.sqldb.open("database.db");
    global.sqldb.executeOn(db, "INSERT INTO users (name) VALUES ('Bob')");
    println(global.sqldb.queryOn(db, "SELECT * FROM users ORDER BY id"));
    global.sqldb.close(db);
}

See also

• stdlib-contracts — the contract this module implements, including its error behavior.
• builtins — the functions the interpreter implements itself.
• limitations — the full divergence register.