Hello!
I’ve been working on a new convenience package available to easily create a Rust canister with the SQLite support. Check out the ic-rusqlite repository.
Simple video demonstration is available here.
Documentation is available here.
Some ready canister examples:
Very cool! Could you explain more about the process of porting SQLite to rust? Are there any gotchas or SQLite functionality that doesn’t work within canisters? My understanding is that many have wanted this for a long time, but that it would be more challenging than it sounds. Did you bump up against any blockers and how did you resolve then?
Honestly, this looks fantastic and will be super useful for onboarding Web2 developers! I haven’t tried it out yet though.
How is data persistence during canister upgrades handled?
cc @marc0olo, this project may be of interest to you.
I did not port SQLite to Rust myself, the convenience package ic-rusqlite is based on the popular Rust package rusqlite, which internally uses API wrapper for C and compiles the original SQLite source to a static library using WASI SDK. The rusqlite supports compilation to a WASI target, which I used with the wasi2ic workflow and got it working. With the wasi2ic workflow you can already make SQLite work, see this demo. The ic-rusqlite does all the DB initialization for you, also has reasonable default PRAGMA settings, and is made maximally convenient for the end-user, it also includes a precompiled version of the SQLite, so you don’t have to explicitly install WASI-SDK to make it run.
The SQLite is configured to always synchronously write into stable memory, once your transaction is complete, the data will be stored and you can upgrade at any moment without issues. It is also nice to explicitly disconnect the database before launching the upgrade. Other than that, you don’t need to do anything special, after upgrade the database connection opens automatically and can work as before.
The SQLite is by design without server-client connection, it is just a library what works in a single-user mode with a single file or even a single piece of memory (on embedded devices), which works nicely in the canister context.
Gotchas:
Other than that, we were able to run complex queries. I did performance comparison with the BTreeMap. My main conclusion so far: BTreeMap works faster on individual get and put calls, SQLite scales better: if you need to insert a 1000 elements into a table in one go, this will faster on the SQLite than on BTreeMap.
SQL is pretty good for prototyping. SQLite uses BTree under the hood. MySQL uses B+Tree. Every table index is a BTree map. Add too many - things slow down too much, add too few - same thing. You can do everything without SQL, but it will require a lot of code. With the SQL language, adding a new field or a new index takes one command. You will need to write a lot of code to do that with custom data structures. At the same time, if you don’t know how SQL engines work under the hood, very likely you won’t get the most out of it (Ask ChatGPT). But really, if you have a web2 background and you are familiar with SQL, you can make an app that’s good enough to serve a few thousand users with SQLite in a canister. Once there, you can figure out something else.
this project is absolutely on our radar, thanks! also note that one of the examples is provided by @kristofer 
Yes, the wasi support created by @sgaflv is a really exciting development, opening the door to run any third party tool that can be compiled to WASI.
I contributed an open source library to this effort. To help with database schema migrations:
I created a long-ish video on how to build a full-stack ICP app, powered by SQLite. This is a nose to tail walkthrough, we look at database migrations, automatic input validation, DTO objects, performant frontend queries using Tanstack Query and lots more.
