wasm-dbms

What is wasm-dbms?

wasm-dbms is an embeddable relational database engine written in Rust, designed to run entirely inside WebAssembly runtimes. Unlike traditional databases that run as external services, wasm-dbms compiles into your WASM module and manages data directly in linear memory — no network calls, no external dependencies.

You define your schema as Rust structs with derive macros, and wasm-dbms provides full CRUD operations, ACID transactions, foreign key integrity, validation, and sanitization — all running within the sandbox of your WASM module.

wasm-dbms supports any WASM runtime (Wasmtime, Wasmer, WasmEdge). For the Internet Computer, see the ic-dbms project.

Why wasm-dbms?

A WASM module is a sandbox with a linear memory and little else. Most embedded databases expect a filesystem and a C toolchain, so using them from WASM means porting a native engine, depending on storage provided by one specific host, or building tables by hand on a key-value store. wasm-dbms is a relational engine designed for the sandbox instead:

• Runs wherever WASM runs: pure Rust, builds for wasm32-unknown-unknown, no C toolchain, WASI or JavaScript glue required
• Storage is a trait: the engine works on 64 KiB pages behind MemoryProvider, so the same database runs on the heap, on a file, on Internet Computer stable memory, or on your own storage
• The schema is Rust code: tables are structs, queries are typed, and mistakes are compile errors
• Relational, not key-value: foreign keys, joins, transactions, indexes and migrations are built in
• Ships inside your module: no connection, no network round trip, no service to operate

Read Why wasm-dbms? for the full reasoning and a comparison with SQLite on WASM, Turso, GlueSQL, DuckDB-Wasm, PGlite, key-value stores and host-provided storage.

Documentation

Guides

Step-by-step guides for building databases with wasm-dbms:

• Getting Started - Set up your first wasm-dbms database
• CRUD Operations - Insert, select, update, and delete records
• Querying - Filters, ordering, pagination, and field selection
• Transactions - ACID transactions with commit/rollback
• SQL - Query and modify data with SQL statements
• Relationships - Foreign keys, delete behaviors, and eager loading
• Custom Data Types - Define your own data types (enums, structs)
• Schema Migrations - Evolve your schema across releases without losing data
• Wasmtime Example - Using wasm-dbms with the WIT Component Model and Wasmtime
• Embedding wasm-dbms - Build an application layer: memory provider, context lifetime, transactions across calls, and ownership

Reference

API and type reference documentation:

• Data Types - All supported column types
• Schema Definition - Table attributes and generated types
• Migrations - Schema migration lifecycle, ops, and policy
• Validation - Built-in and custom validators
• Sanitization - Built-in and custom sanitizers
• JSON - JSON data type and filtering
• SQL - SQL syntax, statements, type conversion, and reserved words
• Errors - Error types and handling

Internet Computer

Internet Computer support lives in the dedicated ic-dbms project. Read its documentation at https://ic.wasm-dbms.cc.

WASI Integration

For deploying wasm-dbms on WASI runtimes (Wasmer, Wasmtime, WasmEdge):

• WASI Memory Provider - File-backed persistent storage for WASI
• WASI Key-Value Memory Provider - Draft2 host-bucket storage with explicit checkpoints

Technical Documentation

For advanced users and contributors:

• Architecture - Three-layer system overview
• Memory Management - Stable memory internals
• Join Engine - Cross-table join query internals

Quick Example

Define your schema:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    #[sanitizer(TrimSanitizer)]
    #[validate(MaxStrlenValidator(100))]
    pub name: Text,
    #[validate(EmailValidator)]
    pub email: Text,
}
}

Use the Database trait for CRUD operations:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

// Insert
let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    email: "alice@example.com".into(),
};
database.insert::<User>(user)?;

// Query
let query = Query::builder()
    .filter(Filter::eq("name", Value::Text("Alice".into())))
    .build();
let users = database.select::<User>(query)?;
}

Features

• Schema-driven: Define tables as Rust structs with derive macros
• Runtime-agnostic: Works on any WASM runtime, not tied to a specific platform
• CRUD operations: Full insert, select, update, delete support
• ACID transactions: Commit/rollback with isolation
• Foreign keys: Referential integrity with cascade/restrict behaviors
• Validation & Sanitization: Built-in validators and sanitizers
• JSON support: Store and query semi-structured data

Why wasm-dbms?

• Why wasm-dbms?
  • The Problem
  • What Makes wasm-dbms Different
  • Comparison with Alternatives
    • SQLite Compiled to WASM
    • Turso Database
    • GlueSQL
    • DuckDB-Wasm
    • PGlite
    • Embedded Key-Value Stores
    • Host-Provided Storage
  • When wasm-dbms Is Not the Right Choice
  • Benchmarks

The Problem

A WebAssembly module is a sandbox. It owns a linear memory and whatever the host decides to import, and nothing else: no filesystem, no sockets, no threads, no libc, unless the runtime grants them. Most embedded databases were designed for an operating system instead. They expect files, file locks and memory mapping, and they often need a C toolchain to build.

That leaves three common ways to keep relational data inside a WASM module:

1. Port a native database to WASM. SQLite, Postgres and DuckDB all have WASM builds. They bring a C or C++ codebase, a storage shim for the missing filesystem, and usually a JavaScript or WASI host to run on.
2. Use the storage the host provides. Many runtimes expose a key-value or SQL API to their guests. The data then lives outside the module, and the code is tied to that one host.
3. Build tables on top of a key-value store. You get persistence, and then write the schema, indexes, foreign keys and transactions yourself.

wasm-dbms is a fourth option: a relational engine designed for the sandbox from the start.

What Makes wasm-dbms Different

• It runs wherever WASM runs. The engine is pure Rust and builds for wasm32-unknown-unknown with plain cargo. It needs no C toolchain, no WASI, and no JavaScript glue.
• Storage is a trait, not a filesystem. The engine reads and writes 64 KiB pages, the same size as a WASM memory page, through the MemoryProvider trait. The same engine runs on the heap for tests, on a file through WASI, on Internet Computer stable memory through ic-dbms, or on any storage you implement the trait for. WASI hosts with durable key-value storage can use the draft2 key-value provider without a filesystem preopen.
• The schema is Rust code. Tables are structs with derive macros. Records, insert requests and update requests are generated types, so a wrong column type is a compile error and no query string is parsed at runtime.
• It is relational, not key-value. Foreign keys with cascade and restrict behaviors, joins, ACID transactions, B+ tree indexes, aggregates and schema migrations are part of the engine.
• Data rules live next to the schema. Validators and sanitizers are declared on the columns they protect and run on every write.
• The database ships inside the module. There is no connection, no network round trip and no service to operate. The host only sees pages.
• It is not limited to Rust hosts. The WIT interface exposes the database as a WebAssembly Component, so hosts written in Go, Python, JavaScript or any language with Component Model tooling can use it.

Comparison with Alternatives

The table below reflects the state of each project in October 2026. If something is out of date, please open an issue.

SQLite Compiled to WASM

SQLite is the most tested embedded database there is, and it has official and community WASM builds. In Rust, rusqlite uses sqlite-wasm-rs on wasm32-unknown-unknown, which stores data in memory or in the browser’s OPFS and relies on wasm-bindgen or on host functions you provide. Building for WASI requires a C toolchain such as the WASI SDK.

Choose SQLite when you need SQL compatibility, the SQLite file format, or its long production record. Choose wasm-dbms when you want a pure Rust build with no C toolchain, storage that is not a filesystem, and a schema checked by the Rust compiler.

Turso Database

Turso Database is a rewrite of SQLite in Rust. It is compatible with the SQLite SQL dialect and file format, has WASM bindings for browsers, and has not reached 1.0 yet.

Choose Turso when you want SQLite compatibility from a Rust codebase. Choose wasm-dbms when you want typed tables instead of SQL strings and need to plug in your own page storage.

GlueSQL

GlueSQL is a SQL engine written in Rust with swappable storages and a query builder. It is the closest project to wasm-dbms in spirit. Its WASM support is delivered as a JavaScript package for browsers and Node.js, with in-memory and browser storage backends.

Choose GlueSQL when you want SQL over many storage formats, such as JSON, CSV or Parquet files. Choose wasm-dbms when you want schemas generated from Rust structs, with foreign keys, validators and sanitizers declared on the columns, and a page-based storage layer made for WASM memory.

DuckDB-Wasm

DuckDB-Wasm brings the DuckDB analytics engine to the browser. It is built for analytical queries over large datasets and remote files such as Parquet.

Choose DuckDB-Wasm for analytics in the browser. Choose wasm-dbms for transactional workloads: many small reads and writes on related records, in any WASM runtime.

PGlite

PGlite is Postgres compiled to WASM and packaged as a TypeScript library for browsers, Node.js and Bun.

Choose PGlite when you need real Postgres behavior and extensions in a JavaScript application. Choose wasm-dbms when the database must live inside a Rust module on a runtime with no JavaScript.

Embedded Key-Value Stores

Stores such as redb give you ACID key-value storage in pure Rust, and redb accepts a custom storage backend. They do not give you tables: the schema, secondary indexes, foreign keys, joins and validation are yours to write and keep consistent.

Choose a key-value store when your data really is keys and values. Choose wasm-dbms when records reference each other and you want the engine to keep them consistent.

Host-Provided Storage

Many platforms give guests a database through host functions: Spin offers SQLite and key-value stores, the wasi:keyvalue interface standardizes key-value access, and Cloudflare Workers bind to D1. The data lives outside the module, the host manages it, and it can be shared between modules.

Choose host-provided storage when the data must outlive the module or be shared with other services on that platform. Choose wasm-dbms when the module must stay portable across runtimes, or when the runtime offers nothing more than memory, as on the Internet Computer.

When wasm-dbms Is Not the Right Choice

wasm-dbms is not the best tool for every job. Pick an alternative when:

• You need compatibility with an existing database. If you must open SQLite files, rely on Postgres features or reuse existing database tooling, use SQLite, Turso or PGlite.
• Your workload is analytical. Scanning and aggregating very large datasets is what DuckDB is built for.
• You need decades of production hardening. wasm-dbms is younger than SQLite and has not reached 1.0, so its API can still change between releases.
• The data must be shared across modules or services. An embedded database belongs to one module. Use host-provided storage or an external database.
• You only need keys and values. A key-value store is simpler and smaller.

Benchmarks

The repository includes a benchmark suite that compares wasm-dbms with in-memory SQLite and DuckDB across CRUD operations, bulk inserts, queries and transactions. Results depend on the machine, so none are published here. Run them yourself from a checkout:

just bench_compare

Get Started

• Prerequisites
• Project Setup
  • Workspace Structure
  • Cargo Configuration
• Define Your Schema
  • Create the Schema Crate
  • Define Tables
• Define a Database Schema
• Using the Database
  • Create a DbmsContext
  • Perform CRUD Operations
• Quick Example: Complete Workflow
• Testing with HeapMemoryProvider
• Next Steps

This guide walks you through setting up a database using wasm-dbms. By the end, you’ll have a working database with CRUD operations and transactions.

Prerequisites

Before starting, ensure you have:

• Rust 1.91.1 or later
• wasm32-unknown-unknown target: rustup target add wasm32-unknown-unknown

Project Setup

Workspace Structure

We recommend organizing your project as a Cargo workspace with a schema crate:

my-dbms-project/
├── Cargo.toml          # Workspace manifest
├── schema/             # Schema definitions (reusable types)
│   ├── Cargo.toml
│   └── src/
│       └── lib.rs
└── app/                # Your application using the database
    ├── Cargo.toml
    └── src/
        └── lib.rs

Workspace Cargo.toml:

[workspace]
members = ["schema", "app"]
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]
wasm-dbms-api = "0.6"

Define Tables

In schema/src/lib.rs, define your database tables using the Table derive macro:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    #[sanitizer(TrimSanitizer)]
    #[validate(MaxStrlenValidator(100))]
    pub name: Text,
    #[validate(EmailValidator)]
    pub email: Text,
    pub created_at: DateTime,
}

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[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, Clone

The Table macro generates additional types for each table:

Define a Database Schema

Once you’ve defined your tables, create a schema struct with #[derive(DatabaseSchema)] to wire them together:

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::DatabaseSchema;

#[derive(DatabaseSchema)]
#[tables(User = "users", Post = "posts")]
pub struct MySchema;
}

The DatabaseSchema derive macro auto-generates the DatabaseSchema<M> trait implementation and a register_tables method. This replaces what would otherwise be ~130+ lines of manual dispatch code.

Using the Database

Create a DbmsContext

The DbmsContext holds all database state. Create one using a MemoryProvider:

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::*;
use wasm_dbms_api::prelude::*;

// For testing, use HeapMemoryProvider
let ctx = DbmsContext::new(HeapMemoryProvider::default());

// Register tables from the schema
MySchema::register_tables(&ctx).expect("failed to register tables");
}

Perform CRUD Operations

Create a WasmDbmsDatabase from the context to perform operations:

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::*;
use my_schema::{User, UserInsertRequest};

// Create a one-shot (non-transactional) database
let database = WasmDbmsDatabase::oneshot(&ctx, MySchema);

// Insert a record
let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    email: "alice@example.com".into(),
    created_at: DateTime::now(),
};
database.insert::<User>(user)?;

// Query records
let query = Query::builder().all().build();
let users = database.select::<User>(query)?;
}

Quick Example: Complete Workflow

Here’s a complete example showing insert, query, update, and delete operations:

#![allow(unused)]
fn main() {
use my_schema::{User, UserInsertRequest, UserUpdateRequest};
use wasm_dbms_api::prelude::*;

fn example(database: &impl Database) -> Result<(), DbmsError> {
    // 1. INSERT a new user
    let insert_req = UserInsertRequest {
        id: 1.into(),
        name: "Alice".into(),
        email: "alice@example.com".into(),
        created_at: DateTime::now(),
    };
    database.insert::<User>(insert_req)?;

    // 2. SELECT users
    let query = Query::builder()
        .filter(Filter::eq("name", Value::Text("Alice".into())))
        .build();
    let users = database.select::<User>(query)?;
    println!("Found {} user(s)", users.len());

    // 3. UPDATE the user
    let update_req = UserUpdateRequest::builder()
        .set_email("alice.new@example.com".into())
        .filter(Filter::eq("id", Value::Uint32(1.into())))
        .build();
    let updated = database.update::<User>(update_req)?;
    println!("Updated {} record(s)", updated);

    // 4. DELETE the user
    let deleted = database.delete::<User>(
        DeleteBehavior::Restrict,
        Some(Filter::eq("id", Value::Uint32(1.into()))),
    )?;
    println!("Deleted {} record(s)", deleted);

    Ok(())
}
}

Testing with HeapMemoryProvider

For unit tests, use HeapMemoryProvider which stores data in heap memory:

#![allow(unused)]
fn main() {
use my_schema::{MySchema, User, UserInsertRequest};
use wasm_dbms::prelude::*;
use wasm_dbms_api::prelude::*;

#[test]
fn test_insert_and_select() {
    let ctx = DbmsContext::new(HeapMemoryProvider::default());
    MySchema::register_tables(&ctx).expect("register failed");
    let database = WasmDbmsDatabase::oneshot(&ctx, MySchema);

    let insert_req = UserInsertRequest {
        id: 1.into(),
        name: "Test User".into(),
        email: "test@example.com".into(),
        created_at: DateTime::now(),
    };

    database.insert::<User>(insert_req).expect("insert failed");

    let query = Query::builder().all().build();
    let users = database.select::<User>(query).expect("select failed");

    assert_eq!(users.len(), 1);
    assert_eq!(users[0].name.as_str(), "Test User");
}
}

For the Internet Computer, see the ic-dbms Getting Started Guide.

Next Steps

Now that you have a working database, explore these topics:

• CRUD Operations - Detailed guide on all database operations
• Querying - Filters, ordering, pagination, and field selection
• Transactions - ACID transactions with commit/rollback
• Relationships - Foreign keys and eager loading
• Custom Data Types - Define your own data types (enums, structs)
• Schema Definition - Complete schema reference
• Data Types - All supported field types

CRUD Operations

• CRUD Operations
  • Overview
  • Insert
    • Basic Insert
    • Handling Primary Keys
    • Nullable Fields
    • Insert with Transaction
  • Select
    • Select All Records
    • Select with Filter
    • Select Specific Columns
    • Select with Eager Loading
  • Update
    • Basic Update
    • Partial Updates
    • Type Checking
    • Update with Filter
    • Update Return Value
  • Delete
    • Delete with Filter
    • Delete Behaviors
    • Delete All Records
  • Operations with Transactions
  • Error Handling

Overview

wasm-dbms provides four fundamental database operations through the Database trait:

All operations:

• Support optional transaction IDs
• Validate and sanitize data according to schema rules
• Enforce foreign key constraints

Insert

Basic Insert

To insert a record, create an InsertRequest and call the insert method:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;
use my_schema::{User, UserInsertRequest};

let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    email: "alice@example.com".into(),
    created_at: DateTime::now(),
};

// Insert without transaction
database.insert::<User>(user)?;
}

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
database.insert::<User>(user1)?;

// Second insert with same ID fails with PrimaryKeyConflict
let result = database.insert::<User>(user2_same_id);
assert!(matches!(result, Err(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, Clone, PartialEq, Eq)]
#[table = "profiles"]
pub struct Profile {
    #[primary_key]
    pub id: Uint32,
    pub bio: Nullable<Text>,      // Optional field
    pub website: Nullable<Text>,  // Optional field
}

// Insert with value
let profile = ProfileInsertRequest {
    id: 1.into(),
    bio: Nullable::Value("Hello world".into()),
    website: Nullable::Null,  // No website
};

database.insert::<Profile>(profile)?;
}

Insert with Transaction

To insert within a transaction, use a transactional database instance:

#![allow(unused)]
fn main() {
// Begin transaction
let tx_id = ctx.begin_transaction();

// Create a transactional database
let database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// Insert within transaction
database.insert::<User>(user)?;

// Commit or rollback
database.commit()?;
}

Select

Select All Records

Use Query::builder().all() to select all records:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

let query = Query::builder().all().build();
let users: Vec<UserRecord> = database.select::<User>(query)?;

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()
    .filter(Filter::eq("name", Value::Text("Alice".into())))
    .build();

let users = database.select::<User>(query)?;
}

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()
    .columns(vec!["id".to_string(), "name".to_string()])
    .build();

let users = database.select::<User>(query)?;
// Only id and name are populated; other fields have default values
}

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 = database.select::<Post>(query)?;
}

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::builder()
    .set_name("Alice Smith".into())
    .filter(Filter::eq("id", Value::Uint32(1.into())))
    .build();

let affected_rows = database.update::<User>(update)?;

println!("Updated {} row(s)", affected_rows);
}

Partial Updates

Only specify the fields you want to change. Unspecified fields remain unchanged:

#![allow(unused)]
fn main() {
// Only update the email, keep everything else
let update = UserUpdateRequest::builder()
    .set_email("new.email@example.com".into())
    .filter(Filter::eq("id", Value::Uint32(1.into())))
    .build();

database.update::<User>(update)?;
}

Type Checking

Every value of an update patch must match the type of its column; Null is accepted only by nullable columns. Building a patch with UpdateRecord::from_values, or sending one through the dynamic schema update, returns a QueryError::InvalidQuery for a mismatched value instead of ignoring it. See Errors.

#![allow(unused)]
fn main() {
let update = UserUpdateRequest::from_values(
    &[(email_col, Value::Text("alice@example.com".into()))],
    Some(Filter::eq("id", Value::Uint32(1.into()))),
)?;
database.update::<User>(update)?;
}

Update with Filter

The filter determines which records are updated:

Updates find their target rows with the same index planning as queries, including composite indexes, OR, AND, and NULL checks; see Index-Accelerated Queries.

#![allow(unused)]
fn main() {
// Update all users with a specific domain
let update = UserUpdateRequest::builder()
    .set_verified(true.into())
    .filter(Filter::like("email", "%@company.com"))
    .build();

let affected = database.update::<User>(update)?;
println!("Verified {} company users", affected);
}

Update Return Value

Update returns the number of affected rows:

#![allow(unused)]
fn main() {
let affected = database.update::<User>(update)?;

if affected == 0 {
    println!("No records matched the filter");
} else {
    println!("Updated {} record(s)", affected);
}
}

Delete

Delete with Filter

Delete records matching a filter:

Deletes use the same index planning as queries, including composite indexes, OR, AND, and NULL checks; see Index-Accelerated Queries.

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::DeleteBehavior;

let filter = Filter::eq("id", Value::Uint32(1.into()));

let deleted = database.delete::<User>(
    DeleteBehavior::Restrict,
    Some(filter),
)?;

println!("Deleted {} record(s)", deleted);
}

Delete Behaviors

When deleting records that are referenced by foreign keys, you must specify a behavior:

Restrict Example:

#![allow(unused)]
fn main() {
// Will fail if any posts reference this user
let result = database.delete::<User>(
    DeleteBehavior::Restrict,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
);

match result {
    Ok(count) => println!("Deleted {} user(s)", count),
    Err(DbmsError::Query(QueryError::ForeignKeyConstraintViolation)) => {
        println!("Cannot delete: user has posts");
    }
    Err(e) => return Err(e),
}
}

Cascade Example:

#![allow(unused)]
fn main() {
// Deletes the user AND all their posts
database.delete::<User>(
    DeleteBehavior::Cascade,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
)?;
}

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 = database.delete::<User>(
    DeleteBehavior::Cascade,
    None,  // No filter = all records
)?;

println!("Deleted all {} users and their related records", deleted);
}

Operations with Transactions

All CRUD operations can be performed within a transaction. When using a transactional database instance, operations won’t be visible to other callers until committed:

#![allow(unused)]
fn main() {
// Begin transaction
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// Perform operations within transaction
database.insert::<User>(user1)?;
database.insert::<User>(user2)?;

// Update within same transaction
let update = UserUpdateRequest::builder()
    .set_verified(true.into())
    .filter(Filter::all())
    .build();
database.update::<User>(update)?;

// Commit all changes atomically
database.commit()?;
}

See the Transactions Guide for comprehensive transaction documentation.

Error Handling

CRUD operations can fail for various reasons. Here are common errors:

Example error handling:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::{DbmsError, QueryError};

let result = database.insert::<User>(user);

match result {
    Ok(()) => println!("Insert successful"),
    Err(DbmsError::Query(QueryError::PrimaryKeyConflict)) => {
        println!("User with this ID already exists");
    }
    Err(DbmsError::Query(QueryError::BrokenForeignKeyReference)) => {
        println!("Referenced record does not exist");
    }
    Err(DbmsError::Validation(msg)) => {
        println!("Validation failed: {}", msg);
    }
    Err(e) => {
        println!("Unexpected error: {:?}", e);
    }
}
}

See the Errors Reference for complete error documentation.

For the Internet Computer, see the ic-dbms CRUD Guide.

Querying

• Querying
  • Overview
  • Query Builder
    • Basic Queries
    • Query Structure
  • Filters
    • Comparison Filters
    • List Membership
    • Pattern Matching
    • Null Checks
    • Combining Filters
  • JSON Filters
  • Ordering
    • Single Column Ordering
    • Multiple Column Ordering
  • Pagination
    • Limit
    • Offset
    • Pagination Pattern
  • Field Selection
    • Select All Fields
    • Select Specific Fields
  • Eager Loading
  • Distinct
    • Basic Distinct
    • Distinct by Multiple Columns
    • Distinct with Ordering and Pagination
    • Distinct Semantics
  • Aggregations
    • Defining Aggregates
    • Group By and Having
    • Aggregate Result Types
  • Joins
    • Join Types
    • Basic Join
    • Left, Right, and Full Joins
    • Chaining Multiple Joins
    • Qualified Column Names
    • Joins vs Eager Loading
  • Index-Accelerated Queries
    • How Indexes Improve Queries
    • Which Filters Use Indexes
    • Composite Indexes
    • OR and AND Across Indexes
    • Null Checks on Indexed Columns
    • Covering Reads
    • When the Engine Scans Instead
    • Transaction-Aware Lookups

Overview

wasm-dbms provides a powerful query API for retrieving data from your tables. Queries are built using the QueryBuilder and can include:

• Filters - Narrow down which records to return
• Ordering - Sort results by one or more columns
• Pagination - Limit results and implement pagination
• Field Selection - Choose which columns to return
• Eager Loading - Load related records in a single query
• Joins - Combine rows from multiple tables

Query Builder

Basic Queries

Use Query::builder() to construct queries:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

// Select all records
let query = Query::builder().all().build();

