We have a system with a persistent storage (the stable memory) that is unstructured, flat and kinda slow. We need to store all relevant data in this storage. And we want to be able to make sense of this data even after changing the code of our canister, or possibly even changing the implementation language.
Problem that developers have on the IC?
1、IC only provides unstructured storage, which is not practical for applications
2、The user wants to index the data, but it is difficult to directly store it on the IC
3、When the canister is upgraded, the user should not be responsible for data loss caused by operation errors
4、After changing the language implemented by the canister, the new language may not understand the original data.
5、Difficult to implement scenarios where multiple applications use the same data.
How to solve？
Luckily, sqlite is designed to be cross-platform and allows us to provide our own storage backend code, using the VFS feature 8.
1、sqlite solves the problem of mapping structured data onto a flat disk, in a time-tested way.
2、sqlite is well known to many developers, as it is the most commonly used data base out there. Even mobile phone apps tend to use sqlite for their data storage!
3、The problem of schema migration, i.e. what to do if your new canister version stores its data differently, is well-known and has plenty of existing solutions to pick from.
4、sqlite is used to work with a slow, block-based backend, so backing it with stable memory is sensible, and will likely make good use of the canister main memory as a cache.
5、sqlite is designed to support large files (>4GB), so you can use large stable memory easily.
6、sqlite guarantees backwards compatibility, so no worry about losing your data because your data serialization library has changes.
7、The sqlite file format can be analyzed by dedicated tools, so if the IC (or your canister) provides read access to stable memory, this can be used for debugging, backup and archival.
8、sqlite is available in many programming languages. By storing your data this way, you can in the future switch from one Canister implementation language to another without losing your data.
9、There are a lot of tools built on sqlite on the market, such as ORM, and you can use them on IC.
More details on ICSQLite
ICSQLite is a SQLite based database on Internet Computer, which provides SDK for developers.
By introducing SQLite into the IC developer ecosystem, the storage and retrieval of structured data are greatly simplified.
Developers can embed it in the Dapp canister, or provide it as a separate database for multiple canisters to call remotely.
ICSQLite mainly includes three modules: rusqlite, sqlite-vfs and ic-sqlite
1、rusqlite(GitHub - froghub-io/rusqlite at wasm32-unknown-unknown)
Added support for wasm32-unknown-unknown feature based on rusqlite library，for packaging in IC environment
2、sqlite-vfs(GitHub - froghub-io/sqlite-vfs: sqlite extend vfs)
Implement VFS (Virtual File System) based on the memory KV data structure
3、ic-sqlite(GitHub - froghub-io/ic-sqlite: Using sqlite on an internet computer)
Provide use case projects of ic-sqlite SDK, including creating tables, inserting data, querying data, deleting data, etc.
1、It supports horizontal expansion and is not limited to use in a single canister, so that its data storage capacity can exceed the limit of a single canister
2、SDK development in various languages, such as Mokoto
Thank you for using ICSQLite. You can use canister to deploy ICSQLite independently to provide structured storage for your canister; or refer to the “ic-sqlite” project to embed your own canister to provide storage. As long as you are familiar enough with SQL, it’s easy to get started.
I know very well that it is not perfect at present. I hope you can provide him with trial feedback, bug reports and issues.
I also hope that everyone can discuss the future of ICSQLite.