Skip to main content
Data Queries select the data that form part of a bucket, using the bucket parameters. Multiple Data Queries can be specified for a single bucket definition.

​
Every Data Query Must Use Every Bucket Parameter

Data Queries are used to group data into buckets, so each Data Query must use every bucket parameter.
When PowerSync does incremental replication of data from your source database, it evaluates every row/document received on the , and computes a list of buckets that that row/document belongs to. This allows PowerSync to efficiently update only the specific buckets that are affected by each change event received. PowerSync uses the Data Queries in the Sync Rules bucket definitions to determine which rows/documents belong to which buckets. Therefore, if it was possible for a certain bucket parameter to not be used in the WHERE clause of a Data Query, the bucket IDs to which the row/document belongs would be ambiguous — we would have to assume “all possible values” for an ambiguous parameter value in the bucket ID – and the row/document would have to be exploded into many buckets. To avoid this, PowerSync imposes the constraint that every Data Query needs to use every parameter defined on the bucket.

​
Supported SQL

The supported SQL in Data Queries is based on a small subset of the SQL standard syntax. Not all SQL constructs are supported. See Supported SQL for full details.

​
Examples

​
Grouping by Parameter Query Values

bucket_definitions:
  owned_lists:
    parameters: |
        SELECT id as list_id FROM lists WHERE
           owner_id = request.user_id()
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id

​
Selecting Output Columns/Fields

When specific columns/fields are selected, only those columns/fields are synced to the client. This is good practice, to ensure the synced data does not unintentionally change when new columns are added to the schema (in the case of Postgres) or to the data structure (in the case of MongoDB). Note: An id column must always be present, and must have a text type. If the primary key is different, use a column alias and/or transformations to output a text id column. 
bucket_definitions:
  global:
    data:
      - SELECT id, name, owner_id FROM lists
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.

​
Renaming Columns/Fields

Different names (aliases) may be specified for columns/fields: 
bucket_definitions:
  global:
    data:
      - SELECT id, name, created_timestamp AS created_at FROM lists

​
Transforming Columns/Fields

A limited set of operators and functions are available to transform the output value of columns/fields. 
bucket_definitions:
  global:
    data:
      # Cast number to text
      - SELECT id, item_number :: text AS item_number FROM todos
      # Alternative syntax for the same cast
      - SELECT id, CAST(item_number as TEXT) AS item_number FROM todos
      # Convert binary data (bytea) to base64
      - SELECT id, base64(thumbnail) AS thumbnail_base64 FROM todos
      # Extract field from JSON or JSONB column
      - SELECT id, metadata_json ->> 'description' AS description FROM todos
      # Convert time to epoch number
      - SELECT id, unixepoch(created_at) AS created_at FROM todos