// Select with filter
let query = Query::builder()
.filter(Filter::eq("status", Value::Text("active".into())))
.build();

// Complex query with multiple options
let query = Query::builder()
.filter(Filter::gt("age", Value::Int32(18.into())))
.order_by("created_at", OrderDirection::Descending)
.limit(10)
.offset(20)
.build();
}

Query Structure

A query consists of these optional components:

Filters

Filters determine which records match your query. All filters are created using the Filter struct.

Comparison Filters

Examples:

#![allow(unused)]
fn main() {
// Find users older than 21
let filter = Filter::gt("age", Value::Int32(21.into()));

// Find products under $50
let filter = Filter::lt("price", Value::Decimal(50.0.into()));

// Find orders from a specific date
let filter = Filter::ge("created_at", Value::DateTime(some_datetime));
}

List Membership

Check if a value is in a list of values:

#![allow(unused)]
fn main() {
// Find users with specific roles
let filter = Filter::in_list("role", vec![
    Value::Text("admin".into()),
    Value::Text("moderator".into()),
    Value::Text("editor".into()),
]);

// Find products in certain categories
let filter = Filter::in_list("category_id", vec![
    Value::Uint32(1.into()),
    Value::Uint32(2.into()),
    Value::Uint32(5.into()),
]);
}

Pattern Matching

Use like for pattern matching with wildcards:

A backslash escapes the character that follows it. A pattern that ends with an unescaped backslash, such as abc\, is rejected with QueryError::InvalidQuery. Write abc\\ to match text ending with a literal backslash.

#![allow(unused)]
fn main() {
// Find users whose email ends with @company.com
let filter = Filter::like("email", "%@company.com");

// Find products starting with "Pro"
let filter = Filter::like("name", "Pro%");

// Find codes with pattern XX-###
let filter = Filter::like("code", "__-___");

// Find text containing literal %
let filter = Filter::like("description", r"%25\% off%");
}

Null Checks

Check for null or non-null values:

#![allow(unused)]
fn main() {
// Find users without a phone number
let filter = Filter::is_null("phone");

// Find users with a profile picture
let filter = Filter::not_null("avatar_url");
}

A NULL value in a nullable Text or Json column never matches a like filter or a JSON filter, so the row is left out of the result instead of failing the query. The pattern or JSON path is still checked, and an invalid one returns an error. not() inverts the result, so a negated like or JSON filter matches NULL values, in the same way ne matches them. Combine the filter with not_null to leave NULL values out:

#![allow(unused)]
fn main() {
// Users whose nickname does not start with "x", without users lacking a nickname
let filter = Filter::like("nickname", "x%")
.not()
.and(Filter::not_null("nickname"));
}

The same rules apply to plain queries and to join filters, where the columns of an unmatched side of a LEFT, RIGHT, or FULL join are NULL.

Combining Filters

Filters can be combined using logical operators:

AND - Both conditions must match:

#![allow(unused)]
fn main() {
// Active users over 18
let filter = Filter::eq("status", Value::Text("active".into()))
.and(Filter::gt("age", Value::Int32(18.into())));
}

OR - Either condition matches:

#![allow(unused)]
fn main() {
// Admins or moderators
let filter = Filter::eq("role", Value::Text("admin".into()))
.or(Filter::eq("role", Value::Text("moderator".into())));
}

NOT - Negate a condition:

#![allow(unused)]
fn main() {
// Users who are not banned
let filter = Filter::eq("status", Value::Text("banned".into())).not();
}

Complex combinations:

#![allow(unused)]
fn main() {
// (active AND age > 18) OR role = "admin"
let filter = Filter::eq("status", Value::Text("active".into()))
.and(Filter::gt("age", Value::Int32(18.into())))
.or(Filter::eq("role", Value::Text("admin".into())));

// NOT (deleted OR archived)
let filter = Filter::eq("status", Value::Text("deleted".into()))
.or(Filter::eq("status", Value::Text("archived".into())))
.not();
}

JSON Filters

For columns with Json type, use specialized JSON filters. See the JSON Reference for comprehensive documentation.

Quick examples:

#![allow(unused)]
fn main() {
// Check if JSON contains a pattern
let pattern = Json::from_str(r#"{"active": true}"#).unwrap();
let filter = Filter::json("metadata", JsonFilter::contains(pattern));

// Extract and compare a value
let filter = Filter::json(
"settings",
JsonFilter::extract_eq("theme", Value::Text("dark".into()))
);

// Check if a path exists
let filter = Filter::json("data", JsonFilter::has_key("user.email"));
}

Ordering

Single Column Ordering

Sort results by a single column:

#![allow(unused)]
fn main() {
// Sort by name ascending (A-Z)
let query = Query::builder()
.all()
.order_by("name", OrderDirection::Ascending)
.build();

// Sort by created_at descending (newest first)
let query = Query::builder()
.all()
.order_by("created_at", OrderDirection::Descending)
.build();
}

Multiple Column Ordering

Chain multiple order_by calls for secondary sorting:

#![allow(unused)]
fn main() {
// Sort by category, then by price within each category
let query = Query::builder()
.all()
.order_by("category", OrderDirection::Ascending)
.order_by("price", OrderDirection::Descending)
.build();

// Sort by status, then by priority, then by created_at
let query = Query::builder()
.all()
.order_by("status", OrderDirection::Ascending)
.order_by("priority", OrderDirection::Descending)
.order_by("created_at", OrderDirection::Ascending)
.build();
}

Pagination

Limit

Restrict the number of records returned:

#![allow(unused)]
fn main() {
// Get only the first 10 records
let query = Query::builder()
.all()
.limit(10)
.build();
}

Offset

Skip a number of records before returning results:

#![allow(unused)]
fn main() {
// Skip the first 20 records
let query = Query::builder()
.all()
.offset(20)
.build();
}

Pagination Pattern

Combine limit and offset for pagination:

#![allow(unused)]
fn main() {
const PAGE_SIZE: u64 = 20;

fn get_page_query(page: u64) -> Query {
    Query::builder()
        .all()
        .order_by("id", OrderDirection::Ascending)  // Consistent ordering is important
        .limit(PAGE_SIZE)
        .offset(page * PAGE_SIZE)
        .build()
}

// Page 0: records 0-19
let page_0 = get_page_query(0);

// Page 1: records 20-39
let page_1 = get_page_query(1);

// Page 2: records 40-59
let page_2 = get_page_query(2);
}

Tip: Always use order_by with pagination to ensure consistent ordering across pages.

Field Selection

Select All Fields

Use .all() to select all columns:

#![allow(unused)]
fn main() {
let query = Query::builder()
.all()
.build();

let users = database.select::<User>(query)?;
// All fields are populated
}

Select Specific Fields

Use .columns() to select only specific columns:

#![allow(unused)]
fn main() {
let query = Query::builder()
.columns(vec!["id".to_string(), "name".to_string(), "email".to_string()])
.build();

let users = database.select::<User>(query)?;
// Only id, name, and email are populated
// Other fields will have default values
}

Note: The primary key is always included, even if not specified.

Eager Loading

Load related records in a single query using .with():

#![allow(unused)]
fn main() {
// Define tables with foreign key
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "posts"]
pub struct Post {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    #[foreign_key(entity = "User", table = "users", column = "id")]
    pub author_id: Uint32,
}

// Query posts with authors eagerly loaded
let query = Query::builder()
.all()
.with("users")
.build();

let posts = database.select::<Post>(query)?;
}

See the Relationships Guide for more on eager loading.

Distinct

Use .distinct(&[...]) to remove duplicate rows from the result set based on one or more columns. Rows are deduplicated by the tuple of values across the listed columns; the first row encountered for each distinct tuple is kept.

Basic Distinct

#![allow(unused)]
fn main() {
// Get the unique set of names from the users table
let query = Query::builder()
    .all()
    .distinct(&["name"])
    .build();

let users = database.select::<User>(query)?;
}

Distinct by Multiple Columns

#![allow(unused)]
fn main() {
// Unique (category, vendor) pairs from products
let query = Query::builder()
    .all()
    .distinct(&["category", "vendor"])
    .build();

let products = database.select::<Product>(query)?;
}

Distinct with Ordering and Pagination

DISTINCT runs before ORDER BY, OFFSET, and LIMIT, so paging through distinct values works as expected:

#![allow(unused)]
fn main() {
// Page 2 (size 10) of unique names, alphabetical
let query = Query::builder()
    .all()
    .distinct(&["name"])
    .order_by_asc("name")
    .offset(10)
    .limit(10)
    .build();
}

Without DISTINCT, LIMIT 10 could yield ten copies of the same name. With DISTINCT, the limit applies to the deduplicated stream.

Distinct Semantics

• Lookup is performed against the source row’s columns. The columns named in .distinct(...) do not need to be in the field selection.
• A column not present on the row is treated as Value::Null. Listing an unknown column collapses every row into a single result.
• Calling .distinct(&[]) (or omitting it) is a no-op.
• Pipeline order: WHERE -> DISTINCT -> eager loading -> column selection -> ORDER BY -> OFFSET / LIMIT. See the Query API Reference for the full pipeline.

Tip: distinct(&[pk_column]) returns at most one row per primary key, which can be useful when joining sources that fan out the parent rows.

Aggregations

Aggregations summarise groups of rows using COUNT, SUM, AVG, MIN, and MAX. Group rows with .group_by(...), filter the resulting groups with .having(...), and describe the aggregates to compute via the AggregateFunction enum.

Defining Aggregates

Each aggregate is one variant of AggregateFunction:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::AggregateFunction;

let aggregates = vec![
    AggregateFunction::Count(None),               // COUNT(*)
    AggregateFunction::Count(Some("email".into())), // COUNT(email)
    AggregateFunction::Sum("amount".into()),
    AggregateFunction::Avg("amount".into()),
    AggregateFunction::Min("created_at".into()),
    AggregateFunction::Max("created_at".into()),
];
}

Count(None) counts every row in the group; Count(Some(col)) counts only rows where col is non-null. The other variants take a column name and operate over its values.

Group By and Having

Use .group_by(&[...]) to define grouping keys and .having(filter) to filter the aggregated groups:

#![allow(unused)]
fn main() {
let query = Query::builder()
    .all()
    .group_by(&["category"])
    .having(Filter::gt("count", Value::Uint64(10u64.into())))
    .order_by_desc("category")
    .build();
}

HAVING is evaluated after aggregation, against grouping keys and aggregate results. WHERE (set with .and_where() / .or_where()) still applies first to the raw rows.

Aggregate Result Types

Aggregated queries return AggregatedRow values:

#![allow(unused)]
fn main() {
pub struct AggregatedRow {
    pub group_keys: Vec<Value>,
    pub values: Vec<AggregatedValue>,
}
}

group_keys carries the grouping tuple (one Value per group_by column). values holds one AggregatedValue per requested aggregate, in the same order as the AggregateFunction list.

#![allow(unused)]
fn main() {
pub enum AggregatedValue {
    Count(u64),
    Sum(Value),
    Avg(Value),
    Min(Value),
    Max(Value),
}
}

Count is always u64; the other variants wrap a Value whose concrete variant matches the source column’s data type.

See the Query API Reference for the full type definitions and pipeline ordering.

Joins

Joins combine rows from two or more tables based on a related column, producing a single result set with columns from all joined tables. Use joins when you need to correlate data across tables in a single flat result – for example, listing posts alongside their author names.

Note: Joins require the select_join method, which returns a JoinResultSet: the JoinColumnDef of every selected column once, each with its source table name, followed by the rows as plain Vec<Value>. Typed select::<T> rejects queries that contain joins with a JoinInsideTypedSelect error.

Join Types

Basic Join

Use .inner_join(table, left_column, right_column) to join two tables:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

// Join users with their posts (INNER JOIN)
let query = Query::builder()
    .all()
    .inner_join("posts", "id", "user_id")
    .build();

// select_join returns the column descriptions once, then the rows
let result = database.select_join("users", query)?;

// Each JoinRow pairs every value with its column description
for row in &result {
    for (col_def, value) in row.iter() {
        // col_def.table tells you which table the column came from
        let table = col_def.table.as_deref().unwrap_or("?");
        println!("{table}.{column} = {value:?}", column = col_def.name.as_str());
    }
}

// Resolve column positions once when reading many rows
let name_index = result
    .column_index("users.name")
    .expect("users.name is selected");
let title_index = result
    .column_index("posts.title")
    .expect("posts.title is selected");
for row in &result.rows {
    println!(
        "{user_name:?} wrote {post_title:?}",
        user_name = &row[name_index],
        post_title = &row[title_index]
    );
}

// Or look up a value directly from a borrowed row
if let Some(row) = result.row(0) {
    let first_title = row.get("posts.title");
    let any_id = row.get("id"); // unqualified: first column named "id"
}
}

A bare column name resolves to the first column with that name in row order, so qualify names that exist on both sides (users.id and posts.id). result.columns is the complete header even when result.rows is empty.

Left, Right, and Full Joins

#![allow(unused)]
fn main() {
// LEFT JOIN: all users, even those without posts
let query = Query::builder()
    .all()
    .left_join("posts", "id", "user_id")
    .build();

// RIGHT JOIN: all posts, even those with missing/deleted authors
let query = Query::builder()
    .all()
    .right_join("posts", "id", "user_id")
    .build();

// FULL JOIN: all users and all posts, matched where possible
let query = Query::builder()
    .all()
    .full_join("posts", "id", "user_id")
    .build();
}

Join keys match only when both values are non-null and equal. A NULL join key does not match another NULL. For LEFT, RIGHT, and FULL joins, the row remains unmatched and columns from the missing side are filled with Value::Null.

Chaining Multiple Joins

Chain multiple joins to combine more than two tables:

#![allow(unused)]
fn main() {
// Users -> Posts -> Comments
let query = Query::builder()
    .all()
    .inner_join("posts", "id", "user_id")
    .left_join("comments", "posts.id", "post_id")
    .build();

let result = database.select_join("users", query)?;
}

Joins are processed left-to-right. The second join operates on the result of the first.

Qualified Column Names

When joining tables that share column names, use table.column syntax to disambiguate:

#![allow(unused)]
fn main() {
// Both "users" and "posts" have an "id" column
let query = Query::builder()
    .field("users.id")
    .field("users.name")
    .field("posts.title")
    .inner_join("posts", "users.id", "user_id")
    .and_where(Filter::eq("users.name", Value::Text("Alice".into())))
    .order_by_asc("posts.title")
    .build();
}

Qualified names (table.column) work in:

• Field selection (.field())
• Filters (.and_where(), .or_where())
• Ordering (.order_by_asc(), .order_by_desc())
• Join ON conditions

Unqualified names default to the FROM table (the table passed to select_join).

Joins vs Eager Loading

Use eager loading when you want typed results with related records attached. Use joins when you need a flat, cross-table result set – for example, for reporting, search, or when you need columns from multiple tables in a single row.

Index-Accelerated Queries

When a table has indexes defined (via #[index] or the automatic primary key index), the query engine can use them to avoid full table scans. This happens transparently — you write the same filters as before, and the engine picks the best available index.

How Indexes Improve Queries

Without indexes, every SELECT, UPDATE, and DELETE scans all records in the table. With an index on the filtered column, the engine navigates the B-tree to locate matching records directly, then loads only those records from memory. Covered projections can return values from index keys without loading record pages.

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    #[index]
    pub email: Text,
    pub name: Text,
}

// This uses the index on `email` — no full table scan
let query = Query::builder()
    .filter(Filter::eq("email", Value::Text("alice@example.com".into())))
    .build();

let users = database.select::<User>(query)?;
}

Which Filters Use Indexes

The planner reads the conditions combined with AND, in any order and nesting, and matches them against every index of the table:

Each result is checked against the query’s filter, so an index narrows the rows to consider without changing which rows qualify. Contradictory conditions such as price >= 5 AND price < 5 or an empty in_list return no rows without reading the table.

Composite Indexes

A composite index is used from its first column onward, in declaration order. For an index on (category, brand, price):

• Equality on all three columns is one exact lookup.
• Equality on category reads only that category.
• Equality on category and a range on brand reads only that range.
• A condition on brand or price alone cannot use the index.
• If an index column has no condition, conditions on later columns are checked on the candidate rows instead.

A leading in_list expands into at most 64 index ranges. Above that limit, the engine falls back or uses a narrower index path available from other conditions.

OR and AND Across Indexes

a OR b uses an index when every branch can; the results are merged and each row is returned once. If one branch has no usable index, the whole OR falls back, although another AND condition outside the OR may still use an index. At most 64 index ranges are read for one OR.

For a AND b on separately indexed columns, the engine prefers a primary-key, unique, or complete composite equality. Otherwise it intersects useful index paths and keeps only rows present in each chosen path before loading records.

Null Checks on Indexed Columns

is_null is an exact lookup. not_null reads the non-NULL part of the index: signed integers, dates, date-times, decimals, booleans, blobs, and JSON sort below NULL; text and unsigned integers sort above it. Nullable Uuid and custom columns have no proven range and use a table scan.

Covering Reads

Outside a transaction, one index can build rows from keys alone when it contains every selected, filtered, ordered, and distinct column, the query loads no relations, and either the filter plans to one range or no filter is given. Duplicate keys still produce one result per record. A secondary index contains the primary key only when the index definition lists it.

Without ordering or distinct processing, covering reads stop after enough matching rows have been read to satisfy the offset and limit. A zero limit does not read index entries.

When the Engine Scans Instead

The engine uses a full table scan with the same results when no index path can provide a complete candidate set. This includes queries where no condition matches an index and the query is not eligible for an unfiltered covering read. For record-fetching plans, a range that would materialize more than 4,096 candidates or a plan that would materialize more than 16,384 also triggers a scan unless another complete index path narrows the candidates within the limit. Covering reads stream index keys instead of materializing addresses. Results are never truncated.

Filters containing like or JSON keep the earlier single-column index plan and residual evaluation so their error and short-circuit behavior stays unchanged.

Transaction-Aware Lookups

Inside a transaction, the engine reads committed index entries, applies the transaction’s inserts, updates, and deletes to the candidate rows, adds rows the transaction moved into the filter, and checks every row against the whole filter. Updates to columns outside the chosen index conditions do not add extra record fetches. Filters containing like or JSON first check the visible index key and then evaluate the earlier residual filter, preserving their error behavior. A row is returned once even when it moved between OR branches or changed its primary key. On commit the indexes are updated; on rollback they are unchanged.

Relationships

• Relationships
  • Overview
  • Defining Foreign Keys
    • Foreign Key Syntax
    • Foreign Key Constraints
  • Referential Integrity
    • Insert Validation
    • Update Validation
  • Delete Behaviors
    • Restrict
    • Cascade
    • Choosing a Delete Behavior
  • Eager Loading
    • Basic Eager Loading
    • Multiple Relations
    • Eager Loading with Filters
    • Cross-Table Queries with Joins
  • Common Patterns
    • One-to-Many
    • Many-to-Many
    • Self-Referential

Overview

wasm-dbms supports foreign key relationships between tables, providing:

• Referential integrity: Ensures foreign keys point to valid records
• Delete behaviors: Control what happens when referenced records are deleted
• Eager loading: Load related records in a single query

Defining Foreign Keys

Foreign Key Syntax

Use the #[foreign_key] attribute to define relationships:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "posts"]
pub struct Post {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    pub content: Text,

    #[foreign_key(entity = "User", table = "users", column = "id")]
    pub author_id: Uint32,
}
}

Attribute parameters:

Foreign Key Constraints

When you define a foreign key:

1. The field type must match the referenced column type
2. The referenced table must be registered in your database schema
3. Foreign key values must reference existing records (enforced on insert/update)

Existence checks, eager loading and delete behaviors all match foreign key values against the declared column. A referenced column other than the primary key should be #[unique], so that each value identifies a single record.

When an update changes the referenced column of a record, whether it is the primary key or another column, the new value is written to every referencing row, so references stay valid. An update that leaves the referenced column unchanged does not touch the referencing rows.

Nullable Foreign Keys

Declare an optional relation by wrapping the foreign key type in Nullable:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "employees"]
pub struct Employee {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    #[foreign_key(entity = "Employee", table = "employees", column = "id")]
    pub manager_id: Nullable<Uint32>,
}
}

A null foreign key references no record, so inserts and updates skip the existence check for it. In the generated record, the relation field has the same type as for a required foreign key, Option<Box<EmployeeRecord>>. It is Some when the relation is eager loaded and the foreign key is set, and None when the foreign key is null or the relation is not eager loaded.

Referential Integrity

wasm-dbms enforces referential integrity automatically.

Insert Validation

When inserting a record with a foreign key, the referenced record must exist:

#![allow(unused)]
fn main() {
// This user exists
database.insert::<User>(UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    ..
})?;

// Insert post referencing existing user - OK
database.insert::<Post>(PostInsertRequest {
    id: 1.into(),
    title: "My Post".into(),
    author_id: 1.into(),  // User 1 exists
    ..
})?;

// Insert post referencing non-existent user - FAILS
let result = database.insert::<Post>(PostInsertRequest {
    id: 2.into(),
    title: "Another Post".into(),
    author_id: 999.into(),  // User 999 doesn't exist
    ..
});

assert!(matches!(
    result,
    Err(DbmsError::Query(QueryError::BrokenForeignKeyReference))
));
}

Update Validation

Updates are also validated:

#![allow(unused)]
fn main() {
// Changing author_id to non-existent user fails
let update = PostUpdateRequest::builder()
    .set_author_id(999.into())  // User 999 doesn't exist
    .filter(Filter::eq("id", Value::Uint32(1.into())))
    .build();

let result = database.update::<Post>(update);
assert!(matches!(
    result,
    Err(DbmsError::Query(QueryError::BrokenForeignKeyReference))
));
}

Delete Behaviors

When deleting a record that is referenced by other records, you must specify how to handle the references.

Restrict

Behavior: Fail if any records reference this one.

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::DeleteBehavior;

// User has posts - delete fails
let result = database.delete::<User>(
    DeleteBehavior::Restrict,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
);

match result {
    Err(DbmsError::Query(QueryError::ForeignKeyConstraintViolation)) => {
        println!("Cannot delete: user has posts");
    }
    _ => {}
}

// Delete posts first, then user
database.delete::<Post>(
    DeleteBehavior::Restrict,
    Some(Filter::eq("author_id", Value::Uint32(1.into()))),
)?;

// Now user can be deleted
database.delete::<User>(
    DeleteBehavior::Restrict,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
)?;
}

Use when: You want to prevent accidental data loss. The caller must explicitly handle related records.

Cascade

Behavior: Delete all records that reference this one (recursively).

#![allow(unused)]
fn main() {
// Deletes user AND all their posts
database.delete::<User>(
    DeleteBehavior::Cascade,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
)?;
}

Cascade is recursive:

#![allow(unused)]
fn main() {
// Schema:
// User -> Posts -> Comments
// Deleting a user cascades to posts, which cascades to comments

database.delete::<User>(
    DeleteBehavior::Cascade,
    Some(Filter::eq("id", Value::Uint32(1.into()))),
)?;
// User deleted
// All user's posts deleted
// All comments on those posts deleted
}

Use when: Related records have no meaning without the parent (e.g., comments on a deleted post).

Choosing a Delete Behavior

Eager Loading

Eager loading fetches related records in a single query, avoiding N+1 query problems.

Basic Eager Loading

Use .with() to eager load a related table:

#![allow(unused)]
fn main() {
// Load posts with their authors
let query = Query::builder()
    .all()
    .with("users")  // Name of the related table
    .build();

let posts = database.select::<Post>(query)?;

// Each post now has author data available
for post in posts {
    println!("Post '{}' by author_id {}", post.title, post.author_id);
}
}

Multiple Relations

Load multiple related tables:

#![allow(unused)]
fn main() {
// Schema:
// Post -> User (author)
// Post -> Category

let query = Query::builder()
    .all()
    .with("users")
    .with("categories")
    .build();

let posts = database.select::<Post>(query)?;
}

Eager Loading with Filters

Combine eager loading with filters:

#![allow(unused)]
fn main() {
// Load published posts with their authors
let query = Query::builder()
    .filter(Filter::eq("published", Value::Boolean(true)))
    .order_by("created_at", OrderDirection::Descending)
    .limit(10)
    .with("users")
    .build();

let posts = database.select::<Post>(query)?;
}

Cross-Table Queries with Joins

In addition to eager loading, wasm-dbms supports SQL-style joins (INNER, LEFT, RIGHT, FULL) for combining rows from multiple tables into a flat result set. Joins are useful when you need columns from several tables in a single row – for example, listing post titles alongside author names. Unlike eager loading, joins return untyped results via the select_raw path.

