Data Queries
Data queries select the data that form part of a bucket, using the bucket parameters.
Multiple data queries can be specified for a single bucket definition.
Data queries are used to group data into buckets, so each data query must use every bucket parameter.
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
When specific columns are selected, only those columns are synchronized to the client.
This is good practice, to ensure the synced data does not unintentionally change when new columns are added to the schema.
Note: An
id column must always be present, and must have a text type. If the primary key is different, use a column alias and/or transformations to output a text id column.bucket_definitions:
global:
data:
- SELECT id, name, owner_id FROM lists
Different names (aliases) may be specified for columns:
bucket_definitions:
global:
data:
- SELECT id, name, created_timestamp as created_at FROM lists
A limited set of operators and functions are available to transform the output value of columns.
bucket_definitions:
global:
data:
# Cast number to text
- SELECT id, item_number :: text as item_number FROM todos
# Alternative syntax for the same cast
- SELECT id, CAST(item_number as TEXT) AS item_number FROM todos
# Convert binary data (bytea) to base64
- SELECT id, base64(thumbnail) AS thumbnail_base64 FROM todos
# Extract field from JSON or JSONB column
- SELECT id, metadata_json ->> 'description' AS description FROM todos
# Convert time to epoch number
- SELECT id, unixepoch(created_at) as created_at FROM todos
Last modified 1mo ago