Marmot: Multi-writer distributed SQLite based on NATS

If you&#x27;re interested in this, here are some related projects that all take slightly different approaches:<p>- LiteSync directly competes with Marmot and supports DDL sync, but is closed source commercial (similar to SQLite EE): <a href="https:&#x2F;&#x2F;litesync.io" rel="nofollow noreferrer">https:&#x2F;&#x2F;litesync.io</a><p>- dqlite is Canonical&#x27;s distributed SQLite that depends on c-raft and kernel-level async I&#x2F;O: <a href="https:&#x2F;&#x2F;dqlite.io" rel="nofollow noreferrer">https:&#x2F;&#x2F;dqlite.io</a><p>- cr-sqlite is a Rust-based loadable extension that adds CRDT changeset generation and reconciliation to SQLite: <a href="https:&#x2F;&#x2F;github.com&#x2F;vlcn-io&#x2F;cr-sqlite">https:&#x2F;&#x2F;github.com&#x2F;vlcn-io&#x2F;cr-sqlite</a><p>Slightly related but not really (no multi writer, no C-level SQLite API or other restrictions):<p>- comdb2 (Bloombergs multi-homed RDBMS using SQLite as the frontend)<p>- rqlite: RDBMS with HTTP API and SQLite as the storage engine, used for replication and strong consistency (does not scale writes)<p>- litestream&#x2F;LiteFS: disaster recovery replication<p>- liteserver: active read-only replication (predecessor of LiteSync)

Author of Marmot here. Marmot was born out of my own use-case (was building replicated SQLite based cache). While working on various problems I realized how well suited it might be for read heavy sites&#x2F;workloads. If I take a typical CMS site 90% of the time it&#x27;s just reading and SQLite is perfect for that, but then how I get independently deployed nodes to replicate data. The philosophy I am sticking to so far:<p>- Sidecar! I would avoid any kind of in process library at any cost. Call me biased but I don&#x27;t trust someone&#x27;s code in my process space causing it to crash.<p>- No master - each node should be able to make progress on its own, if these processes go down your own process will keep functioning. They will converge once everything is back.<p>- Easy to start, yet hard to master - You can get up and running pretty quickly, but make no mistake this tool is not for rookie who doesn&#x27;t understand how incremental primary keys are bad, and how to they can keep things conflict free.<p>I am far from getting everything I need in there, and again my philosophy might evolve over time as well. Talking to people on Discord has helped me think through use-cases a lot, so keep the good feedback coming. Would love to answer any questions people might have here.

I can totally see some junior dev who doesn&#x27;t fully grok transactions (and not reading&#x2F;understanding the &quot;What happens when there is a race condition?&quot; section), trying to deploy something like this and scratching their head why it falls over completely in production. The site worked fine when it was just me testing it!<p>This is a neat proof of concept and I encourage experimentation. But, if you&#x27;re developing something, please just use postgres, and don&#x27;t try to cobble things together with something like this.<p>Edit: already seeing the downvotes. Yes... classic HN... anything that goes against plain old sanity is punished.

&gt; In Marmot every row is uniquely mapped to a JetStream. This guarantees that for any node to publish changes for a row it has to go through same JetStream as everyone else.<p>and<p>&gt; This means there is NO serializability guarantee of a transaction spanning multiple tables. This is a design choice, in order to avoid any sort of global locking, and performance.<p>But since the serialization happens at per row level, does this also mean no serializability guarantee of a transaction within a table too, not only spanning multiple tables?

I&#x27;m using Marmot for my own website on production. Up to this date there were no problems.<p>If I had any technical issues (i.e. questions, optimizations etc) I always asked the developer maxpert and he gave me in-depth answers that helped me personally a lot.<p>In my case I have much love for Marmot and hopefully it grows and helps a bigger community

I see a lot of projects started in this space and all of them appear to have multi-writer as a goal. I&#x27;ve been interested for a long time (and have started and stopped) in a solution for single-write multi-read with eventual consistency. I chatted with @benbjohnson on a LiteStream ticket about the possibility of adding a mobile client to receive the replicas to mobile devices but I think that option isn&#x27;t really consistent with the new direction of that work for LiteFS at Fly.<p>To me the multi-writer &quot;collaborative&quot; use case is super powerful but also has a lot of challenges. I personally would see a lot of value in a solution for eventually consistent read-replicas that are available for end-client (WASM or mobile native) replication but still funnel updates through traditional APIs with store-and-forward for the offline case.<p>Is anybody aware of an open-source project pursuing that goal that maybe I haven&#x27;t come across?

This is really cool. We&#x27;ve just created a POC that bridges Federated GraphQL Subscriptions and NATS, so this could maybe work together? Here&#x27;s a small video of combining Federated Subscriptions and event driven architecture through NATS: <a href="https:&#x2F;&#x2F;twitter.com&#x2F;TheWorstFounder&#x2F;status&#x2F;1734134926113378393" rel="nofollow noreferrer">https:&#x2F;&#x2F;twitter.com&#x2F;TheWorstFounder&#x2F;status&#x2F;17341349261133783...</a>?

Something that wasn&#x27;t clear to me from the README: how does this handle duplicate IDs?<p>If I have a table with a string primary key and I insert a row in one node with ID &quot;hello&quot; and do the same thing (but with different column data) on another node, what happens?

I wonder if this kind of a setup would be useful for federated protocols. Mastodon and Farcaster both maintain websocket connections open from all servers to all servers.

i&#x27;m surprised by this.. No schema changes?<p>Nobody pushes an application out just once.