CloudKit synchronization options

[Jason-Abbott]

Just some musings of possible use to others …

There are some good options for synchronizing GRDB through CloudKit

• Harmony
  • “Just works” if your models are FetchableRecord & PersistableRecord
  • Must write records through its Harmonic layer rather than GRDB directly
• SQLiteChangesetSync
  • Persists raw SQLite changesets to iCloud rather than records
  • Local device just replays changesets to achieve sync
• Matt’s minimalist GRDB CloudKit sync layer
  • Pre-CKSyncEngine
  • Unlike others, uses TransactionObserver to create CKRecords so you can write changes however you want

For me, these all have shortcomings relative to my GRDB usage, which involves a lot of raw SQL, including updates across many rows in one statement and updates through SQLite triggers. That rules out Harmony.

I like the concept of the changeset sync. It seems it would be efficient and uncomplicated for actively used devices. Where it might fall down is adding a new device. Or having cloud sync disabled in your app and then deciding to enable it. It’s not obvious how changesets could efficiently address those common scenarios.

That brings me to Matt’s code. No magic to it, which I appreciate. It seems the right balance for me between high level Harmony and low level changesets.

Continued ...

[Jason-Abbott]

A shortcoming in Matt’s code relative to my usage is foreign key handling. I have many tables and relations.

What happens when CloudKit sends changed records in an order that violates foreign keys? It doesn’t seem to come up in Matt’s example, which has just a few tables. Harmony handles it by catching FK violations and re-queuing those records. Harmony also notes other options, like temporarily disabling FK checking.

Those are reasonable choices for a small database but don’t give me confidence when there’s more complexity. I have multiple “levels” of FKs between tables so requeueing could fail again. Do you just keep requeueing until there are no failures? Aside from sounding like a terrible pattern, Harmony notes that the missing FK dependencies may not even exist in the current batch of records from CloudKit.

So requeueing isn’t a robust solution.

What I’m experimenting with now is the use of CKZones to isolate FK dependencies. CKSyncEngine allows you to define CKSyncEngine.FetchChangesOptions with prioritizedZoneIDs which is just an array in the preferred order.

What I don’t know without experimenting is how that preferred order plays out. Is it a guarantee?

To begin experimenting, I used this rough SQL

WITH RECURSIVE 
  dependents AS (
    SELECT 0 AS level, s.name, NULL AS fk
      FROM sqlite_schema s
      LEFT JOIN pragma_foreign_key_list(s.name) fk 
        ON fk."table" != s.name -- ignore self-joins
     WHERE s.type = 'table'
     GROUP BY s.name
    HAVING COUNT(fk.id) = 0 -- no foreign keys
     UNION
    SELECT d.level + 1 AS level, s.name, fk."table"
      FROM sqlite_schema s,
           pragma_foreign_key_list(s.name) fk
     INNER JOIN dependents d 
        ON d.name = fk."table" 
       AND d.name != s.name
     WHERE s.type = 'table'
  )
SELECT
    MAX(level) AS level,
    name,
    group_concat(fk) AS foreign_keys
   FROM dependents
  GROUP BY name
  ORDER BY level, name

to group tables according to their FKs. The “level” 0 tables have no FKs. The level 1 tables only have FKs in level 0. The level 2 tables only have FKs in level < 2. And so on. I end up with five levels in my database.

I wrote code that generates CKZones for each dependency level and assigns them to the FetchChangesOptions noted above. Then the code that batches up local observed changes assigns the appropriate zone ID based on the table name.

It is pretty clean but there are many failure modes. I’ll comment here with my learnings.

(Edit: Corrected attributions I’d confused between Harmony and Matt)

[groue]

What happens when CloudKit sends changed records in an order that violates foreign keys

My current understanding of the relationship between CloudKit and foreign keys is that apps are all left by themselves. An Apple eng at WWDC recommends to maintain 2 stores:

Usually we recommend having another store that is not CloudKit enabled. Copy the data from the CloudKit store to the local store where you can perform validation. Serve clients from the local store where you know the graph is valid.

If I were to need CloudKit sync (which never happened to me), I would probably spend time to look after a schema without foreign keys in order to avoid as much as possible of this hassle. Bite the bullet, in other words.

[Jason-Abbott]

I reflected on your foreign key remarks last week and quickly realized you’re right.

FKs in a db shared by multiple apps, and perhaps user-queryable, are valuable but less so in an embedded db. They provide me with important debugging clues while coding so, personally, I’ll still define them but disable them for production builds.

