ic-sqlite-vfs

SQLite VFS for the Internet Computer that stores the SQLite database image directly in IC stable memory.

SQLite pager
  -> custom sqlite3_vfs: icstable
  -> ic0.stable64_read / ic0.stable64_write
  -> stable memory pages

ic-sqlite-vfs does not use POSIX files, WASI files, stable-fs, or wasi2ic. SQLite sees /main.db; the canister stores it as a contiguous byte range in stable memory.

Status

Initial public release: 0.1.0.

The core VFS, transaction facade, import/export flow, and upgrade persistence tests are in place. This project has not promised compatibility for deployed canisters yet. 0.x releases may introduce breaking changes.

See docs/API_STABILITY.md for the 0.x compatibility contract.

Why

SQLite already has the abstraction IC canisters need: sqlite3_vfs and sqlite3_io_methods. A VFS receives reads and writes as (offset, length). That maps directly to IC stable memory.

wasi2ic is useful when an existing WASI program must run unchanged. For SQLite, it adds a generic compatibility layer that SQLite does not need:

SQLite -> WASI fd/read/write/seek -> wasi2ic -> file abstraction -> stable memory

This crate uses the shorter path:

SQLite -> sqlite3_io_methods xRead/xWrite -> stable memory

Design

Canister API
  -> Rust DB facade
  -> SQLite C core / libsqlite3-sys
  -> custom sqlite3_vfs: icstable
  -> IC stable memory pages

Stable memory layout:

offset 0..64KiB      superblock
offset 64KiB..       active and inactive SQLite database images

The superblock stores magic, schema version, logical DB size, transaction id, active DB image offset, last verified checksum, import state, and flags. The SQLite database header starts at byte 0 of the active DB image.

checksum is verification metadata. Normal update commits do not scan the full DB image. They advance last_tx_id and set checksum_stale. A controller can run db_refresh_checksum to recompute the checksum, store it, and clear checksum_stale.

SQLite Settings

The reference facade uses:

PRAGMA page_size = 16384;
PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -32768;
PRAGMA busy_timeout = 0;

Durability is based on IC message atomicity and a heap write overlay, not fsync. During an update call, VFS writes stay in heap memory until SQLite COMMIT succeeds. The committed image is then written to inactive stable memory and made active by the final superblock update.

Rules:

• one update call is one DB transaction
• no await inside a transaction
• query calls open read-only connections
• WAL is disabled
• journal and temp data stay in heap memory
• only the DB image is stored in stable memory
• failed update calls return Err without changing the active DB image

Query complexity is the consuming canister's responsibility. This crate does not inspect arbitrary SQL for index use or planner cost. Public APIs should expose bounded application queries with explicit WHERE clauses, indexes, LIMIT/pagination, and input length caps. The reference canister intentionally does not expose an arbitrary SQL endpoint.

Treat these patterns as unsafe for public canister APIs unless they are tightly bounded and measured:

• full table scans and filters without a primary key or index
• huge result sets or unpaginated reads
• LIKE '%foo%'
• join-heavy queries
• unbounded ORDER BY
• huge BLOB values

An IC update or query has a finite instruction/cycles budget. Fetching many rows in one call can exhaust that budget and trap even when SQLite itself is working as designed. Prefer point reads, indexed range reads, and explicit page sizes.

Why Not ic-stable-structures?

Use ic-stable-structures when the data model is a key-value store, BTree, or append-only log. It is simpler, has fewer moving parts, and avoids SQL planner costs.

Use this crate only when SQLite is worth the extra surface area: schema migrations, compound indexes, relational constraints, or ad-hoc queries that would otherwise become custom storage logic.

Why Not rusqlite?

rusqlite is the usual choice for SQLite in normal Rust programs. This crate is for IC canisters that store SQLite directly in stable memory.

The bundled SQLite build uses SQLITE_THREADSAFE=0, which removes SQLite's internal mutex code. That fits the canister model because a Db::update or Db::query closure runs synchronously inside one IC message and must not cross an await boundary.

