Ic-sqlite-vfs: SQLite without WASI dependencies

Developers Rust
1

SQLite is a killer use case for the Internet Computer.

ic-rusqlite is an excellent library, but its current design assumes WASI / wasi2ic, which makes it harder to integrate directly into the wasm32-unknown-unknown canisters commonly used on the IC.

When using WASI, SQLite I/O goes through a generic compatibility path:

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

SQLite already has its own I/O abstraction: sqlite3_vfs.
So instead of going through a generic WASI file-compatibility layer, I implemented a VFS that maps SQLite page I/O directly to fixed offsets in stable memory.

That project is ic-sqlite-vfs.

I also built benchmarks for comparison.

Workload ic-sqlite-vfs wasi2ic + ic-rusqlite Result
reset + insert, 1000 rows 10.80M 83.87M 7.8x fewer instructions
insert only into empty table, 1000 rows 10.29M 83.27M 8.1x fewer instructions
insert only into empty table, 5000 rows 60.95M 426.93M 7.0x fewer instructions
append insert, 5000 existing + 1000 new 13.55M 86.21M 6.4x fewer instructions
insert/update upsert, 1000 rows 13.35M 86.53M 6.5x fewer instructions
update only by primary key, 1000 rows 16.76M 81.20M 4.8x fewer instructions
update only by primary key, 5000 rows 91.05M 413.12M 4.5x fewer instructions
point read, 1 key 0.048M 0.014M wasi2ic lower
point read, 10 keys 0.135M 0.109M wasi2ic lower
point read, 100 keys 1.01M 1.05M ic-sqlite-vfs lower
point read, 1000 keys 10.05M 10.69M ic-sqlite-vfs lower
bulk read ordered scan, 100 rows 0.236M 0.233M wasi2ic lower
bulk read ordered scan, 1000 rows 1.37M 1.66M ic-sqlite-vfs lower
bulk read ordered scan, 5000 rows 6.46M 7.99M ic-sqlite-vfs lower
WHERE key IN (...), 100 keys 1.31M 1.65M ic-sqlite-vfs lower
WHERE key IN (...), 1000 keys 14.35M 18.41M ic-sqlite-vfs lower

crate: crates.io: Rust Package Registry
repository: GitHub - humandebri/ic-sqlite-vfs: SQLite VFS backed directly by Internet Computer stable memory · GitHub

2

bump so you’ll get more visibility

5

Miss me that much? I got other things to do now.

7

Very cool, @hude. I especially like that:

The crate does not own the canister’s raw stable memory

and

ic-sqlite-vfs does not reserve a MemoryId. The consuming canister chooses one MemoryId for SQLite and must keep it stable forever

which makes this composable with other libs that need stable memory. Nice!

Although, how do you do that given you don’t depend on stable structures?

Raw stable memory is managed by a MemoryManager<DefaultMemoryImpl> with the same stable layout as the ic-stable-structures 0.7 MemoryManager

Does that mean you basically copied the logic from SS instead of depending on the crate?

13

ic-sqlite-vfs does not depend on ic-stable-structures. It carries a small MemoryManager-compatible implementation instead.

The implementation keeps the same stable layout as the ic-stable-structures 0.7 MemoryManager, but exposes only the narrower API needed by the SQLite VFS, including the stable64 backend and support for reading directly into the buffer SQLite passes to xRead.

15

IcyDB: IC-native database safety, not SQLite-in-every-canister

This is very cool work. Directly mapping SQLite’s VFS onto stable memory is obviously a much cleaner path than routing SQLite through a WASI compatibility layer, and the instruction reductions on write-heavy workloads are impressive.

I wanted to add some context from another direction we have been working on with IcyDB.

IcyDB is not trying to embed SQLite into canisters. The goal is different:

Build an IC-native database layer with typed schemas, stable-memory ownership, indexes, SQL/fluent query ergonomics, fail-closed recovery, and canister-safe query admission.

SQLite is a fantastic embedded database. It has decades of work behind it, mature SQL semantics, constraints, planner behavior, tooling, import/export workflows, and a huge ecosystem. If the goal is “run SQLite in a canister,” then a direct stable-memory VFS is absolutely the right kind of architecture.

But there is another problem space: canister-native application databases where the storage engine itself understands the IC execution model.

That is where IcyDB is aimed.

The main difference

With SQLite-on-IC, the architecture is roughly:

Application
  -> SQLite SQL engine
  -> SQLite pager/VFS
  -> IC stable memory mapping

That gives you mature SQLite behavior, but the canister still has to be careful about public query surfaces, instruction limits, unbounded scans, large result sets, materialized sorts, upgrades, stable-memory ownership, and import/export policy.

With IcyDB, the architecture is more like:

Application schema
  -> IcyDB typed/fluent/SQL query layer
  -> IcyDB planner/admission layer
  -> IC-native row/index/journal/recovery model
  -> stable memory

So instead of adapting a general-purpose embedded database to the IC, IcyDB starts from IC constraints:

  • stable memory is the persistence substrate;

  • updates must respect IC message execution boundaries;

  • recovery must be explicit and fail-closed;

  • indexes have readiness state;

  • public queries must be bounded;

  • generated canister endpoints should not accidentally expose admin-grade query power.

Where SQLite still wins

SQLite still wins on general-purpose database maturity:

  • full SQL semantics;

  • joins;

  • complex ad-hoc queries;

  • mature relational constraints;

  • migrations;

  • ecosystem tooling;

  • existing SQLite-shaped data.

IcyDB is not trying to beat SQLite at being SQLite.

