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
}
}
// 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: