Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

SQL

ic-dbms canisters can run SQL statements alongside the typed CRUD endpoints. SQL support is opt-in: without the sql feature the canister does not contain the SQL engine and does not expose the SQL endpoints.

The supported dialect (SELECT with joins and aggregates, INSERT, UPDATE, DELETE, BEGIN, COMMIT, ROLLBACK and ? parameters) is described in the wasm-dbms SQL reference. The schema is still defined in Rust with #[derive(Table)]; SQL only reads and writes data.

Enabling SQL on a canister

Enable the sql feature of ic-dbms-canister in the canister crate:

[dependencies]
ic-dbms-canister = { version = "0.10", features = ["sql"] }

#[derive(DbmsCanister)] then generates two more endpoints:

sql : (text, vec Value, opt nat64) -> (Result_SqlResult);
sql_query : (text, vec Value, opt nat64) -> (Result_SqlResult) query;
EndpointCall typeRuns
sqlupdateAny supported statement
sql_queryquerySELECT only; anything else returns SqlError::Unsupported

Both take the statement, one Value per ? placeholder in order, and an optional transaction ID.

Results

A successful statement returns a SqlResult:

VariantReturned by
Rows(rows)SELECT; each row is a list of column definition and value pairs
RowsAffected(n)INSERT, UPDATE, DELETE
TxBegin(id)BEGIN
TxCommitCOMMIT
TxRollbackROLLBACK

For a join, each column definition carries the name of its table.

Transactions

BEGIN returns a transaction ID and makes the caller its owner, exactly like begin_transaction. Pass the ID as the third argument to run later statements inside the transaction, then send COMMIT or ROLLBACK with the same ID. Transactions opened through SQL and through begin_transaction are interchangeable. Using another principal’s transaction ID traps.

#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{SqlResult, Value};
use ic_dbms_client::prelude::Client as _;

let SqlResult::TxBegin(tx) = client.sql("BEGIN", vec![], None).await?? else {
    unreachable!("BEGIN returns TxBegin");
};
client
    .sql(
        "UPDATE users SET name = ? WHERE id = 1",
        vec![Value::from("Alicia")],
        Some(tx),
    )
    .await??;
client.sql("COMMIT", vec![], Some(tx)).await??;
}

Access control

SQL statements are checked against the access control list with the same rules as the typed endpoints:

StatementRequired permission
SELECTRead on the FROM table and on every joined table
INSERTInsert on the table
UPDATEUpdate on the table
DELETEDelete on the table
DELETE ... CASCADEDelete on the table and on every table referencing it
BEGINAny grant
COMMIT, ROLLBACKOwnership of the transaction

The reserved ic_dbms_acl table does not exist for SQL, even for admins: any statement naming it fails with SqlError::UnknownTable. Use acl_list, acl_grant and acl_revoke to manage grants.

Calling SQL from a client

Enable the sql feature of ic-dbms-client to get Client::sql and Client::sql_query:

[dependencies]
ic-dbms-client = { version = "0.10", features = ["sql"] }
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{SqlResult, Value};
use ic_dbms_client::prelude::Client as _;

let result = client
    .sql_query("SELECT name FROM users WHERE id = ?", vec![Value::from(1u32)], None)
    .await??;
if let SqlResult::Rows(rows) = result {
    for row in rows {
        for (column, value) in row {
            println!("{} = {value:?}", column.name);
        }
    }
}
}

Errors

SQL failures are returned as IcDbmsError::Sql(SqlError). Access control failures stay IcDbmsError::Acl. See the errors reference and the wasm-dbms SQL reference for every SqlError variant. UPDATE and DELETE without a WHERE clause are refused with SqlError::MissingWhereClause.