Usage Examples

These examples assume that your app already has a PowerSync database instance, as described in the Getting Started steps for your Client SDK. Examples may leave out other app setup for brevity. React examples that use PowerSync hooks require a PowerSyncContext.Provider above the component, with your database instance as its value.

Using Transactions to Group Changes

A transaction groups several writes into one unit. Either all of the writes are saved, or none of them are. PowerSync also queues the changes from one transaction for upload as a group. These examples delete a list and its todos together, so the app never keeps todos for a deleted list.

writeTransaction() runs your callback inside a transaction. PowerSync saves the changes when the callback finishes, and rolls them back if the callback throws an error.

async function deleteList(db, listId) {
  await db.writeTransaction(async (tx) => {
    await tx.execute('DELETE FROM lists WHERE id = ?', [listId]);
    await tx.execute('DELETE FROM todos WHERE list_id = ?', [listId]);
  });
}

Learn more:

writeTransaction() runs your callback inside a transaction. PowerSync saves the changes when the callback finishes, and rolls them back if the callback throws an error.

async function deleteList(db, listId) {
  await db.writeTransaction(async (tx) => {
    await tx.execute('DELETE FROM lists WHERE id = ?', [listId]);
    await tx.execute('DELETE FROM todos WHERE list_id = ?', [listId]);
  });
}

Learn more:

writeTransaction() runs your callback inside a transaction. PowerSync saves the changes when the callback finishes, and rolls them back if the callback throws an error.

async function deleteList(db, listId) {
  await db.writeTransaction(async (tx) => {
    await tx.execute('DELETE FROM lists WHERE id = ?', [listId]);
    await tx.execute('DELETE FROM todos WHERE list_id = ?', [listId]);
  });
}

Learn more:

writeTransaction() runs your callback inside a transaction. PowerSync saves the changes when the callback finishes, and rolls them back if the callback throws.

Future<void> deleteList(PowerSyncDatabase db, String id) async {
  await db.writeTransaction((tx) async {
    await tx.execute('DELETE FROM lists WHERE id = ?', [id]);
    await tx.execute('DELETE FROM todos WHERE list_id = ?', [id]);
  });
}

Learn more:

writeTransactionAsync runs the block inside a transaction. Run each statement with executeAsync() on the transaction that the block receives. PowerSync saves the changes when the block finishes, and rolls them back if the block throws an exception.

suspend fun deleteList(database: PowerSyncDatabase, listId: String) {
    database.writeTransactionAsync { transaction ->
        transaction.executeAsync(
            sql = "DELETE FROM lists WHERE id = ?",
            parameters = listOf(listId)
        )
        transaction.executeAsync(
            sql = "DELETE FROM todos WHERE list_id = ?",
            parameters = listOf(listId)
        )
    }
}

Learn more:

writeTransaction runs the closure inside a transaction. Statements inside the closure run synchronously, so they use try without await. PowerSync saves the changes when the closure finishes, and rolls them back if the closure throws.

func deleteList(db: any PowerSyncDatabaseProtocol, listId: String) async throws {
    try await db.writeTransaction { tx in
        try tx.execute(sql: "DELETE FROM lists WHERE id = ?", parameters: [listId])
        try tx.execute(sql: "DELETE FROM todos WHERE list_id = ?", parameters: [listId])
    }
}

Learn more:

WriteTransaction runs the callback inside a transaction. PowerSync saves the changes when the callback finishes, and rolls them back if the callback throws an exception.

using PowerSync.Common.Client;

class Demo
{
    static async Task DeleteList(PowerSyncDatabase db, string listId)
    {
        await db.WriteTransaction(async (tx) =>
        {
            await tx.Execute("DELETE FROM lists WHERE id = ?", new object[] { listId });
            await tx.Execute("DELETE FROM todos WHERE list_id = ?", new object[] { listId });
        });
    }
}

Learn more:

db.writer() returns the write connection, and transaction() starts a transaction on it. Call commit() to save the changes. If the function returns early with an error, the transaction is dropped and its changes are rolled back.

use powersync::{PowerSyncDatabase, error::PowerSyncError};
use rusqlite::params;

async fn delete_list(db: &PowerSyncDatabase, id: &str) -> Result<(), PowerSyncError> {
    let mut writer = db.writer().await?;
    let tx = writer.transaction()?;

    tx.execute("DELETE FROM lists WHERE id = ?", params![id])?;
    tx.execute("DELETE FROM todos WHERE list_id = ?", params![id])?;

    tx.commit()?;
    Ok(())
}

Learn more:

Listen for Changes in Data

A watch query runs a SQL query again whenever the tables it reads from change, and gives you the new results. Use it to keep your UI up to date as data changes.

watch() gives you new results each time the query’s tables change. You can read the results with an async iterator (for await), or receive them in an onResult callback.