See the Querying Guide – Joins section for full details and examples.

Common Patterns

One-to-Many

A user has many posts:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
}

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "posts"]
pub struct Post {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    #[foreign_key(entity = "User", table = "users", column = "id")]
    pub author_id: Uint32,
}

// Query all posts by a user
let query = Query::builder()
    .filter(Filter::eq("author_id", Value::Uint32(user_id.into())))
    .build();
let user_posts = database.select::<Post>(query)?;
}

Many-to-Many

Use a junction table for many-to-many relationships:

#![allow(unused)]
fn main() {
// Students and Courses (many-to-many)

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "students"]
pub struct Student {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
}

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "courses"]
pub struct Course {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
}

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "enrollments"]
pub struct Enrollment {
    #[primary_key]
    pub id: Uint32,
    #[foreign_key(entity = "Student", table = "students", column = "id")]
    pub student_id: Uint32,
    #[foreign_key(entity = "Course", table = "courses", column = "id")]
    pub course_id: Uint32,
    pub enrolled_at: DateTime,
}

// Find all courses for a student
let query = Query::builder()
    .filter(Filter::eq("student_id", Value::Uint32(student_id.into())))
    .with("courses")
    .build();
let enrollments = database.select::<Enrollment>(query)?;
}

Self-Referential

A table can reference itself (e.g., categories with parent categories, employees with managers):

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "employees"]
pub struct Employee {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    #[foreign_key(entity = "Employee", table = "employees", column = "id")]
    pub manager_id: Nullable<Uint32>,  // Nullable for top-level employees
}

// Find all employees under a manager
let query = Query::builder()
    .filter(Filter::eq("manager_id", Value::Uint32(manager_id.into())))
    .build();
let direct_reports = database.select::<Employee>(query)?;
}

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "categories"]
pub struct Category {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    #[foreign_key(entity = "Category", table = "categories", column = "id")]
    pub parent_id: Nullable<Uint32>,  // Nullable for root categories
}

// Find root categories
let query = Query::builder()
    .filter(Filter::is_null("parent_id"))
    .build();
let root_categories = database.select::<Category>(query)?;

// Find children of a category
let query = Query::builder()
    .filter(Filter::eq("parent_id", Value::Uint32(parent_id.into())))
    .build();
let children = database.select::<Category>(query)?;
}

Transactions

• Transactions
  • Overview
  • Transaction Lifecycle
    • Begin Transaction
    • Perform Operations
    • Commit
    • Rollback
  • Who May Use a Transaction
  • ACID Properties
    • Atomicity
    • Consistency
    • Isolation
    • Durability
  • Error Handling
    • Handling Failures
    • Transaction Errors
  • Best Practices
    • 1. Keep transactions short
    • 2. Always handle rollback
    • 3. Use transactions for related operations
    • 4. Don’t mix transactional and non-transactional operations
  • Examples
    • Bank Transfer
    • Order Processing

Overview

wasm-dbms supports ACID transactions, allowing you to group multiple database operations into a single atomic unit. Either all operations succeed and are committed together, or none of them take effect.

Key features:

• Atomicity: All operations in a transaction succeed or fail together
• Consistency: Data integrity constraints are maintained
• Isolation: Transactions are isolated from each other
• Durability: Committed changes persist

Transaction Lifecycle

Begin Transaction

Start a new transaction using DbmsContext::begin_transaction():

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::*;

// Begin a new transaction
let tx_id = ctx.begin_transaction();
println!("Started transaction: {}", tx_id);
}

The returned transaction ID is used to create a transactional database instance.

Perform Operations

Create a WasmDbmsDatabase with the transaction ID and perform operations:

#![allow(unused)]
fn main() {
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// Insert within transaction
database.insert::<User>(user)?;

// Update within transaction
database.update::<User>(update)?;

// Delete within transaction
database.delete::<User>(DeleteBehavior::Restrict, Some(filter))?;

// Select within transaction (sees uncommitted changes)
let users = database.select::<User>(query)?;
}

Note: Operations within a transaction are visible to subsequent operations in the same transaction, but not to other callers until committed.

Commit

Commit the transaction to make all changes permanent:

#![allow(unused)]
fn main() {
// Commit the transaction
database.commit()?;
println!("Transaction committed successfully");
}

After commit:

• All changes become visible to other callers
• The transaction ID becomes invalid
• Changes persist in storage

Rollback

Rollback the transaction to discard all changes:

#![allow(unused)]
fn main() {
// Rollback the transaction
database.rollback()?;
println!("Transaction rolled back");
}

After rollback:

• All changes within the transaction are discarded
• The transaction ID becomes invalid
• The database state is as if the transaction never happened

Who May Use a Transaction

The engine identifies a transaction by its id and nothing else. Any code that holds the id can read and write through it, commit it, or roll it back; DbmsContext does not record who opened it. This keeps the engine free of platform concepts such as users, principals, or tenants.

When transactions cross a trust boundary, for example a canister serving many principals or a host serving many tenants, the application layer that embeds the engine decides who may use an id. The Embedding wasm-dbms guide shows how to keep a ledger from transaction id to identity and check it before every transactional call. Single-tenant programs do not need one.

ACID Properties

Atomicity

All operations in a transaction are treated as a single unit. If any operation fails, the entire transaction can be rolled back:

#![allow(unused)]
fn main() {
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// First operation succeeds
database.insert::<User>(user1)?;

// Second operation fails (e.g., primary key conflict)
let result = database.insert::<User>(user2_duplicate);

if result.is_err() {
    // Rollback everything - user1 is also discarded
    database.rollback()?;
}
}

Consistency

Transactions maintain data integrity:

• Primary key uniqueness is enforced
• Foreign key constraints are checked
• Validators run on all data
• Sanitizers are applied

#![allow(unused)]
fn main() {
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// This will fail if referenced user doesn't exist
let post = PostInsertRequest {
    id: 1.into(),
    title: "My Post".into(),
    author_id: 999.into(),  // Non-existent user
};

let result = database.insert::<Post>(post);
// Returns Err(DbmsError::Query(QueryError::BrokenForeignKeyReference))
}

Isolation

Changes made within a transaction are not visible to other callers until committed:

#![allow(unused)]
fn main() {
// Database A starts a transaction
let tx_id = ctx.begin_transaction();
let mut db_a = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);
db_a.insert::<User>(new_user)?;

// Database B queries - does NOT see the new user
let db_b = WasmDbmsDatabase::oneshot(&ctx, my_schema);
let users = db_b.select::<User>(query)?;
assert!(!users.iter().any(|u| u.id == new_user.id));

// Database A commits
db_a.commit()?;

// Now Database B can see the user
let users = db_b.select::<User>(query)?;
assert!(users.iter().any(|u| u.id == new_user.id));
}

Durability

Committed transactions persist in the provider’s logical memory. With the WASI file provider, writes reach the file during normal provider operations. With WasiKeyValueMemoryProvider, call ctx.flush() after the commit to publish the checkpoint; a transaction commit alone does not guarantee that a fresh component instance sees the data after restart. See the key-value provider guide.

Error Handling

Handling Failures

When an operation fails within a transaction, you should typically rollback:

#![allow(unused)]
fn main() {
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

fn process_order(database: &impl Database) -> Result<(), DbmsError> {
    // Multiple operations that should succeed together
    database.insert::<Order>(order)?;
    database.update::<Inventory>(update)?;
    database.insert::<OrderItem>(item)?;
    Ok(())
}

match process_order(&database) {
    Ok(()) => {
        database.commit()?;
        println!("Order processed successfully");
    }
    Err(e) => {
        database.rollback()?;
        println!("Order failed, rolled back: {:?}", e);
    }
}
}

Transaction Errors

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::{DbmsError, TransactionError};

match database.commit() {
    Ok(()) => println!("Committed"),
    Err(DbmsError::Transaction(TransactionError::NoActiveTransaction)) => {
        println!("No active transaction to commit");
    }
    Err(e) => println!("Other error: {:?}", e),
}
}

Best Practices

1. Keep transactions short

Long-running transactions hold resources and block other operations:

#![allow(unused)]
fn main() {
// GOOD: Prepare data outside transaction
let users_to_insert = prepare_users();

let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);
for user in users_to_insert {
    database.insert::<User>(user)?;
}
database.commit()?;

// BAD: Doing expensive work inside transaction
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);
for raw_data in large_dataset {
    let user = expensive_parsing(raw_data);  // Don't do this in transaction
    database.insert::<User>(user)?;
}
database.commit()?;
}

2. Always handle rollback

Ensure transactions are either committed or rolled back:

#![allow(unused)]
fn main() {
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

let result = (|| -> Result<(), DbmsError> {
    database.insert::<User>(user1)?;
    database.insert::<User>(user2)?;
    Ok(())
})();

match result {
    Ok(()) => database.commit()?,
    Err(_) => database.rollback()?,
}
}

3. Use transactions for related operations

Group operations that should succeed or fail together:

#![allow(unused)]
fn main() {
// GOOD: Related operations in transaction
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);
database.insert::<Order>(order)?;
database.insert::<Payment>(payment)?;
database.update::<Inventory>(inv_update)?;
database.commit()?;

// BAD: Unrelated operations in transaction (unnecessary)
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);
database.insert::<UserPreferences>(prefs)?;
database.insert::<AuditLog>(log)?;  // Unrelated
database.commit()?;
}

4. Don’t mix transactional and non-transactional operations

#![allow(unused)]
fn main() {
let tx_id = ctx.begin_transaction();
let mut database = WasmDbmsDatabase::from_transaction(&ctx, my_schema, tx_id);

// GOOD: All operations use the transaction
database.insert::<Order>(order)?;
database.insert::<OrderItem>(item)?;

// BAD: Mixing transaction and non-transaction
let oneshot = WasmDbmsDatabase::oneshot(&ctx, my_schema);
database.insert::<Order>(order)?;
oneshot.insert::<AuditLog>(log)?;  // Not in transaction!
}

Examples

Bank Transfer

Transfer money between accounts atomically:

#![allow(unused)]
fn main() {
fn transfer(
    ctx: &DbmsContext<impl MemoryProvider>,
    from_account: u32,
    to_account: u32,
    amount: Decimal,
) -> Result<(), DbmsError> {
    let tx_id = ctx.begin_transaction();
    let mut database = WasmDbmsDatabase::from_transaction(ctx, my_schema, tx_id);

    // Deduct from source account
    let deduct = AccountUpdateRequest::builder()
        .decrease_balance(amount)
        .filter(Filter::eq("id", Value::Uint32(from_account.into())))
        .build();
    database.update::<Account>(deduct)?;

    // Add to destination account
    let add = AccountUpdateRequest::builder()
        .increase_balance(amount)
        .filter(Filter::eq("id", Value::Uint32(to_account.into())))
        .build();
    database.update::<Account>(add)?;

    // Record the transfer
    let transfer_record = TransferInsertRequest {
        id: Uuid::new_v4().into(),
        from_account: from_account.into(),
        to_account: to_account.into(),
        amount,
        timestamp: DateTime::now(),
    };
    database.insert::<Transfer>(transfer_record)?;

    // Commit atomically
    database.commit()?;
    Ok(())
}
}

Order Processing

Process an order with inventory update:

#![allow(unused)]
fn main() {
fn process_order(
    ctx: &DbmsContext<impl MemoryProvider>,
    order: OrderInsertRequest,
    items: Vec<OrderItemInsertRequest>,
) -> Result<u32, Box<dyn std::error::Error>> {
    let tx_id = ctx.begin_transaction();
    let mut database = WasmDbmsDatabase::from_transaction(ctx, my_schema, tx_id);

    // Insert the order
    database.insert::<Order>(order.clone())?;

    // Insert order items and update inventory
    for item in items {
        // Insert order item
        database.insert::<OrderItem>(item.clone())?;

        // Decrease inventory
        let inv_update = InventoryUpdateRequest::builder()
            .decrease_quantity(item.quantity)
            .filter(Filter::eq("product_id", Value::Uint32(item.product_id)))
            .build();

        let updated = database.update::<Inventory>(inv_update)?;

        if updated == 0 {
            // Product not in inventory, rollback
            database.rollback()?;
            return Err("Product not found in inventory".into());
        }
    }

    // All successful, commit
    database.commit()?;
    Ok(order.id.into())
}
}

SQL

• SQL
  • Overview
  • Setup
    • Dependencies
    • Create the Engine
    • Where to Keep the Engine
  • Running Statements
    • Parameters
    • Reading Results
  • Queries
    • Filtering and Sorting
    • Joins
    • Aggregates
  • Writes
  • Transactions
  • Dates, UUIDs, and Other Types
  • Error Handling
  • SQL and the Typed API

Overview

The wasm-dbms-sql crate lets you read and write a wasm-dbms database with SQL text instead of the query builder:

#![allow(unused)]
fn main() {
let result = engine.execute(
    &ctx,
    None,
    "SELECT name, email FROM users WHERE age >= ? ORDER BY name",
    &[Value::from(18u8)],
)?;
}

It is useful when:

• you already know SQL and want to explore or debug the data;
• the query is easier to read as text than as a chain of builder calls;
• the statement comes from outside your program, such as an admin tool.

SQL covers SELECT (with joins, DISTINCT, aggregates, and GROUP BY), INSERT, UPDATE, DELETE, and transactions. It does not change the schema: tables are still defined with #[derive(Table)]. Every detail of the dialect is in the SQL Reference.

SQL support is a separate crate. Programs that do not depend on it do not pay for it in binary size.

Setup

Dependencies

[dependencies]
wasm-dbms = "0.9"
wasm-dbms-api = "0.9"
wasm-dbms-memory = "0.9"
wasm-dbms-sql = "0.9"

wasm-dbms-sql turns on the sql feature of wasm-dbms and wasm-dbms-api by itself. That feature adds the SqlResult and SqlError types to wasm_dbms_api::prelude.

Create the Engine

The engine needs your database schema, and the schema must be Clone. A schema is normally a unit struct, so deriving Clone is enough:

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::*;
use wasm_dbms_api::prelude::*;
use wasm_dbms_memory::prelude::HeapMemoryProvider;
use wasm_dbms_sql::SqlEngine;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    pub email: Nullable<Text>,
    pub age: Uint8,
}

#[derive(Clone, DatabaseSchema)]
#[tables(User = "users")]
pub struct MySchema;

let ctx = DbmsContext::new(HeapMemoryProvider::default());
MySchema::register_tables(&ctx)?;

let engine = SqlEngine::new(MySchema);
}

Where to Keep the Engine

The engine holds nothing but the schema. Open transactions live in the DbmsContext, so an engine created for a single call and an engine kept for the life of the process behave the same. The engine does not borrow the context, so both can be stored side by side:

#![allow(unused)]
fn main() {
thread_local! {
    static DBMS: DbmsContext<MyMemoryProvider> = DbmsContext::new(MyMemoryProvider::default());
    static SQL: SqlEngine<MySchema> = SqlEngine::new(MySchema);
}

fn run_sql(tx: Option<TransactionId>, sql: &str, params: &[Value]) -> Result<SqlResult, SqlError> {
    DBMS.with(|ctx| SQL.with(|engine| engine.execute(ctx, tx, sql, params)))
}
}

Running Statements

#![allow(unused)]
fn main() {
pub fn execute(
    &self,
    ctx: &DbmsContext<M>,
    tx: Option<TransactionId>,
    sql: &str,
    params: &[Value],
) -> Result<SqlResult, SqlError>
}

tx is a transaction id returned by BEGIN or by DbmsContext::begin_transaction. The engine does not check who passes an id: see Transaction Ownership when statements come from more than one user.

Parameters

Write ? where a value goes and pass the values separately:

#![allow(unused)]
fn main() {
engine.execute(
    &ctx,
    None,
    "INSERT INTO users (id, name, email, age) VALUES (?, ?, ?, ?)",
    &[
        Value::from(1u32),
        Value::from("Alice"),
        Value::Null,
        Value::from(30u8),
    ],
)?;
}

Always pass values that come from users as parameters. A parameter is data: it is never read as SQL, so it cannot change what the statement does. Building the SQL text with format! does not have that guarantee.

Parameters do not need the exact type of the column. An integer of any width fits any integer column if the number is in range, and a text value is converted the same way as a string literal.

Reading Results

execute returns a SqlResult:

#![allow(unused)]
fn main() {
match engine.execute(&ctx, None, sql, &[])? {
    SqlResult::Rows(rows) => {
        for row in rows {
            for (column, value) in row {
                println!("{name} = {value:?}", name = column.name);
            }
        }
    }
    SqlResult::RowsAffected(count) => println!("{count} rows written"),
    SqlResult::TxBegin(tx) => println!("transaction {tx} started"),
    SqlResult::TxCommit | SqlResult::TxRollback => {}
}
}

Each row has one (JoinColumnDef, Value) pair per selected column, in the order of the select list. column.name is the column name, or the alias given with AS. For a join, column.table holds the table the column comes from.

Queries

Filtering and Sorting

SELECT id, name
FROM users
WHERE (age < 18 OR age > 65)
  AND email IS NOT NULL
  AND name LIKE 'A%'
ORDER BY age DESC, name
LIMIT 10 OFFSET 20;

LIMIT and OFFSET accept values through 4294967295 on every target. Larger values are rejected consistently by native and WASM builds.

WHERE supports =, !=, <, <=, >, >=, IN, LIKE, IS NULL, and their negations, combined with AND, OR, NOT, and parentheses. The left side of a test is always a column, and the right side a value.

SELECT DISTINCT removes duplicate rows:

SELECT DISTINCT city FROM users ORDER BY city;

Joins

SELECT u.name, p.title AS headline
FROM users AS u
JOIN posts AS p ON u.id = p.user_id
WHERE p.published = TRUE
ORDER BY p.title;

JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN are supported. The ON condition is one equality between a column of the joined table and a column of a table before it. When two tables have a column with the same name, qualify it with the table name or alias.

The two ON columns must have the same underlying type, even if their nullability differs. Incompatible types produce SqlError::TypeMismatch. Null join keys never match, including two NULL values.

Aggregates

SELECT category, COUNT(*) AS items, SUM(price) AS revenue
FROM sales
WHERE price > 0
GROUP BY category
HAVING COUNT(*) >= 5
ORDER BY revenue DESC
LIMIT 3;

COUNT, SUM, AVG, MIN, and MAX are available. SUM and AVG return a Decimal, and COUNT a Uint64. Aggregate queries read a single table: they cannot be combined with a join.

Writes

INSERT INTO users (id, name, age) VALUES (2, 'Bob', 25);

UPDATE users SET name = 'Robert', email = 'bob@example.com' WHERE id = 2;

DELETE FROM users WHERE id = 2;

Each write returns SqlResult::RowsAffected with the number of rows written.

UPDATE and DELETE must have a WHERE clause. Without one the statement is rejected with SqlError::MissingWhereClause and nothing changes. This protects against wiping a table by accident.

DELETE fails when another table still references the row. Add CASCADE to delete the referencing rows as well:

DELETE FROM users WHERE id = 1 CASCADE;

Writes go through the same sanitizers, validators, and integrity checks as the typed API.

Transactions

BEGIN, COMMIT, and ROLLBACK group several statements into one unit. BEGIN returns the id of the new transaction; every later statement of the unit, including COMMIT or ROLLBACK, passes that id:

#![allow(unused)]
fn main() {
let SqlResult::TxBegin(tx) = engine.execute(&ctx, None, "BEGIN", &[])? else {
    unreachable!("BEGIN returns TxBegin");
};

let transfer = (|| {
    engine.execute(&ctx, Some(tx), "UPDATE accounts SET balance = ? WHERE id = ?", &[
        Value::from(50u64),
        Value::from(1u32),
    ])?;
    engine.execute(&ctx, Some(tx), "UPDATE accounts SET balance = ? WHERE id = ?", &[
        Value::from(150u64),
        Value::from(2u32),
    ])
})();

match transfer {
    Ok(_) => engine.execute(&ctx, Some(tx), "COMMIT", &[])?,
    Err(error) => {
        engine.execute(&ctx, Some(tx), "ROLLBACK", &[])?;
        return Err(error);
    }
};
}

What to know:

• BEGIN is sent without an id and returns the new id in SqlResult::TxBegin. Sending BEGIN with an id fails with SqlError::TransactionAlreadyActive.
• Statements sent with the id run inside the transaction and see its uncommitted writes. Statements sent without an id are applied immediately and do not see them.
• Several transactions can be open at once. Each is addressed by its own id and does not see the writes of the others.
• A statement that fails does not close the transaction. Send ROLLBACK to discard it, as in the example above.
• COMMIT applies all changes together. If it fails, for example because another statement took the same primary key in the meantime, nothing is applied and the transaction is closed. A later COMMIT with the same id fails with SqlError::Runtime(DbmsError::Query(QueryError::TransactionNotFound)).
• COMMIT or ROLLBACK without an id fails with SqlError::NoActiveTransaction.
• ctx.has_transaction(&tx) tells whether a transaction is still open.

The ids are shared with the typed API: a transaction opened with ctx.begin_transaction() can run SQL statements, and one opened with BEGIN can be passed to WasmDbmsDatabase::from_transaction. The engine does not check who sends an id; a program that serves several users keeps its own ledger, as described in Transaction Ownership.

Dates, UUIDs, and Other Types

SQL has literals for numbers, strings, booleans, and NULL. Other values are written as strings and converted by the type of the column:

INSERT INTO events (day, at, token, payload, meta)
VALUES ('2026-04-24', '2026-04-24T10:30:00Z',
        '550e8400-e29b-41d4-a716-446655440000', 'deadbeef',
        '{"kind": "signup"}');

SELECT * FROM events WHERE day >= '2026-01-01';

The same values can be passed as typed parameters (Value::Date, Value::Uuid, and so on), which is the only way to pass a custom data type.

A value that does not fit its column is reported before anything is written, for example 300 for a Uint8 column or '2026-02-30' for a Date column.

Error Handling

execute returns a SqlError:

#![allow(unused)]
fn main() {
match engine.execute(&ctx, None, sql, params) {
    Ok(result) => { /* ... */ }
    Err(SqlError::Parse { line, col, msg }) => {
        println!("syntax error at {line}:{col}: {msg}");
    }
    Err(SqlError::UnknownTable(table)) => println!("no table named {table}"),
    Err(SqlError::UnknownColumn { table, column }) => {
        println!("table {table} has no column {column}");
    }
    Err(SqlError::Runtime(DbmsError::Query(QueryError::PrimaryKeyConflict))) => {
        println!("that key already exists");
    }
    Err(other) => println!("{other}"),
}
}

Problems in the statement itself (syntax, unknown names, values of the wrong type) are found before the database is touched. Errors raised by the database while running the statement, such as constraint violations, are wrapped in SqlError::Runtime. All variants are listed in the Errors Reference.

SQL and the Typed API

SQL and the typed API work on the same data and can be mixed freely: a row inserted with SQL is returned by database.select::<User>(...), and the other way round.

Some features are only available through the typed API:

• JSON filters on the content of Json columns;
• eager loading of related records;
• typed records (UserRecord) instead of lists of column values;
• schema migrations.

Custom Data Types

• Custom Data Types
  • Overview
  • Defining a Custom Type
    • Step 1: Define the Type
    • Step 2: Implement Display
    • Step 3: Implement Encode
    • Step 4: Implement DataType and Derive CustomDataType
  • Using Custom Types in Tables
    • The custom_type Attribute
    • Nullable Custom Types
  • Filtering and Querying
  • Ordering Contract
  • Examples
    • Enum: Priority
    • Struct: Address

Overview

wasm-dbms ships with a set of built-in data types that cover the most common use cases. When your domain requires types that go beyond those built-ins, you can define custom data types.

Custom data types let you store any Rust type – enums, newtypes, structs – inside your tables. The DBMS engine stores them as opaque bytes internally and uses a type tag string to identify each custom type.

When to use custom types:

• Domain-specific enums (e.g., Priority, Status, Role)
• Composite value objects (e.g., Address, Coordinates)
• Newtypes that wrap primitives with domain meaning (e.g., Email(String))

Defining a Custom Type

Creating a custom type requires four steps:

1. Define the type with the required derives
2. Implement Display
3. Implement Encode (binary serialization)
4. Implement DataType and derive CustomDataType

Step 1: Define the Type

Your type must derive or implement several traits. For enums, all must be implemented manually or derived:

#![allow(unused)]
fn main() {
use serde::{Deserialize, Serialize};

#[derive(
    Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize,
)]
pub enum Priority {
    #[default]
    Low,
    Medium,
    High,
}
}

