Repository navigation
SyncEngine treats JSONB as TEXT instead of BLOB #561
Description
Description
My CloudKit-synced schema has several JSONBRepresentation columns. When fetching the records from CloudKit, I get a purple warning saying:
Caught error: SQLite error 19: cannot store TEXT value in BLOB column notes.text - while executing `INSERT INTO "notes" ...
The warning is emitted from SyncEngine.upsertFromServerRecord(_:force:db).
Checklist
- I wrote this in my own words, and aimed to be as succinct as possible, even if I used AI to help research its content.
- I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
- I have determined whether this bug is also reproducible in a vanilla GRDB project.
- If possible, I've reproduced the issue using the
mainbranch of this package. - This issue hasn't been addressed in an existing GitHub issue or discussion.
Expected behavior
The JSONB column is encoded to CKAsset and parsed as JSONB BLOB.
Actual behavior
No response
Reproducing project
No response
SQLiteData version information
No response
Sharing version information
No response
GRDB version information
No response
Destination operating system
No response
Xcode version information
No response
Activity
mvanhorn commentedon Nov 4, 2026
The sync code used query bindings reconstructed from typed row values, which represent JSONB values as JSON text rather than their stored bytes. Conflict detection used the same bindings, so it also missed the BLOB hash comparison used for assets.
The sync queries now fetch raw storage values alongside the typed row and capture BLOB bytes by column name. Record uploads and merge comparisons prefer those bytes, preserving JSONB asset storage and hash comparisons.
#572
mbrandonw commentedon Nov 4, 2026
Hi @lucamegh and @mvanhorn, sorry for the delayed response. Unfortunately we cannot support JSONB in tables that are going to be synchronized to CloudKit right now, for two reasons really. First, right now all blob fields are synchronized as
CKAssets, and it would be weird to send off tiny JSONB blobs off to CloudKit in asset form. And second, we currently kind of hacked JSONB support into StructuredQueries so that we didn't have to maintain a dedicated JSONB encoder/decoder, but sadly that hack only gets us so far.Luckily we do have a plan for StructuredQueries 1.0 that will fix the latter (can see a stack of PRs we have here), and then the former problem can be dealt with in SQLiteData sometime soon too.
This also means that I don't think #572 is the right fix @mvanhorn. For now please do not use JSONB if you want to use the sync engine, and we will be updated
SyncEnginesoon to actually throw an error if it detects JSONB columns in any table.mvanhorn commentedon Nov 4, 2026
Makes sense, thanks for explaining the CKAsset and StructuredQueries side of it. Fine to close #572 then. Throwing an error for JSONB columns in synced tables sounds like the right guard until the 1.0 work lands.
lucamegh commentedon Nov 7, 2026
All right. Just one thing. In my project, I switched to JSONB precisely because SQLiteData synchronizes the JSONB field as
CKAssets, preventing myCKRecordfrom exceeding the 1 MB limit. In 1.0, it would be helpful to have a way to tell SQLiteData that a particular column should be converted toCKAssetrather than JSONB plain text.mbrandonw commentedon Nov 7, 2026
Yeah that is definitely planned.