AsyncIterator approach
Callback approach

async function* pendingLists() {
  for await (const result of db.watch(
    `SELECT * FROM lists WHERE state = ?`,
    ['pending']
  )) {
    yield result.rows?._array ?? [];
  }
}

const pendingLists = (onResult) => {
  db.watch(
    'SELECT * FROM lists WHERE state = ?',
    ['pending'],
    {
      onResult: (result) => {
        onResult(result.rows?._array ?? []);
      }
    }
  );
}

Learn more:

Live Queries / Watch Queries, including incremental and differential results
watch() API reference

watch() gives you new results each time the query’s tables change. You can read the results with an async iterator (for await), or receive them in an onResult callback.

AsyncIterator approach
Callback approach

async function* pendingLists() {
  for await (const result of db.watch(
    `SELECT * FROM lists WHERE state = ?`,
    ['pending']
  )) {
    yield result.rows?._array ?? [];
  }
}

const pendingLists = (onResult) => {
  db.watch(
    'SELECT * FROM lists WHERE state = ?',
    ['pending'],
    {
      onResult: (result) => {
        onResult(result.rows?._array ?? []);
      }
    }
  );
}

Learn more:

Live Queries / Watch Queries, including incremental and differential results
watch() API reference

watch() gives you new results each time the query’s tables change. You can read the results with an async iterator (for await), or receive them in an onResult callback.

AsyncIterator approach
Callback approach

async function* pendingLists() {
  for await (const result of db.watch(
    `SELECT * FROM lists WHERE state = ?`,
    ['pending']
  )) {
    yield result.rows?._array ?? [];
  }
}

const pendingLists = (onResult) => {
  db.watch(
    'SELECT * FROM lists WHERE state = ?',
    ['pending'],
    {
      onResult: (result) => {
        onResult(result.rows?._array ?? []);
      }
    }
  );
}

Learn more:

Live Queries / Watch Queries, including incremental and differential results
watch() API reference

watch() returns a Stream that emits new results each time the query’s tables change. This example uses a StreamBuilder to rebuild the widget with each result.

import 'package:flutter/material.dart';
import 'package:powersync/powersync.dart';

class PendingLists extends StatelessWidget {
  final PowerSyncDatabase db;

  const PendingLists({super.key, required this.db});

  @override
  Widget build(BuildContext context) {
    return StreamBuilder(
      stream: db.watch('SELECT id, name FROM lists WHERE state = ?', ['pending']),
      builder: (context, snapshot) {
        if (snapshot.hasError) {
          return const Text('Could not load lists.');
        }
        if (!snapshot.hasData) {
          return const Center(child: CircularProgressIndicator());
        }

        final lists = snapshot.requireData;
        return ListView(
          children: [
            for (final list in lists)
              ListTile(
                key: ValueKey(list['id']),
                title: Text(list['name'] as String),
              ),
          ],
        );
      },
    );
  }
}

Learn more:

watch() returns a Flow that emits new results each time the query’s tables change. The mapper function converts each row into a ListItem.

fun watchPendingLists(): Flow<List<ListItem>> =
    db.watch(
        "SELECT * FROM lists WHERE state = ?",
        listOf("pending"),
    ) { cursor ->
        ListItem(
            id = cursor.getString("id"),
            name = cursor.getString("name"),
        )
    }

Learn more:

watch() returns an AsyncThrowingStream that emits new results each time the query’s tables change. The mapper closure converts each row into a ListContent.

func watchPendingLists() throws -> AsyncThrowingStream<[ListContent], Error> {
    try db.watch(
        sql: "SELECT * FROM lists WHERE state = ?",
        parameters: ["pending"],
    ) { cursor in
        try ListContent(
            id: cursor.getString(name: "id"),
            name: cursor.getString(name: "name"),
        )
    }
}

Learn more:

Watch() returns an IAsyncEnumerable (since v0.0.11-alpha.1) that yields new results each time the query’s tables change. Cancel the token to stop watching.

// Define a result type with properties matching the schema columns (some columns omitted here for brevity)
// public class ListResult { public string id; public string name; public string owner_id; ... }

// Optional cancellation token to stop watching
var cts = new CancellationTokenSource();

// Register listener synchronously on the calling thread...
var listener = db.Watch<ListResult>(
    "SELECT * FROM lists WHERE owner_id = ?",
    [ownerId],
    new SQLWatchOptions { Signal = cts.Token }
);

// ...then listen to changes on another thread (or await foreach directly if already in an async context)
_ = Task.Run(async () =>
{
    await foreach (var results in listener)
    {
        Console.WriteLine("Lists: ");
        foreach (var result in results)
        {
            Console.WriteLine($"{result.id}: {result.name}");
        }
    }
}, cts.Token);

// To stop watching, cancel the token: cts.Cancel();

Learn more:

