Usage Examples

Code snippets and guidelines for common scenarios

Using transactions to group changes

Read and write transactions present a context where multiple changes can be made then finally committed to the DB or rolled back. This ensures that either all the changes get persisted, or no change is made to the DB (in the case of a rollback or exception).

The writeTransaction(callback) method combines all writes into a single transaction, only committing to persistent storage once.

deleteList(SqliteDatabase db, String id) async {
  await db.writeTransaction((tx) async {
     // Delete the main list
     await tx.execute('DELETE FROM lists WHERE id = ?', [id]);
     // Delete any children of the list
     await tx.execute('DELETE FROM todos WHERE list_id = ?', [id]);
  });
}

Also see readTransaction(callback) .

Subscribe to changes in data

Use watch to watch for changes to the dependent tables of any SQL query.

StreamBuilder(
  // You can watch any SQL query
  stream: db.watch('SELECT * FROM customers order by id asc'),
  builder: (context, snapshot) {
    if (snapshot.hasData) {
      // TODO: implement your own UI here based on the result set
      return ...;
    } else {
      return const Center(child: CircularProgressIndicator());
    }
  },
)

Insert, update, and delete data in the local database

Use execute to run INSERT, UPDATE or DELETE queries.

FloatingActionButton(
  onPressed: () async {
    await db.execute(
      'INSERT INTO customers(id, name, email) VALUES(uuid(), ?, ?)',
      ['Fred', '[e-mail]'],
    );
  },
  tooltip: '+',
  child: const Icon(Icons.add),
);

Send changes in local data to your backend service

Override uploadData to send local updates to your backend service.

@override
Future<void> uploadData(PowerSyncDatabase database) async {
  final batch = await database.getCrudBatch();
  if (batch == null) return;
  for (var op in batch.crud) {
    switch (op.op) {
      case UpdateType.put:
        // Send the data to your backend service
        // Replace `_myApi` with your own API client or service
        await _myApi.put(op.table, op.opData!);
        break;
      default:
        // TODO: implement the other operations (patch, delete)
        break;
    }
  }
  await batch.complete();
}

Accessing PowerSync connection status information

Use SyncStatus and register an event listener with statusStream to listen for status changes to your PowerSync instance.

class _StatusAppBarState extends State<StatusAppBar> {
  late SyncStatus _connectionState;
  StreamSubscription<SyncStatus>? _syncStatusSubscription;

  @override
  void initState() {
    super.initState();
    _connectionState = db.currentStatus;
    _syncStatusSubscription = db.statusStream.listen((event) {
      setState(() {
        _connectionState = db.currentStatus;
      });
    });
  }
  
  @override
  void dispose() {
    super.dispose();
    _syncStatusSubscription?.cancel();
  }

  @override
  Widget build(BuildContext context) {
    final statusIcon = _getStatusIcon(_connectionState);

    return AppBar(
      title: Text(widget.title),
      actions: <Widget>[
        ...
        statusIcon        
      ],
    );
  }
}

Widget _getStatusIcon(SyncStatus status) {
  if (status.anyError != null) {
    // The error message is verbose, could be replaced with something
    // more user-friendly
    if (!status.connected) {
      return _makeIcon(status.anyError!.toString(), Icons.cloud_off);
    } else {
      return _makeIcon(status.anyError!.toString(), Icons.sync_problem);
    }
  } else if (status.connecting) {
    return _makeIcon('Connecting', Icons.cloud_sync_outlined);
  } else if (!status.connected) {
    return _makeIcon('Not connected', Icons.cloud_off);
  } else if (status.uploading && status.downloading) {
    // The status changes often between downloading, uploading and both,
    // so we use the same icon for all three
    return _makeIcon('Uploading and downloading', Icons.cloud_sync_outlined);
  } else if (status.uploading) {
    return _makeIcon('Uploading', Icons.cloud_sync_outlined);
  } else if (status.downloading) {
    return _makeIcon('Downloading', Icons.cloud_sync_outlined);
  } else {
    return _makeIcon('Connected', Icons.cloud_queue);
  }
}

Wait for the initial sync to complete

An example function that returns true once the first full sync has completed after a client connects to PowerSync. This can be used to show a loading animation while the initial sync is in progress.

async function waitForFirstSync(timeout = 10000) {
    const end = new Date().getTime() + timeout;

    while (new Date().getTime() < end) {
        // Assuming 'db.getOptional' is an asynchronous function that returns a promise.
        // Replace this with your actual database call.
        const row = await db.getOptional('SELECT name FROM ps_buckets WHERE last_applied_op > 0 LIMIT 1');
        if (row !== null) {
            return true;
        }

        // Delay for 15 milliseconds.
        await new Promise(resolve => setTimeout(resolve, 15));
    }

    return false;
}

// Example usage of the function
waitForFirstSync().then(result => {
    console.log('Sync result:', result);
});

Using logging to troubleshoot issues

Since version 1.1.2 of the SDK, logging is enabled by default and outputs logs from PowerSync to the console in debug mode.