On the client, PowerSync only supports a single primary key column called
id, of type
For tables where the client will create new rows, we recommend using an UUID for the id. We provide a helper function
uuid()to generate a random UUID (v4) on the client.
To use a different column from the server-side database as the record ID on the client, use a column alias:
SELECT client_id as id FROM my_data
Custom transformations could also be used for the id, for example:
-- Convert an integer id to text
SELECT id :: text as id FROM my_data
-- 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, 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 an 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.
With auto-incrementing / sequential IDs (e.g.
sequencetype in PostgreSQL), 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.
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.
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.
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