cr-sqlite VS Pentive

I'm a happy user of https://github.com/vlcn-io/cr-sqlite/

If you're interested in this, here are some related projects that all take slightly different approaches:

- LiteSync directly competes with Marmot and supports DDL sync, but is closed source commercial (similar to SQLite EE): https://litesync.io

- dqlite is Canonical's distributed SQLite that depends on c-raft and kernel-level async I/O: https://dqlite.io

- cr-sqlite is a Rust-based loadable extension that adds CRDT changeset generation and reconciliation to SQLite: https://github.com/vlcn-io/cr-sqlite

Slightly related but not really (no multi writer, no C-level SQLite API or other restrictions):

- comdb2 (Bloombergs multi-homed RDMS using SQLite as the frontend)

- rqlite: RDMS with HTTP API and SQLite as the storage engine, used for replication and strong consistency (does not scale writes)

- litestream/LiteFS: disaster recovery replication

- liteserver: active read-only replication (predecessor of LiteSync)

Theory is great, but how can we apply this in practice? Instead of starting from 0, and writing a CRDT, let's try and leverage an existing project to do the heavy lifting. My choice is crSQLITE, an extension for SQLite to support CRDT merging of databases. Under the hood, the extension creates tables to track changes and allow inserting into an event log for merging states of separated peers.

Also https://github.com/vlcn-io/cr-sqlite/ which is SQLite + CRDTs

Runs/syncs to the browser too which is just lovely.

If you need multiple writers and can handle eventual correctness, you should really be using cr-sqlite[1]. It'll allow you to have any number of workers/clients that can write locally within the same process (so no network overhead) but still guarantee converge to the same state.

[1] https://github.com/vlcn-io/cr-sqlite

I am fully on the offline-first bandwagon after starting to use cr-sqlite (https://vlcn.io), which works similar to ElectricSQL.

I thought the bundle size of wasm-sqlite would be prohibitive, but it's surprisingly quick to download and boot. Reducing network reliance solves so many problems and corner-cases in my web app. Having access to local data makes everything very snappy too - the user experience is much better. Even if the user's offline data is wiped by the browser (offline storage limits are a bit of a minefield), it is straightforward to get all synced changes back from the server.

I didn't know that. Especially the first approach sounds interesting to me, because as far as I know the transactions of Yjs seem to be a problem on heavily changing documents. https://github.com/vlcn-io/cr-sqlite#approach-1-history-free... Thanks!

I wonder how this compares to https://vlcn.io?

The short ask: Anyone know of any projects that bring incremental view maintenance to SQLite?

The why:

Applications are usually read heavy. It is a sad state of affairs that, for these kinds of apps, we don't put more work on the write path to allow reads to benefit.

Would the whole No-SQL movement ever even have been a thing if relational databases had great support for materialized views that updated incrementally? I'd like to think not.

And more context:

I'm working to push the state of "functional relational programming" [1], [2] further forward. Materialized views with incremental updates are key to this. Bringing them to SQLite so they can be leveraged one the frontend would solve this whole quagmire of "state management libraries." I've been solving the data-sync problem in SQLite (https://vlcn.io/) and this piece is one of the next logical steps.

If nobody knows of an existing solution, would love to collaborate with someone on creating it.

[1] - https://github.com/papers-we-love/papers-we-love/blob/main/design/out-of-the-tar-pit.pdf

> I wonder what the ecosystem would look like if things were otherwise.

Shameless plug - I'm building https://github.com/AlexErrant/Pentive which is basically GitHub/Reddit for flashcards. Very much pre-product and a WIP, though the offline client proof of concept is done.

I also enjoyed the Treesitter talk from 5 years ago by Max Brunsfeld https://www.youtube.com/watch?v=Jes3bD6P0To

I'm currently building a query language whose grammar is very much inspired by Github's search syntax. I'm using Lezer, which is a GLR like Treesitter, so this talk learned me some parser generators (I've no formal CS education). Here's my grammar, a playground, and an example search query if anyone wants to play with it

https://github.com/AlexErrant/Pentive/blob/main/app/src/quer...

https://littletools.app/lezer

    -(a) spider-man -a b -c -"(quote\\"d) str" OR "l o l" OR  a b c ((a "c") b) tag:what -deck:"x y"

Not really. There are options for sharing cards on Anki https://www.reddit.com/r/Anki/comments/14j2jfy/deck_sharing_... but their collaboration features are limited.

I myself am building an Anki clone https://github.com/AlexErrant/Pentive with collaboration built in as a first class citizen, though its far from primetime. Currently stewing on how to get the SR algorithm, FSRS, to compile to wasm.

https://github.com/AlexErrant/Pentive

A free, open source, local-first, spaced repetition system that works offline, has p2p syncing, plugins, and first class support for collaboration. It's GitHub/Reddit for flashcards.

I basically took Anki and turned it into a webapp >_>

I find Solid's model pretty damn close to "compiling down to nothing". I chose Solid for my project because I wanted to support plugins that used other UI frameworks. I recently got a Svelte plugin working with the SolidJS router. I could probably make it prettier... but it's literally a call to Solid's `createComponent` with the Router and an anchoring div to which the Svelte component is mounted. Ezpz.

https://github.com/AlexErrant/Pentive/blob/main/example-plug...

I'm also building an Anki clone (sigh) that I'm calling "Github for flashcards".

>A free, open source, local-first, spaced repetition system that works offline, has p2p syncing, plugins, and first class support for collaboration.

https://github.com/AlexErrant/Pentive

Very much a WIP, completely unusable, but I recently made a video demoing the technical proof of concept.

Anki, imo, already has an open algorithm (that the user can change via plugins), universal interfaces, and is "self-hosted". My eyes perked up at REST api, but it doesn't look like there's a centralized server that hosts shared cards, which is where my mind went.

I'm building https://github.com/AlexErrant/Pentive/ which is basically Anki + Reddit - people can optionally upload their cards for others to download, and the most popular cards rise to the top. It's FLOSS, offline-first, supports plugins and p2p syncing, and is very much a WIP. My proof of concept is almost done though, which demos the critical technologies in a secure way.

I'm using cr-sqlite right now in my Anki clone: https://github.com/AlexErrant/Pentive

It's basically an offline-first flashcard webapp. CR-Sqlite allows for incremental syncing.

With Anki (the app from which I'm taking my inspiration), syncing is _not_ incremental - basically it just copies SQLite files around. So for example, the app could be on an iPhone with cards a card `A` reviewed, but the app on an iPad could make changes to the template on which card `A` is based, and that's enough to cause a conflict - you must take changes from only the iPad or only the iPhone. (To be clear - Anki does have some incremental syncing capabilities - I'm picking an intentionally pathological example.) CR-SQLite will mean that everything is incremental, however.

Basically makes 3 way merges a breeze (or n-way merges, really).

A FLOSS, offline-first, spaced repetition system that has first class support for collaboration, curation, and plugins. It's Reddit for flashcards.

https://github.com/AlexErrant/Pentive

I've been thinking about this for a stupid amount of time... thinking that someday someone's going to improve on Anki. Finally got tired of it and said that person's me.