Parameter Queries

Parameter queries allow parameters to be defined on a bucket to group data. These queries can use parameters from the JWT (we loosely refer to these as token parameters), such as a user_id, or parameters from clients directly.

New syntax

An improved syntax for token parameters and additional functionality is currently being rolled out. The previous syntax (see second tab) is still fully functional, but we recommend updating your sync rules to this new syntax.

Examples throughout these docs will be updated soon. Refer to the below to understand the changes.

The new functions available in sync rules are:

1. request.jwt(): Returns the entire (signed) JWT payload as a JSON string.

2. request.parameters(): Returns client parameters as a JSON string.

3. request.user_id(): Returns the token subject, same as request.jwt() ->> 'sub' and also the same as token_parameters.user_id in the previous syntax.

The major differences to the previous token_parameters is that the entire payloads are preserved as-is, which can make usage a little more intuitive. This also includes JWT payload fields not previously accessible.

Example usage:

request.jwt() ->> 'sub' -- Same as token_parameters.user_id. Makes it clear which JWT field is used.
request.user_id() -- Same as `request.jwt() ->> 'sub'` and `token_parameters.user_id`
request.parameters() ->> 'param' -- Client parameters

-- Some Supabase-specific examples below. These can be used with standard Supabase tokens, 
-- for use cases which previously required custom tokens
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)

Migrating to the new syntax:

1. token_parameters.user_id references can simply be updated to request.user_id()

2. Custom parameters can be updated from token_parameters.my_custom_field to request.jwt() ->> 'parameters.my_custom_field'

   1. This example applies if you keep your existing custom JWT as is.

   2. Supabase users can now make use of Supabase's standard JWT structure and reference app_metadata.my_custom_field directly.

Complete sync rules example:

bucket_definitions:
  by_user_parameter:
    # Use token parameters directly
    # 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
      
  by_user_table:
    # Similar query, but using a table
    # Access can instantly be revoked by deleting the user row
    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

Previous syntax

Sync rules example using token_parameters:

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
    # Access can instantly be revoked by deleting the user row
    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. Using this form has the advantage of being able to instantly revoke access by deleting the user row.

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 limited set of operators and functions are supported — see Operators and Functions.