4 ms·
I recently used SQLite in my side project [1]. Here's what I learned from using it: * Shaving the overhead of network calls for queries sped up my site signifi
by einnjo 6y ago
I recently used SQLite in my side project [1]. Here's what I learned from using it:
* Shaving the overhead of network calls for queries sped up my site significantly.
* Most CI/CD providers include SQLite in their base linux images and setup in local envs is easy as well. Running tests against the actual database is simple.
* Replication is not available out of the box. To share a database with multiple instances of your app you will have to use a shared storage volume or some of the available solutions at [2][3][4], but they each come with their caveats.
[1] https://www.tendielist.com https://www.tendielist.com
[2] http://litereplica.io http://litereplica.io
[3] http://litesync.io http://litesync.io
[4] https://bedrockdb.com https://bedrockdb.com
- tyingq 6y agoMore than replication, but dqlite might be worth a look as well. It is supposedly stable now that it was rewritten in C. https://dqlite.io/ https://dqlite.io/
- deknos 6y agoalso https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- toddgruben 6y agoYou can add this one to the list https://litestream.io/ https://litestream.io/
- benbjohnson 6y agoLitestream author here. Let me know if you have any questions. It's built to run as a separate process and to be super easy to get up and running. We have a GitHub discussion board and an active Slack group as well if you need any help.
- gunnarmorling 6y agoHey, lead of Debezium here, a change data capture tool for a number of databases (not SQLite, though). Out of curiousity, how are you implementing change ingestion, is there some interface/API in SQLite which lets you do this? Or are you manually parsing its log files?
- benbjohnson 6y agoHi Gunnar, good to meet you. Litestream works by reading off the SQLite WAL file which acts as a circular buffer. It takes over the checkpointing process to control when the buffer rolls over so it doesn't miss any frames. Those frames get copied over and each buffer is recreated as a sequential set of WAL files that can be replayed to reconstruct the state of the database at a given point-in-time. It sounds like Litestream differs from Debezium in that it provides physical replication rather than logical row changes. However, I've been toying with the idea of determining row-level changes from the WAL frames by using ptrmap pages to traverse up the b-tree and determine the owner table. There's a bunch of stuff on the roadmap before that like live read replication though. There's some additional info on the site about how Litestream works[1] and I'm planning on making a video similar to this Raft visualization[2] I did a while back. [1]: https://litestream.io/how-it-works/ https://litestream.io/how-it-works/ [2]: http://thesecretlivesofdata.com/raft/ http://thesecretlivesofdata.com/raft/
- gunnarmorling 6y agoThanks for sharing those insights. Indeed Debezium is based on logical replication. I'll definitely keep an eye on Litestream, perhaps there may be some potential for collaboration at some point? SQLite hasn't come up really in our community so far, but personally I find it very interesting.
- benbjohnson 6y agoYeah, for sure. I'm up for some collaboration where it makes sense. I can understand how SQLite probably wouldn't come up so far. If someone is running CDC on Kakfa (Debezium) then they're probably running a client/server database instead of an embedded one. Hit me up on Twitter[1] if you have any questions or we have a pretty friendly, active Slack[2] too. [1]: https://twitter.com/benbjohnson https://twitter.com/benbjohnson [2]: https://join.slack.com/t/litestream/shared_invite/zt-n0j4s3ci-lx1JziR3bV6L2NMF723H3Q https://join.slack.com/t/litestream/shared_invite/zt-n0j4s3c...