SSSync

Problem space

When syncing data to a client, you can express what they client needs by either:

• Live queries (arbitrary, requires a running server)
• A pre-calculated subset of data (user’s working set)

Live queries are in most ways the superior way to do things. They combine the freedom of “just make an API request” with the performance a built-in local cache and the consistency of transactional updates. There’s only 4 (small) downsides with existing libraries:

• They involve stateful servers for satisfying queries (usually pretty scalable read replicas of the main database), which have RAM requirements proportional to O(row results) instead of using the client as the data source.
• Are usually tied to specific databases (often Postgres) or are a database too (Convex, Jazz). This is mostly a good idea, just constraining.
• None of the existing solutions have support for SSR yet.
• Only a couple of them support offline mutations.

I expect that LLMs will make both these problems smaller over time, by rewriting the server code in more memory-efficient languages and make it trivial to add new database adapters.

Meanwhile, the downsides of a pre-calculated subset are many:

1. You (usually) have to front load all the user’s data the first time they open the app.
2. Your subset-calculating code is separate from the database and can introduce bugs.

So why work on it?

1. Be able to make apps that have zero idle cost by running on serverless compute and storage.
2. Possibility of making a meta-framework for CRDTs that adds relational semantics (on the client at least).
3. Have an open source library of my own.

System components

Client query API

I don’t think it should be SQL-based, and there’s people who agree with me.

Nonetheless, SQL was a consistent thorn in our side during this project. The deficiencies of SQL are well-known, so we won’t belabor them here. A few key pain points for us were:

1. Standard SQL doesn’t support nesting, even in the projection step (i.e., what describes the shape of the results). There are various extensions to SQL that support nesting, but many of them are not that good and the good ones are not widely available.We’re big fans of data normalization, but it’s very convenient to nest data when producing outputs.
2. SQL syntax is verbose and non-uniform. SQL makes the hard things possible, but the simple things aren’t easy. Often, making small changes to the query requires rewriting it completely.
3. SQL’s scalar expression language is weird and limited. Often, we wanted to factor out a scalar expression for re-use, but doing this in SQLite was annoying enough that we didn’t do it often.
4. SQL doesn’t have good tools for metaprogramming and changing the shape of a query at runtime: e.g., adding or removing a where filter clause depending on some data in the database. This forced us to often resort to using JavaScript string interpolation.

Geoffrey Litt, Building data-centric apps with a reactive relational database

My requirements are:

• Ability to clearly mark know something is a “fetch-by-id + relations” that can be sent to the server if not found locally.
• Easy to make performant on a simple database (like IndexedDB) without a query planner.
• Easy to build an IVM system for.

Client storage layer

Data-fetching protocol

1. Named subsets allow the developer to know when certain data has been loaded, or run the request for it.
2. Sync stream of updates from the server, begun at bootstrap.
3. Fetch by id + relations: can be marked as satisified by item existence, relations can be confirmed by a client-side index. But what if the relation set is to large?

In each case, full rows are sent and recieved. This lets the server keep sending data to old clients if necessary, by converting rows to a previous schema.

Sync logic

Write path

I prefer semantically named changes because they force best practices while also solving the schema migration problem. Credit to Livestore for the inspiration.

If you feel like commenting on this post, send me an e-mail at judah@joodaloop.com. I check all mail and respond within 2 days.