Rqlite: The lightweight, distributed relational database built on SQLite

It looks like this layers Raft on top of SQLite. I don&#x27;t like when systems replicate high-level changes like &quot;update users set salary = salary + 1000 where ...;&quot; Instead, I prefer they replicate low-level changes like &quot;replace key&#x2F;block X, which should have contents C_x, with C_x&#x27;&quot;.<p>Why? Imagine you&#x27;re doing a rolling update. Some of your replica are running the newer version of SQLite and some are running the older version. They may not execute the high-level query in exactly the same way. For example, in the absence of an &quot;order by&quot; clause, select results&#x27; order is unstated. So imagine someone makes a mutation that depends on this: &quot;insert ... select ... limit&quot;. (Maybe a dumb example but it can happen anyway.) Now the databases start to diverge, not only in underlying bytes and implementation-defined ordering but in actual row data.<p>I worked on a major distributed system that originally replicated high-level changes and switched to replicating low-level changes for this reason. We had a system for detecting when replicas didn&#x27;t match, and replication of high-level changes was the biggest reason for diffs. (Hardware error was the second biggest reason; we added a lot of checksumming because of that.)

So it looks like you can now distribute SQLite at the:<p>Stmt level: <a href="https:&#x2F;&#x2F;github.com&#x2F;rqlite&#x2F;rqlite" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;rqlite&#x2F;rqlite</a><p>VFS Level: <a href="https:&#x2F;&#x2F;github.com&#x2F;canonical&#x2F;dqlite" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;canonical&#x2F;dqlite</a><p>Block Level: <a href="https:&#x2F;&#x2F;github.com&#x2F;benbjohnson&#x2F;litestream" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;benbjohnson&#x2F;litestream</a><p>Really cool enhancements to an awesome project!

I know nothing of consensus algorithms and distributed systems so bear with me please.<p>&gt; rqlite uses Raft to achieve consensus across all the instances of the SQLite databases, ensuring that every change made to the system is made to a quorum of SQLite databases, or none at all.<p>What I understood from this sentence is that, if we have three instances, rqlite will make sure that every change is written to at least two. But what if two changes are written to two different pairs of instances? Then the three instances will have three different versions of the data. For example, change X is written to instances A and B, and change Y is written to B and C. Now A has X, B has X and Y, and C has Y only. How do you decide who is right?

Going from a local db to one over a network has at least one risk. The SQLite docs gives developers the okay to write &quot;n+1&quot; style queries (<a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;np1queryprob.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;np1queryprob.html</a>). When the db is on the same file system as the application this pattern is fine. But as soon a you add a network call it becomes a potential bottleneck.

So if:<p>Alice, Bob and Charlie have a synced copy of the same database<p>Charlie goes on a plane and adds a loads of records without a connection to the other databases<p>Alice and Bob make no changes<p>Charlie comes home and syncs<p>Will Charlie lose all his changes, as his database is different to Alice and Bob&#x27;s?<p>What happens if Alice, Bob and Charlie all makes changes offline then resync?

@otoolep<p>SQLite has a great post on “When to Use” (and not use) SQLite.<p>Would be great if you included these same use cases in the ReamMe docs and make it clear if Rqlite can address them.<p><a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;whentouse.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;whentouse.html</a>

I think microk8s uses this to form a cluster, and k3s used to use it but moved back to etc.<p>Would be good to hear from someone who used it what are the pros and cons of such a setup

Expensify had a version of something like this back in like 2013&#x2F;14 I think.

First of all, great idea, and a brilliant and highly laudable effort!<p>Favorited!<p>One minor caveat (&quot;Here be Dragons&quot;) I have (with respect to my own future adoption&#x2F;production use), however:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;rqlite&#x2F;rqlite&#x2F;blob&#x2F;master&#x2F;DOC&#x2F;FAQ.md" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;rqlite&#x2F;rqlite&#x2F;blob&#x2F;master&#x2F;DOC&#x2F;FAQ.md</a><p>&gt;<i>&quot;Does rqlite support transactions?<p>It supports a form of transactions. You can wrap a bulk update in a transaction such that all the statements in the bulk request will succeed, or none of them will. However the behaviour or rqlite is undefined if you send explicit BEGIN, COMMIT, or ROLLBACK statements. This is not because they won&#x27;t work -- they will -- but if your node (or cluster) fails while a transaction is in progress, the system may be left in a hard-to-use state. So until rqlite can offer strict guarantees about its behaviour if it fails during a transaction, using BEGIN, COMMIT, and ROLLBACK is officially unsupported. Unfortunately this does mean that rqlite may not be suitable for some applications.&quot;</i><p>PDS: Distributed transactions are extremely difficult to get exactly right -- so I&#x27;m not trying to criticize all of the hard work and effort that everyone has put into this (again, it&#x27;s a great idea, and I think it has a terrific future).<p>But Distributed Transactions -- are what differentiate something like rsqlite from say, something like CockroachDB (<a href="https:&#x2F;&#x2F;www.cockroachlabs.com&#x2F;docs&#x2F;stable&#x2F;architecture&#x2F;life-of-a-distributed-transaction.html" rel="nofollow">https:&#x2F;&#x2F;www.cockroachlabs.com&#x2F;docs&#x2F;stable&#x2F;architecture&#x2F;life-...</a>).<p>Of course, CockroachDB is a pay-for product with an actual company with many years of experience backing it, whereas rqlite, as far as I can intuit, at this point in time (someone correct me if I am wrong), appears to be a volunteer effort...<p>Still, I think that rqlite despite this -- has a glorious and wonderful future!<p>Again, a brilliant and laudable effort, suitable for many use cases presently, and I can&#x27;t wait to see what the future holds for this Open Source project!<p>Maybe in the future some code-ninja will step up to the plate and add fully guaranteed, safe, distributed transactions!<p>Until then, it looks like a great idea coupled with a great software engineering effort!<p>As I said, &quot;Favorited!&quot;.

FoundationDB and Comdb2 also use sqlite as a storage engine. Curious that they decided to implement yet another one.<p><a href="https:&#x2F;&#x2F;www.foundationdb.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.foundationdb.org&#x2F;</a><p><a href="http:&#x2F;&#x2F;comdb2.org&#x2F;" rel="nofollow">http:&#x2F;&#x2F;comdb2.org&#x2F;</a>

One thing that jumps out at me after reading a lot of Jepsen analyses - does Rqlite assume that partitions form equality relations? That is, that all nodes belong to one and only partition group? This is not always the case in practice.

I&#x27;m curious how this relates to the Calvin protocol as implemented by FaunaDB. They both use Raft, but FaunaDB and Calvin have additional details about how transactions are retried and aborted. <a href="https:&#x2F;&#x2F;fauna.com&#x2F;blog&#x2F;consistency-without-clocks-faunadb-transaction-protocol" rel="nofollow">https:&#x2F;&#x2F;fauna.com&#x2F;blog&#x2F;consistency-without-clocks-faunadb-tr...</a>

We currently use browsers on several devices (both laptops and android) and rely on google sync currently. Maybe this could be used to sync bookmarks, history etc across my devices but still keep my data local to me?

Could you use this to build a decentralized p2p app? If so, what gotchas and limitations are there?

rqlite is mentioned here quite often, multiple times last year. I don&#x27;t think this entry brings anything new.