IC SQL Lite Real Users Report

Developers
1

We are evaluating the only open source database we have seen ported to the IC, that would be SQLite.

Is anyone using it, has anyone been able to see how it works on production?

FYI The folks at FrogHubIo, a Chinese blockchain firm released it without much fanfare here on the forum, since then I haven’t heard much. Their code is here:

I did notice @lastmjs company seems to be using it, at least that is what Github says for their TypeScript compatible language. Jordan can you share an experience report? Have you used it for a real life application?

If anyone else has built anything on IC SQLite we would appreciate knowing more about it.

Thanks!

P.S. We are aware of CanDB but would prefer a simple MIT License or equivalent, this is CanDB’s license: https://github.com/ORIGYN-SA/CanDB/blob/beta/license.md

1 Like
2

@infu did some experiments with it as well.

@FroghubMan Do you know of any other users?

1 Like
3

We just built a demo for Azle’s plugin system, @infu is the one using it (I believe).

1 Like
4

@josephgranata ,
Out of curiosity, what type and amount of data will you be storing?

Would it fit in a single canister?

5

Thanks Jordan, so no real stress test in production.

6

Yes, for our use case the data could fit in a single canister, and that is why we are exploring using Rust and Stable Structures, but when you do that you have no query language, and no easy way to organize data, it’s all traversing a data structure manually.

So it would still be advantageous to have a real database instead of a data structure :wink:

1 Like
7

It works well.
Here is a demo I made with Sqlite and Azle - which uses a Rust plugin and Froghubs library
https://github.com/infu/internetbase-sql-demo
The goal was to see how well it will perform in a real-world scenario.
Everything on that site works until you insert 300,000 rows into the ‘services’ table and run a query using three indexes. If you work with one index your table can probably use the whole stable memory. When you add two, you are going down to 1mil and 3 indexes 300k. The reason is SQLite has to go around the stable memory and fetch a lot of pages before it finds your rows and you run out of instructions.
Dfinity is working on a stable memory cache, which will put the most used pages in the heap and that will probably cache the indexes and improve performance.
You can also change the library to store indexes in the heap, won’t be easy, but it’s doable in theory.

3 Likes
8

We’ve also made RxMoDB https://forum.dfinity.org/t/rxmodb-database-v-0-1-0/21076
that can carry out some SQL-like queries

Essentially SQL indexes are Btrees. For a lot of use cases, you won’t need all the SQL features and you will prefer having greater control. RxMoDb is a single canister and currently just takes care of maintaining your indexes. It worked well with 10mil documents/rows and 2 indexes. It’s safe to say if you are growing a new app you won’t run into the limits (unless you are making a free-to-play game or using an existing userbase). IC is getting better and faster every month and it will probably cover you.

3 Likes
9

Thanks for the news, and for the link.

It seems then that we have a small database.

I will test it for sure!

10

This is very interesting, perhaps a reason for us to consider Motoko.

Or perhaps we will consider porting it to Rust… we shall see.

Thanks again @infu, one thing is clear, the solution to the database problem is near, and it’s partially here.

I would still prefer to just run a database, and not worry about intercanister calls, and message ingress limits, and the like, but I must admit, it’s indeed getting better.

1 Like