Comment on page
Parameter Queries

Parameter queries select bucket parameters to group data. These queries can use token parameters such as user_id directly, or filter on table rows.
bucket_definitions:
  by_user_parameter:
    # Use token parameters directly
    parameters: SELECT token_parameters.user_id as user_id
    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id
      
  by_user_table:
    # Similar query, but using a table
    parameters: SELECT id as user_id FROM users WHERE users.id = token_parameters.user_id
    data:
      - SELECT * FROM lists WHERE lists.user_id = bucket.user_id
The second example above is similar to the first, except that it returns no results if the corresponding row in the users table does not exist. This is generally the preferred form.
Filter on additional columns
bucket_definitions:
  admin_users:
    parameters: |
        SELECT id as user_id FROM users WHERE
           users.id = token_parameters.user_id AND
           users.is_admin = true
        
    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id
Group according to different columns
bucket_definitions:
  primary_list:
    parameters: |
        SELECT primary_list_id FROM users WHERE
           users.id = token_parameters.user_id
    data:
      - SELECT * FROM todos WHERE todos.list_id = bucket.primary_list_id
Using different tables for parameters
bucket_definitions:
  owned_lists:
    parameters: |
        SELECT id as list_id FROM lists WHERE
           owner_id = token_parameters.user_id
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id
Using a join table
In this example, a single query can return multiple sets of bucket parameters for a single user.
Keep in mind that the total number of buckets per user should remain limited (< 1,000), so don't make buckets too granular.
bucket_definitions:
  user_lists:
    parameters: |
        SELECT list_id FROM user_lists WHERE
           user_lists.user_id = token_parameters.user_id
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id
Multiple bucket parameters
Parameter queries may return multiple bucket parameters.
Note that every bucket parameter must be used in every data query.
bucket_definitions:
  owned_org_lists:
    parameters: |
        SELECT id as list_id, org_id FROM lists WHERE
           owner_id = token_parameters.user_id
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id and lists.org_id = bucket.org_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id and todos.org_id = bucket.org_id
Using multiple parameter queries
Multiple parameter queries can be used in the same bucket definition.
It is important in this case that the output columns are exactly the same for each query in the bucket definition, as these define the bucket parameters.
bucket_definitions:
  user_lists:
    parameters:
      - SELECT id as list_id FROM lists WHERE owner_id = token_parameters.user_id
      - SELECT list_id FROM user_lists WHERE user_lists.user_id = token_parameters.user_id
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id
Keep in mind that the total number of buckets per user should remain limited (< 1,000), so don't make buckets too granular.
Global buckets
Global buckets are buckets with no bucket parameters. This means there is a single bucket for the bucket definition.
When no parameter query is specified, it is automatically a global bucket.
Alternatively, a parameter query with no output columns may be specified to only sync the bucket to a subset of users.
bucket_definitions:
  global_admins:
    parameters: |
        SELECT FROM users WHERE
           users.id = token_parameters.user_id AND
           users.is_admin = true
​
    data:
      - SELECT * FROM admin_settings
Restrictions
Parameter queries are not run directly on a database. Instead, the queries are used to pre-process rows as they are replicated, and index them for efficient use in the sync process.
The supported SQL is based on a small subset of the Postgres syntax.
Notable features and restrictions:
1.Only simple SELECT statements are supported.
2.No JOIN, GROUP BY or other aggregation, ORDER BY, LIMIT, or subqueries are supported.
3.For token parameters, only = operators are supported, and IN to a limited extent.
4.A small set of functions are operators are supported — see Operators and Functions.
Organize Data Into Buckets
Data Queries
Last modified 14h ago