ic-dbms
Overview
IC-DBMS is an adapter layer that brings the wasm-dbms relational database engine to the Internet Computer (IC). While wasm-dbms provides the core database functionality (tables, CRUD operations, transactions, memory management), ic-dbms adds everything needed to run it as an IC canister:
- Candid serialization for all types and API endpoints
- Canister lifecycle management (init, upgrade, inspect)
- ACL-based access control keyed by IC principals and stored in a reserved table
- Procedural macros to generate complete canister APIs from schema definitions
- Client libraries for inter-canister calls, external agent access, and integration testing
If you are using wasm-dbms outside the Internet Computer (e.g., in a standalone WASM runtime), you do not need ic-dbms. See the generic wasm-dbms documentation instead.
Architecture
┌─────────────────────────────────────────────────┐
│ Your Application │
│ (Frontend canister, backend canister, CLI, etc.)│
└──────────────────────┬──────────────────────────┘
│ Candid calls
▼
┌─────────────────────────────────────────────────┐
│ ic-dbms-client │
│ (IcDbmsCanisterClient / IcDbmsAgentClient / │
│ IcDbmsPocketIcClient) │
└──────────────────────┬──────────────────────────┘
│
▼
┌─────────────────────────────────────────────────┐
│ ic-dbms-canister │
│ (Generated canister API, ACL, init/upgrade) │
│ │
│ ┌───────────────────────────────────────────┐ │
│ │ wasm-dbms (core engine) │ │
│ │ Tables, CRUD, Transactions, Memory Mgmt │ │
│ └───────────────────────────────────────────┘ │
└─────────────────────────────────────────────────┘
Crates
IC-DBMS is composed of four crates:
| Crate | Description | Depends On |
|---|---|---|
| ic-dbms-api | Shared types, re-exports wasm-dbms-api types with IC additions. Provides the IcDbmsError enum, the ACL types, and IC-compatible type wrappers. | wasm-dbms-api |
| ic-dbms-canister | Core canister engine. Provides the DbmsCanister derive macro target, ACL management, canister init/upgrade lifecycle, and the IC stable memory provider. | wasm-dbms, ic-dbms-api |
| ic-dbms-macros | Procedural macros: #[derive(DatabaseSchema)] (IC variant, uses IC crate paths) and #[derive(DbmsCanister)] for generating complete canister APIs. | wasm-dbms-macros |
| ic-dbms-client | Client library with three implementations: IcDbmsCanisterClient (inter-canister), IcDbmsAgentClient (external via IC agent), IcDbmsPocketIcClient (integration testing). | ic-dbms-api |
Import convention:
#![allow(unused)]
fn main() {
// In your schema crate
use ic_dbms_api::prelude::*; // Re-exports wasm_dbms_api types
// In your canister crate
use ic_dbms_canister::prelude::DbmsCanister;
// In your client code
use ic_dbms_client::prelude::{Client as _, IcDbmsCanisterClient};
}
Quick Start
- Define your schema with IC-compatible derives:
#![allow(unused)]
fn main() {
use candid::{CandidType, Deserialize};
use ic_dbms_api::prelude::*;
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "users"]
pub struct User {
#[primary_key]
pub id: Uint32,
pub name: Text,
pub email: Text,
}
}
- Generate the canister:
#![allow(unused)]
fn main() {
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users")]
pub struct MyDbmsCanister;
ic_cdk::export_candid!();
}
- Build, deploy, and interact:
cargo build --target wasm32-unknown-unknown --release
dfx deploy my_dbms --argument '(variant { Init = record { allowed_principals = vec { principal "your-principal" } } })'
#![allow(unused)]
fn main() {
let client = IcDbmsCanisterClient::new(canister_id);
client.insert::<User>(User::table_name(), user, None).await??;
}
For the full walkthrough, see the Get Started guide.
Guides
- Get Started - Set up and deploy your first IC database canister
- CRUD Operations - Insert, select, update, delete via the IC client
- Access Control - Admin principals and the reserved ACL table
- Client API - All client types and usage patterns
For core wasm-dbms guides (querying, transactions, relationships, validators, sanitizers, custom data types), see the generic guides.
Reference
- Schema (IC) - DbmsCanister macro, Candid API generation, IC-specific derives
- Data Types (IC) - Principal type, Candid type mappings
- Errors (IC) - IcDbmsError alias, double-Result pattern, client error handling
For the complete reference (all data types, error variants, sanitizers, validators, JSON operations), see the generic reference.
Get Started with IC-DBMS (IC)
Note: This is the IC-specific getting started guide for deploying wasm-dbms as an Internet Computer canister. For the generic wasm-dbms getting started guide (schema definition, core concepts), see the generic get-started guide.
- Get Started with IC-DBMS (IC)
This guide walks you through setting up a complete database canister on the Internet Computer using ic-dbms. The ic-dbms framework is built on top of the wasm-dbms core engine, adding IC-specific functionality such as Candid serialization, canister lifecycle management, ACL-based access control, and inter-canister communication. By the end of this guide, you will have a working canister with CRUD operations, transactions, and access control.
Prerequisites
Before starting, ensure you have:
- Rust 1.96.0 or later
wasm32-unknown-unknowntarget:rustup target add wasm32-unknown-unknown- dfx (Internet Computer SDK)
ic-wasm:cargo install ic-wasmcandid-extractor:cargo install candid-extractor
Project Setup
Workspace Structure
We recommend organizing your project as a Cargo workspace with two crates:
my-dbms-project/
├── Cargo.toml # Workspace manifest
├── schema/ # Schema definitions (reusable types)
│ ├── Cargo.toml
│ └── src/
│ └── lib.rs
└── canister/ # The DBMS canister
├── Cargo.toml
└── src/
└── lib.rs
Workspace Cargo.toml:
[workspace]
members = ["schema", "canister"]
resolver = "2"
Cargo Configuration
Create .cargo/config.toml to configure the getrandom crate for WebAssembly:
[target.wasm32-unknown-unknown]
rustflags = ['--cfg', 'getrandom_backend="custom"']
This is required because the uuid crate depends on getrandom.
Define Your Schema
Create the Schema Crate
Create schema/Cargo.toml:
[package]
name = "my-schema"
version = "0.1.0"
edition = "2024"
[dependencies]
candid = "0.10"
ic-dbms-api = "0.11"
serde = { version = "1", features = ["derive"] }
wasm-dbms-api = "0.11"
Note: The
Tablederive expands to::wasm_dbms_apipaths, so every crate that defines tables must depend onwasm-dbms-apidirectly.
Note:
ic-dbms-apire-exports types fromwasm-dbms-api, souse ic_dbms_api::prelude::*gives you access to the full set of wasm-dbms data types, validators, and sanitizers.
Define Tables
In schema/src/lib.rs, define your database tables using the Table derive macro:
#![allow(unused)]
fn main() {
use candid::{CandidType, Deserialize};
use ic_dbms_api::prelude::*;
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "users"]
pub struct User {
#[primary_key]
pub id: Uint32,
#[sanitizer(TrimSanitizer)]
#[validate(MaxStrlenValidator(100))]
pub name: Text,
#[validate(EmailValidator)]
pub email: Text,
/// Creation time in nanoseconds since the Unix epoch (`ic_cdk::api::time()`).
pub created_at: Uint64,
}
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "posts"]
pub struct Post {
#[primary_key]
pub id: Uint32,
#[validate(MaxStrlenValidator(200))]
pub title: Text,
pub content: Text,
pub published: Boolean,
#[foreign_key(entity = "User", table = "users", column = "id")]
pub author_id: Uint32,
}
}
Required derives: Table, CandidType, Deserialize, Clone. The #[candid] attribute ensures generated types (Record, InsertRequest, UpdateRequest) also derive Candid/Serde traits.
The Table macro generates additional types for each table:
| Generated Type | Purpose |
|---|---|
UserRecord | Full record returned from queries |
UserInsertRequest | Request type for inserting records |
UserUpdateRequest | Request type for updating records |
UserForeignFetcher | Internal type for relationship loading |
Create the DBMS Canister
Canister Dependencies
Create canister/Cargo.toml:
[package]
name = "my-canister"
version = "0.1.0"
edition = "2024"
[lib]
crate-type = ["cdylib"]
[dependencies]
candid = "0.10"
ic-cdk = "0.20"
ic-dbms-api = "0.11"
ic-dbms-canister = "0.11"
my-schema = { path = "../schema" }
wasm-dbms = "0.11"
wasm-dbms-api = "0.11"
wasm-dbms-memory = "0.11"
Generate the Canister API
In canister/src/lib.rs:
#![allow(unused)]
fn main() {
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
// `DbmsCanister` needs every type generated by `Table` (`UserRecord`,
// `UserInsertRequest`, `UserUpdateRequest`, ...) in scope.
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts")]
pub struct MyDbmsCanister;
ic_cdk::export_candid!();
}
The DatabaseSchema derive generates the DatabaseSchema<M> trait implementation that provides schema dispatch (
routing operations to the correct table by name). The DbmsCanister derive generates the complete canister API:
service : (IcDbmsCanisterArgs) -> {
// Access control
acl_grant : (AclGrant) -> (Result);
acl_revoke : (AclGrant) -> (Result);
acl_list : () -> (Result_2) query;
my_permissions : () -> (Result_3) query;
// Transactions
begin_transaction : () -> (Result_4);
commit : (nat64) -> (Result);
rollback : (nat64) -> (Result);
// Users CRUD
insert_users : (UserInsertRequest, opt nat64) -> (Result);
select_users : (Query, opt nat64) -> (Result_1) query;
update_users : (UserUpdateRequest, opt nat64) -> (Result_2);
delete_users : (DeleteBehavior, opt Filter, opt nat64) -> (Result_2);
// Posts CRUD
insert_posts : (PostInsertRequest, opt nat64) -> (Result);
select_posts : (Query, opt nat64) -> (Result_3) query;
update_posts : (PostUpdateRequest, opt nat64) -> (Result_2);
delete_posts : (DeleteBehavior, opt Filter, opt nat64) -> (Result_2);
}
Build the Canister
Create a build script or use the following commands:
# Build the canister
cargo build --target wasm32-unknown-unknown --release -p my-canister
# Optimize the WASM
ic-wasm target/wasm32-unknown-unknown/release/my_canister.wasm \
-o my_canister.wasm shrink
# Extract Candid interface
candid-extractor my_canister.wasm > my_canister.did
# Optionally compress
gzip -k my_canister.wasm --force
Deploy the Canister
Canister Init Arguments
The canister requires initialization arguments specifying which principals can access the database:
type IcDbmsCanisterArgs = variant {
Init : IcDbmsCanisterInitArgs;
Upgrade;
};
type IcDbmsCanisterInitArgs = record {
allowed_principals : opt vec principal;
};
Warning: Callers need grants to perform database operations. Each principal in
allowed_principals(or the deployer, when the list is empty) receives anAdmingrant at install time; give other principals narrower grants withacl_grant.
Deploy with dfx
Create dfx.json:
{
"canisters": {
"my_dbms": {
"type": "custom",
"candid": "my_canister.did",
"wasm": "my_canister.wasm",
"build": []
}
}
}
Deploy:
dfx deploy my_dbms --argument '(variant { Init = record { allowed_principals = vec { principal "your-principal-here" } } })'
Quick Example: Complete Workflow
Here’s a complete example showing insert, query, update, and delete operations:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::*;
use ic_dbms_client::prelude::{Client as _, IcDbmsCanisterClient};
use my_schema::{User, UserInsertRequest, UserUpdateRequest};
async fn example(canister_id: candid::Principal) -> Result<(), Box<dyn std::error::Error>> {
let client = IcDbmsCanisterClient::new(canister_id);
// 1. INSERT a new user
let insert_req = UserInsertRequest {
id: 1.into(),
name: "Alice".into(),
email: "alice@example.com".into(),
created_at: ic_cdk::api::time().into(),
};
client
.insert::<User>(User::table_name(), insert_req, None)
.await??;
// 2. SELECT users
let query = Query::builder()
.and_where(Filter::eq("name", Value::Text("Alice".into())))
.build();
let users = client
.select::<User>(User::table_name(), query, None)
.await??;
println!("Found {} user(s)", users.len());
// 3. UPDATE the user
let update_req = UserUpdateRequest {
email: Some("alice.new@example.com".into()),
where_clause: Some(Filter::eq("id", Value::Uint32(1.into()))),
..Default::default()
};
let updated = client
.update::<User>(User::table_name(), update_req, None)
.await??;
println!("Updated {} record(s)", updated);
// 4. DELETE the user
let deleted = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Restrict,
Some(Filter::eq("id", Value::Uint32(1.into()))),
None,
)
.await??;
println!("Deleted {} record(s)", deleted);
Ok(())
}
}
Integration Testing
For integration tests using PocketIC, add ic-dbms-client with the pocket-ic feature. The
client drives the asynchronous pocket_ic::nonblocking::PocketIc:
[dev-dependencies]
ic-dbms-client = { version = "0.11", features = ["pocket-ic"] }
pocket-ic = "16"
Example test:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{Query, TableSchema as _};
use ic_dbms_client::prelude::{Client as _, IcDbmsPocketIcClient};
use my_schema::{User, UserInsertRequest};
use pocket_ic::nonblocking::PocketIc;
#[tokio::test]
async fn test_insert_and_select() {
let pic = PocketIc::new().await;
// ... setup canister ...
let client = IcDbmsPocketIcClient::new(canister_id, admin_principal, &pic);
let insert_req = UserInsertRequest {
id: 1.into(),
name: "Test User".into(),
email: "test@example.com".into(),
created_at: 1_700_000_000_000_000_000u64.into(),
};
client
.insert::<User>(User::table_name(), insert_req, None)
.await
.expect("call failed")
.expect("insert failed");
let query = Query::builder().all().build();
let users = client
.select::<User>(User::table_name(), query, None)
.await
.expect("call failed")
.expect("select failed");
assert_eq!(users.len(), 1);
// Record fields are `Option` because a query can select a subset of columns.
assert_eq!(users[0].name.as_ref().unwrap().as_str(), "Test User");
}
}
Next Steps
Now that you have a working canister, explore these topics:
- CRUD Operations (IC) - Detailed guide on all database operations via the IC client
- Access Control - Managing the ACL
- Client API - All client types and usage patterns
- Schema Definition (IC) - IC-specific schema reference (DbmsCanister macro, Candid API)
- Data Types (IC) - IC-specific data types (Principal, Candid mappings)
- Errors (IC) - IC-specific error handling (double-Result pattern)
For core wasm-dbms concepts (querying, transactions, relationships, validators, sanitizers), see the generic guides.
CRUD Operations (IC)
Note: This is the IC-specific CRUD operations guide, covering usage via the
ic-dbms-client. For core CRUD concepts (filtering, delete behaviors, error types), see the generic CRUD operations guide.
Overview
ic-dbms provides four fundamental database operations, accessed through the ic-dbms-client crate’s Client trait. All operations use Candid serialization under the hood and support the IC’s inter-canister call model.
| Operation | Description | Returns |
|---|---|---|
| Insert | Add a new record to a table | Result<()> |
| Select | Query records from a table | Result<Vec<Record>> |
| Update | Modify existing records | Result<u64> (affected rows) |
| Delete | Remove records from a table | Result<u64> (affected rows) |
All operations:
- Respect access control (the caller must hold the matching grant)
- Support optional transaction IDs
- Validate and sanitize data according to schema rules
- Enforce foreign key constraints
- Return a double
Result(see Error Handling)
Insert
Basic Insert
To insert a record, create an InsertRequest and call the insert method:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::*;
use ic_dbms_client::prelude::{Client as _, IcDbmsCanisterClient};
use my_schema::{User, UserInsertRequest};
let client = IcDbmsCanisterClient::new(canister_id);
let user = UserInsertRequest {
id: 1.into(),
name: "Alice".into(),
email: "alice@example.com".into(),
created_at: ic_cdk::api::time().into(),
};
// Insert without transaction (None)
client
.insert::<User>(User::table_name(), user, None)
.await??;
}
Handling Primary Keys
Every table must have a primary key. Insert will fail if a record with the same primary key already exists:
#![allow(unused)]
fn main() {
// First insert succeeds
client.insert::<User>(User::table_name(), user1, None).await??;
// Second insert with same ID fails with PrimaryKeyConflict
let result = client.insert::<User>(User::table_name(), user2_same_id, None).await?;
assert!(matches!(
result,
Err(IcDbmsError::Dbms(DbmsError::Query(QueryError::PrimaryKeyConflict)))
));
}
Nullable Fields
For fields wrapped in Nullable<T>, you can insert either a value or null:
#![allow(unused)]
fn main() {
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "profiles"]
pub struct Profile {
#[primary_key]
pub id: Uint32,
pub bio: Nullable<Text>, // Optional field
pub website: Nullable<Text>, // Optional field
}
}
#![allow(unused)]
fn main() {
// Insert with value
let profile = ProfileInsertRequest {
id: 1.into(),
bio: Nullable::Value("Hello world".into()),
website: Nullable::Null, // No website
};
client.insert::<Profile>(Profile::table_name(), profile, None).await??;
}
Insert with Transaction
To insert within a transaction, pass the transaction ID:
#![allow(unused)]
fn main() {
// Begin transaction
let tx_id = client.begin_transaction().await??;
// Insert within transaction
client.insert::<User>(User::table_name(), user, Some(tx_id)).await??;
// Commit or rollback
client.commit(tx_id).await??;
}
Select
Select All Records
Use Query::builder().all() to select all records:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::*;
let query = Query::builder().all().build();
let users: Vec<UserRecord> = client
.select::<User>(User::table_name(), query, None)
.await??;
// Record fields are `Option` because a query can select a subset of columns.
for user in users {
println!("User: {:?} ({:?})", user.name, user.email);
}
}
Select with Filter
Add filters to narrow down results:
#![allow(unused)]
fn main() {
// Select users with specific name
let query = Query::builder()
.and_where(Filter::eq("name", Value::Text("Alice".into())))
.build();
let users = client.select::<User>(User::table_name(), query, None).await??;
}
See the Querying Guide for comprehensive filter documentation.
Select Specific Columns
Select only the columns you need:
#![allow(unused)]
fn main() {
let query = Query::builder()
.fields(["id", "name"])
.build();
let users = client.select::<User>(User::table_name(), query, None).await??;
// Only `id` and `name` are `Some`; the other fields are `None`
}
Select with Eager Loading
Load related records in a single query using with():
#![allow(unused)]
fn main() {
// Load posts with their authors
let query = Query::builder()
.all()
.with("users") // Eager load the related users table
.build();
let posts = client.select::<Post>(Post::table_name(), query, None).await??;
}
See the Relationships Guide for more on eager loading.
Update
Basic Update
Create an UpdateRequest to modify records:
#![allow(unused)]
fn main() {
use my_schema::UserUpdateRequest;
let update = UserUpdateRequest {
name: Some("Alice Smith".into()),
where_clause: Some(Filter::eq("id", Value::Uint32(1.into()))),
..Default::default()
};
let affected_rows = client
.update::<User>(User::table_name(), update, None)
.await??;
println!("Updated {} row(s)", affected_rows);
}
Partial Updates
Only set the fields you want to change. Fields left as None remain unchanged:
#![allow(unused)]
fn main() {
// Only update the email, keep everything else
let update = UserUpdateRequest {
email: Some("new.email@example.com".into()),
where_clause: Some(Filter::eq("id", Value::Uint32(1.into()))),
..Default::default()
};
client.update::<User>(User::table_name(), update, None).await??;
}
Update with Filter
The where_clause determines which records are updated; None updates every
record:
#![allow(unused)]
fn main() {
// Update all users with a specific domain
let update = UserUpdateRequest {
name: Some("Company user".into()),
where_clause: Some(Filter::like("email", "%@company.com")),
..Default::default()
};
let affected = client.update::<User>(User::table_name(), update, None).await??;
println!("Renamed {} company users", affected);
}
Update Return Value
Update returns the number of affected rows:
#![allow(unused)]
fn main() {
let affected = client.update::<User>(User::table_name(), update, None).await??;
if affected == 0 {
println!("No records matched the filter");
} else {
println!("Updated {} record(s)", affected);
}
}
Delete
Delete with Filter
Delete records matching a filter:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::DeleteBehavior;
let filter = Filter::eq("id", Value::Uint32(1.into()));
let deleted = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Restrict,
Some(filter),
None // No transaction
)
.await??;
println!("Deleted {} record(s)", deleted);
}
Delete Behaviors
When deleting records that are referenced by foreign keys, you must specify a behavior:
| Behavior | Description |
|---|---|
Restrict | Fail if any foreign keys reference this record |
Cascade | Delete all records that reference this record |
Restrict Example:
#![allow(unused)]
fn main() {
// Will fail if any posts reference this user
let result = client.delete::<User>(
User::table_name(),
DeleteBehavior::Restrict,
Some(Filter::eq("id", Value::Uint32(1.into()))),
None
).await?;
match result {
Ok(count) => println!("Deleted {} user(s)", count),
Err(IcDbmsError::Dbms(DbmsError::Query(QueryError::ForeignKeyConstraintViolation { .. }))) => {
println!("Cannot delete: user has posts");
}
Err(e) => return Err(e.into()),
}
}
Cascade Example:
#![allow(unused)]
fn main() {
// Deletes the user AND all their posts
client.delete::<User>(
User::table_name(),
DeleteBehavior::Cascade,
Some(Filter::eq("id", Value::Uint32(1.into()))),
None
).await??;
}
Delete All Records
Pass None as the filter to delete all records (use with caution):
#![allow(unused)]
fn main() {
// Delete ALL users (respecting foreign key behavior)
let deleted = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Cascade,
None, // No filter = all records
None
)
.await??;
println!("Deleted all {} users and their related records", deleted);
}
Operations with Transactions
All CRUD operations accept an optional transaction ID. When provided, the operation is performed within that transaction and won’t be visible to other callers until committed:
A transaction belongs to the principal that called begin_transaction. Any
call that names the transaction id from another principal, and any call that
names an unknown or already closed id, traps. The canister keeps this
ownership table on the heap only: it is not written to stable memory. Open
transactions and their owners are discarded together on every upgrade, so
commit or roll back before upgrading.
#![allow(unused)]
fn main() {
// Begin transaction
let tx_id = client.begin_transaction().await??;
// Perform operations within transaction
client.insert::<User>(User::table_name(), user1, Some(tx_id)).await??;
client.insert::<User>(User::table_name(), user2, Some(tx_id)).await??;
// Update within same transaction
let update = UserUpdateRequest {
name: Some("Batch user".into()),
where_clause: None, // every record
..Default::default()
};
client.update::<User>(User::table_name(), update, Some(tx_id)).await??;
// Commit all changes atomically
client.commit(tx_id).await??;
}
See the Transactions Guide for comprehensive transaction documentation.
Error Handling
CRUD operations via the IC client return a double Result: IcDbmsCanisterClientResult<IcDbmsResult<T>>.
- Outer
Result: Network/canister call errors (canister unreachable, cycles exhausted) - Inner
Result: Database logic errors (validation, constraint violations, etc.)
Use ?? to propagate both:
#![allow(unused)]
fn main() {
client.insert::<User>(User::table_name(), user, None).await??;
}
Or handle each layer explicitly:
#![allow(unused)]
fn main() {
match client.insert::<User>(User::table_name(), user, None).await {
Ok(Ok(())) => println!("Insert successful"),
Ok(Err(db_error)) => {
// Handle database errors
match db_error {
IcDbmsError::Dbms(DbmsError::Query(QueryError::PrimaryKeyConflict)) => {
println!("User already exists");
}
IcDbmsError::Dbms(DbmsError::Validation(msg)) => {
println!("Validation error: {}", msg);
}
_ => println!("Database error: {:?}", db_error),
}
}
Err(call_error) => {
// Handle network/call errors
println!("Failed to call canister: {:?}", call_error);
}
}
}
Common error types:
| Error | Cause | Operation |
|---|---|---|
PrimaryKeyConflict | Record with same primary key exists | Insert |
ForeignKeyConstraintViolation | Referenced record doesn’t exist, or delete restricted | Insert, Update, Delete |
BrokenForeignKeyReference | Foreign key points to non-existent record | Insert, Update |
UnknownColumn | Invalid column name in filter or select | Select, Update, Delete |
MissingNonNullableField | Required field not provided | Insert, Update |
RecordNotFound | No record matches the criteria | Update, Delete |
TransactionNotFound | Invalid transaction ID | All |
InvalidQuery | Malformed query (e.g., invalid JSON path) | Select |
See the Errors Reference (IC) for complete IC-specific error documentation, or the generic Errors Reference for the full error hierarchy.
Access Control (IC)
Note: This is the IC-specific access control guide. Access control is an IC-only feature; the wasm-dbms engine has none and leaves it to the embedder.
ic-dbms keeps an Access Control List (ACL) inside the database itself, in a
reserved table called ic_dbms_acl that #[derive(DbmsCanister)] always
registers next to your tables. Each row is one grant: a principal, a
permission and an optional table. A principal may hold many grants. A
principal with no grants may call only my_permissions.
Permissions and grants
#![allow(unused)]
fn main() {
pub enum AclPermission {
/// Everything: every operation on every table, transactions, ACL
/// management and schema migrations.
Admin,
/// Select and aggregate on the granted table.
Read,
/// Insert into the granted table.
Insert,
/// Update rows of the granted table.
Update,
/// Delete rows of the granted table.
Delete,
}
pub struct AclGrant {
pub principal: Principal,
pub permission: AclPermission,
/// `None` means every table, including tables added later.
pub table: Option<String>,
}
}
| Grant | Meaning |
|---|---|
AclGrant::admin(p) | p may do everything. Always has table: None. |
AclGrant::table(p, Read, "users") | p may select and aggregate users. |
AclGrant::all_tables(p, Read) | p may read every table, including ones added later. |
AclGrant::table(p, Insert, "users") | p may insert into users. |
Rules:
- Granting the same grant twice stores one row.
- An
Admingrant naming a table is refused (AdminGrantWithTable). - The table must be one of the canister’s tables (
InvalidTable); the reservedic_dbms_acltable cannot be granted. - The anonymous principal cannot receive grants (
AnonymousPrincipal). - Revoking a grant that is not stored is a no-op.
- At least one
Admingrant must remain (LastAdmin).
Initialization
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::IcDbmsCanisterInitArgs;
let args = IcDbmsCanisterInitArgs {
allowed_principals: Some(vec![operator_principal]),
};
}
allowed_principals | Result |
|---|---|
None | The deployer principal becomes an admin. |
Some(vec![]) | Same as None: the deployer becomes admin. |
Some(vec![p, q]) | Each listed principal becomes an admin. |
init registers your tables, then the ic_dbms_acl table, then stores an
Admin grant for each principal. The anonymous principal makes init trap.
Endpoints
| Endpoint | Kind | Required permission | Effect |
|---|---|---|---|
acl_grant | update | Admin | Store a grant. Idempotent. |
acl_revoke | update | Admin | Remove a grant. Refuses the last admin. |
acl_list | query | Admin | List every grant. |
my_permissions | query | none | Return exactly the caller’s grants. |
type AclPermission = variant { Admin; Read; Insert; Update; Delete };
type AclGrant = record {
principal : principal;
permission : AclPermission;
table : opt text;
};
acl_grant : (AclGrant) -> (variant { Ok; Err : IcDbmsError });
acl_revoke : (AclGrant) -> (variant { Ok; Err : IcDbmsError });
acl_list : () -> (variant { Ok : vec AclGrant; Err : IcDbmsError }) query;
my_permissions : () -> (variant { Ok : vec AclGrant; Err : IcDbmsError }) query;
Enforcement
Every generated endpoint except my_permissions checks the caller before
touching data. A grant on a named table matches only that table; a grant with
no table matches every user table; Admin matches everything.
| Endpoint | Required |
|---|---|
select_<t>, aggregate_<t> | Read on t and on every relation loaded eagerly (QueryBuilder::with) |
select (untyped) | Read on the root table, every joined table and every eager relation |
insert_<t> | Insert on t |
update_<t> | Update on t |
delete_<t> with Restrict | Delete on t |
delete_<t> with Cascade | Delete on t and on every table that references t, transitively |
begin_transaction | At least one grant |
commit, rollback | The principal that opened the transaction |
acl_grant, acl_revoke, acl_list | Admin |
has_drift, pending_migrations, migrate | Admin or a canister controller |
Operations inside a transaction are checked one by one, exactly as outside it: opening a transaction does not widen what the caller may do.
SQL statements sent to sql or sql_query (with the sql feature) are
checked with the same rules, per statement; see the
SQL guide.
The reserved ic_dbms_acl table is never matched by a table-less grant, so
only Admin can read it through the untyped select, directly or through a
join.
The check reads the caller’s grants through the same database view as the
operation, using the index on the principal column.
Transaction ownership is tracked in the canister heap, not in the engine:
begin_transaction records the caller next to the new id, every call that
names the id compares the caller with that record, and commit or rollback
removes it. A mismatch and an unknown id trap with the same message.
Controllers and schema drift
The ACL lives in the database, so while the schema has drifted after an
upgrade every read of the ACL table fails with SchemaDrift, exactly like a
CRUD call would. To avoid locking the canister, its controllers may always
call has_drift, pending_migrations and migrate. Controllers can already
reinstall or delete the canister, so this grants them nothing new. ACL
management endpoints are unavailable during drift: run migrate first.
Reserved table names
Table names starting with ic_dbms_ are reserved. Declaring one in
#[tables(...)] is a compile-time error:
error: table name `ic_dbms_acl` is reserved: names starting with `ic_dbms_` belong to ic-dbms
Errors
| Error | Cause |
|---|---|
IcDbmsError::Acl(AclError::AccessDenied { required, table }) | The caller lacks the specified permission or any grant. |
IcDbmsError::Acl(AclError::AdminGrantWithTable) | acl_grant of Admin naming a table. |
IcDbmsError::Acl(AclError::InvalidTable(name)) | acl_grant naming an unknown or reserved table. |
IcDbmsError::Acl(AclError::AnonymousPrincipal) | acl_grant or init targeted the anonymous principal. |
IcDbmsError::Acl(AclError::LastAdmin) | acl_revoke would remove the last Admin grant. |
AccessDenied.required is AclRequirement::Permission(permission) for a
specific operation and AclRequirement::AnyGrant when an unlisted principal
tries to open a transaction. table is present only for a table-specific
permission check.
See the errors reference for the full IcDbmsError
type.
Recipes
Read-only frontend
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{AclGrant, AclPermission};
client
.acl_grant(AclGrant::table(frontend, AclPermission::Read, "users"))
.await??;
client
.acl_grant(AclGrant::table(frontend, AclPermission::Read, "posts"))
.await??;
}
Auditor that reads everything
#![allow(unused)]
fn main() {
client
.acl_grant(AclGrant::all_tables(auditor, AclPermission::Read))
.await??;
}
Rotate an operator
#![allow(unused)]
fn main() {
client.acl_grant(AclGrant::admin(new_operator)).await??;
client.acl_revoke(AclGrant::admin(old_operator)).await??;
}
Check your own access
#![allow(unused)]
fn main() {
let grants = client.my_permissions().await??;
let can_insert_users = grants.iter().any(|g| {
g.permission == AclPermission::Admin
|| (g.permission == AclPermission::Insert
&& g.table.as_deref().is_none_or(|t| t == "users"))
});
}
Client API (IC)
Note: This is the IC-specific client API guide. For general wasm-dbms documentation, see the generic docs.
- Client API (IC)
Overview
The ic-dbms-client crate provides type-safe Rust clients for interacting with ic-dbms canisters. Instead of manually
constructing Candid calls, you use a high-level API that handles serialization and error handling.
Benefits:
- Type-safe operations with compile-time checking
- Automatic Candid encoding/decoding
- Consistent API across different environments
- Built-in error handling
Client Types
ic-dbms provides three client implementations for different use cases:
| Client | Use Case | Feature Flag |
|---|---|---|
IcDbmsCanisterClient | Inter-canister calls (inside IC canisters) | Default |
IcDbmsAgentClient | External applications (frontend, backend, CLI) | ic-agent |
IcDbmsPocketIcClient | Integration tests with PocketIC | pocket-ic |
IcDbmsCanisterClient
For calls from one IC canister to another:
#![allow(unused)]
fn main() {
use ic_dbms_client::prelude::{IcDbmsCanisterClient, Client as _};
use candid::Principal;
// In your canister code
let dbms_canister_id = Principal::from_text("rrkah-fqaaa-aaaaa-aaaaq-cai").unwrap();
let client = IcDbmsCanisterClient::new(dbms_canister_id);
// Use the client
let users = client.select::<User>(User::table_name(), query, None).await??;
}
IcDbmsAgentClient
For external applications using the IC Agent:
#![allow(unused)]
fn main() {
use ic_dbms_client::prelude::{IcDbmsAgentClient, Client as _};
use ic_agent::Agent;
use candid::Principal;
// Create an IC Agent (with identity, etc.)
let agent = Agent::builder()
.with_url("https://ic0.app")
.with_identity(identity)
.build()?;
agent.fetch_root_key().await?; // Only needed for local replica
let dbms_canister_id = Principal::from_text("rrkah-fqaaa-aaaaa-aaaaq-cai").unwrap();
let client = IcDbmsAgentClient::new(&agent, dbms_canister_id);
// Use the client
let users = client.select::<User>(User::table_name(), query, None).await??;
}
IcDbmsPocketIcClient
For integration tests using PocketIC:
#![allow(unused)]
fn main() {
use ic_dbms_client::prelude::{IcDbmsPocketIcClient, Client as _};
use pocket_ic::nonblocking::PocketIc;
use candid::Principal;
let pic = PocketIc::new().await;
// ... setup canister ...
let client = IcDbmsPocketIcClient::new(
canister_id,
caller_principal, // The principal making calls
&pic
);
// Use the client in tests
let users = client.select::<User>(User::table_name(), query, None).await??;
}
Installation
Add ic-dbms-client to your Cargo.toml:
For canister development (inter-canister calls):
[dependencies]
ic-dbms-client = "0.11"
For external applications:
[dependencies]
ic-dbms-client = { version = "0.11", features = ["ic-agent"] }
For integration tests:
[dev-dependencies]
ic-dbms-client = { version = "0.11", features = ["pocket-ic"] }
For SQL endpoints: enable the sql feature, together with any other
feature you need:
[dependencies]
ic-dbms-client = { version = "0.11", features = ["sql"] }
The Client Trait
All clients implement the Client trait, providing a consistent API:
#![allow(unused)]
fn main() {
// Simplified: every method returns
// `impl Future<Output = IcDbmsCanisterClientResult<IcDbmsResult<T>>>`.
pub trait Client {
fn principal(&self) -> Principal;
// CRUD Operations
async fn insert<T: TableSchema>(
&self,
table: &str,
record: T::Insert,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
async fn select<T: TableSchema>(
&self,
table: &str,
query: Query,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<Vec<T::Record>>>;
async fn select_raw(
&self,
table: &str,
query: Query,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<SelectOutput>>;
async fn aggregate<T: TableSchema>(
&self,
table: &str,
query: Query,
aggregates: Vec<AggregateFunction>,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<Vec<AggregatedRow>>>;
async fn update<T: TableSchema>(
&self,
table: &str,
patch: T::Update,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<u64>>;
async fn delete<T: TableSchema>(
&self,
table: &str,
behaviour: DeleteBehavior,
filter: Option<Filter>,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<u64>>;
// Transactions
async fn begin_transaction(&self) -> IcDbmsCanisterClientResult<IcDbmsResult<TransactionId>>;
async fn commit(&self, tx: TransactionId) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
async fn rollback(&self, tx: TransactionId) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
// Access control
async fn acl_grant(&self, grant: AclGrant) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
async fn acl_revoke(&self, grant: AclGrant) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
async fn acl_list(&self) -> IcDbmsCanisterClientResult<IcDbmsResult<Vec<AclGrant>>>;
async fn my_permissions(&self) -> IcDbmsCanisterClientResult<IcDbmsResult<Vec<AclGrant>>>;
// Schema Migrations
async fn has_drift(&self) -> IcDbmsCanisterClientResult<IcDbmsResult<bool>>;
async fn pending_migrations(
&self,
) -> IcDbmsCanisterClientResult<IcDbmsResult<Vec<MigrationOp>>>;
async fn migrate(
&self,
policy: MigrationPolicy,
) -> IcDbmsCanisterClientResult<IcDbmsResult<()>>;
// SQL (requires the `sql` feature)
async fn sql(
&self,
query: &str,
params: Vec<Value>,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<SqlResult>>;
async fn sql_query(
&self,
query: &str,
params: Vec<Value>,
tx: Option<TransactionId>,
) -> IcDbmsCanisterClientResult<IcDbmsResult<SqlResult>>;
}
}
Note the double Result:
- Outer
Result: Network/communication errors - Inner
Result: Business logic errors (IcDbmsError)
Operations
Insert
#![allow(unused)]
fn main() {
use ic_dbms_client::prelude::Client as _;
use my_schema::{User, UserInsertRequest};
let user = UserInsertRequest {
id: 1.into(),
name: "Alice".into(),
email: "alice@example.com".into(),
};
// Without transaction
client.insert::<User>(User::table_name(), user, None).await??;
// With transaction
client.insert::<User>(User::table_name(), user, Some(tx_id)).await??;
}
Select
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::*;
// Select all
let query = Query::builder().all().build();
let users: Vec<UserRecord> = client
.select::<User>(User::table_name(), query, None)
.await??;
// Select with filter
let query = Query::builder()
.and_where(Filter::eq("status", Value::Text("active".into())))
.order_by_desc("created_at")
.limit(10)
.build();
let users = client.select::<User>(User::table_name(), query, None).await??;
}
Untyped Select
All three client implementations expose select_raw(table, query, transaction_id)
for the shared select endpoint. It returns SelectOutput, without requiring a
record type: Raw for a single-table query or Joined when the query has joins.
This replaces the previous vector return type; use as_raw() to access
single-table rows or as_joined() to access shared join columns and value rows.
Both accessors return None for the other variant.
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{Query, SelectOutput};
use ic_dbms_client::prelude::Client as _;
let result: SelectOutput = client
.select_raw("users", Query::builder().all().build(), None)
.await??;
for row in result.iter() {
for (column, value) in row.iter() {
println!("{name}: {value:?}", name = column.name);
}
}
let query = Query::builder()
.all()
.inner_join("posts", "users.id", "posts.user")
.build();
let result = client.select_raw("users", query, None).await??;
if let Some(joined) = result.as_joined() {
// Qualify names when both tables have an id column.
let index = joined.column_index("posts.id").expect("posts.id column");
for values in &joined.rows {
println!("Post: {id:?}", id = values[index]);
}
}
}
len() and is_empty() report row counts for either variant. The shared
iter() borrows column-value pairs without cloning or expanding joined
metadata. Joined results retain columns even when empty; empty raw results
contain no column metadata. See the engine’s
join query documentation for query
construction and semantics.
Aggregate
Aggregate queries dispatch to the per-table aggregate_<table> endpoint
generated by DbmsCanister. The pipeline (WHERE -> DISTINCT -> GROUP BY
-> aggregate computation -> HAVING -> ORDER BY -> OFFSET/LIMIT) is
described in the Query API reference.
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{AggregateFunction, AggregatedValue, Filter, Query, Uint64, Value};
// COUNT(*) of all rows
let result = client
.aggregate::<User>(
User::table_name(),
Query::default(),
vec![AggregateFunction::Count(None)],
None,
)
.await??;
assert!(matches!(result[0].values[0], AggregatedValue::Count(_)));
// GROUP BY + HAVING: rows per role, only roles with more than 5 users
let query = Query::builder()
.group_by(&["role"])
.having(Filter::gt("agg0", Value::Uint64(Uint64(5))))
.order_by_desc("agg0")
.build();
let result = client
.aggregate::<User>(
User::table_name(),
query,
vec![AggregateFunction::Count(None)],
None,
)
.await??;
}
HAVING and ORDER BY reference aggregate outputs by their positional name
agg{N} (agg0 is the first aggregate, agg1 the second, …). They may
also reference any column listed in group_by.
Update
#![allow(unused)]
fn main() {
use my_schema::UserUpdateRequest;
let update = UserUpdateRequest {
email: Some("new@example.com".into()),
where_clause: Some(Filter::eq("id", Value::Uint32(1.into()))),
..Default::default()
};
let affected_rows: u64 = client
.update::<User>(User::table_name(), update, None)
.await??;
}
Delete
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::DeleteBehavior;
// Delete with filter
let deleted: u64 = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Restrict,
Some(Filter::eq("id", Value::Uint32(1.into()))),
None
)
.await??;
// Delete all (be careful!)
let deleted: u64 = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Cascade,
None, // No filter = all records
None
)
.await??;
}
Transactions
#![allow(unused)]
fn main() {
// Begin transaction
let tx_id = client.begin_transaction().await??;
// Perform operations
client.insert::<User>(User::table_name(), user1, Some(tx_id)).await??;
client.insert::<User>(User::table_name(), user2, Some(tx_id)).await??;
// Commit or rollback
match some_condition {
true => client.commit(tx_id).await??,
false => client.rollback(tx_id).await??,
}
}
Schema Migrations
Three admin-gated methods inspect and apply schema drift. The Candid
endpoints behind them (has_drift query, pending_migrations query, migrate
update) are emitted by #[derive(DbmsCanister)]. See the
IC migrations guide for the upgrade workflow.
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{MigrationOp, MigrationPolicy};
// O(1) once cached on the canister side. True iff a migration is needed.
let drift: bool = client.has_drift().await??;
if !drift {
return Ok(());
}
// Plan without applying. Always recomputes; safe to call during drift.
let plan: Vec<MigrationOp> = client.pending_migrations().await??;
for op in &plan {
eprintln!(" {op:?}");
}
// Apply. Refuses DropTable / DropColumn unless allow_destructive is set.
client.migrate(MigrationPolicy::default()).await??;
// Equivalent to:
client
.migrate(MigrationPolicy { allow_destructive: false })
.await??;
}
migrate is idempotent — when there is no drift, the call is a cheap no-op.
Access Control
#![allow(unused)]
fn main() {
use candid::Principal;
use ic_dbms_api::prelude::{AclGrant, AclPermission};
// Grant admin
let operator = Principal::from_text("aaaaa-aa").unwrap();
client.acl_grant(AclGrant::admin(operator)).await??;
// Let a service insert into one table only
client
.acl_grant(AclGrant::table(service, AclPermission::Insert, "users"))
.await??;
// List grants
for grant in client.acl_list().await?? {
println!("{} -> {} on {:?}", grant.principal, grant.permission, grant.table);
}
// Revoke admin (fails with AclError::LastAdmin for the last one)
client.acl_revoke(AclGrant::admin(operator)).await??;
// Own grants, allowed for everyone
let mine = client.my_permissions().await??;
}
SQL
With the sql feature, sql runs any statement through the update
endpoint and sql_query runs a SELECT through the query endpoint:
#![allow(unused)]
fn main() {
let rows = client
.sql_query("SELECT name FROM users WHERE id = ?", vec![Value::from(1u32)], None)
.await??;
}
See the SQL guide for results, transactions and permissions.
Error Handling
Client operations return nested Results:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{DbmsError, IcDbmsError, QueryError};
// Full error handling
match client.insert::<User>(User::table_name(), user, None).await {
Ok(Ok(())) => {
println!("Insert successful");
}
Ok(Err(db_error)) => {
// Database error (validation, constraint violation, etc.)
match db_error {
IcDbmsError::Dbms(DbmsError::Query(QueryError::PrimaryKeyConflict)) => {
println!("User with this ID already exists");
}
IcDbmsError::Dbms(DbmsError::Validation(msg)) => {
println!("Validation failed: {}", msg);
}
_ => println!("Database error: {:?}", db_error),
}
}
Err(call_error) => {
// Network/canister call error
println!("Call failed: {:?}", call_error);
}
}
}
Simplified with ??:
#![allow(unused)]
fn main() {
// Propagate both error types
client.insert::<User>(User::table_name(), user, None).await??;
}
Examples
Inter-Canister Communication
A backend canister calling the database canister:
#![allow(unused)]
fn main() {
use candid::Principal;
use ic_cdk::update;
use ic_dbms_client::prelude::{Client as _, IcDbmsCanisterClient};
const DBMS_CANISTER: &str = "rrkah-fqaaa-aaaaa-aaaaq-cai";
#[update]
async fn create_user(name: String, email: String) -> Result<u32, String> {
let client = IcDbmsCanisterClient::new(Principal::from_text(DBMS_CANISTER).unwrap());
let user_id = generate_id();
let user = UserInsertRequest {
id: user_id.into(),
name: name.into(),
email: email.into(),
};
client
.insert::<User>(User::table_name(), user, None)
.await
.map_err(|e| format!("Call failed: {:?}", e))?
.map_err(|e| format!("Insert failed: {:?}", e))?;
Ok(user_id)
}
#[update]
async fn get_users() -> Result<Vec<UserRecord>, String> {
let client = IcDbmsCanisterClient::new(Principal::from_text(DBMS_CANISTER).unwrap());
let query = Query::builder().all().build();
client
.select::<User>(User::table_name(), query, None)
.await
.map_err(|e| format!("Call failed: {:?}", e))?
.map_err(|e| format!("Query failed: {:?}", e))
}
}
External Application
A CLI tool or backend service:
use candid::Principal;
use ic_agent::Agent;
use ic_agent::identity::BasicIdentity;
use ic_dbms_api::prelude::{Query, TableSchema as _};
use ic_dbms_client::prelude::{Client as _, IcDbmsAgentClient};
use my_schema::User;
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
// Load identity from PEM file
let identity = BasicIdentity::from_pem_file("identity.pem")?;
// Create agent
let agent = Agent::builder()
.with_url("https://ic0.app")
.with_identity(identity)
.build()?;
// For local development, fetch root key
// agent.fetch_root_key().await?;
let canister_id = Principal::from_text("rrkah-fqaaa-aaaaa-aaaaq-cai")?;
let client = IcDbmsAgentClient::new(&agent, canister_id);
// List all users
let query = Query::builder().all().build();
let users = client
.select::<User>(User::table_name(), query, None)
.await??;
// Record fields are `Option` because a query can select a subset of columns.
for user in users {
println!("User: {:?} ({:?})", user.name, user.email);
}
Ok(())
}
Integration Testing
Testing with PocketIC:
#![allow(unused)]
fn main() {
use candid::{Principal, encode_one};
use ic_dbms_client::prelude::{Client as _, IcDbmsPocketIcClient};
use pocket_ic::nonblocking::PocketIc;
#[tokio::test]
async fn test_user_crud() {
// Setup PocketIC
let pic = PocketIc::new().await;
// Create and install canister
let canister_id = pic.create_canister().await;
pic.add_cycles(canister_id, 2_000_000_000_000).await;
let wasm = std::fs::read("path/to/canister.wasm").unwrap();
let init_args = IcDbmsCanisterArgs::Init(IcDbmsCanisterInitArgs {
allowed_principals: Some(vec![admin_principal]),
});
pic.install_canister(canister_id, wasm, encode_one(init_args).unwrap(), None)
.await;
// Create client
let client = IcDbmsPocketIcClient::new(canister_id, admin_principal, &pic);
// Test insert
let user = UserInsertRequest {
id: 1.into(),
name: "Test User".into(),
email: "test@example.com".into(),
};
client
.insert::<User>(User::table_name(), user, None)
.await
.unwrap()
.unwrap();
// Test select
let query = Query::builder().all().build();
let users = client
.select::<User>(User::table_name(), query, None)
.await
.unwrap()
.unwrap();
assert_eq!(users.len(), 1);
// Record fields are `Option` because a query can select a subset of columns.
assert_eq!(users[0].name.as_ref().unwrap().as_str(), "Test User");
// Test update
let update = UserUpdateRequest {
name: Some("Updated User".into()),
where_clause: Some(Filter::eq("id", Value::Uint32(1.into()))),
..Default::default()
};
let affected = client
.update::<User>(User::table_name(), update, None)
.await
.unwrap()
.unwrap();
assert_eq!(affected, 1);
// Test delete
let deleted = client
.delete::<User>(
User::table_name(),
DeleteBehavior::Restrict,
Some(Filter::eq("id", Value::Uint32(1.into()))),
None,
)
.await
.unwrap()
.unwrap();
assert_eq!(deleted, 1);
// Verify deletion
let users = client
.select::<User>(User::table_name(), Query::builder().all().build(), None)
.await
.unwrap()
.unwrap();
assert_eq!(users.len(), 0);
}
}
Schema Migrations (IC)
Note: This is the IC-specific migrations guide. The schema-design rules (
#[default],#[renamed_from],#[migrate], theMigratetrait,MigrationOpsemantics) are identical to the generic backend; see the generic Schema Migrations Guide and the Migrations Reference for the conceptual material. This page covers only what changes when the database lives inside an IC canister.
Overview
A canister upgrade replaces the WASM but keeps stable memory. If the new
binary’s #[derive(Table)] schemas differ from the snapshots persisted on
disk, the DBMS enters drift state and refuses CRUD until you call migrate.
The ACL lives in the database too, so ACL endpoints fail with SchemaDrift as
well; canister controllers may always call the migration endpoints.
Open transactions do not survive an upgrade: the engine keeps them on the
heap, and so does the canister’s transaction ownership ledger. Both are
discarded together; the generated pre_upgrade hook clears the ledger
explicitly. Commit or roll back before upgrading. Because the macro defines
pre_upgrade, define only post_upgrade yourself. Discard every transaction
ID from before the upgrade because the engine may reuse those numeric IDs.
The drift hash is recomputed lazily, on the first has_drift /
pending_migrations / CRUD call after boot, and cached on the DBMS context.
The canister simply boots, declares drift on first access, and waits for the
operator (or a post_upgrade snippet you write yourself) to call migrate.
Generated Endpoints
#[derive(DbmsCanister)] emits three additional endpoints alongside the
per-table CRUD methods:
| Endpoint | Kind | Purpose |
|---|---|---|
has_drift | query | O(1) once cached; true iff a migration is needed. |
pending_migrations | query | Returns the planned Vec<MigrationOp> without applying. |
migrate | update | Plans, validates, sorts, and applies the diff atomically. |
All three are gated: the caller must hold AclPermission::Admin or be a
controller of the canister. Anonymous and unlisted principals are rejected
before the DBMS is touched.
migrate is an update because it journals writes. has_drift and
pending_migrations are query calls and consume no cycles for the caller
beyond the standard query overhead.
Candid Types
The Candid signatures are:
type MigrationPolicy = record { allow_destructive : bool };
type MigrationOp = variant {
CreateTable : record { name : text; schema : TableSchemaSnapshot };
DropTable : record { name : text };
AddColumn : record { table : text; column : ColumnSnapshot };
DropColumn : record { table : text; column : text };
RenameColumn : record { table : text; old : text; new : text };
AlterColumn : record { table : text; column : text; changes : ColumnChanges };
WidenColumn : record { table : text; column : text; old_type : DataTypeSnapshot; new_type : DataTypeSnapshot };
TransformColumn : record { table : text; column : text; old_type : DataTypeSnapshot; new_type : DataTypeSnapshot };
AddIndex : record { table : text; index : IndexSnapshot };
DropIndex : record { table : text; index : IndexSnapshot };
};
has_drift : () -> (variant { Ok : bool; Err : IcDbmsError }) query;
pending_migrations : () -> (variant { Ok : vec MigrationOp; Err : IcDbmsError }) query;
migrate : (MigrationPolicy)
-> (variant { Ok; Err : IcDbmsError });
Snapshot types (TableSchemaSnapshot, ColumnSnapshot, IndexSnapshot,
ForeignKeySnapshot, DataTypeSnapshot, OnDeleteSnapshot,
ColumnChanges) are the same Candid records the snapshot reference describes
in the generic schema reference. They are
exported automatically by ic_cdk::export_candid!().
Upgrade Workflow
The end-to-end flow for a schema-changing release:
- Edit the schema. Modify the
#[derive(Table)]structs and add#[default]/#[renamed_from]/#[migrate]as needed. - Build the canister.
just build_allcompiles towasm32-unknown-unknown, shrinks the WASM, and extracts the new.did. - Deploy via
dfx canister install --mode upgrade. Stable memory carries over untouched. - Inspect drift. Call
has_driftfromdfx, an admin tool, or aClient. Skip the rest iffalse. - Plan. Call
pending_migrationsand review the returned ops. Look in particular for unintendedDropTable/DropColumnops, which usually signal a typo in#[table = "..."]or a missing#[renamed_from]. - Apply. Call
migrate(record { allow_destructive = false }). If the plan contains a deliberate destructive op, setallow_destructive = trueonly after the review step. - Verify. Re-run
has_drift; expectfalse. CRUD endpoints now work again.
migrate is idempotent: when there is no drift, the call is a cheap no-op.
Calling From a Client
The three methods are part of the Client trait. The signatures are
identical across IcDbmsCanisterClient, IcDbmsAgentClient, and
IcDbmsPocketIcClient:
#![allow(unused)]
fn main() {
async fn has_drift(&self) -> Result<IcDbmsResult<bool>>;
async fn pending_migrations(&self) -> Result<IcDbmsResult<Vec<MigrationOp>>>;
async fn migrate(&self, policy: MigrationPolicy) -> Result<IcDbmsResult<()>>;
}
The outer Result wraps transport / canister-call failures; the inner
IcDbmsResult wraps IcDbmsError (including
IcDbmsError::Dbms(DbmsError::Migration(MigrationError::...))).
Inter-Canister
#![allow(unused)]
fn main() {
use candid::Principal;
use ic_dbms_api::prelude::MigrationPolicy;
use ic_dbms_client::prelude::{Client as _, IcDbmsCanisterClient};
#[ic_cdk::update]
async fn heal_schema(canister: Principal) -> Result<u64, String> {
let client = IcDbmsCanisterClient::new(canister);
let drifted = client
.has_drift()
.await
.map_err(|e| e.to_string())?
.map_err(|e| e.to_string())?;
if !drifted {
return Ok(0);
}
let ops = client
.pending_migrations()
.await
.map_err(|e| e.to_string())?
.map_err(|e| e.to_string())?;
client
.migrate(MigrationPolicy::default())
.await
.map_err(|e| e.to_string())?
.map_err(|e| e.to_string())?;
Ok(ops.len() as u64)
}
}
External Agent
use candid::Principal;
use ic_agent::Agent;
use ic_dbms_api::prelude::MigrationPolicy;
use ic_dbms_client::prelude::{Client as _, IcDbmsAgentClient};
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
let agent = Agent::builder()
.with_url("https://ic0.app")
.with_identity(load_identity()?)
.build()?;
let canister = Principal::from_text("rrkah-fqaaa-aaaaa-aaaaq-cai")?;
let client = IcDbmsAgentClient::new(&agent, canister);
if client.has_drift().await?? {
let plan = client.pending_migrations().await??;
eprintln!("planning {} ops", plan.len());
for op in &plan {
eprintln!(" {op:?}");
}
client
.migrate(MigrationPolicy {
allow_destructive: false,
})
.await??;
}
Ok(())
}
PocketIC Tests
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::MigrationPolicy;
use ic_dbms_client::prelude::{Client as _, IcDbmsPocketIcClient};
#[tokio::test]
async fn upgrade_heals_drift() {
let pic = pocket_ic::nonblocking::PocketIc::new().await;
let canister = install_v1_canister(&pic).await;
insert_fixtures(&pic, canister).await;
upgrade_to_v2(&pic, canister).await;
let client = IcDbmsPocketIcClient::new(canister, admin_principal(), &pic);
assert!(client.has_drift().await.unwrap().unwrap());
let plan = client.pending_migrations().await.unwrap().unwrap();
assert!(!plan.is_empty());
client
.migrate(MigrationPolicy::default())
.await
.unwrap()
.unwrap();
assert!(!client.has_drift().await.unwrap().unwrap());
}
}
Driving Migration From post_upgrade
For canisters where the deployment pipeline already owns the upgrade flow,
you can wire migrate directly into a #[ic_cdk::post_upgrade] hook so the
schema heals before the first CRUD call lands.
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{Database as _, IcDbmsCanisterArgs, MigrationPolicy};
use ic_dbms_canister::prelude::{
CanisterSchema, DBMS_CONTEXT, DatabaseSchema, DbmsCanister, WasmDbmsDatabase,
};
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts")]
pub struct MyCanister;
#[ic_cdk::post_upgrade]
fn post_upgrade(_args: IcDbmsCanisterArgs) {
DBMS_CONTEXT.with(|ctx| {
let mut db = WasmDbmsDatabase::oneshot(ctx, CanisterSchema::new(MyCanister));
let drifted = db
.has_drift()
.unwrap_or_else(|err| ic_cdk::trap(format!("drift check failed: {err}")));
if drifted {
if let Err(err) = db.migrate(MigrationPolicy::default()) {
ic_cdk::trap(format!("migration failed: {err}"));
}
}
});
}
}
The table registry is reloaded from stable memory when the canister starts,
so post_upgrade does not register the tables again: migrate creates the
tables added by the new release.
Note: Always open the database through
CanisterSchema::new(...). The reservedic_dbms_acltable is part of the persisted schema; a bareMyCanisterschema would report drift andmigratewould drop it.
This pattern is convenient but trades safety for convenience:
- An accidental schema change ships destructive ops to production with no human review.
- A bug in
transform_columntraps the canister on upgrade. MigrationPolicy::default()(i.e.allow_destructive: false) refuses destructive ops, but everything else applies silently.
For high-stakes deployments prefer the operator-driven flow below.
Operator-Driven Migration
The recommended flow for production canisters:
- Upgrade the canister. The new WASM boots in drift state.
- Run a one-shot script (CLI / admin canister /
dfx) that callspending_migrations, prints the plan, and waits for confirmation. - On confirmation, call
migrate.
dfx example:
dfx canister call my_dbms has_drift
# (variant { Ok = true })
dfx canister call my_dbms pending_migrations
# (variant { Ok = vec { ... } })
dfx canister call my_dbms migrate '(record { allow_destructive = false })'
# (variant { Ok })
Until migrate succeeds the canister rejects every CRUD endpoint with
MigrationError::SchemaDrift, so any traffic that arrives between the
upgrade and the operator action receives a clear, structured error.
Error Handling
Migration errors propagate through
IcDbmsError::Dbms(DbmsError::Migration(MigrationError)).
The variants worth handling explicitly on the client:
| Variant | Meaning | Caller action |
|---|---|---|
SchemaDrift | CRUD called while drift is set. | Call migrate. |
IncompatibleType | Column type changed without a widening or transform. | Add a transform_column arm or a release that widens via an intermediate type. |
DefaultMissing | New non-nullable column with no #[default] or default_value. | Add the default; redeploy. |
ConstraintViolation | Tightening rejected an existing row. | Backfill the offending rows in a prior release. |
DestructiveOpDenied | Plan contained DropTable / DropColumn and policy disallowed it. | Re-run with allow_destructive: true after operator review. |
TransformAborted | User transform_column returned Err. | Fix the transform; redeploy. |
WideningIncompatible | WidenColumn outside the widening whitelist with no transform_column handler. | Provide a transform_column impl or split the change across multiple releases. |
TransformReturnedNone | Migrate::transform_column returned Ok(None) for a column that needs one. | Implement the transform branch. |
ForeignKeyViolation | Add-FK tightening found a row referencing a missing target. | Clean up orphan rows in a prior release. |
Tip: never unwrap migrate in a post_upgrade hook — a panic there bricks
the canister. Trap with a descriptive message instead, or fall back to the
operator-driven flow.
Drift While Serving Traffic
CRUD endpoints fail fast when drift is set: the very first line of every
select_* / insert_* / update_* / delete_* / aggregate_* handler
checks the cached drift flag and returns Err(MigrationError::SchemaDrift)
without touching the journal. Cost is a single boolean load.
ACL endpoints read the ic_dbms_acl table and therefore fail with
SchemaDrift too. The migration endpoints themselves are exempt and may be
called by any controller; pending_migrations is safe to call regardless of
state.
After a successful migrate, the in-memory drift flag is cleared inside the
same journal session that wrote the new snapshots, so the next CRUD call
proceeds against the new schema with no extra round trip.
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;
| Endpoint | Call type | Runs |
|---|---|---|
sql | update | Any supported statement |
sql_query | query | SELECT 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:
| Variant | Returned by |
|---|---|
Rows(rows) | SELECT; each row is a list of column definition and value pairs |
RowsAffected(n) | INSERT, UPDATE, DELETE |
TxBegin(id) | BEGIN |
TxCommit | COMMIT |
TxRollback | ROLLBACK |
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:
| Statement | Required permission |
|---|---|
SELECT | Read on the FROM table and on every joined table |
INSERT | Insert on the table |
UPDATE | Update on the table |
DELETE | Delete on the table |
DELETE ... CASCADE | Delete on the table and on every table referencing it |
BEGIN | Any grant |
COMMIT, ROLLBACK | Ownership 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.
Schema Reference (IC)
Note: This is the IC-specific schema reference. For complete Table macro details, column attributes, generated types, and best practices, see the generic schema reference.
Overview
When deploying wasm-dbms on the Internet Computer, your schema definitions need additional IC-specific derives, the #[candid] attribute, and a canister generation macro. The core Table macro, column attributes (#[primary_key], #[unique], #[index], #[foreign_key(...)], #[sanitizer(...)], #[validate(...)], #[custom_type], #[alignment], plus the migration attributes #[default], #[renamed_from], #[migrate]), and generated types (Record, InsertRequest, UpdateRequest, ForeignFetcher) work exactly as described in the generic schema reference. This document covers only the IC-specific additions.
Migrations on the IC: schema migrations work the same as on the generic backend, but the
DbmsCanistermacro additionally emits thehas_drift,pending_migrations, andmigrateCandid endpoints (see Migration Endpoints below). See the Schema Migrations Reference, the generic Schema Migrations Guide, and the IC Schema Migrations Guide.
IC-Specific Required Derives
Every table struct for IC deployment must include CandidType and Deserialize in addition to the standard Table and Clone derives. You must also add the #[candid] attribute so that generated types (Record, InsertRequest, UpdateRequest) derive CandidType, Serialize, and Deserialize as well:
#![allow(unused)]
fn main() {
use candid::{CandidType, Deserialize};
use ic_dbms_api::prelude::*;
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "users"]
pub struct User {
#[primary_key]
pub id: Uint32,
pub name: Text,
}
}
| Derive / Attribute | Required for IC | Purpose |
|---|---|---|
Table | Yes | Generates table schema and related types |
CandidType | Yes (IC-specific) | Enables Candid serialization for the table struct |
Deserialize | Yes (IC-specific) | Enables deserialization from Candid wire format |
#[candid] | Yes (IC-specific) | Adds Candid/Serde derives to generated Record, InsertRequest, UpdateRequest types |
Clone | Yes | Required by the macro system |
Debug | Recommended | Useful for debugging |
PartialEq, Eq | Recommended | Useful for comparisons in tests |
Without CandidType, Deserialize, and #[candid], the generated canister API will not compile because Candid is the serialization format used for all IC inter-canister calls.
DatabaseSchema Macro
The DatabaseSchema derive macro generates a DatabaseSchema<M, A> trait implementation that provides schema dispatch – routing database operations to the correct table by name at runtime. This is required by the DbmsCanister macro.
The DatabaseSchema macro is provided by wasm-dbms-macros and re-exported through the ic-dbms-canister prelude.
#![allow(unused)]
fn main() {
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
// `DbmsCanister` needs every type generated by `Table` in scope.
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts")]
pub struct MyDbmsCanister;
}
The macro reads the #[tables(...)] attribute and generates:
- A
DatabaseSchema<M, A>trait implementation that dispatchesselect,insert,update,delete, andselect_rawcalls to the correct table by name - A
register_tablesassociated method for convenient table registration during canister initialization
DbmsCanister Macro
The DbmsCanister macro is an IC-specific procedural macro that generates a complete Internet Computer canister API from your table definitions. It is provided by the ic-dbms-canister crate. It requires the DatabaseSchema derive to also be present on the same struct.
Basic Usage
#![allow(unused)]
fn main() {
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
// `DbmsCanister` needs every type generated by `Table` in scope.
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts", Comment = "comments")]
pub struct MyDbmsCanister;
ic_cdk::export_candid!();
}
Format: #[tables(StructName = "table_name", ...)]
StructNameis the Rust struct name (must be in scope viause)"table_name"is the table name matching the#[table = "..."]attribute on the struct
Generated Candid API
For each table, the macro generates five CRUD/aggregate endpoints plus shared transaction and ACL endpoints:
type JoinResultSet = record {
columns : vec JoinColumnDef;
rows : vec vec Value;
};
type SelectOutput = variant {
Raw : vec vec record { JoinColumnDef; Value };
Joined : JoinResultSet;
};
type Result_SelectOutput = variant { Ok : SelectOutput; Err : IcDbmsError };
service : (IcDbmsCanisterArgs) -> {
// Per-table CRUD (example for "users" table)
insert_users : (UserInsertRequest, opt nat64) -> (Result);
select_users : (Query, opt nat64) -> (Result_Vec_UserRecord) query;
aggregate_users : (Query, vec AggregateFunction, opt nat64) -> (Result_Vec_AggregatedRow) query;
update_users : (UserUpdateRequest, opt nat64) -> (Result_u64);
delete_users : (DeleteBehavior, opt Filter, opt nat64) -> (Result_u64);
// Per-table CRUD (example for "posts" table)
insert_posts : (PostInsertRequest, opt nat64) -> (Result);
select_posts : (Query, opt nat64) -> (Result_Vec_PostRecord) query;
aggregate_posts : (Query, vec AggregateFunction, opt nat64) -> (Result_Vec_AggregatedRow) query;
update_posts : (PostUpdateRequest, opt nat64) -> (Result_u64);
delete_posts : (DeleteBehavior, opt Filter, opt nat64) -> (Result_u64);
// Untyped select (shared)
select : (text, Query, opt nat64) -> (Result_SelectOutput) query;
// Transaction methods (shared)
begin_transaction : () -> (Result_TransactionId);
commit : (nat64) -> (Result);
rollback : (nat64) -> (Result);
// Access control (shared), see the Access Control guide
acl_grant : (AclGrant) -> (Result);
acl_revoke : (AclGrant) -> (Result);
acl_list : () -> (Result_Vec_AclGrant) query;
my_permissions : () -> (Result_Vec_AclGrant) query;
// Schema migrations (shared) — see Migration Endpoints below
has_drift : () -> (Result_bool) query;
pending_migrations : () -> (Result_Vec_MigrationOp) query;
migrate : (MigrationPolicy) -> (Result);
// SQL (only with the `sql` feature), see the SQL guide
sql : (text, vec Value, opt nat64) -> (Result_SqlResult);
sql_query : (text, vec Value, opt nat64) -> (Result_SqlResult) query;
}
Each endpoint checks the caller’s grants before running: per-table endpoints
need the matching Read, Insert, Update or Delete permission, the
untyped select needs Read on every table it touches, and ACL and migration
endpoints need Admin. The sql endpoints need the permission each statement
implies. See the
Access Control guide.
Lifecycle hooks: the derive defines init (registers the tables and the
reserved ACL table, stores an Admin grant for each of allowed_principals), inspect_message
(accepts every call) and pre_upgrade (clears the heap-only transaction
ownership ledger). Define post_upgrade yourself when you need one.
Method naming convention: {operation}_{table_name} (e.g., insert_users, select_posts, aggregate_users, delete_comments)
Parameter patterns:
opt nat64is the optional transaction IDselect,aggregateandsql_querymethods arequerycalls (no state changes, no cycles consumed)- All other methods are
updatecalls
Aggregate endpoint: aggregate_<table> runs Database::aggregate for that
table. The vec AggregateFunction parameter lists COUNT(*) / COUNT(col) /
SUM / AVG / MIN / MAX to compute per group; the Query carries
group_by, having, order_by, limit, and offset. See the
generic Query API reference for
type definitions and the aggregate pipeline.
Untyped select endpoint: select(table, query, transaction_id) returns
SelectOutput::Raw for queries without joins and SelectOutput::Joined for
queries with joins. Raw rows contain (JoinColumnDef, Value) pairs, with
table = None. Joined results store column definitions once in columns,
including source table names, and values in rows in the same column order.
Joined results retain column metadata even when no rows match.
This changes the Candid response from a vector of rows to a variant. Regenerate
Candid bindings and update callers to handle Raw and Joined. Typed
select_<table> endpoints retain their existing return types. Rust clients
use Client::select_raw for the shared endpoint; see
Untyped Select.
SQL endpoints: with the sql feature of ic-dbms-canister enabled,
the derive also emits sql and sql_query. Without the feature neither
endpoint exists. See the SQL guide.
Migration Endpoints
#[derive(DbmsCanister)] adds three admin-gated migration endpoints.
Behaviour, error semantics, and the operator workflow are documented in the
IC Schema Migrations Guide; this section is the
Candid signature reference.
type MigrationPolicy = record {
allow_destructive : bool;
};
type OnDeleteSnapshot = variant { Restrict; Cascade };
type DataTypeSnapshot = variant {
Int8; Int16; Int32; Int64;
Uint8; Uint16; Uint32; Uint64;
Float32; Float64; Decimal;
Boolean; Date; Datetime;
Blob; Text; Uuid; Json;
Custom : text;
};
type ForeignKeySnapshot = record {
table : text;
column : text;
on_delete : OnDeleteSnapshot;
};
type IndexSnapshot = record {
columns : vec text;
unique : bool;
};
type ColumnSnapshot = record {
name : text;
data_type : DataTypeSnapshot;
nullable : bool;
auto_increment : bool;
unique : bool;
primary_key : bool;
foreign_key : opt ForeignKeySnapshot;
default : opt Value;
};
type TableSchemaSnapshot = record {
version : nat8;
name : text;
primary_key : text;
alignment : nat32;
columns : vec ColumnSnapshot;
indexes : vec IndexSnapshot;
};
type ColumnChanges = record {
nullable : opt bool;
unique : opt bool;
auto_increment : opt bool;
primary_key : opt bool;
foreign_key : opt opt ForeignKeySnapshot;
};
type MigrationOp = variant {
CreateTable : record { name : text; schema : TableSchemaSnapshot };
DropTable : record { name : text };
AddColumn : record { table : text; column : ColumnSnapshot };
DropColumn : record { table : text; column : text };
RenameColumn : record { table : text; old : text; new : text };
AlterColumn : record { table : text; column : text; changes : ColumnChanges };
WidenColumn : record { table : text; column : text; old_type : DataTypeSnapshot; new_type : DataTypeSnapshot };
TransformColumn : record { table : text; column : text; old_type : DataTypeSnapshot; new_type : DataTypeSnapshot };
AddIndex : record { table : text; index : IndexSnapshot };
DropIndex : record { table : text; index : IndexSnapshot };
};
has_drift : () -> (variant { Ok : bool; Err : IcDbmsError }) query;
pending_migrations : () -> (variant { Ok : vec MigrationOp; Err : IcDbmsError }) query;
migrate : (MigrationPolicy)
-> (variant { Ok; Err : IcDbmsError });
has_driftisO(1)once the per-context drift flag is cached. CRUD endpoints early-returnIcDbmsError::Dbms(DbmsError::Migration(MigrationError::SchemaDrift))while drift is set. The ACL lives in the database too, so ACL endpoints fail the same way during drift; canister controllers may always call the three migration endpoints.pending_migrationsalways recomputes the diff. Safe to call during drift.migrateplans, validates againstMigrationPolicy, sorts ops into the deterministic apply order, and runs them inside a single journaled session. Failures roll the journal back and leave persisted snapshots untouched.
The IcDbmsError::Dbms(DbmsError::Migration(MigrationError)) variants
(SchemaDrift, IncompatibleType, MissingDefault, ConstraintViolation,
DestructiveOpDenied, TransformAborted, DataRewriteUnsupported) are
documented in the errors reference.
Init arguments:
The generated canister expects IcDbmsCanisterArgs at initialization:
type IcDbmsCanisterArgs = variant {
Init : IcDbmsCanisterInitArgs;
Upgrade;
};
type IcDbmsCanisterInitArgs = record {
allowed_principals : opt vec principal;
};
Each listed principal (or the deployer when the list is null or empty)
receives an AclGrant::admin grant. The derive also registers the reserved
ic_dbms_acl table; table names starting with ic_dbms_ are rejected at
compile time.
Candid Integration
CandidType and Deserialize
These derives are needed because the IC uses Candid as its interface description language. All data crossing canister boundaries must be Candid-serializable.
The ic-dbms-api types (via wasm-dbms-api) already implement CandidType and Deserialize, so your struct only needs the derives:
#![allow(unused)]
fn main() {
use candid::{CandidType, Deserialize};
use ic_dbms_api::prelude::*;
// All field types (Uint32, Text, DateTime, etc.) already implement CandidType
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "events"]
pub struct Event {
#[primary_key]
pub id: Uuid,
pub name: Text,
pub date: DateTime,
pub metadata: Nullable<Json>,
}
}
Candid Export
The ic_cdk::export_candid!() macro at the end of your canister lib.rs generates the .did file that describes your canister’s interface. This is required for:
dfxdeployment- Frontend integration
- Inter-canister calls with type checking
- Candid UI interaction
#![allow(unused)]
fn main() {
// canister/src/lib.rs
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
// `DbmsCanister` needs every type generated by `Table` in scope.
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts")]
pub struct MyDbmsCanister;
// This MUST be at the end of the file
ic_cdk::export_candid!();
}
Complete IC Example
#![allow(unused)]
fn main() {
// schema/src/lib.rs
use candid::{CandidType, Deserialize};
use ic_dbms_api::prelude::*;
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "users"]
pub struct User {
#[primary_key]
pub id: Uint32,
#[sanitizer(TrimSanitizer)]
#[validate(MaxStrlenValidator(100))]
pub name: Text,
#[unique]
#[sanitizer(TrimSanitizer)]
#[sanitizer(LowerCaseSanitizer)]
#[validate(EmailValidator)]
pub email: Text,
pub created_at: DateTime,
pub is_active: Boolean,
}
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "posts"]
pub struct Post {
#[primary_key]
pub id: Uuid,
#[validate(MaxStrlenValidator(200))]
pub title: Text,
pub content: Text,
pub published: Boolean,
#[index(group = "author_date")]
#[foreign_key(entity = "User", table = "users", column = "id")]
pub author_id: Uint32,
pub metadata: Nullable<Json>,
#[index(group = "author_date")]
pub created_at: DateTime,
}
}
#![allow(unused)]
fn main() {
// canister/src/lib.rs
use ic_dbms_canister::prelude::{DatabaseSchema, DbmsCanister};
// `DbmsCanister` needs every type generated by `Table` in scope.
use my_schema::*;
#[derive(DatabaseSchema, DbmsCanister)]
#[tables(User = "users", Post = "posts")]
pub struct BlogDbmsCanister;
ic_cdk::export_candid!();
}
Data Types Reference (IC)
Note: This is the IC-specific data types reference. For the complete list of all data types, usage examples, and general documentation, see the generic data types reference.
Overview
All wasm-dbms data types are available in ic-dbms through ic_dbms_api::prelude::* (which re-exports wasm_dbms_api types). This document covers the IC-specific aspects: the Principal type (which is unique to the Internet Computer) and the Candid type mappings used for canister API serialization.
Principal Type
Principal is an Internet Computer-specific identifier type. It represents a canister ID, user identity, or the anonymous principal. This type is only meaningful in the IC context.
Usage
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::*;
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "users"]
pub struct User {
#[primary_key]
pub id: Uint32,
#[custom_type]
pub owner: Principal, // IC principal who owns this record
}
}
ic_dbms_api::prelude::Principal is a newtype around candid::Principal.
Wrap a candid::Principal with Principal(p) and read it back with .0.
Creating principals:
#![allow(unused)]
fn main() {
use candid::Principal;
// From text representation
let principal = Principal::from_text("aaaaa-aa").unwrap();
// Anonymous principal
let anon = Principal::anonymous();
// Caller principal (inside a canister)
let caller = ic_cdk::api::msg_caller();
// Management canister
let mgmt = Principal::management_canister();
}
Using in insert requests:
#![allow(unused)]
fn main() {
let user = UserInsertRequest {
id: 1.into(),
owner: Principal(ic_cdk::api::msg_caller()), // Store the caller's principal
};
client.insert::<User>(User::table_name(), user, None).await??;
}
Common Patterns
Recording ownership:
#![allow(unused)]
fn main() {
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "documents"]
pub struct Document {
#[primary_key]
pub id: Uuid,
pub title: Text,
#[custom_type]
pub owner: Principal, // Who created this
#[custom_type]
pub last_editor: Principal, // Who last modified this
}
}
Filtering by principal:
#![allow(unused)]
fn main() {
// Find all documents owned by the caller
let filter = Filter::eq("owner", Value::from(Principal(ic_cdk::api::msg_caller())));
let query = Query::builder().and_where(filter).build();
let my_docs = client.select::<Document>(Document::table_name(), query, None).await??;
}
Nullable principal (optional ownership):
#![allow(unused)]
fn main() {
#[derive(Debug, Table, CandidType, Deserialize, Clone, PartialEq, Eq)]
#[candid]
#[table = "tasks"]
pub struct Task {
#[primary_key]
pub id: Uint32,
pub title: Text,
#[custom_type]
pub assignee: Nullable<Principal>, // May be unassigned
}
}
Candid Type Mapping
When ic-dbms generates the Candid interface (.did file) for your canister, each wasm-dbms type maps to a specific Candid type. This mapping is important for frontend integration, inter-canister calls, and using the Candid UI.
| ic-dbms Type | Rust Type | Candid Type | Notes |
|---|---|---|---|
Uint8 | u8 | nat8 | |
Uint16 | u16 | nat16 | |
Uint32 | u32 | nat32 | |
Uint64 | u64 | nat64 | |
Int8 | i8 | int8 | |
Int16 | i16 | int16 | |
Int32 | i32 | int32 | |
Int64 | i64 | int64 | |
Decimal | rust_decimal::Decimal | text | Serialized as string for precision |
Text | String | text | |
Boolean | bool | bool | |
Date | year, month, day fields | record { year; month; day } | Structured record |
DateTime | date, time and offset fields | record { year; month; day; hour; minute; second; microsecond; timezone_offset_minutes } | Structured record; no now() constructor |
Blob | Vec<u8> | blob | |
Principal | candid::Principal | principal | IC-specific |
Uuid | uuid::Uuid | blob | 16 raw bytes |
Json | serde_json::Value | text | Serialized JSON string |
Nullable<T> | Option<T> | opt T | Candid optional |
Frontend integration example (JavaScript/TypeScript):
// Calling from a frontend using @dfinity/agent
const user = await actor.select_users({
filter: [{ Eq: ["name", { Text: "Alice" }] }],
order_by: [],
limit: [10n], // nat64 maps to bigint
columns: [],
with_tables: [],
}, []); // No transaction ID
// Principal values
import { Principal } from "@dfinity/principal";
const owner = Principal.fromText("aaaaa-aa");
ACL Types
The access control list exposes these types via Candid:
type AclPermission = variant {
Admin; Read; Insert; Update; Delete;
};
type AclRequirement = variant {
Permission : AclPermission;
AnyGrant;
};
type AclGrant = record {
principal : principal;
permission : AclPermission;
table : opt text;
};
type AclError = variant {
AccessDenied : record {
required : AclRequirement;
table : opt text;
};
AdminGrantWithTable;
AnonymousPrincipal;
InvalidTable : text;
LastAdmin;
};
An AclGrant with no table applies to every user table. Admin grants always
have no table and apply to every operation, including ACL management and
migrations.
AclRequirement::Permission identifies a specific permission required by an
operation. AclRequirement::AnyGrant is used when an unlisted principal tries
to open a transaction.
IC-Specific Considerations
Re-exports: ic_dbms_api::prelude::* re-exports all types from wasm_dbms_api::prelude::* plus IC-specific additions. You do not need to import wasm_dbms_api directly.
CandidType requirement: All data types used in your table schemas must implement CandidType. The built-in types already do. If you define custom data types, they must also derive CandidType.
Principal storage: The Principal type is stored in binary format in stable memory (29 bytes max). It is serialized to/from its Candid principal representation when crossing canister boundaries.
Decimal precision: The Decimal type is serialized as text in Candid to preserve arbitrary precision. Frontends should parse the string representation rather than using floating-point conversion.
Errors Reference (IC)
Note: This is the IC-specific error handling reference. For the complete error hierarchy, all error variants, and their causes, see the generic errors reference.
Overview
When using ic-dbms through the ic-dbms-client crate, error handling has an additional layer compared to direct wasm-dbms usage. The IC’s inter-canister call model introduces network-level errors alongside database-level errors, resulting in the double Result pattern.
IcDbmsError
IcDbmsError is the error type returned by every endpoint of an ic-dbms
canister. It wraps the engine’s DbmsError and adds the access control
errors, which the engine knows nothing about:
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::IcDbmsError;
pub enum IcDbmsError {
/// The canister access control list refused the call.
Acl(AclError),
/// The database engine reported an error.
Dbms(DbmsError),
/// The SQL front-end rejected or failed to run a statement
/// (only with the `sql` feature).
Sql(SqlError),
}
pub enum AclError {
AccessDenied {
required: AclRequirement,
table: Option<String>,
},
AdminGrantWithTable,
AnonymousPrincipal,
InvalidTable(String),
LastAdmin,
}
}
DbmsError (Memory, Migration, Query, Sanitize, Table,
Transaction, Validation) is documented in the
generic errors reference.
IcDbmsError implements From for DbmsError, AclError, SqlError and
every engine error enum, so ? works in code that mixes them.
AclError
| Variant | Meaning |
|---|---|
AccessDenied { required, table } | The caller lacks required; table names the table for permission-specific operations. |
AdminGrantWithTable | An Admin grant named a table; Admin always covers every table. |
AnonymousPrincipal | Grants cannot be given to the anonymous principal. |
InvalidTable(name) | The grant named an unknown or reserved table. |
LastAdmin | The operation would remove the last Admin grant. |
required is AclRequirement::Permission(permission) for operations needing
a specific permission. It is AclRequirement::AnyGrant when
begin_transaction is called by a principal holding no grants.
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{AclError, IcDbmsError};
match res {
Ok(()) => {}
Err(IcDbmsError::Acl(AclError::AccessDenied { required, table })) => {
eprintln!("missing access: {required} on {table:?}");
}
Err(IcDbmsError::Acl(AclError::LastAdmin)) => {
eprintln!("grant another admin first");
}
Err(other) => return Err(other),
}
}
SqlError
With the sql feature, the sql and sql_query endpoints return
IcDbmsError::Sql(SqlError) when a statement cannot be parsed, planned or
run. The variants are defined by wasm-dbms and documented in the
wasm-dbms SQL reference. Two
cases are specific to canisters:
SqlError::Unsupportedwith the messagesql_query only runs SELECT statements; use sql for writes and transactions, returned bysql_queryfor any other statement.SqlError::UnknownTable("ic_dbms_acl")for any statement that names the reserved access control table.
Engine errors raised while running a statement, such as a primary key
conflict, arrive as SqlError::Runtime(DbmsError).
Double Result Pattern
Why Two Results?
Client operations return Result<Result<T, IcDbmsError>, CallError>:
Result< -- Outer: IC call result
Result<T, IcDbmsError>, -- Inner: Database operation result
CallError -- Network/canister call error
>
-
Outer
Result(CallError): The inter-canister call itself failed. This happens when:- The canister is unreachable or stopped
- The canister ran out of cycles
- The message was rejected (e.g., unauthorized caller)
- Network timeout on agent calls
-
Inner
Result(IcDbmsError): The call succeeded but the database operation failed. This happens when:- Primary key conflict
- Foreign key constraint violation
- Validation failure
- Transaction not found
- Any other database logic error
Using the ?? Operator
The simplest approach is to use ?? to unwrap both layers:
#![allow(unused)]
fn main() {
// Propagates both CallError and IcDbmsError
let users = client.select::<User>(User::table_name(), query, None).await??;
}
This requires your function to return an error type that both CallError and IcDbmsError can convert into (e.g., Box<dyn std::error::Error>, anyhow::Error, or a custom enum).
Explicit Error Handling
#![allow(unused)]
fn main() {
match client.insert::<User>(User::table_name(), user, None).await {
Ok(Ok(())) => {
// Success: call succeeded AND database operation succeeded
println!("Insert successful");
}
Ok(Err(db_error)) => {
// Call succeeded but database operation failed
println!("Database error: {:?}", db_error);
}
Err(call_error) => {
// Inter-canister call itself failed
println!("Call failed: {:?}", call_error);
}
}
}
Client Error Handling Examples
Basic Pattern
#![allow(unused)]
fn main() {
use ic_dbms_api::prelude::{DbmsError, IcDbmsError, QueryError};
let result = client.insert::<User>(User::table_name(), user, None).await;
match result {
Ok(Ok(())) => println!("Insert successful"),
Ok(Err(e)) => println!("Database error: {:?}", e),
Err(e) => println!("Call failed: {:?}", e),
}
}
Detailed Matching
#![allow(unused)]
fn main() {
match client.insert::<User>(User::table_name(), user, None).await {
Ok(Ok(())) => {
println!("Insert successful");
}
Ok(Err(db_error)) => {
match db_error {
IcDbmsError::Dbms(DbmsError::Query(QueryError::PrimaryKeyConflict)) => {
println!("User already exists");
}
IcDbmsError::Dbms(DbmsError::Query(QueryError::BrokenForeignKeyReference { .. })) => {
println!("Referenced record doesn't exist");
}
IcDbmsError::Dbms(DbmsError::Validation(msg)) => {
println!("Validation error: {}", msg);
}
_ => {
println!("Database error: {:?}", db_error);
}
}
}
Err(call_error) => {
println!("Failed to call canister: {:?}", call_error);
}
}
}
Helper Function Pattern
#![allow(unused)]
fn main() {
fn handle_db_error(error: IcDbmsError) -> String {
match error {
IcDbmsError::Dbms(DbmsError::Query(QueryError::PrimaryKeyConflict)) => {
"Record with this ID already exists".to_string()
}
IcDbmsError::Dbms(DbmsError::Query(QueryError::BrokenForeignKeyReference { .. })) => {
"Referenced record not found".to_string()
}
IcDbmsError::Dbms(DbmsError::Query(QueryError::ForeignKeyConstraintViolation {
..
})) => "Cannot delete: record has dependencies".to_string(),
IcDbmsError::Dbms(DbmsError::Validation(msg)) => format!("Invalid data: {}", msg),
_ => format!("Unexpected error: {:?}", error),
}
}
}
#![allow(unused)]
fn main() {
// Usage
let result = client.insert::<User>(User::table_name(), user, None).await;
match result {
Ok(Ok(())) => Ok(()),
Ok(Err(e)) => Err(handle_db_error(e)),
Err(e) => Err(format!("Call failed: {:?}", e)),
}
}
Retry Pattern for Transient Errors
Network-level errors (outer Result) may be transient. Database errors (inner Result) are deterministic and should not be retried.
#![allow(unused)]
fn main() {
use candid::CandidType;
use ic_dbms_api::prelude::{InsertRecord, TableSchema};
use ic_dbms_client::prelude::Client;
async fn insert_with_retry<T>(
client: &impl Client,
table: &str,
record: T::Insert,
max_retries: u32,
) -> Result<(), String>
where
T: TableSchema,
T::Insert: InsertRecord<Schema = T> + CandidType + Clone,
{
for attempt in 0..max_retries {
match client.insert::<T>(table, record.clone(), None).await {
Ok(Ok(())) => return Ok(()),
Ok(Err(e)) => {
// Database errors are deterministic - don't retry
return Err(format!("Database error: {:?}", e));
}
Err(call_err) => {
// Call errors might be transient - retry
if attempt < max_retries - 1 {
println!("Attempt {} failed, retrying...", attempt + 1);
continue;
}
return Err(format!(
"Call failed after {} attempts: {:?}",
max_retries, call_err
));
}
}
}
unreachable!()
}
}