For tables where the client will create new rows:
  • Postgres and MySQL: use a UUID for id. Use the uuid() helper to generate a random UUID (v4) on the client.
  • MongoDB: use an ObjectId for _id. Generate an ObjectId() in your app code and store it in the client’s id column as a string; this will map to MongoDB’s _id.
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 Sync Rule’s data queries when using MongoDB as the backend source database. When inserting new documents from the client, prefer ObjectId values for _id (stored in the client’s id column).
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
If you want to upload data to a table with a custom record ID, ensure that uploadData() isn’t blindly using a field named id when handling CRUD operations. See the Sequential ID mapping tutorial for an example where the record ID is aliased to uuid on the backend.
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 a UUID as a secondary ID. This mapping must be performed wherever the UUIDs are referenced, including for every foreign key column. For more information, have a look at the Sequential ID Mapping tutorial.