For structs, the same traits are required:

#![allow(unused)]
fn main() {
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize)]
pub struct Address {
    pub street: String,
    pub city: String,
    pub zip: String,
}
}

Required traits:

Note: For Internet Computer usage, see the ic-dbms documentation.

Step 2: Implement Display

The Display implementation provides a human-readable representation used for logging and diagnostics:

#![allow(unused)]
fn main() {
use std::fmt;

impl fmt::Display for Priority {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        match self {
            Priority::Low => write!(f, "low"),
            Priority::Medium => write!(f, "medium"),
            Priority::High => write!(f, "high"),
        }
    }
}

impl fmt::Display for Address {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(f, "{}, {} {}", self.street, self.city, self.zip)
    }
}
}

Step 3: Implement Encode

The Encode trait defines how your type is serialized to and from bytes for memory storage. Enums require a manual implementation; for structs, you can use #[derive(Encode)].

Enum (manual implementation):

#![allow(unused)]
fn main() {
use std::borrow::Cow;

use wasm_dbms_api::prelude::*;

impl Encode for Priority {
    const SIZE: DataSize = DataSize::Fixed(1);
    const ALIGNMENT: PageOffset = DEFAULT_ALIGNMENT;

    fn encode(&self) -> Cow<'_, [u8]> {
        Cow::Owned(vec![match self {
            Priority::Low => 0,
            Priority::Medium => 1,
            Priority::High => 2,
        }])
    }

    fn decode(data: Cow<[u8]>) -> MemoryResult<Self> {
        match data[0] {
            0 => Ok(Priority::Low),
            1 => Ok(Priority::Medium),
            2 => Ok(Priority::High),
            other => Err(MemoryError::DecodeError(DecodeError::TryFromSliceError(
                format!("invalid Priority byte: {other}"),
            ))),
        }
    }

    fn size(&self) -> MSize {
        1
    }
}
}

Struct (derive macro):

The #[derive(Encode)] macro works for structs whose fields all implement Encode. Since String does not implement Encode but Text does, use wasm-dbms types for the struct fields:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(
    Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize, Encode,
)]
pub struct Address {
    pub street: Text,
    pub city: Text,
    pub zip: Text,
}
}

A struct without fields (struct Empty {} or struct Empty;) is also supported. It encodes to zero bytes: SIZE is DataSize::Fixed(0) and ALIGNMENT is 1. The alignment is also 1 for any fixed-size struct whose total size is 0, because storage divides offsets by the alignment.

Key Encode concepts:

Step 4: Implement DataType and Derive CustomDataType

Finally, implement the DataType marker trait and derive CustomDataType with a unique type tag:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

impl DataType for Priority {}

// Manual CustomDataType implementation for enums
impl CustomDataType for Priority {
    const TYPE_TAG: &'static str = "priority";
}

// Manual From<Priority> for Value implementation
impl From<Priority> for Value {
    fn from(val: Priority) -> Value {
        Value::Custom(CustomValue {
            type_tag: <Priority as CustomDataType>::TYPE_TAG.to_string(),
            encoded: Encode::encode(&val).into_owned(),
            display: val.to_string(),
        })
    }
}
}

For structs, you can use the CustomDataType derive macro instead of the manual implementation above:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

impl DataType for Address {}

#[derive(CustomDataType)]
#[type_tag = "address"]
pub struct Address {
    // ...
}
}

The #[derive(CustomDataType)] macro generates both the CustomDataType trait implementation and the From<T> for Value conversion. For enums, you must write these implementations manually.

Type tag rules:

• Must be unique across all custom types in your database
• Must be stable across upgrades (changing it makes existing data unreadable)
• Use lowercase, descriptive names (e.g., "priority", "address", "role")

Using Custom Types in Tables

The custom_type Attribute

To use a custom type in a table, annotate the field with #[custom_type]:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "tasks"]
pub struct Task {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    #[custom_type]
    pub priority: Priority,
    #[custom_type]
    pub address: Address,
}
}

Without the #[custom_type] attribute, the Table macro won’t know how to handle your type and compilation will fail.

Nullable Custom Types

Custom types can be wrapped in Nullable<T> for optional fields:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "tasks"]
pub struct Task {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    #[custom_type]
    pub priority: Nullable<Priority>,
}
}

When Nullable::Null, the value is stored as Value::Null. When Nullable::Value(v), it is stored as Value::Custom(...).

Filtering and Querying

To filter on custom type fields, construct a Value::Custom with the appropriate CustomValue:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

// Create a filter for Priority::High
let high_priority = Priority::High;
let filter = Filter::eq("priority", high_priority.into());

// You can also construct the Value manually
let filter = Filter::eq("priority", Value::Custom(CustomValue {
    type_tag: "priority".to_string(),
    encoded: Encode::encode(&Priority::High).into_owned(),
    display: "high".to_string(),
}));
}

To extract a custom type from a Value:

#![allow(unused)]
fn main() {
let value: Value = Priority::High.into();

// Get the raw CustomValue
if let Some(cv) = value.as_custom() {
    println!("type: {}, display: {}", cv.type_tag, cv.display);
}

// Decode into the concrete type
if let Some(priority) = value.as_custom_type::<Priority>() {
    println!("Priority: {priority}");
}
}

Ordering Contract

Custom types support all filter operations: Eq, Ne, In, Gt, Lt, Ge, Le.

For equality filters (Eq, Ne, In), the only requirement is that the Encode implementation produces canonical output – the same value always encodes to the same bytes.

For range filters (Gt, Lt, Ge, Le) and ORDER BY, the encoding must be order-preserving: if a < b according to Ord, then a.encode() < b.encode() lexicographically. This is because the DBMS compares custom values by their encoded bytes.

Example of order-preserving encoding:

The Priority enum above encodes Low = 0, Medium = 1, High = 2. Since Low < Medium < High in the Ord implementation and [0] < [1] < [2] lexicographically, range filters work correctly.

Warning: If your encoding is not order-preserving, equality filters will still work, but range filters and sorting will produce incorrect results.

Examples

Enum: Priority

A complete example of a custom enum type used in a table:

#![allow(unused)]
fn main() {
use std::borrow::Cow;
use std::fmt;

use serde::{Deserialize, Serialize};
use wasm_dbms_api::prelude::*;

// 1. Define the type
#[derive(
    Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize,
)]
pub enum Priority {
    #[default]
    Low,
    Medium,
    High,
}

// 2. Implement Display
impl fmt::Display for Priority {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        match self {
            Priority::Low => write!(f, "low"),
            Priority::Medium => write!(f, "medium"),
            Priority::High => write!(f, "high"),
        }
    }
}

// 3. Implement Encode (manual for enums)
impl Encode for Priority {
    const SIZE: DataSize = DataSize::Fixed(1);
    const ALIGNMENT: PageOffset = DEFAULT_ALIGNMENT;

    fn encode(&self) -> Cow<'_, [u8]> {
        Cow::Owned(vec![match self {
            Priority::Low => 0,
            Priority::Medium => 1,
            Priority::High => 2,
        }])
    }

    fn decode(data: Cow<[u8]>) -> MemoryResult<Self> {
        match data[0] {
            0 => Ok(Priority::Low),
            1 => Ok(Priority::Medium),
            2 => Ok(Priority::High),
            other => Err(MemoryError::DecodeError(DecodeError::TryFromSliceError(
                format!("invalid Priority byte: {other}"),
            ))),
        }
    }

    fn size(&self) -> MSize {
        1
    }
}

// 4. Implement DataType + CustomDataType + From<Priority> for Value
impl DataType for Priority {}

impl CustomDataType for Priority {
    const TYPE_TAG: &'static str = "priority";
}

impl From<Priority> for Value {
    fn from(val: Priority) -> Value {
        Value::Custom(CustomValue {
            type_tag: <Priority as CustomDataType>::TYPE_TAG.to_string(),
            encoded: Encode::encode(&val).into_owned(),
            display: val.to_string(),
        })
    }
}

// Use in a table
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "tasks"]
pub struct Task {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,
    #[custom_type]
    pub priority: Priority,
}
}

Struct: Address

A complete example of a custom struct type. Structs can use #[derive(Encode)] and #[derive(CustomDataType)]:

#![allow(unused)]
fn main() {
use std::fmt;

use serde::{Deserialize, Serialize};
use wasm_dbms_api::prelude::*;

// 1. Define the type with Encode and CustomDataType derives
#[derive(
    Debug,
    Clone,
    PartialEq,
    Eq,
    PartialOrd,
    Ord,
    Hash,
    Default,
    Serialize,
    Deserialize,
    Encode,
    CustomDataType,
)]
#[type_tag = "address"]
pub struct Address {
    pub street: Text,
    pub city: Text,
    pub zip: Text,
}

// 2. Implement Display
impl fmt::Display for Address {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(
            f,
            "{}, {} {}",
            self.street.as_str(),
            self.city.as_str(),
            self.zip.as_str(),
        )
    }
}

// 3. Implement DataType
impl DataType for Address {}

// Use in a table
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "customers"]
pub struct Customer {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    #[custom_type]
    pub address: Address,
}
}

Schema Migrations

• Schema Migrations
  • Overview
  • When You Need a Migration
  • The Workflow
  • Adding a Column
    • Nullable Columns
    • Non-Nullable Columns with a Static Default
    • Non-Nullable Columns with a Dynamic Default
  • Renaming a Column
  • Changing a Column Type
    • Compatible Widening
    • Custom Transform
  • Dropping a Column or Table
  • Tightening Constraints
  • Adding and Dropping Indexes
  • Running Migrations
  • Inspecting Drift Without Migrating
  • Recovering from a Failed Migration
  • Testing Migrations
  • Common Pitfalls

For the full type and API reference (snapshot format, op enum, error variants), see the Migrations Reference.

Overview

A migration in wasm-dbms is the process of bringing the on-disk data layout into agreement with the schema your binary was compiled against. The framework persists a TableSchemaSnapshot for every table on disk and hashes them into a single schema_hash. On boot, the DBMS recomputes the hash from the compiled schema and compares it. If they differ, the database enters drift state and refuses CRUD until you call migrate(policy).

Migrations are:

• Forward-only. Failed migrations roll back to the pre-migration state, but the framework provides no path from a newer snapshot to an older compiled schema.
• Explicit. The DBMS never auto-migrates on init. The operator decides when (and whether) to run them.
• Atomic. Every op runs inside a single journaled session — either every byte change commits, or none does.
• Pre-flighted. Each plan is validated against the current data before any page is touched. Errors here cost nothing.

When You Need a Migration

Drift fires whenever the encoded snapshot of any compiled table differs from the snapshot stored on disk. In practice, that means any of:

• Adding, removing, or renaming a struct that derives Table.
• Adding, removing, or renaming a field on such a struct.
• Changing a field’s type (e.g. Uint32 → Uint64, or Text → custom enum).
• Toggling #[primary_key], #[unique], #[autoincrement], Nullable<T>, or #[foreign_key(...)].
• Adding or removing an #[index] (single-column or grouped).
• Bumping #[alignment = N].

Never trigger drift:

• Adding #[validate(...)], #[sanitizer(...)], or #[default = ...] on its own (sanitizer/validator are runtime-only; #[default] is migration metadata that lives in the snapshot but is consulted by the planner, not by the drift hash for unrelated changes).
• Reordering doc comments or Debug derives.
• Changing the table’s Rust struct name without changing #[table = "..."].

The Workflow

For most schema changes, the loop is:

1. Edit the schema in your #[derive(Table)] structs.
2. Build and deploy the new binary.
3. Inspect drift. Call dbms.has_drift()?. Skip if false.
4. Plan. Call dbms.pending_migrations() and review the Vec<MigrationOp>.
5. Apply. Call dbms.migrate(policy) once the plan looks right.

The remaining sections walk through the common shapes of step 1 and the policy choices for step 5.

Adding a Column

Nullable Columns

Easiest case. The new column is implicitly NULL for every existing row.

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,

    pub bio: Nullable<Text>, // NEW — no further work needed
}
}

Plan output:

AddColumn { table: "users", column: ColumnSnapshot { name: "bio", nullable: true, default: None, ... } }

migrate(MigrationPolicy::default()) applies it cleanly.

Non-Nullable Columns with a Static Default

If the new column is NOT NULL, the planner needs a default value to backfill existing rows. The cheapest way is the #[default = ...] attribute:

#![allow(unused)]
fn main() {
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,

    #[default = 0]
    pub login_count: Uint32,
}
}

The expression must convert into the column’s Value variant via From/Into. Examples:

#![allow(unused)]
fn main() {
#[default = 0]                                pub login_count: Uint32,
#[default = false]                            pub is_admin: Boolean,
#[default = ""]                               pub locale: Text,
#[default = MyCustomEnum::Default]            pub status: MyCustomEnum,  // requires #[custom_type]
}

Non-Nullable Columns with a Dynamic Default

Sometimes the default depends on runtime context (e.g. derived from another column, or generated by a hash). Mark the table #[migrate] and override Migrate::default_value:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "events"]
#[migrate]
pub struct Event {
    #[primary_key]
    pub id: Uint32,
    pub kind: Text,

    pub severity: Uint8, // NEW
}

impl Migrate for Event {
    fn default_value(column: &str) -> Option<Value> {
        match column {
            "severity" => Some(Value::Uint8(Uint8(1))), // medium severity by default
            _ => None,
        }
    }
}
}

Returning None here falls back to the #[default] attribute. Returning None from both produces MigrationError::DefaultMissing.

Note: without #[migrate], the Table macro emits an empty impl Migrate for T {} for you. Adding a hand-written impl on top of it would be a duplicate.

Renaming a Column

A naive rename — change the field name and ship — looks to the planner like a DropColumn followed by an AddColumn. That destroys the data. Use #[renamed_from(...)] to tell the planner the rename history:

#![allow(unused)]
fn main() {
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[renamed_from("name", "username")]
    pub full_name: Text,
}
}

The planner walks the slice in order: it first looks for a stored column named name; if that misses, it tries username. The first hit emits RenameColumn { old, new: "full_name" } and the column’s data carries over intact.

Multiple renames across releases: keep older entries at the tail. If you renamed username → name in v2 and name → full_name in v3, list ["name", "username"] so a v1-installed database upgrading directly to v3 still finds its column.

Changing a Column Type

Compatible Widening

The framework auto-widens these without user code:

Just edit the field type and migrate. Plan output is WidenColumn { ... }.

Custom Transform

Anything else — narrowing, sign flip, int↔float, int↔text, custom enum reshape — needs a transform_column impl:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "events"]
#[migrate]
pub struct Event {
    #[primary_key]
    pub id: Uint32,

    pub severity: Uint8, // was: Text("low" | "medium" | "high")
}

impl Migrate for Event {
    fn default_value(_column: &str) -> Option<Value> {
        None
    }

    fn transform_column(column: &str, old: Value) -> DbmsResult<Option<Value>> {
        match column {
            "severity" => match old {
                Value::Text(Text(s)) => match s.as_str() {
                    "low" => Ok(Some(Value::Uint8(Uint8(1)))),
                    "medium" => Ok(Some(Value::Uint8(Uint8(5)))),
                    "high" => Ok(Some(Value::Uint8(Uint8(9)))),
                    other => Err(DbmsError::Migration(MigrationError::TransformAborted {
                        table: "events".into(),
                        column: column.into(),
                        reason: format!("unknown severity `{other}`"),
                    })),
                },
                _ => Ok(None),
            },
            _ => Ok(None),
        }
    }
}
}

Return values:

• Ok(Some(v)) → store v. The planner emits TransformColumn { old_type: Text, new_type: Uint8 }.
• Ok(None) → no transform. The framework errors with MigrationError::IncompatibleType unless a widening already applies.
• Err(_) → abort the migration. The journal rolls back.

Dropping a Column or Table

DropColumn and DropTable are destructive. The default MigrationPolicy::default() refuses them:

#![allow(unused)]
fn main() {
let plan = dbms.pending_migrations()?;   // shows DropTable / DropColumn ops
let result = dbms.migrate(MigrationPolicy::default());
// → Err(DbmsError::Migration(MigrationError::DestructiveOpDenied { op: "DropColumn" }))
}

Opt in explicitly:

#![allow(unused)]
fn main() {
dbms.migrate(MigrationPolicy { allow_destructive: true })?;
}

Tip: keep allow_destructive: false in the standard upgrade path and set it to true only when the operator has manually inspected pending_migrations() output. A typo in #[table = "..."] looks identical to a deliberate drop in the diff.

Tightening Constraints

A tightening is any AlterColumn change in the restrictive direction:

• nullable: true → nullable: false
• unique: false → unique: true
• adding a #[foreign_key(...)]

Tightenings run after all data rewrites (relaxations, widenings, transforms, adds). The planner validates existing rows against the new constraint at this step. Any violation produces MigrationError::ConstraintViolation { table, column, reason } and rolls back the entire session.

Recommended pattern (split across two releases):

1. Release N — relax + backfill:

   #![allow(unused)]
   fn main() {
   pub email: Nullable<Text>,   // still nullable
   }

   Backfill NULL rows manually or via a one-off update before shipping the next release.

2. Release N+1 — tighten:

   #![allow(unused)]
   fn main() {
   #[unique]
   pub email: Text,             // now NOT NULL + unique
   }

This isolates ConstraintViolation to a release whose cause is obvious.

Adding and Dropping Indexes

Add an #[index] and the planner emits AddIndex. Remove it and you get DropIndex. Composite indexes match by (sorted column list, unique), so changing the group name on a composite index is equivalent to dropping the old one and adding a new one with the same shape.

#![allow(unused)]
fn main() {
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[index] // NEW
    #[unique]
    pub email: Text,
}
}

Index migrations rebuild the B+ tree from scratch, so they scale O(n log n) with row count.

Running Migrations

