sqlite

The sqlite module provides SQLite database access. sqlite::query uses type-directed deserialization — annotate the result type to control how rows are deserialized.

/// A SQLite value: integer, float, string, bytes, or null.
type SqlVal = [i64, f64, string, bytes, null];

/// An opaque SQLite connection handle.
type Connection;

/// Open (or create) a SQLite database. Use ":memory:" for in-memory.
val open: fn(path: string) -> Result<Connection, `SqliteError(string)>;

// SQL text is a Graphix string, so every [..] in it interpolates: values
// go through the ? params, and SQL that holds a [ is written r"..." or
// """...""".
/// Execute a non-returning statement (INSERT/UPDATE/DELETE/DDL) with params. Returns rows affected.
val exec: fn(conn: Connection, sql: string, params: Array<SqlVal>) -> Result<u64, `SqliteError(string)>;

/// Execute multiple semicolon-separated statements (no params). Good for schema setup.
val exec_batch: fn(conn: Connection, sql: string) -> Result<null, `SqliteError(string)>;

/// Query rows, deserializing each into the annotated type.
/// Annotate as Array<{...}> for typed structs, or Array<Map<string, SqlVal>> for raw maps.
val query: fn<'a: Concrete>(conn: Connection, sql: string, params: Array<SqlVal>) -> Result<Array<'a>, [`SqliteError(string), `InvalidCast(string)]>;

/// Begin a transaction.
val begin: fn(conn: Connection) -> Result<null, `SqliteError(string)>;

/// Commit the current transaction.
val commit: fn(conn: Connection) -> Result<null, `SqliteError(string)>;

/// Rollback the current transaction.
val rollback: fn(conn: Connection) -> Result<null, `SqliteError(string)>;

/// Close the connection explicitly (optional — connections close on drop).
val close: fn(conn: Connection) -> Result<null, `SqliteError(string)>;

SQL is written in an ordinary Graphix string, and an ordinary string interpolates every [..] in it. Pass values through the ? params, never by splicing them into the text: "... WHERE name = '[name]'" is SQL injection. SQL that itself holds a [ (a JSON path such as '$.items[0]') is written as a raw string r"..." or a template """...""", where brackets are content.

Type-directed queries

The return type of sqlite::query determines how rows are deserialized. Use struct types for named columns, or Map<string, SqlVal> for raw access.

Calls on one connection are independent async calls, ordered only when something sequences them: here a seq, so the table exists before the insert and the row before the queries.

let (users, raw) = seq {
    try {
        let conn = sqlite::open(":memory:")?;
        sqlite::exec_batch(conn, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")?;
        sqlite::exec(conn, "INSERT INTO users VALUES (?, ?, ?)", [1, "Alice", 30])?;
        // typed struct results
        let users: Array<{id: i64, name: string, age: i64}> =
            sqlite::query(conn, "SELECT * FROM users", [])?;
        // raw map results
        let raw: Array<Map<string, sqlite::SqlVal>> =
            sqlite::query(conn, "SELECT * FROM users", [])?;
        (users, raw)
    } with(e) {
        println("sqlite failed: [e]");
        ([], [])
    }
};
println("[users] [raw]")