In fact, some of those things are deliberate non-goals for IcyDB. For example, I do not want joins in public canister APIs. They are too easy to turn into expensive or surprising execution paths unless very tightly controlled.

Where an IC-native database can win

An IC-native database can win on different axes:

1. Canister-safe public queries

A normal SQL database will happily let you write a query that is valid but operationally dangerous on the IC:

SELECT * FROM events ORDER BY created_at;

or:

SELECT COUNT(*) FROM logs;

or:

SELECT * FROM users WHERE bio LIKE '%abc%';

Those might be legal SQL, but they can imply full scans, materialized sorts, unbounded reads, or large responses.

IcyDB is adding a read admission layer so that public canister query surfaces can be explicitly gated.

The design we worked on today defines query lanes:

  • PublicRead

  • AdminAdHoc

  • DiagnosticExplain

  • DevTest

The important idea is that planning and admission are separate:

Planning asks: what would IcyDB do?
Admission asks: is that selected plan allowed for this surface?

So a query can be valid, and even useful for an admin, but still rejected for a public endpoint.

2. EXPLAIN that tells you whether a query is canister-safe

IcyDB already has SQL/fluent query planning and EXPLAIN output. The next step is to make EXPLAIN report admission as well as planning.

For example, instead of only saying:

selected access = FullScan

it should also say:

admission lane = PublicRead
decision = rejected
reason = UnboundedFullScanRejected

Or:

admission lane = PublicRead
decision = rejected
reason = PublicQueryRequiresLimit

The goal is not only to reject unsafe public queries, but to give developers precise diagnostics so they know how to fix them.

3. Bounded execution by construction

For public reads, IcyDB should be able to require things like:

  • positive LIMIT;

  • max returned rows;

  • max scanned rows where a bound can be proven or enforced;

  • max encoded response bytes;

  • index-required access;

  • full-scan rejection by default;

  • materialized sort rejection unless bounded;

  • grouped-query group and byte limits;

  • no public introspection unless explicitly enabled.

This is different from saying “the query is syntactically valid SQL.” It asks whether the query is safe for the IC surface that is exposing it.

4. Recovery and durability as native concepts

IcyDB’s storage work is built around native recovery concepts: commit markers, journals, fold watermarks, index readiness, accepted schema snapshots, fail-closed persisted decoding, and guarded recovery.

That gives us a vocabulary for durability that is specific to the IC.

If recovery is incomplete, reads and writes should fail closed. If an index is being rebuilt, public query execution should not pretend it is ready. If persisted bytes are corrupt or incompatible, the system should not silently continue.

That is a different kind of safety from “SQLite transaction succeeded.” Both are valuable, but they are not the same design.

5. Typed schema and generated canister surfaces

IcyDB is also trying to make database use feel native to Rust/IC canister development:

  • typed schemas;

  • generated endpoints;

  • fluent APIs;

  • SQL where useful;

  • stable diagnostic codes;

  • compound indexes;

  • unique index integrity;

  • relation validation;

  • stable-memory ownership policy.

The idea is that a canister developer should not need to become a SQLite/VFS/stable-memory expert just to safely expose a small indexed query.

What we worked on today: read query gating

The current design line is about read query gating.

The problem statement is simple:

IcyDB has stronger policy discipline for writes than for reads. Public read endpoints need the same kind of explicit admission boundary.

Writes already have policy concepts around bounded shapes, primary-key proofs, LIMIT, staged row bounds, and response-byte bounds for RETURNING.

Reads now need their equivalent.

The proposed design adds:

  • explicit read lanes;

  • public read policies;

  • admin/diagnostic separation;

  • stable rejection diagnostics;

  • EXPLAIN admission output;

  • SQL/fluent parity tests;

  • generated endpoint policy wiring;

  • response-size policy;

  • materialization policy;

  • grouped-query budgets.

The conservative default should be:

Public reads are rejected unless they are proven or mechanically enforced bounded.

That means estimates can inform EXPLAIN, but they should not be enough to admit a public query. Public execution should require hard bounds, conservative upper bounds, or runtime-enforced caps.

How this compares to SQLite

SQLite is stronger as a general embedded database.

IcyDB is trying to be stronger as a canister-native database layer.

A rough comparison would be:

Area SQLite IcyDB direction
General SQL maturity Excellent Intentionally bounded subset
Joins Excellent Non-goal
Existing ecosystem Excellent Early
IC-native recovery vocabulary External/adapted Native
Public canister query safety App must enforce Built into admission
Stable-memory ownership VFS/application contract Schema/runtime contract
EXPLAIN for IC admission Not native First-class goal
Generated canister surfaces Not native First-class goal
Typed Rust schema integration External Native goal

So I do not see this as “SQLite versus IcyDB” in a winner-takes-all sense.

I see them as different tools:

  • If you want SQLite in a canister, ic-sqlite-vfs looks like a strong direction.

  • If you want an IC-native database framework with bounded public query surfaces and canister-aware recovery semantics, that is where IcyDB is heading.

The bigger point

The IC needs better database options.

Some canisters will benefit from SQLite compatibility. Some will benefit from native stable-structure patterns. Some will benefit from higher-level typed database frameworks.

The important thing is not to pretend that one abstraction fits every canister.

For IcyDB, the design principle is:

Do not chase full SQLite compatibility. Build the safest IC-native database layer possible, and borrow the parts of database discipline that make sense: constraints, indexes, diagnostics, migrations, import/export tooling, and EXPLAIN.

That is the direction we are taking.