#![allow(unused)]
fn main() {
use wasm_dbms::prelude::*;
use wasm_dbms_api::prelude::MigrationPolicy;

fn boot<M>(mut dbms: WasmDbmsDatabase<'_, M>) -> DbmsResult<()>
where
    M: MemoryProvider,
{
    if dbms.has_drift()? {
        let plan = dbms.pending_migrations()?;
        eprintln!("schema drift detected, applying {} ops", plan.len());
        for op in &plan {
            eprintln!("  {op:?}");
        }
        dbms.migrate(MigrationPolicy::default())?;
    }
    Ok(())
}
}

migrate is idempotent: when there is no drift, it is a no-op.

For the migration endpoints and upgrade hooks on the Internet Computer, see the ic-dbms documentation.

Inspecting Drift Without Migrating

pending_migrations() is safe to call regardless of drift state and never writes to memory. Use it to:

• Diff a development branch against production data.
• Generate a changelog entry from MigrationOp Debug output.
• Catch unintended drops in CI before the binary ships.

#![allow(unused)]
fn main() {
let plan = dbms.pending_migrations()?;
for op in plan {
    println!("{op:?}");
}
}

Recovering from a Failed Migration

A failed migrate() call rolls back every page touched in the journal session. Stored snapshots, schema_hash, and the in-memory drift flag are not mutated on failure. So after an error:

• The DBMS stays in drift state.
• Stored data is byte-identical to its pre-migration state.

Recovery is iterative:

1. Read the error variant. IncompatibleType, DefaultMissing, ConstraintViolation, DestructiveOpDenied, and TransformAborted each call out the offending table/column/reason.
2. Fix the cause: add #[default], write a transform_column arm, clean offending rows, or relax the policy.
3. Redeploy the binary (or just retry migrate if the fix is data-side, not schema-side).

There is no partial-success state to clean up. Either the plan applied in full or it didn’t apply at all.

Testing Migrations

The migration pipeline is testable end-to-end on the heap memory provider:

1. Register the old schema with a fresh DbmsContext.
2. Insert representative fixtures.
3. Drop the context and reopen it with the new schema (no rebuild, since this is just Rust code).
4. Assert has_drift() == true, inspect pending_migrations(), call migrate(policy).
5. Read the rows back and assert the expected post-migration state.

#![allow(unused)]
fn main() {
#[test]
fn renames_preserve_data() {
    // v1 schema: column "name"
    let ctx = DbmsContext::new(HeapMemoryProvider::default());
    SchemaV1::register_tables(&ctx).unwrap();
    let mut db = WasmDbmsDatabase::oneshot(&ctx, SchemaV1);
    db.insert::<UserV1>(/* ... */).unwrap();
    drop(db);

    // v2 schema: column renamed to "full_name"
    let mut db = WasmDbmsDatabase::oneshot(&ctx, SchemaV2);
    assert!(db.has_drift().unwrap());
    db.migrate(MigrationPolicy::default()).unwrap();

    let users: Vec<UserV2Record> = db.select::<UserV2>(Query::builder().build()).unwrap();
    assert_eq!(users[0].full_name, Some(/* ... */));
}
}

Round-trip the snapshots through Encode::encode / Encode::decode to confirm the wire format hasn’t shifted.

Common Pitfalls

• Renaming without #[renamed_from]. The planner has no way to know your intent; it will emit DropColumn + AddColumn and silently lose data the moment allow_destructive: true is set.
• Adding a non-nullable column without a default. Pre-flight will reject the plan with DefaultMissing. Either provide #[default], override Migrate::default_value, or make the column Nullable<T>.
• Tightening on dirty data. A nullable: false flip after a release that allowed nulls will fail unless every row already satisfies the constraint. Backfill in a prior release.
• Reordering DataTypeSnapshot discriminants. The on-disk format depends on the exact tag bytes. Treat the enum as frozen — new variants take fresh tags, removed ones leave a reserved hole.
• Bumping #[alignment = N]. This changes the on-disk record layout for the table. Until WidenColumn is generalised to handle alignment changes, this requires a manual rewrite. Avoid unless absolutely necessary.
• Calling migrate before register_tables. The drift hash is computed from the registered set. Always register every table that backs a #[derive(Table)] struct in the compiled binary, even if you don’t expect to write to it this release.

Wasmtime Example

• Overview
• How It Works
  • WIT Interface
  • Guest Component
  • Host Binary
• FileMemoryProvider
• Key-Value Guest Variant
• Building and Running
  • Prerequisites
  • Build
  • Run
• Extending with Custom Tables
• Key Concepts

This guide explains how to use wasm-dbms with the WebAssembly Component Model (WIT) and Wasmtime. It walks through the example in crates/wasm-dbms/example/.

Overview

The WebAssembly Component Model defines a standard way for WASM modules to expose typed interfaces using WIT (WebAssembly Interface Types). This example shows how to:

1. Define a WIT interface for the wasm-dbms CRUD and transaction API
2. Build a guest WASM component that wraps wasm-dbms behind the WIT interface
3. Run the guest inside a native Wasmtime host

This approach makes wasm-dbms usable from any Component Model host, not just Rust. The WIT contract at /wit/dbms.wit can be consumed by hosts written in Go, Python, JavaScript, or any language with Component Model tooling.

How It Works

WIT Interface

The WIT definition (/wit/dbms.wit) exposes a database interface with these operations:

• select — query rows from a table with optional filter, ordering, limit, and offset
• insert — insert a row into a table, optionally within a transaction
• update — update rows matching a filter, optionally within a transaction
• delete — delete rows matching a filter, optionally within a transaction
• begin-transaction — start a new ACID transaction
• commit / rollback — finalize or abort a transaction

Values are passed as a value variant type that covers booleans, integers, floats, strings, blobs, and null. Filters are JSON-serialized strings matching the wasm_dbms_api::Filter type.

Decimals, dates, date-times, JSON documents, and UUIDs travel as strings. The guest parses them strictly and returns an invalid-query error for a malformed value, such as 2025-02-30 or {, instead of storing a default or null value:

This raw/dynamic API is intentional: WIT cannot express Rust generics or user-defined table schemas, so type safety is enforced inside the guest by the wasm-dbms engine.

Guest Component

The guest (crates/wasm-dbms/example/guest/) compiles to wasm32-wasip2 and exports the WIT database interface. Internally it:

1. Initializes a file-backed or key-value DbmsContext lazily on first call; if storage cannot be opened or the tables cannot be registered, the call returns a dbms-error (for example memory-error) instead of trapping, and the next call retries
2. Registers example tables (users, posts) using #[derive(Table)]
3. Converts between WIT variant values and wasm-dbms Value types
4. Dispatches operations through a DatabaseSchema implementation

The bridge layer in lib.rs handles all the type conversions between the WIT boundary and the typed wasm-dbms internals.

Host Binary

The host (crates/wasm-dbms/example/host/) is a native Rust binary using Wasmtime. It:

1. Creates a Wasmtime engine with Component Model enabled
2. Creates a fresh, uniquely named directory under the system temporary directory and preopens it as the guest’s root, so the database file never touches existing data
3. Loads the guest .wasm component and instantiates it
4. Calls the exported database functions to demonstrate all operations

FileMemoryProvider

The FileMemoryProvider implements the MemoryProvider trait using std::fs file I/O. It provides persistent, file-backed storage so that data survives across invocations.

#![allow(unused)]
fn main() {
use wasm_dbms_memory::prelude::MemoryProvider;

pub struct FileMemoryProvider {
    file: File, // open file handle
    size: u64,  // current size in bytes
    pages: u64, // allocated pages (size / PAGE_SIZE)
}
}

Operations:

• grow(n) — extends the file by n × 65536 bytes
• read(offset, buf) — seeks to offset and reads into buffer
• write(offset, buf) — seeks to offset, writes buffer, and flushes

The provider is initialized with a file path relative to the WASI preopened directory (defaults to wasm-dbms.db).

Note: FileMemoryProvider does not handle concurrent access. It assumes single-writer usage.

Key-Value Guest Variant

The guest also has a key-value feature. Build and test it with:

just test_wasm_dbms_key_value_example
wasm-tools component wit .artifact/wasm-dbms-example-guest-key-value.wasm

This variant opens bucket default and database namespace example. It imports only wasi:keyvalue/store@0.2.0-draft2 and wasi:keyvalue/batch@0.2.0-draft2 for persistence; it does not require a filesystem preopen. Successful WIT operations checkpoint through ctx.flush(). The host must retain the bucket across component restarts, provide durable backing, and coordinate a single writer. See the key-value provider guide for the checkpoint and failure model.

Building and Running

Prerequisites

• Rust 1.91.1+

• wasm32-wasip2 target:

  rustup target add wasm32-wasip2
  

• just command runner

Build

# Build guest + host
just build_wasm_dbms_example

This compiles the guest to wasm32-wasip2 (producing a WASM component at .artifact/wasm-dbms-example-guest.wasm) and builds the native host binary.

Run

just test_wasm_dbms_example

Or run manually:

cargo run --release -p wasm-dbms-example-host -- .artifact/wasm-dbms-example-guest.wasm

The demo inserts users and posts, queries them with filters and ordering, demonstrates transaction commit (data persists) and rollback (data discarded), then removes the directory it created for this run. A wasm-dbms.db file in your current directory is never read, modified, or deleted.

Extending with Custom Tables

To add your own tables to the example:

1. Define the table in guest/src/schema.rs:

   #![allow(unused)]
   fn main() {
   #[derive(Debug, Table, Clone, PartialEq, Eq)]
   #[table = "comments"]
   pub struct Comment {
       #[primary_key]
       pub id: Uint32,
       pub body: Text,
       #[foreign_key(entity = "Post", table = "posts", column = "id")]
       pub post_id: Uint32,
   }
   }

2. Add dispatch arms for "comments" in every method of ExampleDatabaseSchema in guest/src/schema.rs (select, insert, update, delete, validate_insert, validate_update, referenced_tables).

3. Register the table in register_tables():

   #![allow(unused)]
   fn main() {
   ctx.register_table::<Comment>()?;
   }

4. Update the column lookup in table_columns() (guest/src/lib.rs):

   #![allow(unused)]
   fn main() {
   "comments" => Ok(schema::Comment::columns()),
   }

5. Register the table name in registered_table_names() (guest/src/lib.rs). The guest resolves caller-provided table names against this finite list, so unknown names are rejected with table-not-found and are never retained:

   #![allow(unused)]
   fn main() {
   fn registered_table_names() -> [&'static str; 3] {
       [
           schema::User::table_name(),
           schema::Post::table_name(),
           schema::Comment::table_name(),
       ]
   }
   }

6. Rebuild with just build_wasm_dbms_example.

Key Concepts

Next Steps

• Getting Started — Set up wasm-dbms from scratch with the Database trait
• CRUD Operations — Detailed guide on all database operations
• Transactions — ACID transactions with commit/rollback
• Schema Definition — Complete schema reference

Embedding wasm-dbms

• Embedding wasm-dbms
  • Overview
  • Choose a Memory Provider
  • Own One Context
  • Open a Session per Call
  • Transactions Across Calls
  • Transaction Ownership
  • Add SQL
  • Restarts and Upgrades
  • Reference Implementations

Overview

wasm-dbms is an engine, not a server. It stores tables, runs queries, and keeps transactions, but it does not know who is calling, how calls arrive, or when the process restarts. The application layer that embeds the engine answers those questions. This guide shows what that layer has to do:

1. pick a MemoryProvider for the target runtime;
2. own one DbmsContext per database for the life of the process;
3. open a WasmDbmsDatabase session per call;
4. run transactions across calls and decide who may use them;
5. optionally expose SQL next to the typed API;
6. know what survives a restart.

The Architecture page describes where this layer sits.

Choose a Memory Provider

The engine reads and writes 64 KiB pages through the MemoryProvider trait of wasm-dbms-memory. The provider decides where those pages live:

HeapMemoryProvider ships with wasm-dbms-memory. WasiMemoryProvider is described in the WASI Memory Provider page. The key-value option is described in the WASI Key-Value Memory Provider page. A new runtime needs a new provider; the Memory Management page explains what the engine expects from it.

The key-value provider caches the complete database and needs an explicit ctx.flush() after committed work. Its host must provide draft2 store and batch, durable bucket backing, and single-writer coordination.

Own One Context

DbmsContext owns everything the engine needs: the memory manager, the schema registry, the open transactions, and the journal. Create it once, when the process starts or on the first call, and keep it alive until the process ends. Creating a context per call would reload the schema registry on every call and would lose every open transaction.

The context is single-threaded on purpose: WASM runtimes run one call at a time, so the context uses RefCell instead of locks and is neither Send nor Sync. A thread_local! is the natural home for it:

#![allow(unused)]
fn main() {
use std::cell::RefCell;

use wasm_dbms::prelude::*;
use wasm_dbms_api::prelude::*;

#[derive(Clone, DatabaseSchema)]
#[tables(User = "users")]
pub struct MySchema;

thread_local! {
    static DBMS: RefCell<Option<DbmsContext<MyMemoryProvider>>> = const { RefCell::new(None) };
}

/// Runs `f` on the database, opening it on the first call.
fn with_dbms<F, R>(f: F) -> R
where
    F: FnOnce(&DbmsContext<MyMemoryProvider>) -> R,
{
    DBMS.with(|cell| {
        let mut slot = cell.borrow_mut();
        let ctx = slot.get_or_insert_with(|| {
            let ctx = DbmsContext::new(MyMemoryProvider::default());
            MySchema::register_tables(&ctx).expect("failed to register tables");
            ctx
        });
        f(ctx)
    })
}
}

register_tables writes the schema of every table that is not already registered and is safe to call on every start.

Open a Session per Call

A WasmDbmsDatabase is a short-lived view on the context. It is cheap to create, so open one per call and let it go when the call ends:

• WasmDbmsDatabase::oneshot(&ctx, MySchema) applies every operation immediately and atomically;
• WasmDbmsDatabase::from_transaction(&ctx, MySchema, tx) stages every operation in the transaction tx.

#![allow(unused)]
fn main() {
fn insert_user(tx: Option<TransactionId>, user: UserInsertRequest) -> Result<(), DbmsError> {
    with_dbms(|ctx| {
        let database = match tx {
            Some(tx) => WasmDbmsDatabase::from_transaction(ctx, MySchema, tx),
            None => WasmDbmsDatabase::oneshot(ctx, MySchema),
        };
        database.insert::<User>(user)
    })
}
}

from_transaction does not check the id. An operation on an id that is not open fails with DbmsError::Query(QueryError::TransactionNotFound).

Transactions Across Calls

Call-based runtimes cannot keep a Rust value alive between two calls, so the engine keeps transactions in the context and addresses them by TransactionId. An embedder exposes three kinds of entry points:

1. one that opens a transaction and returns its id;
2. operations that take an optional id and run inside it when present;
3. one that commits and one that rolls back a given id.

#![allow(unused)]
fn main() {
fn begin() -> TransactionId {
    with_dbms(|ctx| ctx.begin_transaction())
}

fn commit(tx: TransactionId) -> Result<(), DbmsError> {
    with_dbms(|ctx| WasmDbmsDatabase::from_transaction(ctx, MySchema, tx).commit())
}

fn rollback(tx: TransactionId) -> Result<(), DbmsError> {
    with_dbms(|ctx| WasmDbmsDatabase::from_transaction(ctx, MySchema, tx).rollback())
}
}

A client then calls begin, passes the id to as many operations as it needs, and ends with commit or rollback. The Transactions guide covers what happens inside the engine during each step. commit consumes the transaction whether it succeeds or fails; ctx.has_transaction(&tx) reports whether an id is still open.

Transaction Ownership

The engine identifies a transaction by its id and nothing else. Whoever holds the id can read and write through it, commit it, or roll it back. The engine does not record who opened it, because “who” is a platform concept: a principal on the Internet Computer, a tenant behind a host function, a session of a CLI.

A single-tenant embedder, such as a CLI or a program that serves one user, needs nothing more.

An embedder that exposes transactions across a trust boundary, such as a canister serving many principals or a host serving many tenants, keeps its own ledger from TransactionId to its identity type and checks it before every call that touches a transaction:

1. Insert on begin. Record the identity of the caller next to the new id.
2. Check on every transactional call. Before from_transaction, commit, and rollback, look the id up and compare the identity. Report a mismatch the same way as an unknown id, so that an outsider cannot tell whether an id exists.
3. Remove on commit and rollback, whether or not the engine call succeeded: the engine consumes the transaction in both cases.
4. Evict closed entries. A transaction can be closed through a path the ledger did not see, for example a typed commit next to a SQL COMMIT. When has_transaction returns false for an id in the ledger, drop the entry.

#![allow(unused)]
fn main() {
use std::cell::RefCell;
use std::collections::HashMap;

/// Whoever the runtime says made the current call.
type Identity = Vec<u8>;

thread_local! {
    static OWNERS: RefCell<HashMap<TransactionId, Identity>> = RefCell::new(HashMap::new());
}

fn begin(identity: Identity) -> TransactionId {
    let tx = with_dbms(|ctx| ctx.begin_transaction());
    OWNERS.with(|owners| owners.borrow_mut().insert(tx, identity));
    tx
}

/// Checks that `identity` opened `tx` and that `tx` is still open.
fn authorize(identity: &[u8], tx: TransactionId) -> Result<(), DbmsError> {
    let open = with_dbms(|ctx| ctx.has_transaction(&tx));
    if !open {
        // closed through a path this ledger did not see
        OWNERS.with(|owners| owners.borrow_mut().remove(&tx));
        return Err(DbmsError::Query(QueryError::TransactionNotFound));
    }
    let owned = OWNERS.with(|owners| {
        owners
            .borrow()
            .get(&tx)
            .is_some_and(|owner| owner.as_slice() == identity)
    });
    if owned {
        Ok(())
    } else {
        Err(DbmsError::Query(QueryError::TransactionNotFound))
    }
}

fn insert_user(
    identity: &[u8],
    tx: Option<TransactionId>,
    user: UserInsertRequest,
) -> Result<(), DbmsError> {
    if let Some(tx) = tx {
        authorize(identity, tx)?;
    }
    with_dbms(|ctx| {
        let database = match tx {
            Some(tx) => WasmDbmsDatabase::from_transaction(ctx, MySchema, tx),
            None => WasmDbmsDatabase::oneshot(ctx, MySchema),
        };
        database.insert::<User>(user)
    })
}

fn commit(identity: &[u8], tx: TransactionId) -> Result<(), DbmsError> {
    authorize(identity, tx)?;
    let result = with_dbms(|ctx| WasmDbmsDatabase::from_transaction(ctx, MySchema, tx).commit());
    // the engine consumed the transaction whether or not the commit succeeded
    OWNERS.with(|owners| owners.borrow_mut().remove(&tx));
    result
}
}

rollback follows the same shape as commit. The ledger lives on the heap next to the context; see Restarts and Upgrades for why it must not outlive it.

Add SQL

SqlEngine from wasm-dbms-sql runs SQL text against the same context and uses the same transaction ids. It holds only the schema, so it can live next to the context or be created per call:

#![allow(unused)]
fn main() {
use wasm_dbms_sql::SqlEngine;

fn run_sql(
    identity: &[u8],
    tx: Option<TransactionId>,
    sql: &str,
    params: &[Value],
) -> Result<SqlResult, SqlError> {
    if let Some(tx) = tx {
        authorize(identity, tx)?;
    }
    let result = with_dbms(|ctx| SqlEngine::new(MySchema).execute(ctx, tx, sql, params));
    match &result {
        Ok(SqlResult::TxBegin(tx)) => {
            OWNERS.with(|owners| owners.borrow_mut().insert(*tx, identity.to_vec()));
        }
        Ok(SqlResult::TxCommit | SqlResult::TxRollback) => {
            if let Some(tx) = tx {
                OWNERS.with(|owners| owners.borrow_mut().remove(&tx));
            }
        }
        _ => {}
    }
    result
}
}

A transaction opened with BEGIN can be committed through the typed API, and one opened with ctx.begin_transaction() can run SQL. The SQL guide describes the statement semantics.

Restarts and Upgrades

Two kinds of state exist:

Committed data is written through the memory provider as soon as commit returns, so it survives whatever the provider survives. A transaction that is open when the process stops is lost together with its writes, and so is every ledger entry that points at it. Nothing needs to be saved before a restart or restored after one: the ledger and the open transactions disappear together and stay consistent.

Transaction ids restart from 0 with every new context. A ledger that survived while the context did not would therefore authorize stale ids for transactions it never saw. Keep the ledger in the same heap as the context, and never persist it.

Reference Implementations

• ic-dbms is the application layer for the Internet Computer: a stable-memory provider, a canister that owns the context, Candid entry points, and a ledger from transaction id to principal.
• The Wasmtime Example in crates/wasm-dbms/example/ is a single-tenant WASI embedder: a file-backed provider, a thread_local! context, and a WIT interface with begin-transaction, commit, and rollback entry points.

Schema Definition

• Schema Definition
  • Overview
  • Table Definition
    • Required Derives
    • Table Attribute
    • Generic Tables
  • Column Attributes
    • Primary Key
    • Autoincrement
    • Unique
    • Index
    • Foreign Key
    • Custom Type
    • Sanitizer
    • Validate
    • Candid
    • Alignment
  • Migration Attributes
    • Default Value
    • Renamed From
    • Migrate Override
  • Generated Types
    • Record Type
    • InsertRequest Type
    • UpdateRequest Type
    • ForeignFetcher Type
  • Complete Example
  • Best Practices

Overview

wasm-dbms schemas are defined entirely in Rust using derive macros and attributes. Each struct represents a database table, and each field represents a column.

Key concepts:

• Structs with #[derive(Table)] become database tables
• Fields become columns with their types
• Attributes configure primary keys, foreign keys, validation, and more

Warning

The schema snapshot format used for migration detection imposes hard limits on identifier lengths and table shape. Exceeding any of these will cause the snapshot encoder to truncate or panic at runtime:

• Table name: at most 255 bytes (UTF-8).
• Column name: at most 255 bytes (UTF-8). Applies to every column, including the primary key and any column referenced by an index or foreign key.
• Custom data type name: at most 255 bytes (UTF-8).
• Reserved column name: where_clause cannot be used as a column name, because the generated update request stores its filter in a field with that name.
• Foreign key target (table name and column name): each at most 255 bytes.
• Columns per index: at most 255.
• Columns per table: at most 65,535.
• Indexes per table: at most 65,535.

Pick short, snake_case identifiers. The 255-byte cap is well above any sensible name length, but binary identifiers or non-ASCII text can blow past it faster than expected because the limit is in bytes, not characters.

Table Definition

Required Derives

Every table struct must have these derives:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
}
}

Note: For Internet Computer usage, see the ic-dbms Schema Reference.

Table Attribute

The #[table = "name"] attribute specifies the table name in the database:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "user_accounts"] // Table name in database
pub struct UserAccount {
    // Rust struct name (can differ)
    // ...
}
}

Naming conventions:

• Use snake_case for table names
• Table names should be plural (e.g., users, posts, order_items)
• Keep names short but descriptive

Generic Tables

#[derive(Table)] and #[derive(DatabaseSchema)] accept type parameters and where clauses. The generics are carried over to every generated type and impl, so a table can be generic over a custom data type:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "tagged_items"]
pub struct TaggedItem<T>
where
    T: CustomDataType,
{
    #[primary_key]
    pub id: Uint32,
    #[custom_type]
    pub tag: T,
}

#[derive(DatabaseSchema)]
#[tables(TaggedItem<T> = "tagged_items")]
pub struct TaggedSchema<T>
where
    T: CustomDataType,
{
    _tag: std::marker::PhantomData<T>,
}
}

Type parameters must be 'static, and the generated impls add that bound. Lifetime parameters and const generic parameters are rejected with a compile error.

Column Attributes

Primary Key

Every table must have exactly one primary key:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32, // Primary key
    pub name: Text,
}
}

Primary key rules:

• Exactly one field must be marked with #[primary_key]
• Primary keys must be unique across all records
• Primary keys cannot be null
• Common types: Uint32, Uint64, Uuid, Text

UUID as primary key:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "orders"]
pub struct Order {
    #[primary_key]
    pub id: Uuid, // UUID primary key
    pub total: Decimal,
}
}

Autoincrement

Automatically generate sequential values for a column on insert:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    #[autoincrement]
    pub id: Uint32, // Automatically assigned 1, 2, 3, ...
    pub name: Text,
}
}

Autoincrement rules:

• Only integer types are supported: Int8, Int16, Int32, Int64, Uint8, Uint16, Uint32, Uint64
• The counter starts at zero and increments by one on each insert
• Each autoincrement column has an independent counter
• Counters are stored in memory pages, so they persist for as long as the memory provider does
• When the counter reaches the type’s maximum value, inserts return an AutoincrementOverflow error
• Deleted records do not recycle their autoincrement values
• A table can have multiple #[autoincrement] columns

Choosing the right type:

Tip: Uint64 is recommended for most use cases. Only use smaller types when storage space is critical and you are certain the record count will stay within bounds.

Combining with other attributes:

#![allow(unused)]
fn main() {
#[primary_key]
#[autoincrement]
pub id: Uint64,  // Auto-generated unique primary key
}

Unique

Enforce uniqueness on a column:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[unique]
    pub email: Text, // Must be unique across all rows

    pub name: Text,
}
}

Unique constraint rules:

• Insert and update operations that would create a duplicate value return a UniqueConstraintViolation error
• Multiple fields in the same table can each be marked #[unique] independently
• A #[unique] field automatically gets a B+ tree index – no separate #[index] annotation is needed
• Primary keys are always unique by definition; you don’t need #[unique] on a #[primary_key] field

Combining with other attributes:

#![allow(unused)]
fn main() {
#[unique]
#[sanitizer(TrimSanitizer)]
#[sanitizer(LowerCaseSanitizer)]
#[validate(EmailValidator)]
pub email: Text,  // Sanitized, validated, then checked for uniqueness
}

Note: Sanitization and validation run before the uniqueness check, so the sanitized value is what gets compared.

Index

Define indexes on columns for faster lookups:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[index]
    pub email: Text, // Single-column index

    pub name: Text,
}
}

The primary key is always an implicit index – you don’t need to add #[index] to it.

Composite indexes:

Use group to group multiple fields into a single composite index:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uint32,

    #[index(group = "category_brand")]
    pub category: Text,

    #[index(group = "category_brand")]
    pub brand: Text,

    pub name: Text,
}
}

Fields sharing the same group name form a composite index, with columns ordered by field declaration order. In the example above, the composite index covers (category, brand). The primary key can be a member of a composite index.

Queries use a composite index from its first column onward: equality on the leading columns, optionally followed by a range on the next column. A condition on a later column alone cannot use the index. See Composite Indexes.

Indexes with the same column list are created once: an #[index] on the primary key or on a #[unique] field (which already has an implicit index) adds nothing.

Syntax variants:

#![allow(unused)]
fn main() {
// Single-column index
#[index]

// Composite index (group multiple fields by name)
#[index(group = "group_name")]
}

Foreign Key

Define relationships between tables:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "posts"]
pub struct Post {
    #[primary_key]
    pub id: Uint32,
    pub title: Text,

    #[foreign_key(entity = "User", table = "users", column = "id")]
    pub author_id: Uint32,
}
}

Attribute parameters:

The referenced column does not need to be the primary key, but it should be #[unique]. Existence checks, eager loading, delete behaviors and updates all use the declared column; updating it rewrites the referencing rows.

Nullable foreign key:

#![allow(unused)]
fn main() {
#[foreign_key(entity = "User", table = "users", column = "id")]
pub manager_id: Nullable<Uint32>,  // Can be null
}

Self-referential foreign key:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "categories"]
pub struct Category {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,

    #[foreign_key(entity = "Category", table = "categories", column = "id")]
    pub parent_id: Nullable<Uint32>,
}
}

Custom Type

Mark a field as a user-defined custom data type:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "tasks"]
pub struct Task {
    #[primary_key]
    pub id: Uint32,
    #[custom_type]
    pub priority: Priority, // User-defined type
}
}

The #[custom_type] attribute tells the Table macro that this field implements the CustomDataType trait. Without it, the macro won’t know how to serialize and deserialize the field.

Nullable custom types:

#![allow(unused)]
fn main() {
#[custom_type]
pub priority: Nullable<Priority>,  // Optional custom type
}

See the Custom Data Types Guide for how to define custom types.

Sanitizer

Apply data transformations before storage:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[sanitizer(TrimSanitizer)]
    pub name: Text,

    #[sanitizer(LowerCaseSanitizer)]
    #[sanitizer(TrimSanitizer)]
    pub email: Text,

    #[sanitizer(RoundToScaleSanitizer(2))]
    pub balance: Decimal,

    #[sanitizer(ClampSanitizer, min = 0, max = 120)]
    pub age: Uint8,
}
}

Syntax variants:

#![allow(unused)]
fn main() {
// Unit struct (no parameters)
#[sanitizer(TrimSanitizer)]

// Tuple struct (positional parameter)
#[sanitizer(RoundToScaleSanitizer(2))]

// Named fields struct
#[sanitizer(ClampSanitizer, min = 0, max = 100)]
}

See Sanitization Reference for all available sanitizers.

Validate

Add validation rules:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[validate(MaxStrlenValidator(100))]
    pub name: Text,

    #[validate(EmailValidator)]
    pub email: Text,

    #[validate(UrlValidator)]
    pub website: Nullable<Text>,
}
}

Validation happens after sanitization:

#![allow(unused)]
fn main() {
#[sanitizer(TrimSanitizer)]           // 1. First: trim whitespace
#[validate(MaxStrlenValidator(100))]  // 2. Then: check length
pub name: Text,
}

See Validation Reference for all available validators.

Candid

The #[candid] attribute adds candid::CandidType, serde::Serialize, and serde::Deserialize derives to the types generated by the Table macro. It requires the candid feature and is meant for Internet Computer integration. See the ic-dbms Schema Reference for details.

Alignment

Advanced: Configure memory alignment for dynamic-size tables:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "large_records"]
#[alignment = 64] // 64-byte alignment
pub struct LargeRecord {
    #[primary_key]
    pub id: Uint32,
    pub data: Text, // Variable-size field
}
}

When to use:

• Performance tuning for specific access patterns
• Optimizing memory layout for large records

Rules:

• Minimum alignment is 8 bytes for dynamic types
• Default alignment is 32 bytes
• Fixed-size tables ignore this attribute (alignment equals record size)

Caution: Only change alignment if you understand the performance implications.

Migration Attributes

These attributes feed the schema migration subsystem. They produce no runtime behaviour for normal CRUD; the planner only consults them when the compiled schema diverges from the snapshot stored in memory.

See the Schema Migrations Guide for the end-to-end flow (drift detection, pending_migrations, migrate(policy)).

Default Value

Attach a per-column default that the migration planner uses when adding a non-nullable column to an existing table:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    pub name: Text,

    #[default = 0]
    pub login_count: Uint32,
}
}

How it is used:

• When migrate() plans an AddColumn op for a non-nullable column, it pulls the value from #[default = ...] (after first checking Migrate::default_value).
• Without a resolvable default, planning aborts with MigrationError::MissingDefault.

Rules:

