Question! how does this compare to alternatives? advantages? disadvantages?

[jessekrubin]

Hi @ryanfowler !

This lib looks quite nice!

Just curious if you have any thoughts/opnions/ideas on how this (your lib) compares to tokio-rusqlite and/or deadpool-sqlite?

Thanks!

[ryanfowler]

Thanks! Both of the alternatives you mention are great sqlite libraries 😄

It appears that all of these libraries have roughly the same architecture, where Connections/Clients map to a dedicated thread containing the actual connection to the database (and are based on the awesome rusqlite).

Some differences that I notice are:

• tokio-rusqlite is very similar to the Client implementation in this library, but it doesn't contain a connection pool.
• deadpool-sqlite takes advantage of the the deadpool ecosystem for a connection pool, I just personally find the ergonomics of it not as nice as the alternatives
• sqlx is another library that provides similar functionality for sqlite, but does not use rusqlite under the hood. It may be worth checking out as well though

This library contains individual connections to sqlite (Clients) that can be send/shared safely between threads, where all logic is run on a dedicated thread containing the sqlite connection. It also includes a lightweight connection pool (Pool) to take advantage of multiple CPUs if needed. One additional feature is that this library exposes blocking APIs if you need to also interact with your database from a non-async context.

Overall, I don't think you can go wrong using any of the libraries mentioned!

[ryanfowler]

Oh and I forgot to mention that this library should support all async runtimes, and is actively tested against both tokio and async-std 😄

[jessekrubin]

Hi @ryanfowler!

Thanks for the great write-up! Super helpful.

sqlx looks pretty nice in some ways but also looks like overkill to me (for what I am doing -- it is all sqlite based and sqlx seems much larger than rusqlite based libs).

I do have another question (which might be dumb). When would using your sqlite Pool be a good? and when would it be bad? is there overhead?

My understanding of sqlite is that it is basically always synchronous which makes these async+sqlite things feel murky to me.

[ryanfowler]

Good question! The Pool creates a number of Clients (by default it uses the number of CPUs), and round-robins between them all. There is a bit of overhead for this because each Client has a dedicated thread with the sqlite connection in it, but this overhead is likely negligible in your application.

Sqlite only really supports a single writer at a time, but it does support multiple concurrent readers. I've seen a performance increase when I've got many concurrent reads that could utilize all the threads. But if you're not too worried about squeezing out all of the performance possible, a single Client is likely good enough and can handle pretty significant throughput. Also, if you're primarily writing to the database, using a single Client will likely be more efficient.

Hopefully that makes sense!

[jessekrubin]

Yet again @ryanfowler yields a great response!

Thanks so much for the explanation! For reference (if you are interested) this is all aimed at serving tiles from mbtiles/geopackage (and other geo-spatial datasets) sqlite databases in this project/repo ( https://github.com/jessekrubin/utiles ).

Given what you said I wonder if a pool is right for me; the current geo-db server I have (a fastify/nodejs server) often will have many open connections (hundreds and even sometimes thousands 😰 ). Do you think using a pool could/would cause problems in such a scenario? The current node version of the server lru-cache-and-drop connections based on how recently datasets are read, but I am new to rust and that sounds like it would be tougher in rust.

Can a pool be downgraded to a single client? and (visa versa) can a client be upgraded to a pool?

[ryanfowler]

Nice! I think a Pool should work well for that scenario, as it opens up a set number of sqlite connections and utilizes those for all incoming requests. As of now you can't downgrade or upgrade between a Pool and Client, but I don't think a Pool would cause any problems for your use case 🙂

[jessekrubin]

I will (when I find the time) give it a whirl! Thanks so much for your time, (excellent) explanations, and thoughts!

I will report back!

[ryanfowler]

Sounds good! If you run into any issues, just let me know 🙂

[jessekrubin]

@ryanfowler just thought you might be interested to know...

With my geo-data sqlite server that opens a bazillion connections, the performance of the Client absolutely annihilates the pool (if only because there are so many open dbs).

[jessekrubin]

food for thought! your api is really really really fantastic to work with btw.