Custom Types, Arrays and JSON

PowerSync is compatible with advanced Postgres types, including arrays and JSON/JSONB. These types are represented as text columns in the client-side schema. When updating client data, you have the option to replace the entire column value with a string or enable advanced schema features to track more granular changes and include custom metadata.

​

Advanced Schema Options to Process Writes

With arrays and JSON fields, it’s common for only part of the value to change during an update. To make handling these writes easier, you can enable advanced schema options that let you track exactly what changed in each row—not just the new state.

• trackPreviousValues: Access previous values for diffing custom types, arrays, or JSON fields. Accessible later via CrudEntry.previousValues.
• trackMetadata: Adds a _metadata column for storing custom metadata. Value of the column is accessible later via CrudEntry.metadata.
• ignoreEmptyUpdates: Skips updates when no data has actually changed.

​

Custom Types

PowerSync serializes custom types as text. For details, see types in sync rules.

​

Postgres

Postgres allows developers to create custom data types for columns. For example:

create type location_address AS (
    street text,
    city text,
    state text,
    zip numeric
);

​

Sync Rules

Custom type columns are converted to text by the PowerSync Service. A column of type location_address, as defined above, would be synced to clients as the following string:

("1000 S Colorado Blvd.",Denver,CO,80211)

It is not currently possible to extract fields from custom types in Sync Rules, so the entire column must be synced as text.

​

Client SDK

Schema

Add your custom type column as a text column in your client-side schema definition. For advanced update tracking, see Advanced Schema Options.

Table(
  name: 'todos',
  columns: [
    Column.text('location'),
    // ... other columns ...
  ],

  // Optionally, enable advanced update tracking options:
  trackPreviousValues: true, 
  trackMetadata: true, 
  ignoreEmptyUpdates: true, 
)

Writing Changes

You can write the entire updated column value as a string, or, with trackPreviousValues enabled, compare the previous and new values to process only the changes you care about:

// Full replacement (basic):
await db.execute('UPDATE todos set location = ? WHERE id = ?', [
  '("1234 Update Street",Denver,CO,80212)',
  'faffcf7a-75f9-40b9-8c5d-67097c6b1c3b'
]);

// Advanced: Diffing custom types in uploadData
if (op.op == UpdateType.put && op.previousValues != null) {
  final oldCustomType = op.previousValues['location'] ?? 'null';
  final newCustomType = op.opData['location'] ?? 'null';
  // Compare oldCustomType and newCustomType to determine what changed
}

​

Arrays

PowerSync treats array columns as JSON text. This means that the SQLite JSON operators can be used on any array columns.

Additionally, some helper methods such as array membership are available in Sync Rules.

Note: Native Postgres arrays, JSON arrays, and JSONB arrays are effectively all equivalent in PowerSync.

​

Postgres

Array columns are defined in Postgres using the following syntax:

ALTER TABLE todos

ADD COLUMN unique_identifiers text[];

​

Sync Rules

Array columns are converted to text by the PowerSync Service. A text array as defined above would be synced to clients as the following string:

["00000000-0000-0000-0000-000000000000", "12345678-1234-1234-1234-123456789012"]

Array Membership

It’s possible to sync rows dynamically based on the contents of array columns using the IN operator. For example:

bucket_definitions:
  custom_todos:
    # Separate bucket per To-Do list
    parameters: SELECT id AS list_id FROM lists WHERE owner_id = request.user_id()
    data:
      - SELECT * FROM todos WHERE bucket.list_id IN unique_identifiers

​

Client SDK

Schema

Add your array column as a text column in your client-side schema definition. For advanced update tracking, see Advanced Schema Options.

Table(
  name: 'todos',
  columns: [
    Column.text('unique_identifiers'),
    // ... other columns ...
  ],

  // Optionally, enable advanced update tracking options:
  trackPreviousValues: true, 
  trackMetadata: true, 
  ignoreEmptyUpdates: true, 
)

Writing Changes

You can write the entire updated column value as a string, or, with trackPreviousValues enabled, compare the previous and new values to process only the changes you care about:

// Full replacement (basic):
await db.execute('UPDATE todos set unique_identifiers = ? WHERE id = ?', [
  '["DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF", "ABCDEFAB-ABCD-ABCD-ABCD-ABCDEFABCDEF"]',
  '00000000-0000-0000-0000-000000000000'
]);

// Advanced: Diffing arrays in uploadData
if (op.op == UpdateType.put && op.previousValues != null) {
  final oldArray = jsonDecode(op.previousValues['unique_identifiers'] ?? '[]');
  final newArray = jsonDecode(op.opData['unique_identifiers'] ?? '[]');
  // Compare oldArray and newArray to determine what changed
}

​

JSON and JSONB

The PowerSync Service treats JSON and JSONB columns as text and provides many helpers for working with JSON in Sync Rules.

Note: Native Postgres arrays, JSON arrays, and JSONB arrays are effectively all equivalent in PowerSync.

​

Postgres

JSON columns are represented as:

ALTER TABLE todos
ADD COLUMN custom_payload json;

​

Sync Rules

PowerSync treats JSON columns as text and provides transformation functions in Sync Rules such as json_extract().

bucket_definitions:
  my_json_todos:
    # Separate bucket per To-Do list
    parameters: SELECT id AS list_id FROM lists WHERE owner_id = request.user_id()
    data:
      - SELECT * FROM todos WHERE json_extract(custom_payload, '$.json_list') = bucket.list_id

​

Client SDK

Schema

Add your JSON column as a text column in your client-side schema definition. For advanced update tracking, see Advanced Schema Options.

Table(
  name: 'todos',
  columns: [
    Column.text('custom_payload'),
    // ... other columns ...
  ],
  // Optionally, enable advanced update tracking options:
  trackPreviousValues: true, 
  trackMetadata: true, 
  ignoreEmptyUpdates: true, 
)

Writing Changes

You can write the entire updated column value as a string, or, with trackPreviousValues enabled, compare the previous and new values to process only the changes you care about:

// Full replacement (basic):
await db.execute('UPDATE todos set custom_payload = ? WHERE id = ?', [
  '{"foo": "bar", "baz": 123}',
  '00000000-0000-0000-0000-000000000000'
]);

// Advanced: Diffing JSON in uploadData
import 'dart:convert';

if (op.op == UpdateType.put && op.previousValues != null) {
  var oldJson = jsonDecode(op.previousValues['custom_payload'] ?? '{}');
  var newJson = jsonDecode(op.opData['custom_payload'] ?? '{}');
  // Compare oldJson and newJson to determine what changed
}

​

Bonus: Mashup

What if we had a column defined as an array of custom types, where a field in the custom type was JSON? Consider the following Postgres schema:

-- define custom type
CREATE TYPE extended_location AS (
    address_label text,
    json_address json
);

-- add column
ALTER TABLE todos
ADD COLUMN custom_locations extended_location[];