Skip to main content
Parameter Queries allow parameters to be defined on a bucket to group data. Each bucket can have zero or more Parameter Queries. Parameter Queries can return multiple rows/documents. The values selected in each row/document become parameters for the bucket. The following values can be selected in Parameter Queries:
  • Authentication Parameters, which come from the JWT token.
  • Client Parameters, which are passed directly from clients (specified at connection)
  • Values From a Table/Collection (in your source database)
Parameter Queries are not run directly on your source database. Instead, the Parameter Queries in your Sync Rules are used to pre-process rows/documents as they are replicated from your source database. During replication, parameter values are indexed for efficient use in the sync process.

​
Using Authentication Parameters

The following functions allow you to select Authentication Parameters in your Parameter Queries:
FunctionDescription
request.user_id()Returns the JWT subject (sub). Same as request.jwt() ->> 'sub' (see below)
request.jwt()Returns the entire (signed) JWT payload as a JSON string. If there are other claims in your JWT (in addition to the user ID), you can select them from this JSON string.
Since request.jwt() is a string containing JSON, use the ->> operator to select values from it: 
request.jwt() ->> 'sub' -- the 'subject' of the JWT - same as `request.user_id()
As an example, Supabase Auth includes various claims in their JWTs: 
request.jwt() ->> 'role' -- 'authenticated' or 'anonymous'
request.jwt() ->> 'email' -- automatic email field
request.jwt() ->> 'app_metadata.custom_field' -- custom field added by a service account (authenticated)
This is a simple example of Sync Rules with a single bucket definition with a Parameter Query that selects the user ID from the JWT: 
bucket_definitions:
  # Bucket Name
  user_lists:
    # Parameter Query
    parameters: SELECT request.user_id() as user_id
    # Data Query
    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id
A legacy syntax for Parameter Queries used token_parameters.user_id to return the JWT subject. Example:
bucket_definitions:
  by_user_parameter:
    parameters: SELECT token_parameters.user_id as user_id
    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id
That legacy syntax also allowed custom claims from the JWT, but only if they were nested under a parameters claim in the JWT.If you are still using this legacy syntax, you can migrate to the current syntax as follows:
  1. token_parameters.user_id references can simply be updated to request.user_id()
  2. For custom parameters, if you keep your custom JWT in the format required by the legacy syntax, you can update token_parameters.my_custom_field references to request.jwt() ->> 'parameters.my_custom_field'
  3. Alternatively, you can get custom parameters directly from the JWT payload/claims, e.g. request.jwt() ->> 'my_custom_field'
Example:
bucket_definitions:
  by_user_parameter:
    # request.user_id() is the same as the previous token_parameter.user_id
    parameters: SELECT request.user_id() as user_id
    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id

​
Using Client Parameters

FunctionDescription
request.parameters()Returns Client Parameters as a JSON string.
Example usage: 
request.parameters() ->> 'param' -- select Client Parameter named 'param'
For full details, see the dedicated page on Client Parameters.

​
Using Values From a Table/Collection

A Parameter Query can select a parameter from a table/collection in your source database, for example: 
bucket_definitions:
  user_lists_table:
    # This is similar to the 'user_lists' example above, but with the advantage that access
    # can instantly be revoked by deleting the user row/document from the source database:
    parameters: SELECT id as user_id FROM users WHERE users.id = request.user_id()
    data:
      - SELECT * FROM lists WHERE lists.user_id = bucket.user_id

​
Supported SQL

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

​
Usage Examples

​
Filter on Additional Columns/Fields

bucket_definitions:
  admin_users:
    parameters: |
        SELECT id as user_id FROM users WHERE
           users.id = request.user_id() AND
           users.is_admin = true

    data:
      - SELECT * FROM lists WHERE lists.owner_id = bucket.user_id

​
Group According to Different Columns/Fields

bucket_definitions:
  primary_list:
    parameters: |
        SELECT primary_list_id FROM users WHERE users.id = request.user_id()
    data:
      - SELECT * FROM todos WHERE todos.list_id = bucket.primary_list_id

​
Using Different Tables/Collections for Parameters

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

​
Multiple Columns/Fields

Parameter Queries may select multiple columns/fields 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 = request.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 a Join Table/Collection

In this example, the Parameter Query can return multiple rows/documents, resulting in multiple sets of bucket parameters for a single user. 
bucket_definitions:
  user_lists:
    parameters: |
        SELECT list_id FROM user_lists WHERE user_lists.user_id = request.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 by default), so buckets should not be too granular.
For more advanced details on many-to-many relationships and join tables, see this guide.

​
Expanding JSON Array Into Multiple Parameters

Using the json_each() function and -> operator, we can expand a parameter that is a JSON array into multiple rows, thereby filtering by multiple parameter values: 
bucket_definitions:
  user_projects:
    parameters: SELECT project_id FROM json_each(request.jwt() -> 'project_ids')
    data:
      - SELECT * FROM projects WHERE id = bucket.project_id

​
Multiple Parameter Queries

Multiple Parameter Queries can be used in the same bucket definition, however, the output columns must be exactly the same for each of these Parameter Queries: 
bucket_definitions:
  user_lists:
    parameters:
      - SELECT id as list_id FROM lists WHERE owner_id = request.user_id()
      - SELECT list_id FROM user_lists WHERE user_lists.user_id = request.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 by default), so buckets should not be too granular.

​
No Output Columns/Fields

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 = request.user_id() AND
           users.is_admin = true

    data:
      - SELECT * FROM admin_settings

​
No Parameter Query

Any bucket with no Parameter Query in the bucket definition is automatically a Global Bucket. These buckets will be synced to all clients/users. See Global Buckets