> ## 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.

# Supported SQL

> Reference for SQL syntax, operators, built-in functions, and type casting supported in Sync Streams queries.

This guide explains the SQL supported in [Sync Streams](/sync/streams/overview): what you can write, with examples and restrictions.

For the exact syntax the compiler accepts, with railroad diagrams and grammar-rule references, see the [Sync Streams grammar reference](/sync/grammar/sync-streams/index).

<Warning>
  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](#filtering-where-clause).
</Warning>

## Query Syntax

The supported SQL is based on a subset of the standard SQL syntax:

* `SELECT` with column selection and [`WHERE` filtering](#filtering-where-clause)
* [Subqueries](/sync/streams/queries#using-subqueries) with `IN (SELECT ...)` and nested subqueries
* [`INNER JOIN`](#join-syntax) (selected columns must come from a single table)
* [Common Table Expressions (CTEs)](#cte-and-with-syntax) via the `with:` block
* Multiple queries per stream via `queries:`
* Table-valued functions such as `json_each()` for [expanding arrays](/sync/streams/parameters#expanding-json-arrays)
* `BETWEEN` and `CASE` expressions
* A limited set of [operators](#operators) and [functions](#functions)

**Not supported**: aggregation, sorting, or set operations (`GROUP BY`, `ORDER BY`, `LIMIT`, `UNION`, etc.). See [Writing Queries](/sync/streams/queries) for details.

## Filtering: WHERE Clause

Sync Streams queries support a subset of SQL `WHERE` syntax. Allowed operators and combinations are more restrictive than standard SQL.

**`=` and `IS NULL`** — Compare a row column to a static value, a parameter, or another column:

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

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

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

```sql theme={null}
-- 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](#filtering-where-clause).

<AccordionGroup>
  <Accordion title="Comparison and null" defaultOpen={true}>
    * **Comparison:** `=`, `!=`, `<`, `>`, `<=`, `>=` — If either side is `null`, the result is `null`.
    * **Null:** `IS NULL`, `IS NOT NULL`
  </Accordion>

  <Accordion title="Logical and mathematical">
    * **Logical:** `AND`, `OR`, `NOT` — See [Filtering: WHERE Clause](#filtering-where-clause) for restrictions when filtering on parameters.
    * **Mathematical:** `+`, `-`, `*`, `/`
  </Accordion>

  <Accordion title="Text concatenation">
    * `||` — Joins two text values together.
  </Accordion>

  <Accordion title="JSON">
    * `json -> 'path'` - Returns the value as a JSON string.
    * `json ->> 'path'` — Returns the extracted value.
  </Accordion>

  <Accordion title="IN (Arrays)">
    * `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 ...)`.
  </Accordion>

  <Accordion title="BETWEEN">
    * `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`
  </Accordion>

  <Accordion title="&& (Array overlap)">
    * `<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.
  </Accordion>
</AccordionGroup>

## 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](https://www.sqlite.org/datatype3.html).

Most functions are from [SQLite built-in functions](https://www.sqlite.org/lang_corefunc.html) and [SQLite JSON functions](https://www.sqlite.org/json1.html).

<AccordionGroup>
  <Accordion title="String and binary">
    * **[upper(text)](https://www.sqlite.org/lang_corefunc.html#upper)** — Convert text to upper case.
    * **[lower(text)](https://www.sqlite.org/lang_corefunc.html#lower)** — Convert text to lower case.
    * **[substring(text, start, length)](https://www.sqlite.org/lang_corefunc.html#substr)** — 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.
    * **[instr(string, substring)](https://www.sqlite.org/lang_corefunc.html#instr)** — Finds the first occurrence of the substring within the string and returns the number of prior characters plus 1, or 0 if the substring is not found. Useful for locating a delimiter in compound strings. For example, `substring(value, 1, instr(value, '|') - 1)` extracts the portion before a `|` character.
    * **[hex(data)](https://www.sqlite.org/lang_corefunc.html#hex)** — Convert blob or text data to hexadecimal text.
    * **base64(data)** — Convert blob or text data to base64 text.
    * **[length(data)](https://www.sqlite.org/lang_corefunc.html#length)** — 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.
  </Accordion>

  <Accordion title="Cast and types">
    * `CAST(x AS type)` or `x :: type` — Cast to `text`, `numeric`, `integer`, `real`, or `blob`. See [Type mapping](/sync/types) and [SQLite types](https://www.sqlite.org/datatype3.html).
    * **[typeof(data)](https://www.sqlite.org/lang_corefunc.html#typeof)** — Returns `text`, `integer`, `real`, `blob`, or `null`.
  </Accordion>

  <Accordion title="JSON">
    * **[json\_each(data)](https://www.sqlite.org/json1.html#jeach)** — Expands a JSON array into rows. 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/streams/parameters#expanding-json-arrays).
    * **[json\_extract(data, path)](https://www.sqlite.org/json1.html#jex)** — Same as `->>` operator, but the path must start with `$.`
    * **[json\_array\_length(data)](https://www.sqlite.org/json1.html#jarraylen)** — 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)](https://www.sqlite.org/json1.html#jvalid)** — 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 id, json_keys(permissions_json) AS permission_keys FROM items`.
  </Accordion>

  <Accordion title="Null handling">
    * **[ifnull(x, y)](https://www.sqlite.org/lang_corefunc.html#ifnull)** — Returns x if non-null, otherwise returns y.
  </Accordion>

  <Accordion title="Conditional">
    * **[iif(x, y, z)](https://www.sqlite.org/lang_corefunc.html#iif)** — Returns y if x is true, otherwise returns z.
  </Accordion>

  <Accordion title="Date/time and UUID">
    * **[unixepoch(time-value, \[modifier\])](https://www.sqlite.org/lang_datefunc.html)** — 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\])](https://www.sqlite.org/lang_datefunc.html)** — 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)](https://sqlite.org/src/file/ext/misc/uuid.c)** — Convert a UUID string to bytes.
  </Accordion>

  <Accordion title="Table metadata">
    These functions return metadata about the table a row comes from, rather than operating on a value. Prefix each call with the name or alias of a table in the query's `FROM` clause, the same way you would qualify a column reference: with `FROM "todos_%" AS todos`, write `todos.table_suffix()`. The functions take no arguments, and can appear in `WHERE` clauses, in selected columns, and inside subqueries.

    * **schema()** — Returns the schema the row was replicated from. Combined with a [wildcard schema](/sync/advanced/schemas-and-connections#wildcard-schemas-postgres), this allows filtering rows by their source schema, e.g. `SELECT * FROM "%".work_orders WHERE work_orders.schema() = auth.parameter('tenant_schema')`.
    * **table\_name()** — Returns the name of the table the row was replicated from. This is the source table's name, not the alias or the output table name.
    * **table\_suffix()** — Returns the part of the table name matched by the trailing `%` of a [wildcard table name](/sync/advanced/partitioned-tables). For example, with `FROM "todos_%" AS todos`, `todos.table_suffix()` returns `2024` for rows from the `todos_2024` table. On tables without a wildcard name, the result is always empty and the compiler reports a warning.

    <Info>Requires PowerSync Service v1.24.0 or later.</Info>
  </Accordion>

  <Accordion title="GIS (PostGIS)">
    * **[ST\_AsGeoJSON(geometry)](/client-sdks/advanced/gis-data-postgis)** — Convert [PostGIS](/client-sdks/advanced/gis-data-postgis) (in Postgres) geometry from WKB to GeoJSON. Combine with JSON operators to extract specific fields.
    * **[ST\_AsText(geometry)](/client-sdks/advanced/gis-data-postgis)** — Convert [PostGIS](/client-sdks/advanced/gis-data-postgis) (in Postgres) geometry from WKB to Well-Known Text (WKT).
    * **[ST\_X(point)](/client-sdks/advanced/gis-data-postgis)** — Get the X coordinate of a [PostGIS](/client-sdks/advanced/gis-data-postgis) point (in Postgres).
    * **[ST\_Y(point)](/client-sdks/advanced/gis-data-postgis)** — Get the Y coordinate of a [PostGIS](/client-sdks/advanced/gis-data-postgis) point (in Postgres).
  </Accordion>
</AccordionGroup>

If you need an operator or function not listed, [contact us](/resources/contact-us) so we can consider adding it.

## JOIN Syntax

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.

```sql theme={null}
-- Valid: columns from one table
SELECT comments.* FROM comments INNER JOIN issues ON comments.issue_id = issues.id

-- Valid: multiple equality conditions (composite keys)
SELECT comments.* FROM comments, issues
  WHERE comments.issue_id = issues.id
    AND comments.region = issues.region
    AND issues.user_id = auth.user_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](/sync/streams/queries#using-joins).

## CTE and WITH Syntax

Common Table Expressions (CTEs) can be defined in a `with:` block **inside a stream** (stream-level, scoped to that stream) or at the **top level** of the Sync Config (global, shared across all streams). Each CTE is a name and a single `SELECT` query. The following rules apply:

* **Stream-level CTEs take precedence over global CTEs.** If a stream defines a CTE with the same name as a global CTE, the stream-level definition is used within that stream.
* **Global CTE names must not shadow source table names.** If a global CTE has the same name as a database table or collection, PowerSync reports a validation error. Stream-level CTE names are not subject to this restriction.
* **Global CTEs require `config: edition: 3`.**
* **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.
* **Stream-level CTE names take precedence over table names** within that stream. Use distinct names to avoid confusion.
* **Short-hand `IN cte_name`** works only when the CTE has exactly one column.

```yaml theme={null}
config:
  edition: 3

# Global CTE — available to all streams
with:
  user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()

streams:
  # Stream-level CTE — scoped to this stream only; global CTE available here too
  org_data:
    with:
      active_projects: SELECT id FROM projects WHERE archived = false
    queries:
      - SELECT * FROM projects WHERE org_id IN user_orgs AND id IN active_projects
      - SELECT * FROM repositories WHERE org_id IN user_orgs
      - SELECT * FROM team_members WHERE org_id IN user_orgs

  # Different stream - still uses the same global CTE
  audit_logs:
    auto_subscribe: true
    query: SELECT * FROM audit_log 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)](/sync/streams/ctes).

## CASE Expressions

`CASE` is allowed anywhere an expression is allowed — in `SELECT` columns or `WHERE` clauses.

**Searched CASE** — Each `WHEN` is an independent boolean condition:

```sql theme={null}
CASE
  WHEN <condition> THEN <result>
  WHEN <condition> THEN <result>
  ELSE <default>
END
```

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

```sql theme={null}
CASE <expression>
  WHEN <value> THEN <result>
  WHEN <value> THEN <result>
  ELSE <default>
END
```

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