• The expression must convert into the column’s Value variant via From/Into. Examples: #[default = 0] on Uint32, #[default = ""] on Text, #[default = false] on Boolean.
• The expression is evaluated at migration time, not at insert time, so it has no effect on regular INSERT calls — those still need an explicit value (or omit the field if nullable).
• Custom data types must implement From<MyType> for Value; the #[derive(CustomDataType)] macro emits this automatically.
• Defaults are persisted into the table’s snapshot (ColumnSnapshot::default), so the planner can compare them across releases.

Combining with nullable:

#![allow(unused)]
fn main() {
// Redundant — nullable columns default to NULL implicitly. Don't write
// #[default] on a Nullable<T> field.
pub bio: Nullable<Text>,
}

Renamed From

Tell the migration planner that a column used to be known by one or more previous names:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[renamed_from("username", "user_name")]
    pub name: Text,
}
}

How it is used:

When planning a migration, the planner first matches stored columns against compiled columns by name. For each compiled column with no direct match, it walks renamed_from in order and looks for a stored column with one of those names. The first hit is emitted as a RenameColumn op, preserving the column’s data.

Rules:

• Entries are string literals.
• Order matters: list newer renames first, older renames last (mirroring the chronological order of releases).
• A stored column matched by renamed_from is not matched by another compiled column. If two compiled columns claim the same previous name, the earlier-declared field wins.
• Without #[renamed_from], a column rename is indistinguishable from a DropColumn + AddColumn pair, which loses data.

Migrate Override

By default, #[derive(Table)] emits an empty impl Migrate for T {} for every table, giving you trait defaults for default_value and transform_column. Add #[migrate] at the struct level to suppress that emission and provide a hand-written impl:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "events"]
#[migrate]
pub struct Event {
    #[primary_key]
    pub id: Uint32,
    pub kind: Text,
    pub severity: Uint8,
}

impl Migrate for Event {
    fn default_value(column: &str) -> Option<Value> {
        match column {
            "severity" => Some(Value::Uint8(Uint8(1))),
            _ => None,
        }
    }

    fn transform_column(column: &str, old: Value) -> DbmsResult<Option<Value>> {
        match column {
            // Example: convert legacy text severities into the new Uint8 column.
            "severity" => match old {
                Value::Text(Text(s)) => match s.as_str() {
                    "low" => Ok(Some(Value::Uint8(Uint8(1)))),
                    "medium" => Ok(Some(Value::Uint8(Uint8(5)))),
                    "high" => Ok(Some(Value::Uint8(Uint8(9)))),
                    other => Err(DbmsError::Migration(MigrationError::TransformAborted {
                        table: "events".into(),
                        column: column.into(),
                        reason: format!("unknown severity `{other}`"),
                    })),
                },
                _ => Ok(None),
            },
            _ => Ok(None),
        }
    }
}
}

When to use #[migrate]:

• The new column is non-nullable and the default cannot be a constant literal (e.g. requires hashing the row, or pulls from another column).
• A column changed to an incompatible type that is not in the widening whitelist, and you can derive the new value from the old one.

Trait contract:

Note: Without #[migrate], do not write impl Migrate for T {} yourself — the macro already emitted one and you would get a duplicate-impl error.

Generated Types

The Table macro generates several types for each table. Generated types use the same visibility as the table struct, so a private table struct can derive Table and its generated types stay private too.

Record Type

{StructName}Record - The full record type returned from queries:

#![allow(unused)]
fn main() {
// Generated from User struct
pub struct UserRecord {
    pub id: Uint32,
    pub name: Text,
    pub email: Text,
}

// Usage
let users: Vec<UserRecord> = database.select::<User>(query)?;

for user in users {
    println!("{}: {}", user.id, user.name);
}
}

InsertRequest Type

{StructName}InsertRequest - Request type for inserting records:

#![allow(unused)]
fn main() {
// Generated from User struct
pub struct UserInsertRequest {
    pub id: Uint32,
    pub name: Text,
    pub email: Text,
}

// Usage
let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    email: "alice@example.com".into(),
};

database.insert::<User>(user)?;
}

UpdateRequest Type

{StructName}UpdateRequest - Request type for updating records:

#![allow(unused)]
fn main() {
// Generated from User struct (with builder pattern)
let update = UserUpdateRequest::builder()
    .set_name("New Name".into())
    .set_email("new@example.com".into())
    .filter(Filter::eq("id", Value::Uint32(1.into())))
    .build();

// Usage
database.update::<User>(update)?;
}

The generated update request stores its filter in a where_clause field, so where_clause is a reserved column name: a table field with that name is rejected with a compile error.

Builder methods:

• set_{field_name}(value) - Set a field value
• filter(Filter) - WHERE clause (required)
• build() - Build the update request

ForeignFetcher Type

{StructName}ForeignFetcher - Internal type for eager loading:

#![allow(unused)]
fn main() {
// Generated automatically, used internally
// You typically don't interact with this directly
}

Complete Example

#![allow(unused)]
fn main() {
// schema/src/lib.rs
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[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, Clone, PartialEq, Eq)]
#[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,
}

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "comments"]
pub struct Comment {
    #[primary_key]
    pub id: Uuid,

    #[validate(MaxStrlenValidator(1000))]
    pub content: Text,

    #[foreign_key(entity = "User", table = "users", column = "id")]
    pub author_id: Uint32,

    #[foreign_key(entity = "Post", table = "posts", column = "id")]
    pub post_id: Uuid,

    pub created_at: DateTime,
}
}

For the Internet Computer, see the ic-dbms Schema Reference.

Best Practices

1. Keep schema in a separate crate

my-project/
├── schema/           # Reusable types
│   ├── Cargo.toml
│   └── src/lib.rs
└── app/              # Application using the database
    ├── Cargo.toml
    └── src/lib.rs

2. Use appropriate primary key types

#![allow(unused)]
fn main() {
// Sequential IDs - simple, good for internal use
pub id: Uint32,

// UUIDs - better for distributed systems, no guessing
pub id: Uuid,
}

3. Always validate user input

#![allow(unused)]
fn main() {
#[validate(MaxStrlenValidator(1000))]  // Prevent huge strings
pub content: Text,

#[validate(EmailValidator)]  // Validate format
pub email: Text,
}

4. Use nullable for optional fields

#![allow(unused)]
fn main() {
pub phone: Nullable<Text>,  // Clearly optional
pub bio: Nullable<Text>,
}

5. Consider sanitization for consistency

#![allow(unused)]
fn main() {
#[sanitizer(TrimSanitizer)]
#[sanitizer(LowerCaseSanitizer)]
pub email: Text,  // Always lowercase, no whitespace
}

6. Document your schema

#![allow(unused)]
fn main() {
/// User account information
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    /// Unique user identifier
    #[primary_key]
    pub id: Uint32,

    /// User's display name (max 100 chars)
    #[validate(MaxStrlenValidator(100))]
    pub name: Text,
}
}

Data Types

• Data Types
  • Overview
  • Integer Types
    • Unsigned Integers
    • Signed Integers
  • Decimal
  • Text
  • Boolean
  • Date and Time
    • Date
    • DateTime
  • Binary Data
    • Blob
  • Identifiers
    • Uuid
  • Semi-Structured Data
    • Json
  • Nullable
  • Custom Types
  • Type Conversion Reference

Overview

wasm-dbms provides a rich set of data types for defining table schemas. Each type maps to standard Rust types for seamless integration.

Type categories:

Note: For Internet Computer types such as Principal, see the ic-dbms documentation.

Integer Types

Unsigned Integers

Uint8 - 8-bit unsigned integer (0 to 255)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Uint8;

#[derive(Table, ...)]
#[table = "settings"]
pub struct Setting {
    #[primary_key]
    pub id: Uint32,
    pub priority: Uint8,  // 0-255
}

// Usage
let setting = SettingInsertRequest {
    id: 1.into(),
    priority: 10.into(),  // or Uint8::from(10)
};
}

Uint16 - 16-bit unsigned integer (0 to 65,535)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Uint16;

pub struct Product {
    pub stock_count: Uint16,  // 0-65,535
}

let count: Uint16 = 1000.into();
}

Uint32 - 32-bit unsigned integer (0 to 4,294,967,295)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Uint32;

pub struct User {
    #[primary_key]
    pub id: Uint32,  // Common for primary keys
}

let id: Uint32 = 12345.into();
}

Uint64 - 64-bit unsigned integer (0 to 18,446,744,073,709,551,615)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Uint64;

pub struct Transaction {
    pub amount_e8s: Uint64,  // For large numbers like token amounts
}

let amount: Uint64 = 1_000_000_000u64.into();
}

Signed Integers

Int8 - 8-bit signed integer (-128 to 127)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Int8;

pub struct Temperature {
    pub celsius: Int8,  // -128 to 127
}

let temp: Int8 = (-10).into();
}

Int16 - 16-bit signed integer (-32,768 to 32,767)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Int16;

pub struct Altitude {
    pub meters: Int16,  // Can be negative (below sea level)
}

let altitude: Int16 = (-100).into();
}

Int32 - 32-bit signed integer (-2,147,483,648 to 2,147,483,647)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Int32;

pub struct Account {
    pub balance_cents: Int32,  // Can be negative (debt)
}

let balance: Int32 = (-5000).into();
}

Int64 - 64-bit signed integer

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Int64;

pub struct Statistics {
    pub total_change: Int64,  // Large signed values
}

let change: Int64 = (-1_000_000_000i64).into();
}

Decimal

Decimal - Arbitrary-precision decimal number

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Decimal;

pub struct Product {
    pub price: Decimal,      // $19.99
    pub weight_kg: Decimal,  // 2.5
}

// From f64
let price: Decimal = 19.99.into();

// From string (more precise)
let price: Decimal = "19.99".parse().unwrap();

// With sanitizer for rounding
#[derive(Table, ...)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uint32,
    #[sanitizer(RoundToScaleSanitizer(2))]  // Round to 2 decimal places
    pub price: Decimal,
}
}

Note: Use RoundToScaleSanitizer to ensure consistent decimal precision.

Text

Text - UTF-8 string

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Text;

pub struct User {
    pub name: Text,
    pub email: Text,
    pub bio: Text,
}

// From &str
let name: Text = "Alice".into();

// From String
let email: Text = String::from("alice@example.com").into();

// Access the string
let text: Text = "Hello".into();
assert_eq!(text.as_str(), "Hello");
}

With validation:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    #[validate(MaxStrlenValidator(100))]
    pub name: Text,
    #[validate(EmailValidator)]
    pub email: Text,
}
}

With sanitization:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(CollapseWhitespaceSanitizer)]
    pub name: Text,
    #[sanitizer(LowerCaseSanitizer)]
    pub email: Text,
}
}

Boolean

Boolean - True or false value

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Boolean;

pub struct User {
    pub is_active: Boolean,
    pub email_verified: Boolean,
}

let active: Boolean = true.into();
let verified: Boolean = false.into();

// Convert back
let value: bool = active.into();
}

Filtering by boolean:

#![allow(unused)]
fn main() {
// Find active users
let filter = Filter::eq("is_active", Value::Boolean(true));

// Find unverified users
let filter = Filter::eq("email_verified", Value::Boolean(false));
}

Date and Time

Date

Date - Calendar date (year, month, day)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Date;

pub struct Event {
    pub event_date: Date,
}

// Create from components
let date = Date::new(2024, 6, 15);  // June 15, 2024

// From chrono NaiveDate (if using chrono)
use chrono::NaiveDate;
let naive = NaiveDate::from_ymd_opt(2024, 6, 15).unwrap();
let date: Date = naive.into();
}

DateTime

DateTime - Date and time with timezone

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::DateTime;

pub struct User {
    pub created_at: DateTime,
    pub last_login: DateTime,
}

// Current time
let now = DateTime::now();

// From chrono DateTime<Utc>
use chrono::{DateTime as ChronoDateTime, Utc};
let chrono_dt: ChronoDateTime<Utc> = Utc::now();
let dt: DateTime = chrono_dt.into();
}

With sanitization:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "events"]
pub struct Event {
    #[primary_key]
    pub id: Uint32,
    #[sanitizer(UtcSanitizer)] // Convert to UTC
    pub scheduled_at: DateTime,
}
}

Binary Data

Blob

Blob - Binary large object (byte array)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Blob;

pub struct Document {
    pub content: Blob,      // File content
    pub thumbnail: Blob,    // Image data
}

// From Vec<u8>
let data: Vec<u8> = vec![0x89, 0x50, 0x4E, 0x47];  // PNG header
let blob: Blob = data.into();

// From slice
let blob: Blob = Blob::from(&[1, 2, 3, 4][..]);

// Access bytes
let bytes: &[u8] = blob.as_slice();
}

Note: Be mindful of storage costs when storing large blobs. Consider storing only references (hashes, URLs) for very large files.

Identifiers

Uuid

Uuid - Universally unique identifier (128-bit)

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Uuid;

pub struct Order {
    #[primary_key]
    pub id: Uuid,  // UUID as primary key
}

// Generate new UUID
let id = Uuid::new_v4();

// From string
let id: Uuid = "550e8400-e29b-41d4-a716-446655440000".parse().unwrap();

// From bytes
let bytes: [u8; 16] = [/* 16 bytes */];
let id = Uuid::from_bytes(bytes);
}

Benefits over sequential IDs:

• Globally unique without coordination
• No sequential guessing
• Safe for distributed systems

Semi-Structured Data

Json

Json - JSON object or array

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::Json;
use std::str::FromStr;

pub struct User {
    pub metadata: Json,    // Flexible schema
    pub preferences: Json, // User settings
}

// From string
let json = Json::from_str(r#"{"theme": "dark", "language": "en"}"#).unwrap();

// From serde_json::Value
use serde_json::json;
let json: Json = json!({
    "notifications": true,
    "timezone": "UTC"
}).into();
}

Querying JSON:

#![allow(unused)]
fn main() {
// Check if JSON contains pattern
let filter = Filter::json("metadata", JsonFilter::contains(
    Json::from_str(r#"{"active": true}"#).unwrap()
));

// Extract and compare
let filter = Filter::json("preferences",
    JsonFilter::extract_eq("theme", Value::Text("dark".into()))
);

// Check path exists
let filter = Filter::json("metadata", JsonFilter::has_key("email"));
}

See the JSON Reference for comprehensive JSON documentation.

Nullable

Nullable<T> - Optional value wrapper

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::{Nullable, Text, Uint32};

pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    pub phone: Nullable<Text>,      // Optional phone number
    pub age: Nullable<Uint32>,      // Optional age
    pub bio: Nullable<Text>,        // Optional biography
}

// Insert with value
let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    phone: Nullable::Value("555-1234".into()),
    age: Nullable::Null,
    bio: Nullable::Null,
};

// Check if null
let phone = user.phone;
match phone {
    Nullable::Value(p) => println!("Phone: {}", p.as_str()),
    Nullable::Null => println!("No phone number"),
}
}

Filtering nullable fields:

#![allow(unused)]
fn main() {
// Find users with phone numbers
let filter = Filter::not_null("phone");

// Find users without phone numbers
let filter = Filter::is_null("phone");

// Find users with specific phone
let filter = Filter::eq("phone", Value::Text("555-1234".into()));
}

Nullable foreign keys:

#![allow(unused)]
fn main() {
pub struct Employee {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    #[foreign_key(entity = "Employee", table = "employees", column = "id")]
    pub manager_id: Nullable<Uint32>, // Top-level employees have no manager
}
}

Custom Types

Beyond the built-in types listed above, wasm-dbms supports user-defined custom data types. Custom types let you store enums, structs, and newtypes in your tables by implementing the CustomDataType trait.

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "tasks"]
pub struct Task {
    #[primary_key]
    pub id: Uint32,
    #[custom_type]
    pub priority: Priority, // User-defined custom type
}
}

See the Custom Data Types Guide for step-by-step instructions on defining and using custom types.

Type Conversion Reference

Note: For the Candid mapping of these types, see the ic-dbms documentation.

Conversion examples:

#![allow(unused)]
fn main() {
// Rust primitive to wasm-dbms type
let uint: Uint32 = 42u32.into();
let text: Text = "hello".into();
let boolean: Boolean = true.into();

// wasm-dbms type to Rust primitive
let num: u32 = uint.into();
let s: String = text.into();
let b: bool = boolean.into();
}

Query API Reference

• Query API Reference
  • Overview
  • Query Struct
  • QueryBuilder
    • Field Selection
    • Filters
    • Joins
    • Join Results
    • Eager Loading
    • Distinct
    • Aggregations
    • Ordering
    • Pagination
  • Aggregate Types
    • AggregateFunction
    • AggregatedRow
    • AggregatedValue
  • Execution Order
  • Errors
  • Related Types

Overview

A Query describes what to retrieve from the database: which rows match, which columns to return, how to order and paginate them, and how to combine data across tables. Queries are constructed with QueryBuilder and consumed by Database::select, Database::select_raw, and Database::select_join.

select_join returns a JoinResultSet with the selected columns once and the rows as Vec<Value>. See Join Results.

For an introductory walkthrough, see the Querying Guide.

Query Struct

#![allow(unused)]
fn main() {
pub struct Query {
    columns: Select,
    pub distinct_by: Vec<String>,
    pub eager_relations: Vec<String>,
    pub filter: Option<Filter>,
    pub group_by: Vec<String>,
    pub having: Option<Filter>,
    pub joins: Vec<Join>,
    pub limit: Option<usize>,
    pub offset: Option<usize>,
    pub order_by: Vec<(String, OrderDirection)>,
}
}

Use Query::builder() to obtain a QueryBuilder.

QueryBuilder

Field Selection

The primary key is always included by Database::select::<T> even when not explicitly listed.

Filters

See Filters in the Querying Guide and JSON Filters for the full filter API.

Joins

Queries containing joins must be executed via Database::select_join. Calling Database::select::<T> with a joined query returns QueryError::JoinInsideTypedSelect.

A join condition matches only equal, non-null values. A NULL join key never matches another row, including a row whose join key is also NULL. Outer joins retain such rows as unmatched and fill the missing side with Value::Null.

Join Results

Database::select_join returns a JoinResultSet:

Helpers: len(), is_empty(), column_index("table.column") resolves a name to a column position, row(index) and iter() give JoinRow views, and &result can be iterated directly. A JoinRow offers get("table.column"), iter() over (column, value) pairs, columns(), and values(). A bare name matches the first column with that name in row order. columns is complete even when rows is empty. With the candid feature the type derives CandidType.

Eager Loading

#![allow(unused)]
fn main() {
.with("posts")
}

Adds a foreign-key relation to load eagerly. Each relation is loaded once via a batch fetch keyed by the foreign-key column.

Distinct

#![allow(unused)]
fn main() {
.distinct(&["name"])
.distinct(&["category", "vendor"])
}

Sets distinct_by to the supplied list of column names. Rows are deduplicated by the tuple of values across those columns; the first row encountered for each distinct tuple is retained. Passing an empty slice is a no-op.

Semantics:

• Columns are looked up on the source record (ValuesSource::This).
• Missing columns are treated as Value::Null, so listing an unknown column collapses every row into a single result.
• Deduplication runs before ordering, offset, and limit.
• The selected fields (Select::Columns) do not need to include the distinct_by columns.

Aggregations

#![allow(unused)]
fn main() {
.group_by(&["category"])
.having(Filter::gt("count", Value::Uint64(10u64.into())))
}

Aggregations operate over the rows that survive WHERE and DISTINCT. Each group of rows sharing the same group_by tuple produces one AggregatedRow. The aggregate functions to compute are described by AggregateFunction; their results are returned as AggregatedValue entries inside the row.

The HAVING filter is evaluated after aggregation, against the grouping keys and aggregate results.

Ordering

Multiple order_by_* calls produce stable multi-key sorts; later keys break ties from earlier keys.

Pagination

Aggregate Types

Types used to describe and return aggregated query results. All three are re-exported from the wasm-dbms-api prelude.

AggregateFunction

#![allow(unused)]
fn main() {
pub enum AggregateFunction {
    Count(Option<String>),
    Sum(String),
    Avg(String),
    Min(String),
    Max(String),
}
}

Describes one aggregate function to compute over a group of rows.

AggregatedRow

#![allow(unused)]
fn main() {
pub struct AggregatedRow {
    pub group_keys: Vec<Value>,
    pub values: Vec<AggregatedValue>,
}
}

A single row of aggregated output. group_keys holds the values of the group_by columns that identify the group; values holds the aggregate results in the same order as the AggregateFunction list supplied with the query.

AggregatedValue

#![allow(unused)]
fn main() {
pub enum AggregatedValue {
    Count(u64),
    Sum(Value),
    Avg(Value),
    Min(Value),
    Max(Value),
}
}

Carries the result of one aggregate function. Count is always a u64; the remaining variants wrap a Value whose concrete variant depends on the source column’s data type.

Execution Order

The select pipeline applies the query elements in this order — matching standard SQL semantics:

1. WHERE — filter is applied while scanning records (or via an index plan).
2. DISTINCT — distinct_by deduplicates the surviving rows.
3. GROUP BY / aggregates — when group_by is set, surviving rows are bucketed by the grouping tuple and the requested AggregateFunctions are computed per bucket, producing AggregatedRows.
4. HAVING — having filters the aggregated groups.
5. Eager loading — relations declared by with(...) are batch-fetched (non-aggregate selects only).
6. Column selection — non-selected columns are dropped from each row.
7. ORDER BY — order_by keys are applied in declared order.
8. OFFSET / LIMIT — applied last when order_by or distinct_by is set; otherwise applied during the scan for early termination.

When neither order_by nor distinct_by is present, the engine applies offset/limit during iteration to avoid materialising the entire result set.

Errors

All variants come from QueryError. Most are surfaced at planning time (before any rows are scanned) so callers fail fast.

Aggregate-specific (Database::aggregate)

Non-aggregate select paths

Related Types

• AggregateFunction — aggregate to compute per group
• AggregatedRow — single row of aggregated output
• AggregatedValue — single aggregate result value
• Filter — predicates for WHERE
• JsonFilter — JSON-specific predicates
• Join, JoinType — join clauses
• OrderDirection — ascending/descending
• Select — All or Columns(...)
• QueryError — query-time error variants

SQL Reference

• SQL Reference
  • Overview
  • Lexical Structure
    • Whitespace and Comments
    • Keywords
    • Identifiers
    • Literals
    • Placeholders
    • Statement Terminator
  • Grammar
  • SELECT
    • Select List
    • FROM and Table Aliases
    • JOIN
    • WHERE
    • DISTINCT
    • GROUP BY and HAVING
    • ORDER BY
    • LIMIT and OFFSET
    • Execution Order
  • INSERT
  • UPDATE
  • DELETE
  • Transactions
  • Conditions
    • Comparison Operators
    • IN
    • LIKE
    • IS NULL
    • AND, OR, NOT
    • NULL Handling
  • Aggregate Functions
  • Type Conversion
    • Literals by Column Type
    • String Formats
  • Parameters
  • Results
  • Errors
  • Limits
  • Reserved Words
  • Not Supported

Overview

The wasm-dbms-sql crate runs SQL statements against a wasm-dbms database. It covers reading and writing data:

Tables, columns, indexes, and foreign keys are defined in Rust with #[derive(Table)]. There is no SQL to create or alter them: schema changes go through migrations.

SQL is executed by SqlEngine::execute, one statement per call. For setup and worked examples, see the SQL Guide.

Lexical Structure

Whitespace and Comments

Spaces, tabs, and line breaks separate tokens and are otherwise ignored. A statement can span any number of lines.

Two comment forms are skipped like whitespace:

-- a line comment runs to the end of the line
SELECT name /* a block comment can sit between tokens
               and span lines */ FROM users;

Block comments do not nest: the first */ ends the comment. A block comment that is never closed is an error. # does not start a comment.

Keywords

Keywords are case-insensitive: SELECT, select, and SeLeCt are the same. Every keyword is reserved, which means it cannot be used as a table, column, or alias name unless it is quoted. The full list is in Reserved Words.

The aggregate function names COUNT, SUM, AVG, MIN, and MAX are not reserved. They are case-insensitive and are read as functions only when an opening parenthesis follows, so a column can be called count or max.

Identifiers

Identifiers name tables, columns, and aliases. They are case-sensitive and must match the names declared in the Rust schema exactly: users and Users are different tables.

An unquoted identifier starts with an ASCII letter or _, followed by ASCII letters, digits, or _.

A quoted identifier is written between double quotes. It can contain any character, including spaces and non-ASCII letters. Write "" for a double quote inside it. Quoting is required for a name that is a reserved word, and never changes the meaning of a name that does not need it.

SELECT "order", "first name" FROM "group" WHERE "select" = 1;

