Parameter Queries

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:

Function	Description
`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

Legacy Syntax (`token_parameters`)

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:

token_parameters.user_id references can simply be updated to request.user_id()
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'
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

Function	Description
`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

Get Started

Configuration Details

Sync Streams & Rules

Client SDKs

Handling Writes

Maintenance & Ops

Integrations

Tools

Resources

Using Authentication Parameters

Using Client Parameters

Using Values From a Table/Collection

Supported SQL

Usage Examples

Filter on Additional Columns/Fields

Group According to Different Columns/Fields

Using Different Tables/Collections for Parameters

Multiple Columns/Fields

Using a Join Table/Collection

Expanding JSON Array Into Multiple Parameters

Multiple Parameter Queries

No Output Columns/Fields

No Parameter Query

​Using Authentication Parameters

​Using Client Parameters

​Using Values From a Table/Collection

​Supported SQL

​Usage Examples

​Filter on Additional Columns/Fields

​Group According to Different Columns/Fields

​Using Different Tables/Collections for Parameters

​Multiple Columns/Fields

​Using a Join Table/Collection

​Expanding JSON Array Into Multiple Parameters

​Multiple Parameter Queries

​No Output Columns/Fields

​No Parameter Query

Using Authentication Parameters

Using Client Parameters

Using Values From a Table/Collection

Supported SQL

Usage Examples

Filter on Additional Columns/Fields

Group According to Different Columns/Fields

Using Different Tables/Collections for Parameters

Multiple Columns/Fields

Using a Join Table/Collection

Expanding JSON Array Into Multiple Parameters

Multiple Parameter Queries

No Output Columns/Fields

No Parameter Query