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.