rusqlite assumes SQLite was built with thread-safety support before exposing its safe Rust API. A SQLITE_THREADSAFE=0 build violates that assumption, so this crate uses a small SQLite C FFI facade instead of rusqlite.

Use this crate when SQLite must persist in IC stable memory. Use rusqlite for ordinary Rust applications that store SQLite in regular files.

Usage

Library users should disable default features. The canister-api feature is only for this repository's reference canister.

[dependencies]
ic-sqlite-vfs = { version = "0.1.0", default-features = false }

Consumers must build bundled SQLite with SQLITE_OS_OTHER=1 and a C compiler that can emit wasm32-unknown-unknown compatible objects. Install the reference support files into the consuming canister workspace:

scripts/install-build-support.sh /path/to/canister-workspace

The installer adds .cargo/config.toml, scripts/wasm32-unknown-unknown-cc.sh, and c/include/*. It refuses to overwrite existing files unless --force is passed.

See docs/BUILD_SETUP.md for details and rationale.

Minimal canister pattern:

use ic_sqlite_vfs::db::migrate::Migration;
use ic_sqlite_vfs::Db;

const MIGRATIONS: &[Migration] = &[Migration {
    version: 1,
    sql: "CREATE TABLE IF NOT EXISTS kv (
        key TEXT PRIMARY KEY NOT NULL,
        value TEXT NOT NULL
    );",
}];

#[ic_cdk::init]
fn init() {
    Db::migrate(MIGRATIONS).unwrap();
}

#[ic_cdk::update]
fn put(key: String, value: String) -> Result<(), String> {
    Db::update(|connection| {
        connection.execute_with_texts(
            "INSERT INTO kv(key, value) VALUES (?1, ?2)
             ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            &[key.as_str(), value.as_str()],
        )
    })
    .map_err(|error| error.to_string())
}

#[ic_cdk::query]
fn get(key: String) -> Result<Option<String>, String> {
    Db::query(|connection| {
        connection.query_optional_string_with_text(
            "SELECT value FROM kv WHERE key = ?1",
            &key,
        )
    })
    .map_err(|error| error.to_string())
}

For repeated operations in one message, reuse a prepared statement:

Db::query(|connection| {
    let mut statement = connection.prepare("SELECT value FROM kv WHERE key = ?1")?;
    let value = statement.query_optional_string_with_text("alpha")?;
    Ok(value)
})

Typed parameters and row reads are available for SQLite TEXT, INTEGER, REAL, BLOB, and NULL values:

use ic_sqlite_vfs::db::NULL;

Db::update(|connection| {
    let blob = vec![0, 1, 2, 255];
    connection.execute(
        "INSERT INTO records(name, count, score, payload, note)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        &[&"alpha", &42_i64, &3.5_f64, &blob, &NULL],
    )
})?;

let values = Db::query(|connection| {
    connection.query_one(
        "SELECT name, count, score, payload, note FROM records WHERE name = ?1",
        &[&"alpha"],
        |row| {
            Ok((
                row.get::<String>(0)?,
                row.get::<i64>(1)?,
                row.get::<f64>(2)?,
                row.get::<Vec<u8>>(3)?,
                row.get::<Option<String>>(4)?,
            ))
        },
    )
})?;

Db::update exposes savepoints only inside the update closure:

Db::update(|connection| {
    connection.execute("INSERT INTO logs(body) VALUES (?1)", &[&"outer"])?;
    let inner = connection.savepoint(|connection| {
        connection.execute("INSERT INTO logs(body) VALUES (?1)", &[&"inner"])?;
        connection.execute("INSERT INTO missing_table(value) VALUES (?1)", &[&1_i64])
    });
    assert!(inner.is_err());
    Ok(())
})?;

Reference Canister

This repository includes a reference canister behind the canister-api feature.

icp build
icp network start -d
icp deploy

The reference canister exposes:

• kv_put, kv_get, kv_count
• db_meta
• db_integrity_check
• db_checksum
• db_refresh_checksum
• db_refresh_checksum_chunk
• db_export_chunk
• db_begin_import, db_import_chunk, db_finish_import

Admin import/export and integrity methods require the caller to be a controller.

Recommended export sequence:

1. run db_refresh_checksum_chunk(max_bytes) until it returns complete = true
2. read db_meta and record db_size, checksum, and last_tx_id
3. read all chunks with db_export_chunk
4. read db_meta again and confirm last_tx_id did not change

db_refresh_checksum still exists for small databases. Large databases should use the chunked API so checksum verification does not depend on one update message scanning the whole DB image.

Build Flags

The bundled SQLite build uses:

SQLITE_OS_OTHER=1
SQLITE_THREADSAFE=0
SQLITE_OMIT_LOAD_EXTENSION
SQLITE_OMIT_SHARED_CACHE
SQLITE_OMIT_WAL
SQLITE_DEFAULT_MEMSTATUS=0
SQLITE_TEMP_STORE=3

SQLITE_OS_OTHER=1 removes SQLite's default Unix/Windows/OS backends. This crate provides sqlite3_os_init() and registers only the icstable VFS.

Benchmarks

Measured locally on 2026-05-13 with icp local network. The main metric is IC instructions from ic_cdk::api::performance_counter(0).

The benchmark harness lives in benchmarks/kv-canister and can be run with:

scripts/bench-kv-local.sh 1000

The benchmark project uses local gateway port 8001 to avoid clashing with the default icp local network on 8000.

KV workload, 1000 rows:

Workload	ic-sqlite-vfs	wasi2ic + ic-rusqlite	Result
reset + insert	20.64M	149.36M	7.2x fewer instructions
point read	23.36M	44.53M	1.9x fewer instructions
insert/update	22.65M	172.56M	7.6x fewer instructions

Memory after the 1000-row run:

Implementation	Canister memory
ic-sqlite-vfs	3.96 MB
wasi2ic + ic-rusqlite	89.64 MB

Wasm size:

Implementation	Wasm
ic-sqlite-vfs reference canister	1.84 MB
wasi2ic KV benchmark canister	3.00 MB

Wall-clock measurements are included only as a local sanity check because they include icp canister call process startup:

Workload	ic-sqlite-vfs	wasi2ic + ic-rusqlite
reset + insert 1000	0.27s	0.20s
point read 1000	0.45s	0.06s
insert/update 1000	0.31s	0.22s

The instruction gap comes from removing WASI fd emulation and mapping SQLite pager I/O directly to stable memory offsets.

The write workload numbers exclude a full DB checksum scan from the commit path. db_refresh_checksum and db_refresh_checksum_chunk are separate controller verification operations.

Tests

cargo fmt --check
bash scripts/check-no-await.sh
cargo test
cargo test --features canister-api
cargo build --target wasm32-unknown-unknown
cargo build --target wasm32-unknown-unknown --features canister-api
icp build
npm run test:pocketic
cargo package --no-verify --offline
wasm-objdump -x target/wasm32-unknown-unknown/debug/ic_sqlite_vfs.wasm

Current coverage:

• VFS read/write/truncate/filesize behavior
• rollback on SQL error
• read-only query mode
• reusable prepared statements
• chunked export/import with checksum verification
• failed import preserving the existing database
• capacity and sparse write bounds
• failpoints for overlay write, truncate, commit capacity, DB image flush, and superblock publish
• stable write trap, grow failure, SQLite step error, and panic during update
• fuzz-style deterministic operation sequences
• long-running transaction endurance
• PocketIC upgrade persistence and schema migration persistence
• wasm import audit: only ic0.*

Operations

See docs/OPERATIONS.md for transaction rules, import recovery, capacity handling, and integrity checks.

See docs/RELEASE.md for release gates.

See docs/API_STABILITY.md for 0.x compatibility.

See docs/BUILD_SETUP.md for consumer build setup.

Limitations

• WAL is intentionally unsupported.
• mmap and SQLite shared-memory methods are not implemented.
• VACUUM should be treated as admin maintenance, not a normal API path.
• Transactions must not cross await boundaries.
• The stable memory layout should be considered unstable until a 1.0 release.

License

Licensed under either MIT or Apache-2.0.