[Proposal] Syncing binary data

[simolus3]

Motivation

Fundamentally, PowerSync evaluates and syncs data as JSON text. Because every part of the stack (from SQLite to the SDKs) has great JSON support, this is a reasonable decision to keep the protocol simple.
However, it also means that we can only sync the following SQLite data types:

1. null
2. Strings
3. Numbers (with the format carefully designed to avoid parsing JSON in JS so that we can distinguish between integers and doubles).

Notably missing is support for binary data (the BLOB type in SQLite). For larger binary data such as images, our recommendation is to store them out of the primary database, instead using references to attachment ids to drive that sync proces.

Especially for smaller binary data though, redesigning a database to use attachments is a significant change. Given that both SQLite and all source databases support binary data, the lack of support for binary data is a pain point for some users.
One workaround is to hex() data in sync stream definitions and to use raw tables / views to unhex() it on the client.

Status

2025-09-08: Proposal created

Feedback wanted!

We see that support for binary data is a popular request. As this proposal shows however, it requires changes to the entire PowerSync stack. And especially for Supabase, uploading binary data requires a custom procedure.
So, we're curious to hear from users interested in binary data:

1. How large is the data you intend to sync:
   a. If it's not small (say >1MB), why are attachment helpers not an option for you?
   b. If it is small, why is serializing binary data with hex / unhex not an option?
2. If you're using Supabase / PostgREST, how are you uploading binary data today?

Proposal

Handling binary data requires changes across the entire stack:

1. Bucket operations are stored as JSON in the storage layer. This would have to change to a format with support for binary data.
2. We currently support three transport mechanisms: JSON text over HTTP, BSON over HTTP and BSON over RSocket. JSON over HTTP does not support binary data.
3. In the client database, we store both the oplog history as well as materialized sync results as JSON text. SQLite supports a binary JSON representation, but that doesn't support binary values.
4. During local writes, we'd have to detect changes to binary data and record the write (which we're currently storing as JSON).
5. Finally, when iterating over CRUD transactions, we'd have to support binary data in the connectors.

Bucket storage

To store evaluated rows with binary data in them, JSON is not a viable option. Given that we're already using BSON in a few places, that seems like a reasonable alternative.
Our checksum calculation currently uses strings, but is fundamentally based on SHA-256 which natively works on bytes. Changing that means the checksum for identical rows would also change, but since this only affects newly-processed rows that may be fine (TODO: investigate).

Protocol changes

Since not all transport protocols (or clients) support binary data, this would have to be opt-in. Clients could set a flag in the sync request indicating that they're ready to process binary data, and then the sync service could detect whether a compatible transport mode is used. If that is the case, we'd sync BSON documents potentially containing binary data.

If not, we'd have to decode the documents, strip out binary values and re-encode them as JSON for older clients. This would make the sync process slightly more expensive on the sync service side.

Another option could be to split evaluated rows into two objects: A JSON-object containing non-binary data, and a BSON object for binary fields. For the majority of rows which don't contain binary data, no additional processing would be necessary with that mode. Clients would have to merge these values together, but since this requires client-side changes either way it shouldn't be a problem.

Sync client changes

On clients, we would like to keep using standard JSON values where possible. This:

• makes PowerSync databases more portable.
• makes databases easier to inspect.
• is faster, since JSON support builtin to SQLite contains a few optimizations we'd have to replicate.

Again, we could use the trick of keeping the JSON subset intact, and only introduce a separate BSON object for binary values (setting it to null for rows without binary data).

The ps_data tables would have to be migrated to add the binary column, which would then allow copying it from oplog entries in the sync_local process.

To expose binary data from PowerSync-managed views, we'd need a custom SQL function parsing and extracting from BSON.

To support binary data for raw tables, the "extract from column" source would have to take the separate binary value into account.

Another issue is what we'd do with mismatched client schemas. Today, a column defines as an int on the client can be interpreted from a synced text value because we insert a CAST operation parsing the value. If we split data into JSON and a binary object however, reading an int column would look up values from the JSON object. So if the actual value was a BLOB, the result would be NULL. Similarly, interpreting a value synced as text as a BLOB would return NULL because the value would not be present in the BSON column. This may be acceptable, but it's still a weird distinction we don't have for other types.

Local writes

PowerSync detects local writes with triggers inserting into ps_crud. Depending on table options, we'd either insert the entire updated row or just the updated fields.

Supporting binary data requires multiple changes to that table:

1. ps_crud: Similar to the other tables, this would need a BSON column for binary values in addition to the existing JSON column.
2. For tables with binary values, the triggers would have to be updated to potentially diff binary values as well (we're currently using a JSON-based function for that, which would have to be replaced).

CRUD representation

While we have a small BSON implementation in the core extension we need for binary sync lines, we'd like to avoid introducing BSON to all SDKs. So if binary values for local writes are represented as BSON, how do we load that into the SDKs?

We could introduce a helper that allows SDKs to interpret these values without a full BSON decoder, e.g.

SELECT ps_with_binary_offsets(data, binary_data) AS data, ps_concat_binary_values(binary_data) AS blob FROM ps_crud

-- data: {"name": "MyUser", "publicKey": {"offset": 0, "length": 4096}}
-- blob: <4096 bytes of data[0].publicKey>, ...

Since SDKs expose binary values as a Map<String, Object?>, introducing a Uint8List value would not be breaking.

Finally, a popular backend connector (also offered as a builtin API in some SDKs) is postgrest with Supabase. Postgrest does not support uploading binary data as-is. For inserts, postgrest also doesn't allow specifying a SQL transformation for columns (so we can't do a unhex($1)).
So this would mean that support for binary data ends at the connector, with users being responsible for writing a binary-aware connector if they want to upload those values.

[abdelhameedhamdy]

Feedback wanted!

We see that support for binary data is a popular request. As this proposal shows however, it requires changes to the entire PowerSync stack. And especially for Supabase, uploading binary data requires a custom procedure. So, we're curious to hear from users interested in binary data:

1. How large is the data you intend to sync:
   a. If it's not small (say >1MB), why are attachment helpers not an option for you?
   b. If it is small, why is serializing binary data with hex / unhex not an option?
2. If you're using Supabase / PostgREST, how are you uploading binary data today?

Thanks @simolus3 for the this important proposal and for the detailed clarification.

Well, my use case is based on spatial data through PostGIS and Spatialite extension in SQLite, which depends on Geometries as Binary data type.
Hexing/Unhexing is my current workaround, but it adds two issues here increasing the client db size, and inconsistent data types between postgis and sqlite, and the sql spatial operations between postgis and spatialite is different i.e. using spatial operations on geoms directly, in client an additional step is required for every spatial operation.

And as you mentioned, the binary upload would be through a separate rpc endpoint.

[rkistner]

Finally, a popular backend connector (also offered as a builtin API in some SDKs) is postgrest with Supabase. Postgrest does not support uploading binary data as-is. For inserts, postgrest also doesn't allow specifying a SQL transformation for columns (so we can't do a unhex($1)).

In theory, now that we support custom domains on Postgres, it should be possible to use that. You can essentially create a custom domain that persists data as a bytea, and automatically convert to/from hex when using the Supabase/Postgrest APIs. Of course, there's no way to transfer data directly as binary using those APIs.