Once I clear up a few things in my code (replacing FK cascades with triggers) that aren’t really about CloudKit, I’ll wrap up the part that may be useful “show and tell”, where I connect SyncEngine more directly to GRDB TransactionObserver, without the encode/decode of instantiating models, and post some sample code here.

[Jason-Abbott]

Another thing I’m experimenting with is avoiding all model initializations by sending the raw rows and DatabaseValue.Storage members through CloudKit. In other words, the Cloud Database records will just mirror SQLite tables. No point in multiple encode/decodes just to store Int64/Double/String/Data, I figured.

I’ll share code when I have something more put together.

[x-creates]

It's 2025. I stumbled upon your post. Is there any update on this thread?

[Jason-Abbott]

Hi @x-creates. For my part, I moved ahead with my last comment and made peace with having no production foreign keys, though I do still define them and have them enabled while running unit tests, which has been helpful. I had relied on a lot of cascade relations between tables which, in the absence of FKs, I replaced with triggers.

I will share a bit more about the actual CloudKit process I’ve landed on and some sample code. Just let me type it up ...

[Jason-Abbott]

I won’t go into much detail @x-creates but I’ll offer an overview of what I ended up with and am glad to answer specific questions about it. One of the solutions linked at the top is best for most people, I think.

A core piece for me is a CloudSyncable protocol that my models conform to, which handles creating a CKRecord from a table row and creating a table row from an incoming CKRecord. For the most part, protocol extensions implement everything needed so no extra code is required in the models themselves.

CloudSyncable makes use of GRDB columns(in:in:) to collect the values to build the record.

The other core piece is, of course, a subclass of TransactionObserver. It watches for table changes and uses the CloudSyncable methods to create a CKRecord as needed, passed to CKSyncEngine in the usual way.

syncEngine.state.add(pendingRecordZoneChanges: pendingChanges.map(\.recordZone))

I fear I’ve only stated the obvious, here. As I typed this up and looked at my code, I realized how much it’s infused with the peculiarities of my own models. It would be confusing to dump all that into this discussion.

I am happy to share code to answer any specific questions you might have.

[csd998aaa]

@Jason-Abbott hi Jason , May I ask if you can share how you handled it? Can you solve the problem of GRDB fk? If possible, would you be willing to share your code demo?

[Jason-Abbott]

@csd998aaa I’d happily share if I had handled it. 🙂 Instead, I took @groue ’s advice and disabled foreign keys.

As you can see above, I hoped to fashion an algorithm that could automatically re-order incoming changes to preserve foreign keys, based on SQLite metadata. But edge cases arose that would balloon its complexity and, at least for my uses, outweigh the benefits of foreign keys.

I replaced the cascading functionality of foreign keys with triggers. And I still have FKs enabled while testing, which has caught a couple things.

Coming from years of working with shared databases on the server, this was psychologically hard but, for embedded use, it now seems to me like a non-issue.

[sobri909]

Just stumbled onto this, having missed all the earlier discussion last year.

@Jason-Abbott I agree with all your points on the weaknesses of my minimalist approach! And would add that it also doesn't even attempt to acknowledge conflicts let alone set a path towards resolving them. It's just "accept everything that comes in, because YOLO".

Though if I did ever take another stab at it (and no doubt I will - CloudKit sync is something that keeps coming back up as a requirement) I'd probably attempt to work in some minimalist conflict acknowledgement and resolution.

The foreign keys thing, I think I dealt with the same way as you proposed: try to type order the incoming objects before writing, to minimise foreign key conflicts. Which as you say, depends on it being a small and simple database. And even then there's still potential for edge cases blowing things up. There's a reason that gist never migrated to being a proper repo - it's appealingly simple and sneakily hiding traps.

When I get to doing all this again I'll probably take a similar approach to last time, albeit using CKSyncEngine. But would be keen to also learn from improvements / divergences you found worked better in your own code!

[Jason-Abbott]

Your code was a big help to get me started. Thank you. I’d be happy to share more of what I have when you get underway with CKSyncEngine — not sure it’s ideal but it seems to be working. 🙂

[MartinP7r]

Seems like this might be a new option worth considering?

• https://github.com/pointfreeco/sharing-grdb

I'm curious to hear your thoughts on it!

[Jason-Abbott]

As I understand it, sharing-grdb is akin to GRDBQuery, which you could say is about synchronizing SQLite with a SwiftUI view, not synchronizing with CloudKit. But I may well have overlooked something.

[MartinP7r]

I'm sorry, you're right. I completely misread the context there...