Backticks (`name`) and square brackets ([name]) are not accepted. Double quotes always mean an identifier, never a string.

A column can be qualified with its table: users.name. When the table has an alias, the alias is the qualifier.

Literals

• A number is made negative with a leading -. Unsigned integer literals go up to 18446744073709551615.
• A decimal needs digits on both sides of the dot. .5, 5., and exponent notation such as 1e3 are not accepted.
• A string can contain line breaks. Write '' for a single quote inside it. A backslash has no special meaning in a string.
• There are no date, UUID, or binary literals. Those values are written as strings and converted by the type of the column they are used with. See Type Conversion.

Placeholders

A ? stands for a value supplied separately when the statement is executed. Placeholders are positional: the first ? takes the first parameter, the second ? the second, and so on.

SELECT name FROM users WHERE age > ? AND city = ? LIMIT ?;

A placeholder can be used wherever a literal can, and as the argument of LIMIT and OFFSET. Named (:name, @name) and numbered ($1) placeholders are not accepted. See Parameters.

Statement Terminator

A statement can end with one ;. It is optional. Only one statement is accepted per call: anything after the ; other than whitespace and comments is an error.

Grammar

The grammar below uses these conventions: [ x ] is optional, { x } repeats zero or more times, a | b is a choice, and quoted words are keywords or symbols. Keywords are shown in upper case and are case-insensitive.

statement       = ( select | insert | update | delete
                  | begin | commit | rollback ) [ ";" ]

begin           = "BEGIN" [ "TRANSACTION" ]
commit          = "COMMIT" [ "TRANSACTION" ]
rollback        = "ROLLBACK" [ "TRANSACTION" ]

select          = "SELECT" [ "DISTINCT" ] select_list
                  "FROM" table_ref { join }
                  [ "WHERE" condition ]
                  [ "GROUP" "BY" column_ref { "," column_ref } ]
                  [ "HAVING" condition ]
                  [ "ORDER" "BY" order_item { "," order_item } ]
                  [ limit_offset ]
select_list     = "*" | select_item { "," select_item }
select_item     = operand [ "AS" identifier ]
table_ref       = identifier [ [ "AS" ] identifier ]
join            = join_type table_ref "ON" column_ref "=" column_ref
join_type       = [ "INNER" ] "JOIN"
                | ( "LEFT" | "RIGHT" | "FULL" ) [ "OUTER" ] "JOIN"
order_item      = operand [ "ASC" | "DESC" ]
limit_offset    = "LIMIT" row_count [ "OFFSET" row_count ]
                | "OFFSET" row_count [ "LIMIT" row_count ]
row_count       = integer | "?"

insert          = "INSERT" "INTO" identifier
                  "(" identifier { "," identifier } ")"
                  "VALUES" "(" value { "," value } ")"

update          = "UPDATE" identifier
                  "SET" assignment { "," assignment }
                  "WHERE" condition
assignment      = identifier "=" value

delete          = "DELETE" "FROM" identifier
                  "WHERE" condition
                  [ "CASCADE" | "RESTRICT" ]

condition       = and_condition { "OR" and_condition }
and_condition   = not_condition { "AND" not_condition }
not_condition   = "NOT" not_condition
                | "(" condition ")"
                | predicate
predicate       = operand comparison value
                | operand [ "NOT" ] "IN" "(" value { "," value } ")"
                | operand [ "NOT" ] "LIKE" ( string | "?" )
                | operand "IS" [ "NOT" ] "NULL"
comparison      = "=" | "!=" | "<>" | "<" | "<=" | ">" | ">="

operand         = column_ref | aggregate
column_ref      = identifier [ "." identifier ]
aggregate       = "COUNT" "(" ( "*" | column_ref ) ")"
                | ( "SUM" | "AVG" | "MIN" | "MAX" ) "(" column_ref ")"

value           = literal | "?"
literal         = [ "-" ] integer | [ "-" ] decimal | string
                | "TRUE" | "FALSE" | "NULL"

Two rules are not visible in the grammar:

• An aggregate is allowed in the select list, in HAVING, and in ORDER BY, but not in WHERE.
• NULL is allowed as a value in INSERT and in UPDATE ... SET, but not on the right of a comparison or inside an IN list. Use IS NULL instead.

SELECT

SELECT name, age
FROM users
WHERE age >= 18
ORDER BY age DESC, name
LIMIT 10 OFFSET 20;

Select List

* returns every column, in the order the columns are declared in the table. For a join, it returns the columns of every table, in the order of the tables in the statement. * cannot be mixed with other items, and table.* is not supported.

A list of columns returns exactly those columns, in the order written. The same column can appear more than once.

AS gives a column a different name in the result. The AS keyword is required.

SELECT id AS user_id, name AS "full name" FROM users;

The select list can also contain aggregate functions. Expressions, arithmetic, literals, and scalar functions are not supported.

FROM and Table Aliases

FROM names the table to read. An alias can follow the table name, with or without AS:

SELECT u.name FROM users AS u WHERE u.id = 1;
SELECT u.name FROM users u WHERE u.id = 1;

Once a table has an alias, its columns are qualified with the alias and no longer with the table name: users.id is an error in the queries above.

JOIN

A join combines the rows of two tables whose columns are equal.

SELECT users.name, posts.title
FROM users
JOIN posts ON users.id = posts.user_id
LEFT JOIN comments ON comments.post_id = posts.id;

The columns of the missing side of an outer join are NULL. Null join keys never match, including two NULL values.

Rules for the ON condition:

• It is exactly one equality between two columns.
• Both columns must have the same underlying type; their nullability may differ. Incompatible types produce SqlError::TypeMismatch during planning.
• One column belongs to the table being joined, and the other to a table that comes before it in the statement. The two sides can be written in either order.
• AND, OR, other operators, and literals are not accepted. Put any other condition in WHERE.

In a join, a column name that exists in more than one table must be qualified, in every clause. An unqualified name that is ambiguous is an error.

Other rules:

• A table can appear only once in a statement, so a table cannot be joined with itself.
• CROSS JOIN, NATURAL JOIN, USING (...), and comma-separated tables in FROM are not supported.
• DISTINCT, aggregate functions, GROUP BY, and HAVING cannot be used together with a join.

WHERE

WHERE keeps the rows for which the condition is true.

SELECT * FROM users
WHERE (age < 18 OR age > 65) AND email IS NOT NULL;

DISTINCT

SELECT DISTINCT returns each combination of the selected columns once.

SELECT DISTINCT city, country FROM users ORDER BY country, city;

• With a column list, rows are compared on the listed columns. With *, rows are compared on every column.
• Every ORDER BY column must be in the select list.
• DISTINCT cannot be combined with a join or with aggregate functions.

GROUP BY and HAVING

GROUP BY puts the rows that have the same values in the listed columns into one group. The query then returns one row per group.

SELECT category, COUNT(*) AS items, SUM(price) AS revenue
FROM sales
WHERE price > 0
GROUP BY category
HAVING COUNT(*) >= 5
ORDER BY revenue DESC;

• Every column in the select list must be listed in GROUP BY, or be used inside an aggregate function. SELECT * is not allowed.
• Without GROUP BY, a query that uses an aggregate function treats the whole table as one group and returns one row, even when the table is empty.
• HAVING filters the groups. Its condition can compare aggregate functions and grouped columns. An aggregate used in HAVING does not need to be in the select list. LIKE is not supported in HAVING.
• WHERE is applied to the rows before they are grouped, and HAVING to the groups afterwards.

ORDER BY

ORDER BY sorts the result. Each item is a column, optionally followed by ASC (the default) or DESC. Earlier items take precedence; later items break ties.

SELECT name AS n, age FROM users ORDER BY age DESC, n;

• An item can be an alias defined in the select list. When a name is both an alias and a column, the alias wins.
• A column does not have to be in the select list, except with DISTINCT.
• In a query with aggregate functions or GROUP BY, an item must be a grouped column, an aggregate function, or an alias of either.
• Sorting by position (ORDER BY 1) and NULLS FIRST / NULLS LAST are not supported.

Without ORDER BY, the order of the rows is not defined.

LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET n skips the first n rows. Each takes a non-negative integer or a ? placeholder, and they can be written in either order. The maximum value is 4294967295 on every target; larger values are rejected so native and WASM builds accept the same statements.

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;
SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?;

Execution Order

A SELECT is evaluated in this order:

1. FROM and JOIN build the rows.
2. WHERE filters them.
3. DISTINCT removes duplicates, or GROUP BY forms groups and the aggregate functions are computed.
4. HAVING filters the groups.
5. ORDER BY sorts.
6. OFFSET and then LIMIT are applied.
7. The select list picks and names the columns.

INSERT

INSERT INTO users (id, name, email) VALUES (1, 'Alice', NULL);
INSERT INTO users (id, name, email) VALUES (?, ?, ?);

INSERT adds one row and reports one affected row.

• The column list is required, and each column can be listed once. There must be exactly one value per column.
• A column that is not listed is left to the DBMS: a nullable column becomes NULL, and an #[autoincrement] column gets the next number. Leaving out any other column is an error.
• Each value is a literal or a placeholder, converted to the type of its column. See Type Conversion.
• Sanitizers, validators, primary key, unique, and foreign key checks run as they do for the typed API.

Inserting several rows in one statement, INSERT ... SELECT, and DEFAULT VALUES are not supported.

UPDATE

UPDATE users SET name = 'Bob', email = NULL WHERE id = 2;

UPDATE changes the listed columns of every row that matches the WHERE condition, and reports the number of rows written. Zero matching rows is not an error.

• WHERE is required. A statement without it is rejected before anything is written. To change every row on purpose, write a condition that is true for all of them, such as WHERE id >= 0.
• Each column can be assigned once. The new value is a literal or a placeholder: a value cannot be computed from a column (SET n = n + 1 is not supported).
• Updating a primary key also updates the foreign keys that point at it.

Table aliases, ORDER BY, and LIMIT are not supported in UPDATE.

DELETE

DELETE FROM posts WHERE user_id = 3;
DELETE FROM users WHERE id = 3 CASCADE;

DELETE removes every row that matches the WHERE condition and reports the number of rows removed.

• WHERE is required. A statement without it is rejected before anything is deleted.
• An optional keyword after the condition says what happens when other rows reference a deleted row through a foreign key:

Outside a transaction, the reported count includes the rows removed by CASCADE. Inside a transaction, it only counts the rows that matched the condition.

CASCADE and RESTRICT are an extension to standard SQL, which has no way to choose the behavior in the statement.

Table aliases, ORDER BY, and LIMIT are not supported in DELETE.

Transactions

BEGIN;
UPDATE accounts SET balance = 50 WHERE id = 1;
UPDATE accounts SET balance = 150 WHERE id = 2;
COMMIT;

Each statement above is a separate execute call. BEGIN returns the id of the new transaction; the statements that follow pass that id to execute, and so do COMMIT and ROLLBACK.

• BEGIN is sent without a transaction id. Sending it with one is an error.
• A statement sent with a transaction id runs inside that transaction and sees its uncommitted changes. Statements sent without an id, and other transactions, do not see them until COMMIT.
• A statement that fails does not end the transaction. The program decides whether to continue, COMMIT, or ROLLBACK.
• COMMIT checks the constraints again while applying the changes. If it fails, nothing is applied and the transaction is over.
• COMMIT or ROLLBACK without a transaction id is an error, and COMMIT with the id of a transaction that is already over reports it as not found.
• Without a transaction id, each statement is applied immediately and atomically.

START TRANSACTION, END, savepoints, and isolation level settings are not supported.

Conditions

A condition is used in WHERE and HAVING. It is built from predicates combined with AND, OR, NOT, and parentheses.

Every predicate has a column on its left side and literals or placeholders on its right side. In HAVING, the left side can also be an aggregate function. Comparing two columns with each other, a literal on the left side, arithmetic, BETWEEN, EXISTS, and subqueries are not supported.

Comparison Operators

SELECT * FROM users WHERE age >= 18 AND name != 'admin';

The value is converted to the type of the column before it is compared. Text is compared character by character, and case matters.

IN

IN is true when the column equals any value of the list. The list needs at least one value. NOT IN is its negation.

SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE city NOT IN ('Rome', ?);

LIKE

LIKE matches a text column against a pattern. NOT LIKE is its negation. The pattern is a string literal or a placeholder.

SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM products WHERE code LIKE 'A_-%';

Matching is case-sensitive. A pattern that ends with a single backslash is an error.

IS NULL

IS NULL is true when the column has no value, and IS NOT NULL when it has one. They are the only way to test for NULL.

SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

AND, OR, NOT

From highest to lowest precedence:

1. Parentheses
2. NOT
3. AND
4. OR

AND and OR group from left to right.

-- read as: a = 1 OR (b = 2 AND c = 3)
SELECT * FROM t WHERE a = 1 OR b = 2 AND c = 3;

-- read as: (NOT a = 1) AND b = 2
SELECT * FROM t WHERE NOT a = 1 AND b = 2;

SELECT * FROM t WHERE (a = 1 OR b = 2) AND NOT (c = 3 OR d = 4);

NULL Handling

A condition is either true or false for a row: there is no third “unknown” result as in standard SQL. For a column that is NULL:

• =, IN, and LIKE are false.
• !=, NOT IN, NOT LIKE, and NOT (...) around a false predicate are true. WHERE email != 'a@example.com' therefore also returns the rows where email is NULL.
• The result of <, <=, >, and >= is not defined.

Add AND column IS NOT NULL when rows without a value must be left out. The position of NULL values in an ORDER BY is not defined either.

Aggregate Functions

• SUM and AVG need a numeric column: any integer type or Decimal.
• The argument is a column. COUNT(DISTINCT column), expressions, and nested aggregates are not supported.
• In the result, an aggregate column is named after the function call with the plain column name, in upper case: COUNT(*), SUM(price). Use AS to give it another name.
• A literal compared with an aggregate in HAVING is converted to the result type of the aggregate.

SELECT COUNT(*), COUNT(email), AVG(age), MIN(age), MAX(age) FROM users;

Type Conversion

A value is always used with a column: it is assigned to it, or compared with it. The value is converted to the exact type of that column. A value that cannot be converted is an error, and the statement does nothing.

Literals by Column Type

• “No” is reported as a type mismatch. A value of the right kind that does not fit, such as 300 for a Uint8 column or '2026-02-30' for a Date column, is reported as an invalid value.
• NULL can be written for any column type. Whether the column accepts it is checked by the DBMS.
• Nullable<T> columns convert like T.
• A custom data type has no literal form. Its values are passed as parameters.

String Formats

• A date must exist in the calendar: '2025-02-29' is rejected.
• The optional parts of a date-time are a fraction of a second with one to six digits (.5, .123456), followed by a time zone: Z for UTC, or an offset such as +02:00 or -08:00. Without a time zone, the value is UTC.
• Hexadecimal digits can be upper or lower case.

INSERT INTO events (day, at, token, payload, meta)
VALUES ('2026-04-24', '2026-04-24T10:30:00Z',
        '550e8400-e29b-41d4-a716-446655440000', 'deadbeef',
        '{"kind": "signup"}');

SELECT * FROM events WHERE day >= '2026-01-01';

Parameters

Parameters are the values passed to SqlEngine::execute next to the SQL text. There must be exactly one per ? placeholder.

A parameter is never read as SQL: its content cannot change the statement. Use parameters for every value that comes from outside the program.

A parameter is converted to the type of its column like a literal:

For example, a Value::Uint64(5) parameter can be compared with a Uint8 column, and a Value::Text("2026-04-24") parameter with a Date column.

A Null parameter is accepted in INSERT and UPDATE ... SET, and rejected in a comparison or an IN list. A LIMIT or OFFSET parameter must be a non-negative integer. A LIKE parameter must be Text.

Results

SqlEngine::execute returns a SqlResult:

A row is a list of (JoinColumnDef, Value) pairs, one per column of the select list, in order. The column definition describes the column:

table is always the real table name, even when the statement uses an alias.

Errors

A failed statement returns a SqlError. Syntax errors carry the line and column of the offending token, both starting at 1. The variants are described in the Errors Reference.

Limits

A term is one predicate, one AND, OR, or NOT, or one pair of parentheses. The conditions of WHERE and HAVING are counted together. The values of an IN list are not counted, so a long list of alternatives is best written with IN.

Reserved Words

These words cannot be used as names unless they are written between double quotes. Some are reserved for clauses that are not supported yet.

AND          AS           ASC          BEGIN        BY
CASCADE      COMMIT       CROSS        DELETE       DESC
DISTINCT     EXCEPT       FALSE        FROM         FULL
GROUP        HAVING       IN           INNER        INSERT
INTERSECT    INTO         IS           JOIN         LEFT
LIKE         LIMIT        NATURAL      NOT          NULL
OFFSET       ON           OR           ORDER        OUTER
RESTRICT     RIGHT        ROLLBACK     SELECT       SET
TRANSACTION  TRUE         UNION        UPDATE       USING
VALUES       WHERE

Not Supported

• Schema statements: CREATE, ALTER, DROP, TRUNCATE. Use migrations.
• Subqueries, UNION, INTERSECT, EXCEPT, and WITH.
• Expressions and scalar functions: arithmetic, string concatenation, CASE, COALESCE, LOWER, and so on.
• Comparisons between two columns, BETWEEN, and EXISTS.
• Self-joins, CROSS JOIN, NATURAL JOIN, and USING.
• DISTINCT or aggregate functions together with a join.
• COUNT(DISTINCT ...).
• Multi-row INSERT, INSERT ... SELECT, and RETURNING.
• Several statements in one call.
• Named and numbered placeholders.
• Filters on the content of Json columns. Use the JSON filters of the query builder.
• Eager loading of relations. Use a JOIN, or the query builder.

Validation Reference

• Validation Reference
  • Overview
  • Syntax
  • Built-in Validators
    • String Length Validators
    • Format Validators
    • Case Validators
    • Locale Validators
  • Implementing Custom Validators
  • Validation Errors
  • Examples

Overview

Validators enforce constraints on data being inserted or updated. If validation fails, the operation is rejected with a Validation error.

Key points:

• Validators run after sanitizers
• Validation failure rejects the entire operation
• Multiple validators can be applied to a single field
• Validators are applied on both insert and update

Syntax

The #[validate(...)] attribute adds validation rules to fields:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    // Unit struct validator (no parameters)
    #[validate(EmailValidator)]
    pub email: Text,

    // Tuple struct validator (positional parameter)
    #[validate(MaxStrlenValidator(100))]
    pub name: Text,

    // Several validators, run top to bottom
    #[validate(MinStrlenValidator(3))]
    #[validate(MaxStrlenValidator(20))]
    pub username: Text,
}
}

When a field has several #[validate(...)] attributes, every validator runs in declaration order (top to bottom) and the value is rejected with the error of the first validator that fails. The generated table schema combines them into a ValidatorChain, which you can also build yourself when implementing TableSchema by hand.

Built-in Validators

All validators are available in wasm_dbms_api::prelude.

String Length Validators

String lengths are counted in Unicode characters (scalar values), not bytes: "🙂" and "é" each have length 1. A character written with a combining mark, such as e followed by U+0301, counts as two.

MaxStrlenValidator - Maximum string length

#![allow(unused)]
fn main() {
#[validate(MaxStrlenValidator(255))]
pub description: Text,  // Max 255 characters
}

MinStrlenValidator - Minimum string length

#![allow(unused)]
fn main() {
#[validate(MinStrlenValidator(8))]
pub password: Text,  // At least 8 characters
}

RangeStrlenValidator - String length within range

#![allow(unused)]
fn main() {
#[validate(RangeStrlenValidator(3, 50))]
pub username: Text,  // Between 3 and 50 characters
}

Format Validators

EmailValidator - Valid email format

#![allow(unused)]
fn main() {
#[validate(EmailValidator)]
pub email: Text,  // Must be valid email
}

UrlValidator - Valid URL format

#![allow(unused)]
fn main() {
#[validate(UrlValidator)]
pub website: Text,  // Must be valid URL
}

PhoneNumberValidator - Valid phone number format

#![allow(unused)]
fn main() {
#[validate(PhoneNumberValidator)]
pub phone: Text,  // Must be valid phone number
}

MimeTypeValidator - Valid MIME type format

#![allow(unused)]
fn main() {
#[validate(MimeTypeValidator)]
pub content_type: Text,  // e.g., "application/json", "image/png"
}

The value must be type/subtype, where both names follow the RFC 6838 section 4.2 restricted-name grammar: 1 to 127 characters, starting with an ASCII letter or digit, followed by ASCII letters, digits, or any of ! # $ & - ^ _ . +. Parameters such as ; charset=utf-8 are not accepted.

RgbColorValidator - Valid RGB color format

#![allow(unused)]
fn main() {
#[validate(RgbColorValidator)]
pub color: Text,  // e.g., "#FF5733", "rgb(255, 87, 51)"
}

Case Validators

CamelCaseValidator - Must be camelCase

#![allow(unused)]
fn main() {
#[validate(CamelCaseValidator)]
pub identifier: Text,  // e.g., "myVariableName"
}

KebabCaseValidator - Must be kebab-case

#![allow(unused)]
fn main() {
#[validate(KebabCaseValidator)]
pub slug: Text,  // e.g., "my-page-slug"
}

SnakeCaseValidator - Must be snake_case

#![allow(unused)]
fn main() {
#[validate(SnakeCaseValidator)]
pub code: Text,  // e.g., "my_constant_name"
}

Locale Validators

CountryIso639Validator - ISO 639 language code

#![allow(unused)]
fn main() {
#[validate(CountryIso639Validator)]
pub language: Text,  // e.g., "en", "es", "fr"
}

CountryIso3166Validator - ISO 3166 country code

#![allow(unused)]
fn main() {
#[validate(CountryIso3166Validator)]
pub country: Text,  // e.g., "US", "GB", "DE"
}

Implementing Custom Validators

Create a struct implementing the Validate trait:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::{DbmsError, DbmsResult, Validate, Value};

/// Validates that a number is positive
pub struct PositiveValidator;

impl Validate for PositiveValidator {
    fn validate(&self, value: &Value) -> DbmsResult<()> {
        match value {
            Value::Int32(n) if n.0 > 0 => Ok(()),
            Value::Int64(n) if n.0 > 0 => Ok(()),
            Value::Decimal(d) if d.0 > rust_decimal::Decimal::ZERO => Ok(()),
            Value::Int32(_) | Value::Int64(_) | Value::Decimal(_) => {
                Err(DbmsError::Validation("Value must be positive".to_string()))
            }
            _ => Err(DbmsError::Validation(
                "PositiveValidator only applies to numeric types".to_string(),
            )),
        }
    }
}

// Usage
#[derive(Table, ...)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uint32,
    #[validate(PositiveValidator)]
    pub price: Decimal,
}
}

Custom validator with parameters (tuple struct):

#![allow(unused)]
fn main() {
/// Validates that a string matches a regex pattern
pub struct RegexValidator(pub &'static str);

impl Validate for RegexValidator {
    fn validate(&self, value: &Value) -> DbmsResult<()> {
        if let Value::Text(text) = value {
            let re = regex::Regex::new(self.0).unwrap();
            if re.is_match(text.as_str()) {
                return Ok(());
            }
        }
        Err(DbmsError::Validation(
            format!("Value does not match pattern: {}", self.0)
        ))
    }
}

// Usage
#[validate(RegexValidator(r"^[A-Z]{2}-\d{4}$"))]
pub product_code: Text,  // Must match "XX-1234" format
}

Custom validator with named parameters:

#![allow(unused)]
fn main() {
/// Validates a number is within a range
pub struct RangeValidator {
    pub min: i64,
    pub max: i64,
}

impl Validate for RangeValidator {
    fn validate(&self, value: &Value) -> DbmsResult<()> {
        let num = match value {
            Value::Int32(n) => n.0 as i64,
            Value::Int64(n) => n.0,
            _ => return Err(DbmsError::Validation("RangeValidator requires integer".to_string())),
        };

        if num >= self.min && num <= self.max {
            Ok(())
        } else {
            Err(DbmsError::Validation(
                format!("Value must be between {} and {}", self.min, self.max)
            ))
        }
    }
}

// Usage
#[validate(RangeValidator, min = 1, max = 100)]
pub percentage: Int32,
}

Validation Errors

When validation fails, a DbmsError::Validation(String) is returned:

#![allow(unused)]
fn main() {
let result = database.insert::<User>(user);

match result {
    Ok(()) => println!("Insert successful"),
    Err(DbmsError::Validation(msg)) => {
        println!("Validation failed: {}", msg);
        // e.g., "Invalid email format"
        // e.g., "String length exceeds maximum of 100"
    }
    Err(e) => println!("Other error: {:?}", e),
}
}

Examples

Comprehensive user validation:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    #[validate(RangeStrlenValidator(2, 50))]
    pub name: Text,

    #[validate(EmailValidator)]
    pub email: Text,

    #[validate(MinStrlenValidator(8))]
    pub password_hash: Text,

    #[validate(PhoneNumberValidator)]
    pub phone: Nullable<Text>,

    #[validate(UrlValidator)]
    pub website: Nullable<Text>,

    #[validate(CountryIso3166Validator)]
    pub country: Nullable<Text>,

    #[validate(CountryIso639Validator)]
    pub language: Text,
}
}

