Skip to main content
This page documents the SQL supported in Sync Streams and Sync Rules (legacy).
Some fundamental restrictions on the usage of SQL expressions are:
  1. They must be deterministic — no random or time-based functions.
  2. No external state can be used.
  3. They must operate on data available within a single row/document. For example, no aggregation functions are allowed.
For parameter-specific WHERE restrictions, see Filtering: WHERE Clause.

​
Query Syntax

The supported SQL is based on a subset of the standard SQL syntax. Sync Streams support more SQL features than the legacy Sync Rules.
Not supported: aggregation, sorting, or set operations (GROUP BY, ORDER BY, LIMIT, UNION, etc.). See Writing Queries for details.

​
Filtering: WHERE Clause

Sync queries support a subset of SQL WHERE syntax. Allowed operators and combinations differ between Sync Streams and Sync Rules, and are more restrictive than standard SQL.
= and IS NULL — Compare a row column to a static value, a parameter, or another column:
-- Static value
WHERE status = 'active'
WHERE deleted_at IS NULL

-- Parameter (auth, connection, or subscription)
WHERE owner_id = auth.user_id()
WHERE region = connection.parameter('region')
AND — Fully supported. You can mix parameter comparisons, subqueries, and row-value conditions in the same clause.
-- Two parameter conditions
WHERE owner_id = auth.user_id()
  AND org_id = auth.parameter('org_id')

-- Parameter condition + row-value condition
WHERE owner_id = auth.user_id()
  AND status = 'active'

-- Parameter condition + subquery
WHERE list_id = subscription.parameter('list_id')
  AND list_id IN (SELECT id FROM lists WHERE owner_id = auth.user_id())
OR — Supported, including OR nested inside AND. PowerSync rewrites combinations like A AND (B OR C) into separate branches before evaluating. Each OR branch must be a valid filter on its own; you cannot have a branch that only makes sense when combined with the other.
-- Top-level OR
WHERE owner_id = auth.user_id()
   OR shared_with = auth.user_id()

-- OR nested inside AND
WHERE status = 'active'
  AND (owner_id = auth.user_id() OR shared_with = auth.user_id())
NOT — Supported for simple conditions on row values. NOT IN with a literal set of values is supported: use a JSON array string (e.g. '["draft", "hidden"]'), or the ARRAY['draft', 'hidden'] and ROW('draft', 'hidden') forms. You cannot negate a subquery or a parameter array expansion.
-- Simple row-value conditions
WHERE status != 'archived'
WHERE deleted_at IS NOT NULL

-- NOT IN with JSON array string (any of these forms)
WHERE category NOT IN '["draft", "hidden"]'
WHERE category NOT IN ARRAY['draft', 'hidden']
WHERE category NOT IN ROW('draft', 'hidden')

-- Not supported: negating a subquery
-- WHERE issue_id NOT IN (SELECT id FROM issues WHERE owner_id = auth.user_id())

-- Not supported: negating a parameter array
-- WHERE id NOT IN subscription.parameter('excluded_ids')

​
Operators

Operators can be used in WHERE clauses and in SELECT expressions. When filtering on parameters (e.g. auth.user_id(), subscription.parameter('id')), some combinations are restricted — see Filtering: WHERE Clause.

Comparison and null

  • Comparison: =, !=, <, >, <=, >= — If either side is null, the result is null.
  • Null: IS NULL, IS NOT NULL
  • Logical: AND, OR, NOT — See Filtering: WHERE Clause for restrictions when filtering on parameters.
  • Mathematical: +, -, *, /
  • || — Joins two text values together.
  • json -> 'path' - Returns the value as a JSON string.
  • json ->> 'path' — Returns the extracted value.
  • Sync Streams: left IN right — left can be a row column and right a parameter array (e.g. id IN subscription.parameter('ids')), or left a parameter and right a row JSON array column. Also supports subqueries: id IN (SELECT ...).
  • Sync Rules: Returns true if left is in the right JSON array. In Data Queries, left must be a row column and right cannot be a bucket parameter. In Parameter Queries, either side may be a parameter.
  • x BETWEEN a AND b, x NOT BETWEEN a AND b — True if x is in the inclusive range [a, b]. Usable in WHERE or as a SELECT expression. If any operand is null, the result is null.
Example: WHERE price BETWEEN 10 AND 100
Supported in Sync Streams only. Not available in Sync Rules.
  • <left> && <right> — True if the JSON array in left and the set right share at least one value. Use when the row stores an array (e.g. a tagged_users column). left must be a row column (JSON array); right must be a subquery or parameter array.
Example: WHERE tagged_users && (SELECT id FROM org_members WHERE org_id = auth.parameter('org_id'))Use IN when the row has a single value to check against a set; use && when the row has an array and you want to match any element.
Supported in Sync Streams only. Not available in Sync Rules.

​
Functions

