5 ms·

Wild stuff - Fork of SQLite with new features https://github.com/libsql/libsql https://github.com/libsql/libsql - And you can embed it in your application and

by maxmcd 4y ago

Wild stuff - Fork of SQLite with new features https://github.com/libsql/libsql https://github.com/libsql/libsql - And you can embed it in your application and it will really talk to your SQLite/libsql database over the network (subject of this blogpost): https://github.com/libsql/sqld https://github.com/libsql/sqld - Oh, and if you're wondering about backup to S3, they have that too: https://github.com/libsql/bottomless https://github.com/libsql/bottomless - Uh, sqld can integrated with this https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite, so now your SQLite is backed by FoundationDB!? -- - Meanwhile Litestream exists https://github.com/benbjohnson/litestream/ https://github.com/benbjohnson/litestream/ - Ben is developing https://github.com/superfly/litefs https://github.com/superfly/litefs at Fly so that you can talk to your SQLite through a FUSE filesystem. (and work has stopped on the streaming replication effort https://github.com/benbjohnson/litestream/issues/8 https://github.com/benbjohnson/litestream/issues/8) - And, of course, SQLite has announced a new backend that hopes to support concurrent writes and streaming replication: https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html What a time for SQLite Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons.

v3ss0n 4y ago
Cool but NIH syndrome.. I don't see any point here. SQLite was doing one thing and doing it well. The rest is well handled by pg
maxmcd 4y ago
Questions for libsql: - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to Litestream? - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?
- psarna 4y ago
  First of all, let me start by reiterating the first sentence from the bottomless repo - it's work in heavy progress (and we'll move it under the sqld/ repo soon, to keep everything in one place). Now, answers: > - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? It's a valid concern, but what always happens on boot is starting a new generation (a generation is basically a snapshot of the main file + its continuously replicated WAL), distinguished by a uuid v7, the timestamped one. So even if a collision happens, it would be recoverable - e.g. one of the stray generations should be deleted. > - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to litestream? The only con I see is that a bug in the extension could interfere with the database. As for pros: way less maintenance work, because everything is already embedded, we're also hooked into the database via a virtual WAL interface (libSQL-only), so we have full control over when to replicate, without having to observe the .wal file and reason about it from a separate process. > - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about? There are going to be multiple flavors of sqld, but the rough idea would be to trust the users to only launch a single primary. In the current state of the code, replicas contact the primary in order to register themselves, so the model is centralized. Once we build something on top of a consensus algorithm, leader election will be pushed to the algorithm itself.
  - maxmcd 4y ago
    Very cool, thank you for the insights
aaviator42 4y ago
I wrote a small PHP library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX https://github.com/aaviator42/StorX I've been dogfooding for a while by using it in my side projects. And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API https://github.com/aaviator42/StorX-API
- vdm 4y ago
  ICYMI, similar concept for python https://dataset.readthedocs.io/ https://dataset.readthedocs.io/
houqp 4y ago
bottomless looks really nice, thanks for sharing!
stuaxo 4y ago
Related: michaelc @ uktrade did some intresting work streaming sqlite with python in this repo - https://github.com/uktrade/stream-sqlite https://github.com/uktrade/stream-sqlite
eduction 4y ago
> Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons. I’m glad you’re into it but none of that sounds fun or appealing to me. I have no issue with SQLite or any of these projects, but my default for stuff that needs eg concurrent writes or streaming replication is Postgres which was designed from the ground up to be this sort of database and had those features built into the mainline release for some time. I trust the default of Postgres way more than I trust myself to pick some new fork of SQLite via what I learn in blog posts and have it be well fit to the problem and reliable and adaptable. I get that SQLite can be easier to set up and faster on reads but I feel like we’ve been down this road before trading away Postgres for those sorts of seeming gains - I’m getting MongoDB flashbacks. Don’t get me wrong, I’d choose SQLite for a local app storage solution in a heartbeat but I’m wary of overstretching it.

by maxmcd 4y ago

v3ss0n 4y ago

Cool but NIH syndrome.. I don't see any point here. SQLite was doing one thing and doing it well. The rest is well handled by pg

maxmcd 4y ago

Questions for libsql: - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to Litestream? - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?

psarna 4y ago
First of all, let me start by reiterating the first sentence from the bottomless repo - it's work in heavy progress (and we'll move it under the sqld/ repo soon, to keep everything in one place). Now, answers: > - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? It's a valid concern, but what always happens on boot is starting a new generation (a generation is basically a snapshot of the main file + its continuously replicated WAL), distinguished by a uuid v7, the timestamped one. So even if a collision happens, it would be recoverable - e.g. one of the stray generations should be deleted. > - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to litestream? The only con I see is that a bug in the extension could interfere with the database. As for pros: way less maintenance work, because everything is already embedded, we're also hooked into the database via a virtual WAL interface (libSQL-only), so we have full control over when to replicate, without having to observe the .wal file and reason about it from a separate process. > - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about? There are going to be multiple flavors of sqld, but the rough idea would be to trust the users to only launch a single primary. In the current state of the code, replicas contact the primary in order to register themselves, so the model is centralized. Once we build something on top of a consensus algorithm, leader election will be pushed to the algorithm itself.
- maxmcd 4y ago
  Very cool, thank you for the insights

aaviator42 4y ago

I wrote a small PHP library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX https://github.com/aaviator42/StorX I've been dogfooding for a while by using it in my side projects. And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API https://github.com/aaviator42/StorX-API

vdm 4y ago
ICYMI, similar concept for python https://dataset.readthedocs.io/ https://dataset.readthedocs.io/

houqp 4y ago

bottomless looks really nice, thanks for sharing!

stuaxo 4y ago

Related: michaelc @ uktrade did some intresting work streaming sqlite with python in this repo - https://github.com/uktrade/stream-sqlite https://github.com/uktrade/stream-sqlite

eduction 4y ago

> Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons. I’m glad you’re into it but none of that sounds fun or appealing to me. I have no issue with SQLite or any of these projects, but my default for stuff that needs eg concurrent writes or streaming replication is Postgres which was designed from the ground up to be this sort of database and had those features built into the mainline release for some time. I trust the default of Postgres way more than I trust myself to pick some new fork of SQLite via what I learn in blog posts and have it be well fit to the problem and reliable and adaptable. I get that SQLite can be easier to set up and faster on reads but I feel like we’ve been down this road before trading away Postgres for those sorts of seeming gains - I’m getting MongoDB flashbacks. Don’t get me wrong, I’d choose SQLite for a local app storage solution in a heartbeat but I’m wary of overstretching it.