cr-sqlite VS edgedb

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

A new DB, with a new query language that's like "SQL done right"? This immediately reminded me of EdgeDB: https://edgedb.com/

Is there anyone here who knows enough about these two products to do a compare/contrast?

The whole thing consists of these main parts:

1. SQL parser: https://github.com/edgedb/edgedb/tree/master/edb/pgsql/parse...

>relational no-sql

Do you mean something like edgeDB?[0]

Or do you mean some non-declarative language completely? I don't see the latter making much sense. The issue with SQL for me is the "natural language" which quickly loses all intended readabilty when you have SELECT col1, col2 FROM (SELECT * FROM ... WHERE 1=0 AND ... which is what edgeDB is trying to solve.

[0]https://edgedb.com/

We need to land #3747, then something like this should work

You have to do your own optimiser to avoid, for instance, the N+1 query problem. (Just Google that, plenty of explanations around.) Many GraphQL frameworks have a “naive” subquery implementation that performs N individual subqueries. You either have to override this for each parent/child pairing, or bolt something on the back to delay all the “SELECT * FROM tbl_subquery WHERE id = ?” operations and convert them into one “… WHERE id IN (…)”. Sounds like a great use of your time.

In the end you might think to yourself “why am I doing this, when my SQL database already has query optimisation?”. And it’s a fair question, you are onto it. Try one of those auto-GraphQL things instead. EdgeDB (https://edgedb.com) does it as we speak, runs atop Postgres. Save yourself the enormous effort if you’re only building a GraphQL API for a single RBDMS, and not as a façade for a cluster of microservices and databases and external requests.

Or just nod to your boss and go back to what being a backend developer has always meant: laboriously building by hand completely ad hoc JSON versions of SQL RBDMS schemas, each terribly unhappy in its own way. In no way does doing it manually but presenting GraphQL deviate from this Sisyphean tradition.

I read in the article that NOT having GraphQL exactly match your DB schema is a best practice. My response is “did a backend developer write this?”

Same idea, yeah. Unfortunately, in our case we couldn't use pytest due to complicated test setup, so we used a customized unittest runner instead.

EdgeDB [1] has indeed a rich GraphQL layer, but it's a very different project.

While it also builds on top of Postgres, EdgeDB replaces the entire relational database front-end. EdgeDB features a SQL replacement language called EdgeQL (analytical capabilities of SQL married with deep-fetching in GraphQL), a higher-level data model (tables -> object types), integrated migrations engine, a custom protocol with great performance & great client APIs, and many other things. Read more here [2].

(disclaimer: I'm EdgeDB co-founder)

[1] https://github.com/edgedb/edgedb

[2] https://www.edgedb.com/blog/edgedb-1-0

I'm curious how this squares up with what someone linked elsewhere: https://github.com/edgedb/edgedb/discussions/3403

> EdgeDB does not treat Postgres as a simple standard SQL store. The opposite is true. To realize the full potential of the graph-relational model and EdgeQL efficiently, we must squeeze every last bit of functionality out of PostgreSQL's implementation of SQL and its schema.

This would seem to be an opposing view of how coupled EdgeDB and PostgreSQL are. Which is it?