How to use powersync in a Home Screen widget?

[galacticgibbon]

In my app, I have Home Screen widgets which retrieve data from the db, and can write to the db with intents, on both android and iOS.

I recently ran into an issue where reading directly from the app's DB using android.database.sqlite.SQLiteDatabase lead to errors that looked like this:
E/SQLiteLog(28610): (1) near "STRICT": syntax error in "CREATE TABLE ps_buckets(
This corrupted the db on client devices. I think it was working before I switched to the rust client.

I just wanted to clear up my understanding of using powersync (+ drift) in separate processes.
I've seen this:
powersync on isolate - #178
drift on isolate - https://drift.simonbinder.eu/isolates/

In this package https://docs.page/abausg/home_widget/features/interactive-widgets , communication with the main app is done with a background worker, which appears to work a little differently to isolates.

So I just would like some guidance on best practice for reading/writing to the db from a home screen widget or other extensions e.g. AppIntents (iOS). I'm not sure about whether powersync syncing the db from separate processes could cause issues and how to avoid that, and not sure the safe way to read from the db to avoid the above error.

Other than that, I've just completed migration from firestore to postgres + powersync and it's amazing 🤩 I had built my own firestore -> sqlite sync engine before which was brittle, so I know it ain't easy. Thanks :)

[galacticgibbon]

Here's an example of what I was using to trigger intents

class ToggleCompleteAction : ActionCallback {
    companion object {
        val key = ActionParameters.Key<String>("entry_id_key")
    }

    override suspend fun onAction(
        context: Context,
        glanceId: GlanceId,
        parameters: ActionParameters
    ) {
        val entryId = parameters[key] ?: return
        
        // Trigger Flutter background callback to update database via PowerSync
        // The widget's FileObserver will detect the change and collectAsState will trigger a refresh
        val uri = Uri.parse("app://toggletask?entryId=$entryId")
        val backgroundIntent = HomeWidgetBackgroundIntent.getBroadcast(context, uri)
        backgroundIntent.send()
    }
}

@pragma("vm:entry-point")
void widgetBackgroundCallback(Uri? uri) async {
  await initializeFirebase(
    Env.flavor == Flavor.prod
        ? prod.DefaultFirebaseOptions.currentPlatform
        : dev.DefaultFirebaseOptions.currentPlatform,
  );

  if (uri?.host == 'toggletask') {
    final entryId = uri?.queryParameters['entryId'];

    if (entryId != null) {
       await _toggleTaskComplete(entryId);
    }
  } else if (uri?.host == 'addentry') {
    final entriesJson = uri?.queryParameters['entries'];

    if (entriesJson != null) {
      await _addEntries(entriesJson);
    }
  }
}

/// Toggle task completion status using PowerSync
Future<void> _toggleTaskComplete(String entryId) async {
  try {
    // Initialize PowerSync database (standalone, no Riverpod)

    final db = await initPowerSync();
    
    // ideally I'd use drift here
    await db.execute('...')

[simolus3]

First of all:

I've just completed migration from firestore to postgres + powersync and it's amazing 🤩 I had built my own firestore -> sqlite sync engine before which was brittle, so I know it ain't easy. Thanks :)

Congrats and thanks! We're glad to hear that.

I recently ran into an issue where reading directly from the app's DB using android.database.sqlite.SQLiteDatabase lead to errors that looked like this:

That is expected, Android's SQLite APIs doesn't support loading extension and may reference an older SQLite version without support for strict tables. If you want to use a PowerSync database from Kotlin code, you can use our Kotlin SDK for that. You can share databases between the two SDKs by using the same path, but you should only call .connect() on one of them.

It's possible that a recent version of the core extension made this worse, but it was never really supposed to work.

drift on isolate - https://drift.simonbinder.eu/isolates/

Just to be clear, that isn't applicable to PowerSync databases (even if you're using drift). PowerSync has its own code to talk to SQLite, and NativeDatabase from drift is something else (although they are conceptually quite similar).

In this package https://docs.page/abausg/home_widget/features/interactive-widgets , communication with the main app is done with a background worker, which appears to work a little differently to isolates.

True - because that likely uses a separate Flutter engine, we can only pass primitive objects between isolates. That will break sqlite_async.

I'm not sure about whether powersync syncing the db from separate processes could cause issues and how to avoid that, and not sure the safe way to read from the db to avoid the above error.

PowerSync uses transactions for that, so this shouldn't cause any data corruption issues. We also configure the database to use WAL, which generally means that a writer and an arbitrary amount of readers can use the database concurrently.

So if you only do reads on for the homescreen widget, the advice is pretty simple: Just open another database instance on the background callback and completely ignore the main app.
For writes, things are a bit more complicated. Ideally you'd want an async lock shared between the two database instances to ensure only one of them can be active at a time. Once Dart 3.10 is out, you could use a package like weblocks for that (it's possible sqlite_async will migrate to that as well).
In the meantime, also ignoring the other writer is not catastrophic. It can lead to SQLITE_BUSY errors, but sqlite_async installs an automated retry mechanism. You should also be able to wrap the second database instance with drift, an issue is that table updates would not be shared at the moment (so stream queries aren't updated for writes in the other database).

So the TL;DR is:

• Use a separate database instance for homescreen widgets.
• This will get much better with Dart 3.10.

[galacticgibbon]

Thanks for the quick reply.

I'm glad to hear that I don't run the risk of corrupting the DB writing from the background worker, I expect that powersync syncing at the same time would be a rare edge case anyway, and sqlite busy errors I'm fine with ultimately.

Thanks for the info!

[rkistner]

One additional thing to note is that using multiple different sqlite libraries in the same process can bypass database locks and cause corruption: https://www.sqlite.org/howtocorrupt.html#multiple_copies_of_sqlite_linked_into_the_same_application

This could be an issue when using both the Dart SDK and the Kotlin SDK (or Android SQLite libs) in the same app.