Networking & Syncing


cr-sqlite is network agnostic. You can use crsql_changes to pull changes from one database, ship them over any protocol you like, then use crsql_changes on the other end to apply the changes. In any case, a WebSocket implementation is provided for convenience here and an overview of writing your own network code here.

Only changes made to CRRs are returned by crsql_changes. This allows you to have data which should be completely local (regular tables) and data which is intended to be synced with other databases (CRRs).

Single-Tenancy (Whole CRR Sync)

In this model, CRRs in databases that are being synced with one another are intended to be identical. All data from one should exist in the other and vice-versa.

This is the simplest model and works for use cases where who can see what is determined at the table / crr level. A user sharing private data with themselves or SQLite as an application file format (opens in a new tab) both fit this model.

Under single-tenancy, each database tracks the latest change it has seen from another database. When a database wants to sync with another, it sends the latest change it has seen from the other database. The other database then sends all changes that have happened since that change.

You can see this idea in an example implementation of whole crr sync.

Multi-Tenancy (Partial CRR Sync)

Most applications have a monotlithic database on the backend with data from many users in a sigle table or crr. In this model, you want to sync only the data that is relevant to a particular user.

This can be achieved through a combination of specifying what queries to sync and row level security policies.

Single-tenancy is currently what is supported by default and is the mosted tested and performant route. Multi-tenancy is still in development and not ready for production use. You could roll your own multi-tenancy via the filters available to you on crsql_changes but it is not recommended as the indices behind crsql_changes are not optimized for multi-tenant use cases.

Scalable multi-tenancy is expected to land in Q3/Q4 2023.

If you do plan on trying to roll your own multi-tenant sync before official supporting APIs are ready, a key technical difference is that multi-tenancy requires tracking the latest change you've seen from a database for each query you want to sync rather than just tracking a single database version.

Message Delivery Order

Messages can be received in any order by cr-sqlite and, so long as all messages are eventually delivered, all databases will converge to the same state.

This allows cr-sqlite to be used in both client-server and peer-to-peer network configurations as well as over network protocols that can't guarantee ordered message delivery.

If you are using a networking layer which can deliver messages out of order then your bookkeeping logic that asks databases for "changes since X" should be aware of this and be able to handle out of order delivery. I.e., don't record yourself as being up to date up to version Y if you haven't received versions prior to Y.

Given every changeset includes its starting and ending versions, you can easily ensure in-order delivery if the layers below you do not.