Client ID

On the client, PowerSync only supports a single primary key column called id, of type text.

For tables where the client will create new rows, we recommend using a UUID for the ID. We provide a helper function uuid() to generate a random UUID (v4) on the client.

To use a different column/field from the server-side database as the record ID on the client, use a column/field alias in your Sync Rules:

SELECT client_id as id FROM my_data

MongoDB uses _id as the name of the ID field in collections. Therefore, PowerSync requires using SELECT _id as id in the data queries when using MongoDB as the backend source database.

Custom transformations could also be used for the ID, for example:

-- Concatenate multiple columns into a single id column
SELECT org_id || '.' || record_id as id FROM my_data

PowerSync does not perform any validation that IDs are unique. Duplicate IDs on a client could occur in any of these scenarios:

1. A non-unique column is used for the ID.

2. Multiple table partitions are used (Postgres), with the same ID present in different partitions.

3. Multiple data queries returning the same record. This is typically not an issue if the queries return the same values (same transformations used in each query).

We recommend using a unique index on the fields in the source database to ensure uniqueness — this will prevent (1) at least.

If the client does sync multiple records with the same ID, only one will be present in the final database. This would typically be the one modified last, but this is subject to change — do not depend on any specific record being picked.

Postgres: Strategies for Auto-Incrementing IDs

With auto-incrementing / sequential IDs (e.g. sequence type in Postgres), the issue is that the ID can only be generated on the server, and not on the client while offline. If this must be used, there are some options, depending on the use case.

Option 1: Generate ID when server receives record

If the client does not use the ID as a reference (foreign key) elsewhere, insert any unique value on the client in the id field, then generate a new ID when the server receives it.

Option 2: Pre-create records on the server

For some use cases, it could work to have the server pre-create a set of e.g. 100 draft records for each user. While offline, the client can populate these records without needing to generate new IDs. This is similar to providing an employee with a paper book of blank invoices — each with an invoice number pre-printed.

This does mean that a user has a limit on how many records can be populated while offline.

Care must be taken if a user can populate the same records from different devices while offline — ideally each device must have a unique set of pre-created records.

Option 3: Use an ID mapping

Use UUIDs on the client, then map them to sequential IDs when performing an update on the server. This allows using a sequential primary key for each record, with an UUID as a secondary ID.

This mapping must be performed wherever the UUIDs are referenced, including for every foreign key column