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.

Note: token_parameters are the claims in the JWT returned from fetchCredentials()

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.