[einnjo]

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

[2] http://litereplica.io

[3] http://litesync.io

[4] https://bedrockdb.com

[toddgruben]

You can add this one to the list https://litestream.io/

[benbjohnson]

Litestream 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]

Hey, 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]

Hi 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/

[2]: http://thesecretlivesofdata.com/raft/

[gunnarmorling]

Thanks 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]

Yeah, 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

[2]: https://join.slack.com/t/litestream/shared_invite/zt-n0j4s3c...

[avinassh]

How does Litstream actually works? Do you read from WAL and then sync with S3?

[benbjohnson]

Yes, it reads off the WAL then compresses the frames using LZ4 and then uploads to S3. The SQLite WAL acts as a circular buffer so Litestream takes over the checkpointing process to control when it rolls over so it can recreate that buffer as separate files. There's additional information on the web site: https://litestream.io/how-it-works/

[tyingq]

More 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/

[deknos]

also https://github.com/rqlite/rqlite