Sync Streams

​

Defining Streams

config:
  edition: 3

streams:
  todos:
    query: SELECT * FROM todos WHERE owner_id = auth.user_id()

config:
  edition: 3

streams:
  todos:
    query: SELECT * FROM todos WHERE owner_id = auth.user_id()

config:
  edition: 3

streams:
  <stream_name>:
    # CTEs (optional) - define with block inside each stream
    with:
      <cte_name>: SELECT ... FROM ...

    # Behavior options (place above query/queries)
    auto_subscribe: true    # Auto-subscribe clients on connect (default: false)
    priority: 1             # Sync priority (optional). Lower number -> higher priority
    accept_potentially_dangerous_queries: true  # Silence security warnings (default: false)

    # Query options (use one)
    query: SELECT * FROM <table> WHERE ...         # Single query
    queries:                                       # Multiple queries
      - SELECT * FROM <table_a> WHERE ...
      - SELECT * FROM <table_b> WHERE ...

    

​

Basic Examples

• What data the stream returns. For example:
  • Global data: No parameters. Same data for all users (e.g. reference tables like categories).
  • Filtered data: Filters the data by a parameter value. This can make use of auth parameters from the JWT token (such as the user ID or other JWT claims), subscription parameters (specified by the client when it subscribes to a stream at any time), or connection parameters (specified at connection). Different users will get different sets of data based on the parameters. See Using Parameters for the full reference.
• When the client syncs the data
  • Auto-subscribe: Client automatically subscribes on connect (auto_subscribe: true)
  • On-demand: Client explicitly subscribes when needed (default behavior)

​

Global Data

config:
  edition: 3

streams:
  # Same categories for everyone
  categories:
    query: SELECT * FROM categories

  # Same active products for everyone
  products:
    query: SELECT * FROM products WHERE active = true

​

Filtering Data by User

config:
  edition: 3

streams:
  # Each user gets their own lists
  my_lists:
    query: SELECT * FROM lists WHERE owner_id = auth.user_id()

  # Each user gets their own orders
  my_orders:
    query: SELECT * FROM orders WHERE user_id = auth.user_id()

​

Filtering Data Based on Subscription Parameters

config:
  edition: 3

streams:
  # Sync todos for a specific list when the client subscribes with a list_id
  list_todos:
    query: |
      SELECT * FROM todos 
      WHERE list_id = subscription.parameter('list_id')
        AND list_id IN (SELECT id FROM lists WHERE owner_id = auth.user_id())

// Client subscribes with the list they want to view
const sub = await db.syncStream('list_todos', { list_id: 'abc123' }).subscribe();

​

Using Auto-Subscribe

• Reference data that all users need, or that are needed in many screens in the app.
• User data that should always be available offline
• Maintaining Sync Rules default behavior (“sync everything upfront”) when migrating to Sync Streams

config:
  edition: 3

streams:
  # Global data, synced automatically
  categories:
    auto_subscribe: true
    query: SELECT * FROM categories

  # User-scoped data, synced automatically
  my_orders:
    auto_subscribe: true
    query: SELECT * FROM orders WHERE user_id = auth.user_id()

  # Parameterized data, subscribed on-demand (no auto_subscribe)
  order_items:
    query: |
      SELECT * FROM order_items 
      WHERE order_id = subscription.parameter('order_id')
        AND order_id IN (SELECT id FROM orders WHERE user_id = auth.user_id())

​

Client-Side Usage

const sub = await db.syncStream('list_todos', { list_id: 'abc123' })
  .subscribe({ ttl: 3600 });

// Wait for this subscription to have synced
await sub.waitForFirstSync();

// When the component needing the subscription is no longer active...
sub.unsubscribe();

const stream = useSyncStream({ name: 'list_todos', parameters: { list_id: 'abc123' } });
// Check download progress or subscription information
stream?.progress;
stream?.subscription.hasSynced;

const { data } = useQuery(
  'SELECT * FROM todos WHERE list_id = ?',
  [listId],
  { streams: [{ name: 'list_todos', parameters: { list_id: listId }, waitForStream: true }] }
);

final sub = await db
  .syncStream('list_todos', {'list_id': 'abc123'})
  .subscribe(ttl: const Duration(hours: 1));

// Wait for this subscription to have synced
await sub.waitForFirstSync();

// When the component needing the subscription is no longer active...
sub.unsubscribe();

val sub = database.syncStream("list_todos", mapOf("list_id" to JsonParam.String("abc123")))
  .subscribe(ttl = 1.0.hours)

// Wait for this subscription to have synced
sub.waitForFirstSync()

// When the component needing the subscription is no longer active...
sub.unsubscribe()

let sub = try await db.syncStream(name: "list_todos", params: ["list_id": JsonValue.string("abc123")])
  .subscribe(ttl: 60 * 60, priority: nil) // 1 hour

// Wait for this subscription to have synced
try await sub.waitForFirstSync()

// When the component needing the subscription is no longer active...
try await sub.unsubscribe()

var sub = await db.SyncStream("list_todos", new() { ["list_id"] = "abc123" })
  .Subscribe(new SyncStreamSubscribeOptions { Ttl = TimeSpan.FromHours(1) });

// Wait for this subscription to have synced
await sub.WaitForFirstSync();

// When the component needing the subscription is no longer active...
sub.Unsubscribe();

​

TTL (Time-To-Live)

// Set TTL in seconds when subscribing
const sub = await db.syncStream('todos', { list_id: 'abc' })
  .subscribe({ ttl: 3600 }); // Cache for 1 hour after unsubscribe

​

Developer Notes

• SQL Syntax: Stream queries use a SQL-like syntax with SELECT statements. You can use subqueries, INNER JOIN, and CTEs for filtering. GROUP BY, ORDER BY, and LIMIT are not supported. See Writing Queries for details on joins, multiple queries per stream, and other features.
• Type Conversion: Data types from your source database (Postgres, MongoDB, MySQL, SQL Server or Convex) are converted when synced to the client’s SQLite database. SQLite has a limited type system, so most types become text and you may need to parse or cast values in your app code. See Type Mapping for details on how each type is handled.
• Primary Key: PowerSync requires every synced table to have a primary key column named id of type text. If your backend uses a different column name or type, you’ll need to map it. For MongoDB, collections use _id as the ID field; you must alias it in your stream queries (e.g. SELECT *, _id as id FROM your_collection).
• Case Sensitivity: To avoid issues across different databases and platforms, use lowercase identifiers for all table and column names in your Sync Streams. If your backend uses mixed case, see Case Sensitivity for how to handle it.
• Bucket Limits: PowerSync uses internal partitions called buckets to efficiently sync data. There’s a default limit of 1,000 buckets per user/client. Each stream creates one bucket per unique value of its filter expression, whether from a subquery, JOIN, auth parameter, or subscription parameter. You can use multiple queries per stream and other strategies to reduce bucket count. See Too Many Buckets in the troubleshooting guide for how to diagnose and resolve high bucket count (PSYNC_S2305 errors).
• Troubleshooting: If data isn’t syncing as expected, the Sync Diagnostics Client helps you inspect what’s happening for a specific user — you can see which buckets the user has and what data is being synced.

​

Examples & Demos

​

Migrating from Legacy Sync Rules