Product validation:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uuid,

    #[validate(RangeStrlenValidator(1, 200))]
    pub name: Text,

    #[validate(MaxStrlenValidator(2000))]
    pub description: Text,

    #[validate(KebabCaseValidator)]
    pub slug: Text,

    #[validate(MimeTypeValidator)]
    pub image_type: Nullable<Text>,

    #[validate(RgbColorValidator)]
    pub accent_color: Nullable<Text>,
}
}

Combined with sanitizers:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "articles"]
pub struct Article {
    #[primary_key]
    pub id: Uuid,

    // Sanitize first, then validate
    #[sanitizer(TrimSanitizer)]
    #[validate(MaxStrlenValidator(200))]
    pub title: Text,

    // Convert to slug format, then validate
    #[sanitizer(SlugSanitizer)]
    #[validate(KebabCaseValidator)]
    pub slug: Text,

    #[sanitizer(TrimSanitizer)]
    pub content: Text,
}
}

Sanitization Reference

• Overview
• Syntax
  • Attribute Forms
• Built-in Sanitizers
  • String Sanitizers
  • Numeric Sanitizers
  • DateTime Sanitizers
  • Null Sanitizers
• Implementing Custom Sanitizers
• Sanitization Order
• Examples

Overview

Sanitizers automatically transform data before it’s stored in the database. Unlike validators (which reject invalid data), sanitizers modify data to conform to expected formats.

Key points:

• Sanitizers run before validators
• Data is transformed, not rejected
• Multiple sanitizers can be chained
• Sanitizers apply on both insert and update

Syntax

The #[sanitizer(...)] attribute adds sanitization rules to fields:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    // Unit struct sanitizer (no parameters)
    #[sanitizer(TrimSanitizer)]
    pub name: Text,

    // Tuple struct sanitizer (positional parameter)
    #[sanitizer(RoundToScaleSanitizer(2))]
    pub balance: Decimal,

    // Named fields sanitizer
    #[sanitizer(ClampSanitizer, min = 0, max = 120)]
    pub age: Uint8,
}
}

Attribute Forms

The first argument of #[sanitizer(...)] is always the path of the sanitizer type. The arguments that follow depend on how the sanitizer is declared:

Rules:

• The sanitizer path may be qualified, for example #[sanitizer(wasm_dbms_api::prelude::TrimSanitizer)].
• Tuple arguments are positional and are passed to the tuple struct in order. Named options are name = value pairs, written after the sanitizer path and separated by commas. Values are Rust expressions, so negative numbers such as min = -100 are allowed.
• A tuple sanitizer cannot be combined with named options: #[sanitizer(RoundToScaleSanitizer(2), scale = 3)] is rejected.
• The sanitizer path must come first. #[sanitizer(min = 0, ClampSanitizer)] is rejected.
• Every named option must be an assignment. #[sanitizer(ClampSanitizer, 5)] is rejected.
• An empty attribute, #[sanitizer()], is rejected.
• Invalid attributes fail at compile time with an error pointing at the offending tokens.
• A field can have several #[sanitizer(...)] attributes. They run in the order they are written, as described in Sanitization Order.

Built-in Sanitizers

All sanitizers are available in wasm_dbms_api::prelude.

String Sanitizers

TrimSanitizer - Remove leading/trailing whitespace

#![allow(unused)]
fn main() {
#[sanitizer(TrimSanitizer)]
pub name: Text,
// "  Alice  " → "Alice"
}

CollapseWhitespaceSanitizer - Collapse multiple spaces into one

#![allow(unused)]
fn main() {
#[sanitizer(CollapseWhitespaceSanitizer)]
pub description: Text,
// "Hello    World" → "Hello World"
}

LowerCaseSanitizer - Convert to lowercase

#![allow(unused)]
fn main() {
#[sanitizer(LowerCaseSanitizer)]
pub email: Text,
// "Alice@Example.COM" → "alice@example.com"
}

UpperCaseSanitizer - Convert to uppercase

#![allow(unused)]
fn main() {
#[sanitizer(UpperCaseSanitizer)]
pub country_code: Text,
// "us" → "US"
}

SlugSanitizer - Convert to URL-safe slug

#![allow(unused)]
fn main() {
#[sanitizer(SlugSanitizer)]
pub slug: Text,
// "Hello World! This is a Test" → "hello-world-this-is-a-test"
}

UrlEncodingSanitizer - URL encode special characters

#![allow(unused)]
fn main() {
#[sanitizer(UrlEncodingSanitizer)]
pub path: Text,
// "hello world" → "hello%20world"
}

Numeric Sanitizers

RoundToScaleSanitizer - Round decimal to specific precision

#![allow(unused)]
fn main() {
#[sanitizer(RoundToScaleSanitizer(2))]
pub price: Decimal,
// 19.999 → 20.00
// 19.994 → 19.99
}

ClampSanitizer - Clamp value to range (signed)

#![allow(unused)]
fn main() {
#[sanitizer(ClampSanitizer, min = -100, max = 100)]
pub temperature: Int32,
// 150 → 100
// -150 → -100
}

ClampUnsignedSanitizer - Clamp value to range (unsigned)

#![allow(unused)]
fn main() {
#[sanitizer(ClampUnsignedSanitizer, min = 0, max = 100)]
pub percentage: Uint8,
// 150 → 100
// 0 → 0
}

ClampSanitizer applies to Int8, Int16, Int32, and Int64 values, while ClampUnsignedSanitizer applies to Uint8, Uint16, Uint32, and Uint64 values. Bounds outside the range of the column’s integer type are saturated to that range: clamping an Int8 with min = -1000, max = 1000 keeps every value, and clamping it with min = 1000, max = 2000 yields 127.

DateTime Sanitizers

TimezoneSanitizer - Convert to specific timezone

#![allow(unused)]
fn main() {
#[sanitizer(TimezoneSanitizer(-300))] // UTC-05:00, offset in minutes
pub local_time: DateTime,
}

The target offset and the offset of the value must both be less than one day, within -1439..=1439 minutes; a value outside that range is rejected with DbmsError::Sanitize.

UtcSanitizer - Convert to UTC

#![allow(unused)]
fn main() {
#[sanitizer(UtcSanitizer)]
pub timestamp: DateTime,
// Any timezone → UTC
}

Null Sanitizers

NullIfEmptySanitizer - Convert empty strings to null

#![allow(unused)]
fn main() {
#[sanitizer(NullIfEmptySanitizer)]
pub bio: Nullable<Text>,
// "" → Null
// "Hello" → "Hello"
}

Implementing Custom Sanitizers

Create a struct implementing the Sanitize trait:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::{Sanitize, Value, DbmsResult};

/// Capitalizes the first letter of each word
pub struct TitleCaseSanitizer;

impl Sanitize for TitleCaseSanitizer {
    fn sanitize(&self, value: Value) -> DbmsResult<Value> {
        match value {
            Value::Text(text) => {
                let title_case = text
                    .as_str()
                    .split_whitespace()
                    .map(|word| {
                        let mut chars = word.chars();
                        match chars.next() {
                            None => String::new(),
                            Some(first) => {
                                first.to_uppercase().to_string() +
                                chars.as_str().to_lowercase().as_str()
                            }
                        }
                    })
                    .collect::<Vec<_>>()
                    .join(" ");
                Ok(Value::Text(title_case.into()))
            }
            other => Ok(other),  // Pass through non-text values
        }
    }
}

// Usage
#[sanitizer(TitleCaseSanitizer)]
pub title: Text,
// "hello world" → "Hello World"
}

Custom sanitizer with parameters:

#![allow(unused)]
fn main() {
/// Truncates string to max length
pub struct TruncateSanitizer(pub usize);

impl Sanitize for TruncateSanitizer {
    fn sanitize(&self, value: Value) -> DbmsResult<Value> {
        match value {
            Value::Text(text) => {
                let truncated: String = text.as_str().chars().take(self.0).collect();
                Ok(Value::Text(truncated.into()))
            }
            other => Ok(other),
        }
    }
}

// Usage
#[sanitizer(TruncateSanitizer(100))]
pub summary: Text,
// "very long text..." → truncated to 100 chars
}

Custom sanitizer with named parameters:

#![allow(unused)]
fn main() {
/// Replaces a pattern with replacement
pub struct ReplaceSanitizer {
    pub pattern: &'static str,
    pub replacement: &'static str,
}

impl Sanitize for ReplaceSanitizer {
    fn sanitize(&self, value: Value) -> DbmsResult<Value> {
        match value {
            Value::Text(text) => {
                let replaced = text.as_str().replace(self.pattern, self.replacement);
                Ok(Value::Text(replaced.into()))
            }
            other => Ok(other),
        }
    }
}

// Usage
#[sanitizer(ReplaceSanitizer, pattern = "\n", replacement = " ")]
pub single_line: Text,
}

Sanitization Order

When multiple sanitizers are applied, they run in declaration order:

#![allow(unused)]
fn main() {
#[derive(Table, ...)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    // Order matters!
    #[sanitizer(TrimSanitizer)] // 1. Trim whitespace
    #[sanitizer(CollapseWhitespaceSanitizer)] // 2. Collapse spaces
    #[sanitizer(LowerCaseSanitizer)] // 3. Lowercase
    pub email: Text,
}

// Input: "  Alice@Example.COM  "
// After TrimSanitizer: "Alice@Example.COM"
// After CollapseWhitespaceSanitizer: "Alice@Example.COM" (no change)
// After LowerCaseSanitizer: "alice@example.com"
}

Each sanitizer receives the output of the previous one, and the first error stops the chain. The generated table schema combines them into a SanitizerChain, which you can also build yourself when implementing TableSchema by hand.

Sanitizers run before validators:

#![allow(unused)]
fn main() {
#[sanitizer(TrimSanitizer)]           // 1. Trim
#[validate(MaxStrlenValidator(100))]  // 2. Validate length (after trim)
pub name: Text,
}

Examples

User profile sanitization:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,

    // Clean up name
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(CollapseWhitespaceSanitizer)]
    pub name: Text,

    // Normalize email
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(LowerCaseSanitizer)]
    pub email: Text,

    // Convert empty to null
    #[sanitizer(NullIfEmptySanitizer)]
    pub bio: Nullable<Text>,

    // Uppercase country code
    #[sanitizer(UpperCaseSanitizer)]
    pub country: Nullable<Text>,
}
}

Financial data sanitization:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "transactions"]
pub struct Transaction {
    #[primary_key]
    pub id: Uuid,

    // Round to cents
    #[sanitizer(RoundToScaleSanitizer(2))]
    pub amount: Decimal,

    // Ensure positive (clamp negatives to 0)
    #[sanitizer(ClampUnsignedSanitizer, min = 0, max = 1000000)]
    pub fee: Uint32,

    // Always store in UTC
    #[sanitizer(UtcSanitizer)]
    pub timestamp: DateTime,
}
}

Content sanitization:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "articles"]
pub struct Article {
    #[primary_key]
    pub id: Uuid,

    // Clean title
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(CollapseWhitespaceSanitizer)]
    pub title: Text,

    // Generate URL-safe slug
    #[sanitizer(SlugSanitizer)]
    pub slug: Text,

    // Clean up content
    #[sanitizer(TrimSanitizer)]
    pub content: Text,

    // Optional summary
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(NullIfEmptySanitizer)]
    pub summary: Nullable<Text>,
}
}

Combined sanitization and validation:

#![allow(unused)]
fn main() {
#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uuid,

    // Sanitize then validate
    #[sanitizer(TrimSanitizer)]
    #[sanitizer(CollapseWhitespaceSanitizer)]
    #[validate(RangeStrlenValidator(1, 200))]
    pub name: Text,

    // Sanitize price to 2 decimals, no validation needed
    #[sanitizer(RoundToScaleSanitizer(2))]
    pub price: Decimal,

    // Create slug and validate format
    #[sanitizer(SlugSanitizer)]
    #[validate(KebabCaseValidator)]
    #[validate(MaxStrlenValidator(100))]
    pub slug: Text,

    // Clean URL and validate format
    #[sanitizer(TrimSanitizer)]
    #[validate(UrlValidator)]
    pub image_url: Nullable<Text>,
}
}

JSON Reference

• Overview
• Defining JSON Columns
• Creating JSON Values
• JSON Filtering
  • Path Syntax
• Filter Operations
  • Contains (Structural Containment)
  • Extract (Path Extraction + Comparison)
  • HasKey (Path Existence)
• Combining JSON Filters
• Type Conversion
• Complete Example
• Error Handling

Overview

The Json data type allows you to store and query semi-structured JSON data within your database tables. This is useful for:

• Flexible schemas where structure varies between records
• Metadata storage
• User preferences and settings
• Any scenario where data structure may evolve

Defining JSON Columns

To use JSON in your schema, use the Json type:

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "users"]
pub struct User {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    pub metadata: Json,           // Required JSON field
    pub settings: Nullable<Json>, // Optional JSON field
}
}

Creating JSON Values

From string:

#![allow(unused)]
fn main() {
use std::str::FromStr;
use wasm_dbms_api::prelude::Json;

let json = Json::from_str(r#"{"name": "Alice", "age": 30}"#).unwrap();
}

From serde_json::Value:

#![allow(unused)]
fn main() {
use serde_json::json;
use wasm_dbms_api::prelude::Json;

let json: Json = json!({
    "name": "Alice",
    "age": 30,
    "tags": ["developer", "rust"],
    "address": {
        "city": "New York",
        "country": "US"
    }
}).into();
}

In insert requests:

#![allow(unused)]
fn main() {
let user = UserInsertRequest {
    id: 1.into(),
    name: "Alice".into(),
    metadata: json!({
        "role": "admin",
        "permissions": ["read", "write", "delete"]
    }).into(),
    settings: Nullable::Value(json!({
        "theme": "dark",
        "notifications": true
    }).into()),
};
}

JSON Filtering

wasm-dbms provides powerful JSON filtering through the JsonFilter enum:

• Contains: Check if JSON contains a pattern (structural containment)
• Extract: Extract value at path and compare
• HasKey: Check if a path exists

Path Syntax

Paths use dot notation with bracket array indices:

Path examples:

{
  "name": "Alice",           // Path: "name"
  "user": {
    "email": "a@b.com"       // Path: "user.email"
  },
  "tags": ["a", "b", "c"],   // Path: "tags[0]" = "a"
  "matrix": [[1,2], [3,4]]   // Path: "matrix[1][0]" = 3
}

Filter Operations

Contains (Structural Containment)

Checks if the JSON column contains a specified pattern. Implements PostgreSQL @> style containment:

• Objects: All key-value pairs in pattern must exist in target (recursive)
• Arrays: All elements in pattern must exist in target (order-independent)
• Primitives: Must be equal

#![allow(unused)]
fn main() {
use wasm_dbms_api::prelude::*;
use std::str::FromStr;

// Filter where metadata contains {"active": true}
let pattern = Json::from_str(r#"{"active": true}"#).unwrap();
let filter = Filter::json("metadata", JsonFilter::contains(pattern));
}

Containment behavior:

Use cases:

• Check if user has specific role: contains({"role": "admin"})
• Check if array contains value: contains({"tags": ["important"]})
• Check nested properties: contains({"settings": {"theme": "dark"}})

Extract (Path Extraction + Comparison)

Extract a value at path and apply comparison:

#![allow(unused)]
fn main() {
// Equal
let filter = Filter::json("metadata",
    JsonFilter::extract_eq("user.name", Value::Text("Alice".into()))
);

// Greater than
let filter = Filter::json("metadata",
    JsonFilter::extract_gt("user.age", Value::Int64(18.into()))
);

// In list
let filter = Filter::json("metadata",
    JsonFilter::extract_in("status", vec![
        Value::Text("active".into()),
        Value::Text("pending".into()),
    ])
);

// Is null (path doesn't exist or value is null)
let filter = Filter::json("metadata",
    JsonFilter::extract_is_null("deleted_at")
);

// Not null (path exists and value is not null)
let filter = Filter::json("metadata",
    JsonFilter::extract_not_null("email")
);
}

Available comparison methods:

extract_is_null checks for a JSON null inside a stored document. A nullable Json column holding NULL matches no JSON filter, including extract_is_null. Use Filter::is_null to find those rows.

HasKey (Path Existence)

Check if a path exists in the JSON:

#![allow(unused)]
fn main() {
// Check for root-level key
let filter = Filter::json("metadata", JsonFilter::has_key("email"));

// Check for nested path
let filter = Filter::json("metadata", JsonFilter::has_key("user.address.city"));

// Check for array element
let filter = Filter::json("metadata", JsonFilter::has_key("items[0]"));
}

Note: HasKey returns true even if the value at path is null. It only checks for path existence.

Combining JSON Filters

JSON filters combine with other filters using and(), or(), not():

#![allow(unused)]
fn main() {
// has email AND age > 18
let filter = Filter::json("metadata", JsonFilter::has_key("email"))
    .and(Filter::json("metadata", JsonFilter::extract_gt("age", Value::Int64(18.into()))));

// role = "admin" OR role = "moderator"
let filter = Filter::json("metadata", JsonFilter::extract_eq("role", Value::Text("admin".into())))
    .or(Filter::json("metadata", JsonFilter::extract_eq("role", Value::Text("moderator".into()))));

// Combine with regular filters
let pattern = Json::from_str(r#"{"active": true}"#).unwrap();
let filter = Filter::eq("id", Value::Int32(1.into()))
    .and(Filter::json("metadata", JsonFilter::contains(pattern)));

// NOT has deleted_at
let filter = Filter::json("metadata", JsonFilter::has_key("deleted_at")).not();
}

Type Conversion

When extracting JSON values, they’re converted to DBMS types:

A number too large to fit a Decimal, such as 1e100, is extracted as a Value::Json holding that number. It is still a present, non-null value: has_key and extract_not_null match it, and extract_is_null does not.

Numeric comparisons use the numeric magnitude. An extracted number compared with any integer value (Int8 to Int64, Uint8 to Uint64) or with a Decimal matches as you would expect from the numbers alone: 1.5 is greater than Value::Int64(1), and 1 is equal to Value::Decimal(1.0). This applies to Eq, Ne, Gt, Lt, Ge, Le and In.

Comparison examples:

#![allow(unused)]
fn main() {
// JSON: {"count": 42}
// Extracted as Int64, compare with Int64
JsonFilter::extract_eq("count", Value::Int64(42.into()))

// JSON: {"price": 19.99}
// Extracted as Decimal, compare with Decimal
JsonFilter::extract_gt("price", Value::Decimal(10.0.into()))

// JSON: {"active": true}
// Extracted as Boolean
JsonFilter::extract_eq("active", Value::Boolean(true))

// JSON: {"name": "Alice"}
// Extracted as Text
JsonFilter::extract_eq("name", Value::Text("Alice".into()))
}

Complete Example

#![allow(unused)]
fn main() {
use std::str::FromStr;

use wasm_dbms_api::prelude::*;

#[derive(Debug, Table, Clone, PartialEq, Eq)]
#[table = "products"]
pub struct Product {
    #[primary_key]
    pub id: Uint32,
    pub name: Text,
    pub attributes: Json, // {"color": "red", "size": "M", "tags": ["sale", "new"], "price": 29.99}
}

fn example_queries(database: &impl Database) -> Result<(), Box<dyn std::error::Error>> {
    // Find all red products
    let filter = Filter::json(
        "attributes",
        JsonFilter::extract_eq("color", Value::Text("red".into())),
    );
    let query = Query::builder().filter(filter).build();
    let red_products = database.select::<Product>(query)?;

    // Find products with "sale" tag
    let pattern = Json::from_str(r#"{"tags": ["sale"]}"#)?;
    let filter = Filter::json("attributes", JsonFilter::contains(pattern));
    let query = Query::builder().filter(filter).build();
    let sale_products = database.select::<Product>(query)?;

    // Find products with size attribute
    let filter = Filter::json("attributes", JsonFilter::has_key("size"));
    let query = Query::builder().filter(filter).build();
    let sized_products = database.select::<Product>(query)?;

    // Find red products with price > 20
    let filter = Filter::json(
        "attributes",
        JsonFilter::extract_eq("color", Value::Text("red".into())),
    )
    .and(Filter::json(
        "attributes",
        JsonFilter::extract_gt("price", Value::Decimal(20.0.into())),
    ));
    let query = Query::builder().filter(filter).build();
    let expensive_red = database.select::<Product>(query)?;

    // Find products in specific sizes
    let filter = Filter::json(
        "attributes",
        JsonFilter::extract_in(
            "size",
            vec![
                Value::Text("S".into()),
                Value::Text("M".into()),
                Value::Text("L".into()),
            ],
        ),
    );
    let query = Query::builder().filter(filter).build();
    let standard_sizes = database.select::<Product>(query)?;

    Ok(())
}
}

Error Handling

JSON filter operations return errors for:

Invalid path syntax:

• Empty paths
• Trailing dots ("user.")
• Unclosed brackets ("items[0")
• Negative indices ("items[-1]")
• Non-numeric array indices ("items[abc]")

Non-JSON column:

• Applying JSON filter to a non-JSON column

#![allow(unused)]
fn main() {
// Invalid path - will error
let filter = Filter::json("metadata", JsonFilter::has_key("user."));  // Trailing dot

// Non-JSON column - will error
let filter = Filter::json("name", JsonFilter::has_key("field"));  // "name" is Text, not Json

let result = database.select::<User>(
    Query::builder().filter(filter).build(),
);

match result {
    Err(DbmsError::Query(QueryError::InvalidQuery)) => {
        println!("Invalid JSON filter");
    }
    _ => {}
}
}

Schema Migrations

• Schema Migrations
  • Overview
  • Lifecycle
  • Drift Detection
  • Schema Snapshots
  • Migration Plan
    • MigrationOp
    • Apply Order
  • Compatible Widening Whitelist
  • Per-Table Hooks
    • #[default] Attribute
    • #[renamed_from] Attribute
    • Migrate Trait
  • Migration Policy
  • Errors
  • API Surface
    • Generic (wasm-dbms)
    • DatabaseSchema Dispatch
  • Non-Goals
  • Worked Example
  • Best Practices

Overview

Schema migrations let #[derive(Table)] schemas evolve across releases without losing data or requiring manual stable-memory surgery. The framework:

1. Stores a TableSchemaSnapshot for every registered table on disk.
2. Hashes those snapshots into a single schema_hash cached on Page 0 of the schema registry.
3. On every boot, recomputes the hash from the compiled schema and compares it against the stored hash to detect drift.
4. Refuses CRUD while in drift state and waits for an explicit dbms.migrate(policy) call.
5. Applies the diff between stored and compiled snapshots transactionally via the journaled writer.

Migrations are forward-only and explicit. The DBMS never auto-migrates on init — the caller decides when (and whether) to run them.

Lifecycle

┌─────────────────────────────────────────────────────────┐
│  boot / post_upgrade                                    │
│    ├─ load SchemaRegistry (Page 0) → read schema_hash   │
│    ├─ compute current_hash from compiled schemas        │
│    └─ drift = (stored_hash != current_hash)             │
├─────────────────────────────────────────────────────────┤
│  drift == false                                         │
│    ├─ CRUD allowed                                      │
│    └─ migrate() is a no-op                              │
├─────────────────────────────────────────────────────────┤
│  drift == true                                          │
│    ├─ CRUD returns DbmsError::Migration(SchemaDrift)    │
│    ├─ pending_migrations() → Vec<MigrationOp>               │
│    └─ migrate(policy) applies ops, clears drift         │
└─────────────────────────────────────────────────────────┘

Performance contract:

• Boot: one u64 read from Page 0 plus one xxh3 hash of the encoded compiled snapshots. O(tables × columns).
• Hot path (CRUD): a single bool load (drift flag on the DBMS context) plus a branch. No snapshot decode, no hash recompute.
• Snapshot decode: only on pending_migrations() or migrate(). Never during CRUD.