Common Table Expressions (CTEs)

Reuse common query patterns across Sync Streams using Common Table Expressions (CTEs) to simplify configurations and reduce duplication.

When streams need reusable filtering logic, you can define it once in a Common Table Expression (CTE) and reference it in queries. CTEs can be defined at the stream level (scoped to one stream) or at the top level of your sync config (shared across all streams). This keeps stream definitions DRY and makes it easier to maintain. For the supported syntax of the with block and CTE rules, see Supported SQL — CTE and WITH syntax.

Why Use CTEs

Consider an app where users belong to organizations. Several tables need to filter by the user’s organizations:

# Without CTEs - repetitive and hard to maintain
streams:
  org_projects:
    query: |
      SELECT * FROM projects 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_repositories:
    query: |
      SELECT * FROM repositories 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_settings:
    query: |
      SELECT * FROM settings 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())

The same subquery appears three times. You can merge these into one stream and define the logic once as a stream-level CTE:

# With a CTE and multiple queries
streams:
  org_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN user_orgs
      - SELECT * FROM repositories WHERE org_id IN user_orgs
      - SELECT * FROM settings WHERE org_id IN user_orgs

If the membership logic changes, you update it in one place. When the streams cannot be merged (for example, because they have different auto_subscribe settings or separate subscription parameters), use a global CTE to share the logic across streams instead.

Stream-Level CTEs

Define CTEs in a with block inside a stream. Each CTE has a name and a SELECT query, and is available only within that stream:

streams:
  my_stream:
    with:
      cte_name: SELECT columns FROM table WHERE conditions
    query: SELECT * FROM some_table WHERE col IN cte_name

The CTE query can include any filtering logic, including parameters:

streams:
  user_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      active_projects: SELECT id FROM projects WHERE archived = false
    query: SELECT * FROM projects WHERE org_id IN user_orgs AND id IN active_projects

Global CTEs

When the same filtering logic is needed across streams that cannot be merged — for example because they have different auto_subscribe settings or different subscription parameters — define the CTE once in a top-level with block. All streams can then reference it without repeating the definition:

config:
  edition: 3

with:
  user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()

streams:
  # Auto-synced overview data
  org_overview:
    auto_subscribe: true
    queries:
      - SELECT * FROM organizations WHERE id IN user_orgs
      - SELECT * FROM team_members WHERE org_id IN user_orgs

  # On-demand project detail, subscribed per project
  project_detail:
    queries:
      - SELECT * FROM projects WHERE id = subscription.parameter('project_id') AND org_id IN user_orgs
      - SELECT * FROM tasks WHERE project_id = subscription.parameter('project_id')

If the membership logic changes, you update it in one place and all streams pick up the change.

A stream can still define its own local with block. Local CTEs take precedence over global CTEs when names conflict, so you can override a global CTE for a specific stream:

config:
  edition: 3

with:
  user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()

streams:
  admin_projects:
    # Local CTE overrides the global one for this stream
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id() AND role = 'admin'
    query: SELECT * FROM projects WHERE org_id IN user_orgs

Using CTEs in Queries

Once defined — either in a stream’s local with block or in the top-level global with block — use the CTE name in the stream’s query or queries. You can use it like a subquery or join it as if it were a table.

Short-hand syntax (when the CTE has exactly one column):

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

The short-hand IN cte_name is equivalent to IN (SELECT * FROM cte_name). If the CTE has more than one column, this form is an error; use explicit subquery or join syntax instead.

Explicit subquery syntax (when you need to select specific columns):

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 (SELECT org_id FROM user_orgs)

Join syntax (you can join a CTE as if it were a table). Only INNER JOIN is supported:

streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT projects.* FROM projects INNER JOIN user_orgs ON user_orgs.org_id = projects.org_id

Combining with Multiple Queries

CTEs work well with the queries feature (multiple queries per stream). This lets you share the CTE and keep all query results in one stream: the client only needs to manage one subscription instead of multiple.

streams:
  user_data:
    with:
      my_org: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN my_org
      - SELECT * FROM repositories WHERE org_id IN my_org
      - SELECT * FROM team_members WHERE org_id IN my_org

Complete Example

A full configuration using both global and stream-level CTEs. The accessible_projects logic is shared across streams via a global CTE; user_orgs is only needed by org_and_projects, so it stays stream-level:

config:
  edition: 3

# Global CTE - shared across all streams
with:
  accessible_projects: |
    SELECT id FROM projects
    WHERE org_id IN (SELECT org_id FROM org_memberships WHERE user_id = auth.user_id())
      OR id IN (SELECT project_id FROM project_shares WHERE shared_with = auth.user_id())

streams:
  # Organization-level data (auto-sync) - stream-level CTE + global CTE
  org_and_projects:
    auto_subscribe: true
    with:
      user_orgs: |
        SELECT org_id FROM org_memberships WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM organizations WHERE id IN user_orgs
      - SELECT * FROM projects WHERE id IN accessible_projects

  # Project details (on-demand) - uses only the global CTE
  project_details:
    queries:
      - |
        SELECT * FROM tasks
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects
      - |
        SELECT * FROM files
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects

Limitations

The following rules apply to CTEs. For the full syntax reference, see Supported SQL — CTE and WITH syntax.

CTE names must not shadow source table names. This prevents hard-to-debug situations where a global CTE silently changes the behavior of a stream defined elsewhere in the config.

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

CTEs cannot reference other CTEs. Each CTE must be self-contained:

# This won't work - cte2 cannot reference cte1
streams:
  my_stream:
    with:
      cte1: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      cte2: SELECT id FROM projects WHERE org_id IN cte1  # Error!

If you need to chain filters, use nested subqueries in your stream query instead:

streams:
  tasks:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: |
      SELECT * FROM tasks
      WHERE project_id IN (
        SELECT id FROM projects WHERE org_id IN user_orgs
      )

The short-hand IN cte_name works only when the CTE has exactly one column. If the CTE has multiple columns, use explicit subquery syntax or join the CTE as a table.

Stream-level CTE names take precedence over table/collection names. If you define a stream-level CTE with the same name as a database table/collection, the CTE is used within that stream. Choose distinct names to avoid confusion.