Using transactions to group changes

Use writeTransaction to group statements that can write to the database.

database.writeTransaction {
    database.execute(
        sql = "DELETE FROM list WHERE id = ?",
        parameters = listOf(listId)
    )
    database.execute(
        sql = "DELETE FROM todos WHERE list_id = ?",
        parameters = listOf(listId)
    )
}

Subscribe to changes in data

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

// You can watch any SQL query
fun watchCustomers(): Flow<List<User>> {
    // TODO: implement your UI based on the result set
    return database.watch("SELECT * FROM customers", mapper = { cursor ->
        User(
            id = cursor.getString("id"),
            name = cursor.getString("name"),
            email = cursor.getString("email")
        )
    })
}

Insert, update, and delete data in the local database

Use execute to run INSERT, UPDATE or DELETE queries.

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

Send changes in local data to your backend service

Override uploadData to send local updates to your backend service. If you are using Supabase, see SupabaseConnector.kt for a complete implementation.

/**
 * This function is called whenever there is data to upload, whether the device is online or offline.
 * If this call throws an error, it is retried periodically.
 */
override suspend fun uploadData(database: PowerSyncDatabase) {

    val transaction = database.getNextCrudTransaction() ?: return;

    var lastEntry: CrudEntry? = null;
    try {

        for (entry in transaction.crud) {
            lastEntry = entry;

            val table = supabaseClient.from(entry.table)
            when (entry.op) {
                UpdateType.PUT -> {
                    val data = entry.opData?.toMutableMap() ?: mutableMapOf()
                    data["id"] = entry.id
                    table.upsert(data)
                }

                UpdateType.PATCH -> {
                    table.update(entry.opData!!) {
                        filter {
                            eq("id", entry.id)
                        }
                    }
                }

                UpdateType.DELETE -> {
                    table.delete {
                        filter {
                            eq("id", entry.id)
                        }
                    }
                }
            }
        }

        transaction.complete(null);

    } catch (e: Exception) {
        println("Data upload error - retrying last entry: ${lastEntry!!}, $e")
        throw e
    }
}

Accessing PowerSync connection status information

// Intialize the DB
val db = remember { PowerSyncDatabase(factory, schema) }
// Get the status as a flow
val status = db.currentStatus.asFlow().collectAsState(initial = null)
// Use the emitted values from the flow e.g. to check if connected
val isConnected = status.value?.connected

Wait for the initial sync to complete

Use the hasSynced property and register a listener to indicate to the user whether the initial sync is in progress.

val db = remember { PowerSyncDatabase(factory, schema) }
val status = db.currentStatus.asFlow().collectAsState(initial = null)
val hasSynced by remember { derivedStateOf { status.value?.hasSynced } }

when {
    hasSynced == null || hasSynced == false -> {
        Box(
            modifier = Modifier.fillMaxSize().background(MaterialTheme.colors.background),
            contentAlignment = Alignment.Center
        ) {
                Text(
                    text = "Busy with initial sync...",
                    style = MaterialTheme.typography.h6
                )
            }
    }
    else -> {
    ... show rest of UI

For async use cases, use waitForFirstSync method which is a suspense function that resolves once the first full sync has completed.

Report sync download progress

You can show users a progress bar when data downloads using the syncStatus.downloadProgress property. This is especially useful for long-running initial syncs. downloadProgress.downloadedFraction gives a value from 0.0 to 1.0 representing the total sync progress.

Example (Compose):

import androidx.compose.foundation.background
import androidx.compose.foundation.layout.Arrangement
import androidx.compose.foundation.layout.Column
import androidx.compose.foundation.layout.fillMaxSize
import androidx.compose.foundation.layout.fillMaxWidth
import androidx.compose.foundation.layout.padding
import androidx.compose.material.LinearProgressIndicator
import androidx.compose.material.MaterialTheme
import androidx.compose.material.Text
import androidx.compose.runtime.Composable
import androidx.compose.runtime.getValue
import androidx.compose.ui.Alignment
import androidx.compose.ui.Modifier
import androidx.compose.ui.unit.dp
import com.powersync.PowerSyncDatabase
import com.powersync.bucket.BucketPriority
import com.powersync.compose.composeState

/**
 * Shows a progress bar while a sync is active.
 *
 * The [priority] parameter can be set to, instead of showing progress until the end of the entire
 * sync, only show progress until data in the [BucketPriority] is synced.
 */
@Composable
fun SyncProgressBar(
    db: PowerSyncDatabase,
    priority: BucketPriority? = null,
) {
    val state by db.currentStatus.composeState()
    val progress = state.downloadProgress?.let {
        if (priority == null) {
            it
        } else {
            it.untilPriority(priority)
        }
    }

    if (progress == null) {
        return
    }

    Column(
        modifier = Modifier.fillMaxSize().background(MaterialTheme.colors.background),
        horizontalAlignment = Alignment.CenterHorizontally,
        verticalArrangement = Arrangement.Center,
    ) {
        LinearProgressIndicator(
            modifier = Modifier.fillMaxWidth().padding(8.dp),
            progress = progress.fraction,
        )

        if (progress.downloadedOperations == progress.totalOperations) {
            Text("Applying server-side changes...")
        } else {
            Text("Downloaded ${progress.downloadedOperations} out of ${progress.totalOperations}.")
        }
    }
}

Also see: