marmot VS wp-sqlite-db

If you're willing to accept eventual consistency (a big ask, but acceptable in some scenarios) then there are options like marmot [1] that replicate cdc over nats.

[1]: https://github.com/maxpert/marmot

I've written a whole SQLite replication system that works on top of RAFT ( https://github.com/maxpert/marmot ). Best part is RAFT has a well understood and strong library ecosystem as well. I started of with libraries and when I noticed I am reimplementing distributed streams, I just took off the shelf implementation (https://docs.nats.io/nats-concepts/jetstream) and embedded it in system. I love the simplicity and reasoning that comes with RAFT. However I am playing with epaxos these days (https://www.cs.cmu.edu/~dga/papers/epaxos-sosp2013.pdf), because then I can truly decentralize the implementation for truly masterless implementation. Right now I've added sharding mechanism on various streams so that in high load cases masters can be distributed across nodes too.

Very interesting! I have question ( out of my experience in https://github.com/maxpert/marmot ) how do get around the boot time, specially when a change log of table is pretty large in Postgres? I've implemented snapshotting mechanism in Marmot as part of quickly getting up to speed. At some level I wonder if we can just feed this PG replication log into NATS cluster and Marmot can just replicate it across the board.

One of the reasons I started writing Marmot (https://maxpert.github.io/marmot/) was for replicating bunch of tables across regions that were read heavy. I even used it for cache replication (because who cares if it’s a cache miss, but a hit will save me time and money). It’s hard to make such blue prints in early days of product, and by the time you hit a true growth almost everyone builds a custom solution for multi-region IAM.

Amazing I was just looking for a good mail server to configure for my demo. Which reminds me since you folks have mentioned LiteStream, have you tried Marmot (https://github.com/maxpert/marmot); I recently configured Isso with Marmot to scale it out horizontally (https://maxpert.github.io/marmot/demo). I am super curious what kind of write workload on a sub thousand people organization will have and if Marmot can help scale it horizontally without Foundation DB. I always find the the convenience of SQLite amazing.

Great that you brought it up. I will fill in the perspective of what I am doing for solving this in Marmot (https://github.com/maxpert/marmot). Today Marmot already records changes via installing triggers to record changes of a table, hence all the offline changes (while Marmot is not running) are never lost. Today when Marmot comes up after a long offline (depending upon max_log_size configuration), it realizes that and tries to catch up changes via restoring a snapshot and then applying rest of logs from NATS (JetStream) change logs. I am working on change that will be publishing those change logs to NATS before it restores snapshots, and once it reapplies those changes after restoring snapshot everyone will have your changes + your DB will be up to date. Now in this case one of the things that bothers people is the fact that if two nodes coming up with conflicting rows the last writer wins.

For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/sqlite-y-crdt) which can help me treat each row as document, and then try to merge them. I personally think CRDT gets harder to reason sometimes, and might not be explainable to an entry level developers. Usually when something is hard to reason and explain, I prefer sticking to simplicity. People IMO will be much more comfortable knowing they can't use auto incrementing IDs for particular tables (because two independent nodes can increment counter to same values) vs here is a magical way to merge that will mess up your data.

Before they added SQLite as WP plugin, I would use https://github.com/aaemnnosttv/wp-sqlite-db/ and I would use `define('DB_DIR', '/absolute/custom/path/to/directory/for/sqlite/database/file/');` to define the database location of my choice; I believe they would let users do the same with core support.

They basically took this implementation and just adapted it to coding standards:

https://github.com/aaemnnosttv/wp-sqlite-db

This has been around since some time and is itself a fork of a previous work.

The interesting part is that this drop-in replacement (mostly) already works well, there are a few issues that are related to some quirks in the WordPress core itself, for example: https://github.com/aaemnnosttv/wp-sqlite-db/issues/18

And maybe now they will be fixed.

Author here, here's an in-depth writeup on how this works and why it's useful:

https://make.wordpress.org/core/2022/09/23/client-side-webas...

To answer your questions directly:

WebAssembly is the magic sauce that transforms server-side code into client-side code. MySQL unfortunately is not yet supported by WebAssembly, so I applied a plugin that adds SQLite supports to WordPress [0]. The WebAssembly application has its own in-memory filesystem that lives in a specific browser tab and is scraped as soon as you close it.

So – technically it exposes db credentials, and even the entire DB, but that you are the only user of that DB so it's okay.

> What would the backend look like

The only backend is a static file server where the code and the database live. Your browser downloads a copy of the database and allows you to modify it in the current tab, but the updates are never saved back to the server.

[0] https://github.com/aaemnnosttv/wp-sqlite-db

1. Plugins that register their own database tables (however there already exists prior art such as https://github.com/aaemnnosttv/wp-sqlite-db for handling these cases)

2. Plugins that do direct queries against the standard database schema (broadly either for invalid (bad code) or performance (valid but slim use case) reasons)

Also, WordPress would of course keep the old query functions around and they would likely add a tag to the plugin repository so authors can mark plugins as supporting thes new ORM features.

Great idea in my opinion!

There's a WordPress plugin that adds support for SQLite as an alternative to MySQL.

Apparently it works really well. The implementation is (to my) simply astonishing: they run regular expressions against the SQL to convert it from MySQL dialect to SQLite! https://github.com/aaemnnosttv/wp-sqlite-db/blob/9a5604cce13...

This wp-sqlite-db one. Not super active, but maintained, at least.