Just thinking out loud

[Jason-Abbott]

I really like this idea. I appreciate how well you've documented it.

I am working on an app with local-first GRDB and was weighing sync options, wanting to avoid pointless encode/decode cycles just to get the data back to SQLite on the other end. Your approach is perfect for that.

What might be a blocker for me are no migration support and the idea of getting a new device up-to-speed that's months or even years behind. I wonder about the practicality of having periodic snapshots of some kind (certainly after schema changes) to immediately bring new devices to some revision.

That sounds worryingly complex to me but maybe something clever will come to mind.

[gerdemb]

Thank you for your message and your interest in this project. I’m glad you find the documentation helpful!

This was a personal proof-of-concept project that I thought was useful enough to share. Although I’ve experimented with it in development, I haven’t used it in a production project. You’ve touched on some critical challenges, such as handling schema migrations and the performance issues associated with updating new devices that are significantly out of sync.

Here are a couple of my thoughts:

• Schema Migrations: One approach could be to make schema changes in a backward-compatible manner, such as only adding columns and tables without deleting any. This isn’t ideal and I haven’t tested it, but it should work for simpler cases. A more complete solution would be to allow changesets to include schema migrations. Since the SQLite Session Extension captures only data changes and not schema migrations, this feature would need to be implemented within the SQLiteChangesetSync library itself.
• Periodic Snapshots: Using periodic snapshots to “fast-forward” data migrations, especially for new devices, is feasible. Uploading database snapshots could be handled within your application and stored online for other devices to download. A new device could start by downloading a snapshot of the entire database, and since the changeset data is stored alongside the data itself, it would simply be a matter of pulling and merging any new changesets created since the snapshot to bring the data up to date.

That sounds worryingly complex to me but maybe something clever will come to mind.

I agree! These are complex problems, but I don’t think they are impossible to solve.

If you’re willing to share, I’m curious to hear more about your project that is using GRDB in a local-first manner.