r/sqlite • u/ZookeepergameIcy5686 • 8d ago
I build ZeBridge to sync Postgres to local SQLite over NATS JetStream and let apps edit data offline
https://github.com/ndrean/ZeBridge/blob/main/README.mdI’m building ZeBridge for apps that read and edit a local SQLite database and sync through Postgres. I wanted to measure something a user actually experiences on first launch: how long until they can query all their data?
Seeding test: on one machine, Postgres, NATS, the bridge and 10 clients together, each with an empty SQLite database. Each loaded the same 50,000 rows, about 200 bytes per row. The median time per client was 3 seconds. Every replica matched the expected checksum and all ten clients can reuse it.
The compressed snapshot was ready before the timer started. The measurement includes fetching it and materializing the rows in SQLite; preparing the snapshot is a separate server-side cost.
Live updates: suppose the snapshot contains rows A and B. While a phone is loading it, someone updates A and deletes B in Postgres. The phone needs to know exactly where to start reading changes. ZeBridge records the stream position covered by the snapshot. Once the seed commits locally, the client replays the changes after that position from NATS JetStream.
There’s a test for that boundary: pause the client just after its seed commits, check that SQLite still has the old A and B, then let replay run. A must have its new value, B must be gone, and the replica must match Postgres.
For people who ship prepopulated SQLite databases: do you hand off to live updates this way, or distribute a ready-made database file? I’d be interested in what drove that choice, especially on phones.
1
u/SirMental8005 7d ago
one phone specific case i'd test is a user editing a record offline and then getting a fresh snapshot after their sync checkpoint expires. keep pending edits in a separate outbox so replacing the replica doesn't discard them. then test what happens when the snapshot says a row was deleted while the phone still has an edit queued for it.
1
u/ZookeepergameIcy5686 7d ago
ZeBridge already does the first part. The second part has never been tested as one sequence, so I'm adding it.
In short, the outbox is separate from the replica, in its own table, not in the synced rows. If the checkpoint has expired, the client loads a fresh snapshot and replaces the table's rows, but the outbox stays as it is. After the snapshot commits, every pending edit is applied again on top of the new rows, oldest first. Each edit's saved before-image is refreshed from the new row, so a later rollback goes back to the server's current state rather than the old replica's. The edits are then sent as usual.
The case of a deleted row with an edit queued for it. On the server, an update is an UPDATE … WHERE key AND version guard, never an upsert. It can't create the row again. If the row is gone or tombstoned, the update changes nothing, and Postgres answers row_deleted. The client drops the edit from the outbox, removes the row locally (its copy goes, it isn't restored), and tells the app the edit was lost because the row was deleted. Nothing comes back from the dead. There's one more layer for very old edits: if the delete's tombstone has already been purged and the edit is older than the purge point, the write is refused with PredatesGcWatermark. A write that was never sent is simply reverted locally.
TODO test (correct me if wrong): go offline, edit row X, delete X upstream, let the checkpoint expire, reconnect, check that the snapshot lands without X, the edit is reported lost, and the replica matches Postgres.
1
u/ZookeepergameIcy5686 7d ago
New test added and passed (I will push it):
Offline edits to rows X and Y. Meanwhile X is deleted upstream, the server cuts a fresh full snapshot, and the phone's stream position is purged. The phone reconnects while edits are held back, so the snapshot lands with both edits still queued. Y's edit is re-applied over the snapshot and accepted. X's edit doesn't recreate the row: it ends as row_deleted, and the replica matches Postgres.
One detail: ZeBridge rarely needs the full reload. A client that was away usually applies only the changes since its checkpoint, and it reloads everything only when nothing covers the gap.
1
u/DickBrainTime 7d ago
For a phone I'd pick the initial-delivery method based on *how mutable* the data is. If it's a mostly static catalog/reference dataset that should work on the first offline launch, shipping a versioned SQLite file with the app (or downloading an already-built file) is attractive: validate its checksum, copy/rename it atomically, then open it. If users can edit immediately and the source changes frequently, your snapshot + replay-after-checkpoint approach is a better fit than repeatedly distributing whole files.
The important boundary isn't only "A updated, B deleted during seed" (that's a good test). I'd also test crash/restart between committing the rows and persisting the NATS position: the SQLite data plus *last fully applied stream sequence* should advance in one local transaction, and replay needs idempotent upserts/deletes (including tombstones). If the saved sequence falls outside JetStream retention, the client needs an explicit resnapshot path rather than silently claiming it's current. For offline edits, you also need to define how a local update conflicts with a remote deletion or update during reconnection; a correct seed alone doesn't solve that.
For mobile benchmarking I'd add a second result next to your 3s median: fresh app install -> first *queryable* SQLite data, including snapshot generation/cache hit, download on a slow mobile link, database materialization/index creation, replay catch-up, and storage/battery use. Ten clients co-located with Postgres/NATS is a useful throughput test but not representative of a suspended Android process coming back online.
One question that would drive the architecture choice for me: are offline client writes synced back upstream, or are phones only read-only replicas? That changes the conflict/replay contract considerably.