> ## Documentation Index
> Fetch the complete documentation index at: https://docs.powersync.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Parameter Queries

> Define bucket parameters in Sync Rules using Parameter Queries with JWT claims and client parameters.

*Parameter Queries* allow [parameters](/sync/rules/overview#parameters) to be defined on a [bucket](/sync/rules/overview#bucket-definition) to group data.

Each [bucket](/sync/rules/overview#bucket-definition) 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](/sync/rules/client-parameters#usage))
* **Values From a Table/Collection** (in your source database)

<Note>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](/architecture/powersync-service#replication-from-the-source-database). During replication, parameter values are indexed for [efficient use](/architecture/powersync-service#bucket-system) in the sync process.</Note>

## 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](/sync/supported-sql#operators) to select values from it:

```sql theme={null}
request.jwt() ->> 'sub' -- the 'subject' of the JWT - same as `request.user_id()
```

As an example, Supabase Auth includes [various claims](https://supabase.com/docs/guides/auth/jwt-fields) in their JWTs:

```sql theme={null}
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:

```yaml theme={null}
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
```

<Accordion title="Legacy Syntax (`token_parameters`)">
  A legacy syntax for Parameter Queries used `token_parameters.user_id` to return the JWT subject. Example:

  ```yaml theme={null}
  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:

  ```yaml theme={null}
  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
  ```
</Accordion>

## Using Client Parameters

| Function               | Description                                                                  |
| ---------------------- | ---------------------------------------------------------------------------- |
| `request.parameters()` | Returns [Client Parameters](/sync/rules/client-parameters) as a JSON string. |

Example usage:

```sql theme={null}
request.parameters() ->> 'param' -- select Client Parameter named 'param'
```

For full details, see the dedicated page on [Client Parameters](/sync/rules/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:

```yaml theme={null}
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](/sync/supported-sql) for full details.

## Usage Examples

### Filter on Additional Columns/Fields

```yaml theme={null}
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

```yaml theme={null}
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

```yaml theme={null}
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>
  Note that [every bucket parameter must be used in every Data Query](/sync/rules/data-queries#every-data-query-must-use-every-bucket-parameter).
</Note>

```yaml theme={null}
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.

```yaml theme={null}
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
```

<Warning>
  Keep in mind that the total number of buckets per user should [remain limited](/sync/rules/organize-data-into-buckets#limit-on-number-of-buckets-per-client) (\<= 1,000 [by default](/resources/performance-and-limits)), so buckets should not be too granular.
</Warning>

For more advanced details on many-to-many relationships and join tables, see [this guide](/sync/rules/many-to-many-join-tables).

### Expanding JSON Array Into Multiple Parameters

Using the `json_each()` [function](/sync/supported-sql#functions) and `->` [operator](/sync/supported-sql#operators), we can expand a parameter that is a JSON array into multiple rows, thereby filtering by multiple parameter values:

```yaml theme={null}
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:

```yaml theme={null}
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
```

<Warning>
  Keep in mind that the total number of buckets per user should [remain limited](/sync/rules/organize-data-into-buckets#limit-on-number-of-buckets-per-client) (\<= 1,000 [by default](/resources/performance-and-limits)), so buckets should not be too granular.
</Warning>

### No Output Columns/Fields

A Parameter Query with no output columns may be specified to only sync the bucket to a subset of users.

```yaml theme={null}
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](/sync/rules/global-buckets)