Functions can be used to transform columns/fields before being synced to a client. They operate on row data or parameters. Type names below (text, integer, real, blob, null) refer to SQLite storage classes. Most functions are from SQLite built-in functions and SQLite JSON functions.
  • upper(text) — Convert text to upper case.
  • lower(text) — Convert text to lower case.
  • substring(text, start, length) — Extracts a portion of a string based on specified start index and length. Start index is 1-based. Example: substring(created_at, 1, 10) returns the date portion of the timestamp.
  • hex(data) — Convert blob or text data to hexadecimal text.
  • base64(data) — Convert blob or text data to base64 text.
  • length(data) — For text, return the number of characters. For blob, return the number of bytes. For null, return null. For integer and real, convert to text and return the number of characters.
  • json_each(data) — Expands a JSON array into rows.
    • Sync Streams: Works with auth and connection parameters (e.g. JOIN json_each(auth.parameter('ids')) AS t or WHERE id IN (SELECT value FROM json_each(auth.parameter('ids')))). Can also be used with columns from joined tables in some cases (e.g. SELECT * FROM lists WHERE id IN (SELECT lists.value FROM access_control a, json_each(a.allowed_lists) as lists WHERE a.user = auth.user_id())). See Expanding JSON arrays.
    • Sync Rules: Expands a JSON array or object from a request or token parameter into a set of parameter rows. Example: SELECT value AS project_id FROM json_each(request.jwt() -> 'project_ids').
  • json_extract(data, path) — Same as ->> operator, but the path must start with $.
  • json_array_length(data) — Given a JSON array (as text), returns the length of the array. If data is null, returns null. If the value is not a JSON array, returns 0.
  • json_valid(data) — Returns 1 if the data can be parsed as JSON, 0 otherwise.
  • json_keys(data) — Returns the set of keys of a JSON object as a JSON array. Example: SELECT * FROM items WHERE bucket.user_id IN json_keys(permissions_json).
  • ifnull(x, y) — Returns x if non-null, otherwise returns y.
  • iif(x, y, z) — Returns y if x is true, otherwise returns z.
  • unixepoch(time-value, [modifier]) — Returns a time-value as Unix timestamp. If modifier is “subsec”, the result is a floating point number, with milliseconds included in the fraction. The time-value argument is required — this function cannot be used to get the current time.
  • datetime(time-value, [modifier]) — Returns a time-value as a date and time string, in the format YYYY-MM-DD HH:MM:SS. If the specifier is “subsec”, milliseconds are also included. If the modifier is “unixepoch”, the argument is interpreted as a Unix timestamp. Both modifiers can be included: datetime(timestamp, 'unixepoch', 'subsec'). The time-value argument is required — this function cannot be used to get the current time.
  • uuid_blob(id) — Convert a UUID string to bytes.
If you need an operator or function not listed, contact us so we can consider adding it.

​
JOIN Syntax

Supported in Sync Streams only. Not available in Sync Rules.
Sync Streams support a subset of join syntax. The following rules define what is valid:
  • Only inner joins: Use JOIN or INNER JOIN. LEFT, RIGHT, and OUTER joins are not supported.
  • Single output table: All selected columns must come from one table. Use table.* or list columns from that table (e.g. comments.*, comments.id). Selecting columns from multiple tables is invalid.
  • Simple join conditions: Join conditions must be equality comparisons of the form table1.column = table2.column. Other comparisons (e.g. a.x > b.y) are not supported.
  • Table-valued functions in JOINs: json_each() and similar functions work with auth or connection parameters (e.g. json_each(auth.parameter('ids'))). They can also be used with columns from joined tables in some cases.
-- Valid: columns from one table
SELECT comments.* FROM comments INNER JOIN issues ON comments.issue_id = issues.id

-- Invalid: columns from multiple tables
SELECT comments.*, issues.title FROM comments JOIN issues ON comments.issue_id = issues.id

-- Invalid: non-equality join condition
SELECT * FROM a JOIN b ON a.x > b.y
For how to use JOINs in your stream queries (when to use them, patterns, and examples), see Using Joins.

​
CTE and WITH Syntax

Supported in Sync Streams only. Not available in Sync Rules.
Common Table Expressions (CTEs) are defined in a with: block inside a stream. Each CTE is a name and a single SELECT query. The following rules apply:
  • CTEs cannot reference other CTEs. Each CTE must be self-contained. To chain logic (e.g. orgs → projects), use nested subqueries in your stream query and reference only the CTE at the leaf level.
  • CTE names take precedence over table names. If a CTE has the same name as a database table, the CTE is used. Use distinct names to avoid confusion.
  • Short-hand IN cte_name works only when the CTE has exactly one column.
# Valid: CTE in a stream
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT * FROM projects WHERE org_id IN user_orgs

# Invalid: CTE referencing another CTE
# streams:
#   my_stream:
#     with:
#       user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
#       project_ids: SELECT id FROM projects WHERE org_id IN user_orgs  # Error
For how to use CTEs, see Common Table Expressions (CTEs).

​
CASE Expressions

Supported in Sync Streams only. Not available in Sync Rules.
CASE is allowed anywhere an expression is allowed — in SELECT columns or WHERE clauses. Searched CASE — Each WHEN is an independent boolean condition: 
CASE
  WHEN <condition> THEN <result>
  WHEN <condition> THEN <result>
  ELSE <default>
END

-- Compute a label based on a column value
SELECT id,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 70 THEN 'B'
    ELSE 'C'
  END AS grade
FROM results
Simple CASE — Compares one expression against a list of values: 
CASE <expression>
  WHEN <value> THEN <result>
  WHEN <value> THEN <result>
  ELSE <default>
END

-- Map numeric status codes to readable labels
SELECT id,
  CASE status
    WHEN 1 THEN 'pending'
    WHEN 2 THEN 'active'
    WHEN 3 THEN 'closed'
    ELSE 'unknown'
  END AS status_label
FROM tasks
ELSE is optional. If omitted and no WHEN matches, the result is null.