watch_statement() returns a stream that emits new results each time the query’s tables change. The closure reads the rows of each result.

use std::pin::pin;
use futures_lite::StreamExt;
use powersync::{PowerSyncDatabase, error::PowerSyncError};
use rusqlite::params;

struct PendingList {
    id: String,
    name: String,
}

async fn watch_pending_lists(db: &PowerSyncDatabase) -> Result<(), PowerSyncError> {
    let stream = db.watch_statement(
        "SELECT id, name FROM lists WHERE state = ?".to_string(),
        params!["pending"],
        |stmt, params| {
            let mut rows = stmt.query(params)?;
            let mut lists = vec![];

            while let Some(row) = rows.next()? {
                lists.push(PendingList {
                    id: row.get("id")?,
                    name: row.get("name")?,
                });
            }

            Ok(lists)
        },
    );
    let mut stream = pin!(stream);

    // Run this function in a separate async task because the stream keeps listening.
    while let Some(lists) = stream.try_next().await? {
        for list in lists {
            println!("{}: {}", list.id, list.name);
        }
    }
    Ok(())
}

Learn more:

Insert, Update, and Delete Data in the Local Database

Write to the local SQLite database with standard SQL INSERT, UPDATE, and DELETE statements. PowerSync applies each write locally right away and queues it for upload to your backend. The insert examples use uuid(), a SQL function that PowerSync provides, to generate an ID for each new row.

execute() runs a SQL statement. The ? placeholders take their values from the parameter array. This React button inserts a new customer when clicked.

export function AddCustomerButton({ db }) {
  const handleButtonClick = async () => {
    await db.execute(
      'INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)',
      ['Fred', 'fred@example.org']
    );
  };

  return <button onClick={handleButtonClick}>Add customer</button>;
}

Learn more:

execute() runs a SQL statement. The ? placeholders take their values from the parameter array. This React Native button inserts a new customer when pressed.

import { Button } from 'react-native';

export function AddCustomerButton({ db }) {
  const handleButtonClick = async () => {
    await db.execute(
      'INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)',
      ['Fred', 'fred@example.org']
    );
  };

  return <Button title="Add customer" onPress={handleButtonClick} />;
}

Learn more:

execute() runs a SQL statement. The ? placeholders take their values from the parameter array.

await db.execute(
  'INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)',
  ['Fred', 'fred@example.org']
);

Learn more:

execute() runs a SQL statement. The ? placeholders take their values from the parameter list. This button inserts a new customer when tapped.

FloatingActionButton(
  onPressed: () async {
    await db.execute(
      'INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)',
      ['Fred', 'fred@example.org'],
    );
  },
  tooltip: 'Add customer',
  child: const Icon(Icons.add),
);

Learn more:

execute() runs a SQL statement. The ? placeholders take their values from the parameter list. This function updates a customer’s name and email.

suspend fun updateCustomer(id: String, name: String, email: String) {
    database.execute(
        "UPDATE customers SET name = ?, email = ? WHERE id = ?",
        listOf(name, email, id)
    )
}

Learn more:

execute() runs a SQL statement. The ? placeholders take their values from the parameters array. This function inserts a new customer.

func insertCustomer(name: String, email: String) async throws {
    try await db.execute(
        sql: "INSERT INTO customers (id, name, email) VALUES (uuid(), ?, ?)",
        parameters: [name, email]
    )
}

Learn more:

Execute() runs a SQL statement. The ? placeholders take their values from the parameter array.

await db.Execute(
    "INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)",
    new object[] { "Fred", "fred@example.org" }
);

Learn more:

db.writer() returns the write connection. Call execute() on it with the SQL statement and its parameters.

use powersync::{PowerSyncDatabase, error::PowerSyncError};
use rusqlite::params;

async fn insert_customer(
    db: &PowerSyncDatabase,
    name: &str,
    email: &str,
) -> Result<(), PowerSyncError> {
    let writer = db.writer().await?;
    writer.execute(
        "INSERT INTO customers (id, name, email) VALUES (uuid(), ?, ?)",
        params![name, email],
    )?;
    Ok(())
}

Learn more:

Send Changes in Local Data to Your Backend Service

PowerSync does not write to your source database directly. While the app is connected, the Client SDK calls the uploadData() method of your backend connector whenever local changes are waiting in the upload queue. Your implementation sends these changes to your backend API, which applies them to your source database. The examples below take the oldest queued transaction, send all of its changes to your backend, and then mark the transaction as complete. Each change includes the operation (PUT, PATCH, or DELETE), the table, the row ID, and the changed data. backendApi stands for your own API client. If the upload fails, throw an error. The SDK then keeps the changes in the queue and retries later. To generate a complete backend connector for your SDK, open your instance in the PowerSync Dashboard, select Client SDK Setup, and select your app language.