Having used D1 in production - it's not very reliable. There was a comment on HN from one of D1 devs [1] saying that D1 is a sql wrapper on top of durable objects that mimicks sqlite distributed like nature.

I'll let you in on a sort of dirty secret: It's almost always better to use Durable Objects storage, rather than D1. Even if you only want a single global database, it's better to implement that as a singleton Durable Object, than by using D1. Because that's all D1 itself actually is: a singleton Durable Object that exposes an API to its SQLite database. It's just a wrapper.

[1] https://smackernews.com/item/48611834 HN

Its funny, when I was reading part time parliament paper (paxos) and while learning zig and odin language created a pure paxos library and then the first application for that library was to build a multi-master sqlite. The two approaches I took are very different in case of zig I build zaxonlite (https://github.com/insanai/zaxonlite) and have a benchmark on it that includes rqlite. It uses WAL replication as strategy. The second attempt I tried in odin is sqlodin (https://github.com/insanai/sqlodin), in this the system does not use WAL but run sql query on each node, as result subset of sql query in sqlite are not supported.

As someone who loved sqlite and the simplicity, the truth is that with agents I am finding it is extremely easy to manage postgres and it is realyl nice having all the SQL features of postgres -- sqlite is quite limiting in a lot of SQL. So if you are considering remote sqlite and all that, feels a better time than ever to just use a database server. Of course, sqlite is amazing for local tools etc.

I feel like there should be a comparison table listing all of these engines and how they compare on their external dependencies, whether a single leader/any node can write, their shape (embeddable library vs server), etc.

IMHO support for transactions is a pretty important dividing line. Some engines support full SQLite transactions while others - like rqlite - don't officially support BEGIN/COMMIT [0], which matters if you rely on multi-statement transactions.

A friend/colleague has been building a pure-Python client for dqlite [1] (DB-API and SQLAlchemy), which may interest folks here. dqlite has full support for transactions, and like rqlite doesn't have external dependencies (ie no S3/Consul).

No knock on rqlite or any other engine - dqlite has warts too [2].

[0] https://rqlite.io/docs/faq/#does-rqlite-support-transactions

[1] https://github.com/letsdiscodev/python-dqlite-dbapi

[2] https://github.com/canonical/dqlite/issues/882 opened by my friend, https://github.com/canonical/dqlite/issues/908 , https://github.com/canonical/dqlite/issues/912 opened by me

The comparisons between various projects, including between rqlite and Litestream, were interesting. (rqlite uses a consensus algorithm between nodes to keep them synced and allow changing the primary node, while Litestream just syncs itself to S3 so another node could take over with the data from there.)

I fully expected the article to get into D1 shortly and mention how that's about the same relationship between Cloudflare Durable Objects (which D1 is based on) and celld, an open source reimplementation of Cloudflare Durable Objects which writes to S3. But then the article treats D1 with a very sudden snarky disinterest, and then the final section feels very awkwardly defensive of rqlite and defeatist.

If you don't get any joy out of writing about your personal experiences in your own voice, why bother writing about them at all?

Is there a project that does all of these together?

- embeddable like sqlite

- clustered like dqlite

- has a standalone node like rqlite

- can be backed up to S3 like litesteeam

- optionally AP like cr-sqlite alongside CP

I have never examined this in any detail before. It looks far easier than the mass majority of clustered database architectures.

https://rqlite.io/

Was a fun read until the end where the author made damn sure that history records who drew the blueprint.

I maintain one of the client libraries for rqlite - I’ve found it an excellent concept really well implemented. One of my favourite bits is it gives you back json making Dapper-like mapping super easy in .NET and I suspect other languages.

I've been looking for a reason to use rqlite, but so far sqlite + litestream have been safe enough (some dataloss acceptable in extreme cases)

My understanding is that rqlite gives you "stored in more than place" guarantees, which is nice if you need it. Much slower than a 1 node db though.

We use rqlite in production, it's the database we chose for (the control plane of) an open source observability solution we're building (morio.it).

Just came here to say two things:

- rqlite is amazing and warmly recommend - Philip is an exceptionally dedicated, kind, and helpful maintainer

5 stars, no notes. Thank you Philip, I'm eternally grateful for all your great work.

I know that latest rails suggests SQLite as one of the production options. I don’t get how it’d work with concurrent access though. Time to look into it.

So, has this been Jepsen-tested yet?

Because if it hasn't, it's likely there are problems lurking.

There is also hctree from sqlite.

Very timely for me. I run dqlite in a small cluster and I'm looking to move off it to something more robust. What's recommended in this regime?

Really disappointing to see that someone can't be bothered to tell the story of their own project in their own words.

Like the idea of distributed SQLite, but do we have benchmark? What use cases it has advantage?

I'd love to see a distributed sqlite solution using something like OmniPaxos[1] being used for consensus instead of raft

[1]: https://github.com/haraldng/omnipaxos