marmot VS sqlite-utils

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.

https://github.com/simonw/sqlite-utils

So, his code might not be a good place to find best patterns (for ex, I don't think they are fully typed), but his repos are very pragmatic, and his development process is super insightful (well documented PRs for personal repos!). Best part, he blogs about every non-trivial update, so you get all the context!

Sounds like your problem is with SQLAlchemy, not with SQLite.

My https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy.

There are a few things you can do here.

SQLite is great at JSON - so I often dump JSON structures in a TEXT column and query them using https://www.sqlite.org/json1.html

I also have plugins for running jq() functions directly in SQL queries - https://datasette.io/plugins/datasette-jq and https://github.com/simonw/sqlite-utils-jq

I've been trying to drive the cost of turning semi-structured data into structured SQL queries down as much as possible with https://sqlite-utils.datasette.io - see this tutorial for more: https://datasette.io/tutorials/clean-data

This is also an area that I'm starting to explore with LLMs. I love the idea that you could take a bunch of messy data, tell Datasette Cloud "I want this imported into a table with this schema"... and it does that.

I have a prototype of this working now, I hope to turn it into an open source plugin (and Datasette Cloud feature) pretty soon. It's using this trick: https://til.simonwillison.net/gpt3/openai-python-functions-d...

I've baked a ton of different SQLite tricks - including things like full-text indexing support and advanced alter table methods - into my sqlite-utils CLI tool and Python library: https://sqlite-utils.datasette.io

My Datasette project provides tools for exploring, analyzing and publishing SQLite databases, plus ways to expose them via a JSON API: https://datasette.io

I've also written a ton of stuff about SQLite on my two blogs:

- https://simonwillison.net/tags/sqlite/

- https://til.simonwillison.net/sqlite

This is really fun. I have an experimental branch of my sqlite-utils CLI tool (which has dozens of sub-commands) running with this now and it really did only take 4 lines of code - I'm treating Trogon as an optional dependency because people using my package as a Python library rather than a CLI tool may not want the extra installed components:

https://github.com/simonw/sqlite-utils/commit/ec12b780d5dcd6...

There's an animated GIF demo of the result here: https://github.com/simonw/sqlite-utils/issues/545#issuecomme...

My project https://datasette.io/ is ideal for this kind of thing. You can use https://sqlite-utils.datasette.io/ to load JSON data into a SQLite database, then publish it with Datasette.

I've been using this for about six months now and I absolutely love it.

Make never stuck for me - I couldn't quite get it to fit inside my head.

Just has the exact set of features I want.

Here's one example of one of my Justfiles: https://github.com/simonw/sqlite-utils/blob/fc221f9b62ed8624... - documented here: https://sqlite-utils.datasette.io/en/stable/contributing.htm...

I also wrote about using Just with Django in this TIL: https://til.simonwillison.net/django/just-with-django

SQLite with the open source toolchain I've been building over the past five years:

https://datasette.io as the interface for running queries against (and visualizing) my data.

https://sqlite-utils.datasette.io/ as a set of tools for creating and modifying my databases (inserting JSON or CSV data, enabling full text search text)

https://dogsheep.github.io as a suite of tools for importing my personal data - see also this talk I gave about that project: https://simonwillison.net/2020/Nov/14/personal-data-warehous...

Here's an example: https://github.com/simonw/sqlite-utils/pull/468

> After identifying about 7 commits (with pretty basic/useless messages, and no PR link!), I then had to find the corresponding PRs based on timestamps, and search the PR history for PRs merged around those timestamps.

Not sure if this would save any time, but it is possible to search PRs by commit. For example, say git blame led me to this commit: https://github.com/simonw/sqlite-utils/commit/129141572f249e...

I could have found PR #373 via this search: https://github.com/simonw/sqlite-utils/pulls?q=bb16f52681b6d...

> I thus treat PRs as ephemeral

I think I see what you're saying but as others have pointed out, sometimes you want to add screenshots etc to the context, and you can't capture this kind of info in commit messages. So then you have two choices: issues or PRs.

> Then any review comments are preferably not addressed directly in the PR

I would think that sometimes you really do want to have a back and forth conversation in the PR, rather than just a "make this change" -> "ok done" type of feedback loop.

I view the PR as an decent place for all of this because it's basically a commit of commits, capturing the related changes/conversation/context all in a single place at the point of merge.