​
Overview

While basic/incremental watch queries enable reactive UIs by automatically re‑running queries when underlying data changes and returning updated results, they don’t specify which individual rows were modified. To get these details, you can use differential watch queries, which return a structured diff between successive query results. However, on large result sets they can be slow because they re‑run the query and compare full results (e.g., scanning ~1,000 rows to detect 1 new item). That’s why we introduced trigger‑based table diffs: a more performant approach that uses SQLite triggers to record changes on a table as they happen. This means that the overhead associated with tracking these changes overhead is more proportional to the number of rows inserted, updated, or deleted.
JavaScript Only: Trigger-based table diffs are available in the JavaScript SDKs starting from:
  • Web v1.26.0
  • React Native v1.24.0
  • Node.js v0.10.0
The db.triggers APIs are experimental. We’re actively seeking feedback on:
  • API design and developer experience
  • Additional features or optimizations needed
Join our Discord community to share your experience and get help.

​
Key differences vs. differential watch queries

  • Scope: Trigger-based diffs track row-level changes on a single table. Differential watches work with arbitrary query results (including joins).
  • Overhead: Trigger-based diffs do per-row work at write time (overhead grows with number of affected rows). Differential watches re-query and compare result sets on each change (overhead grows with result set size).
  • Processing path: Trigger-based diffs record changes at write time and require a writeLock during processing (only a single writeLock is allowed). Differential watches run on read connections and re-query/compare results on each change (often concurrent on some platforms).
  • Storage/shape: Trigger-based diffs store changes as rows in a temporary SQLite table that you can query with SQL. Differential watch diffs are exposed to app code as JS objects/arrays.
  • Filtering: Trigger-based diffs can filter/skip storing diff records inside the SQLite trigger, which prevents emissions on a lower level. Differential watches query the SQLite DB on any change to the query’s dependent tables, and the changes are filtered after querying SQLite.
In summary, differential watch queries are the most flexible (they work with arbitrary, multi‑table queries), but they can be slow on large result sets. For those cases, trigger-based diffs are more efficient, but they only track a single table and add some write overhead. For usage and examples of differential watch queries, see Differential Watch Queries.

​
Trigger-based diffs

Trigger-based diffs create temporary SQLite triggers and a temporary table to record row‑level inserts, updates, and deletes as they happen. You can then query the diff table with SQL to process the changes.
SQLite triggers and PowerSync viewsIn PowerSync, the tables you define in the client schema are exposed as SQLite views. The actual data is stored in underlying SQLite tables, with each row’s values encoded as JSON (commonly in a single data column).SQLite cannot attach triggers to INSERT/UPDATE/DELETE operations on views — triggers must target the underlying base tables. The db.triggers API handles these details for you:
  • You can reference the view name in source; PowerSync resolves and targets the corresponding underlying table internally.
  • Column filters are applied by inspecting JSON changes in the underlying row and determining whether the configured columns changed.
  • Diff rows can be queried as if they were real columns (not raw JSON) using the withExtractedDiff(...) helper.
You can also create your own triggers manually (for example, as shown in the Full‑Text Search example), but be mindful of the view/trigger limitation and target the underlying table rather than the view.
The primary API is trackTableDiff. It wraps the lower-level trigger setup, automatically manages a writeLock during processing, exposes a DIFF table alias to join against, and cleans up when you call the returned stop() function. Think of it as an automatic “watch” that processes diffs as they occur. 
const stop = await db.triggers.trackTableDiff({
  // PowerSync source table/view to trigger and track changes from.
  // This should be present in the PowerSync database's schema.
  source: 'todos',
  // Specifies which columns from the source table to track in the diff records.
  // Defaults to all columns in the source table.
  // Use an empty array to track only the ID and operation.
  columns: ['list_id'],
  // Required WHEN clause per operation to filter inside the trigger. Use 'TRUE' to track all.
  when: { INSERT: sanitizeSQL`json_extract(NEW.data, '$.list_id') = ${firstList.id}` },
  onChange: async (context) => {
    // // Fetches the todo records that were inserted during this diff
    const newTodos = await context.withDiff(/* sql */ `
      SELECT todos.*
      FROM DIFF
      JOIN todos ON DIFF.id = todos.id
    `);

    // Handle new todos here
  }
});

// Later, dispose triggers and internal resources
await stop();

​
Filtering with when

The required when parameter lets you add conditions that determine when the triggers should fire. This corresponds to a SQLite WHEN clause in the trigger body.
  • Use NEW for INSERT/UPDATE and OLD for DELETE.
  • Row data is stored as JSON in the data column; the row identifier is id.
  • Use json_extract(NEW.data, '$.column') or json_extract(OLD.data, '$.column') to reference logical columns.
  • Set the clause to 'TRUE' to track all changes for a given operation.
Example: 
const stop = await db.triggers.trackTableDiff({
  source: 'todos',
  when: {
    // Track all INSERTs
    INSERT: 'TRUE',
    // Only UPDATEs where status becomes 'active' for a specific record
    UPDATE: sanitizeSQL`NEW.id = ${sanitizeUUID('abcd')} AND json_extract(NEW.data, '$.status') = 'active'`,
    // Only DELETEs for a specific list
    DELETE: sanitizeSQL`json_extract(OLD.data, '$.list_id') = 'abcd'`
  }
});
The strings in when are embedded directly into the SQLite trigger creation SQL. Sanitize any user‑derived values. The sanitizeSQL helper performs some basic sanitization; additional sanitization is recommended.

​
Lower-level: createDiffTrigger (advanced)

Set up temporary triggers that write change operations into a temporary table you control. Prefer trackTableDiff unless you need to manage lifecycle and locking manually (e.g., buffer diffs to process them later). Note that since the table is created as a temporary table on the SQLite write connection, it can only be accessed within operations performed inside a writeLock. 
// Define the temporary table to store the diff
const tempTable = 'listsDiff';

// Configure triggers to record INSERT and UPDATE operations on `lists`
const dispose = await db.triggers.createDiffTrigger({
  // PowerSync source table/view to trigger and track changes from.
  // This should be present in the PowerSync database's schema.
  source: 'lists',
  // Destination table to send changes to.
  // This table is created internally as a SQLite temporary table.
  // This table will be dropped once the trigger is removed.
  destination: tempTable,
  // Required WHEN clause per operation to filter inside the trigger. Use 'TRUE' to track all.
  when: {
    INSERT: 'TRUE',
    UPDATE: sanitizeSQL`json_extract(NEW.data, '$.name') IS NOT NULL`
  },
  // Specifies which columns from the source table to track in the diff records.
  // Defaults to all columns in the source table.
  // Use an empty array to track only the ID and operation.
  columns: ['name']
});

// ... perform writes on `lists` ...

// Consume and clear changes within a writeLock
await db.writeLock(async (tx) => {
  const changes = await tx.getAll(/* sql */ `
    SELECT * FROM ${tempTable}
  `);

  // Process changes here

  // Clear after processing
  await tx.execute(/* sql */ `DELETE FROM ${tempTable};`);
});

// Later, clean up triggers and temp table
await dispose();