Updating a database from a background Isolate

Help General
, ,
1

Hi all,

In my app I use the excellent Sembast database; however, like some of the other databases solutions available for Flutter - such as SQLFlite - you can only access it from the UI/main thread.

I am looking to add background fetching of data using something like WorkManager or flutter_background_service and need a way to update my database.

One solution would be to rip out Sembast and rebuild my data layer using something like sqlite3 which I believe can be accessed from background Isolates. The biggest problem with this approach is the risk of breaking installs of my app out in production. Or I could download the data in the background and request Sembast refresh when the app returns to the foreground, but this could introduce some lag and make for a poor user experience.

Does anyone here have an experience of updating a database in the background or could offer any suggestions on how I could implement this?

Many thanks.

2

As long as you can guarantee that you your main thread won’t write to the database while your isolage makes an update it could possibly work.
However, you isolate won’t be executed while your app goes into deep sleep. So not sure if you will gain anything from this.
With background service or backeground_downloader you could download the latest data into a new file and on resuming your app you reinitializes sembast with the new file instead of copying the data

3

I recommend drift, a sqlite based library that has support for this:
Isolates - Drift,
particularly here: Isolates - Drift

2 Likes
4

As @knaeckeKami mentioned you should be good to use drift and combine it with their isolate setup.

To prevent issues with concurrent writes you can:

  • use pragma journal_mode=WAL; and pragma busy_timeout = 200; pragmas so that the database retries writes if they fail (don’t quote me on that exactly :wink: but rather check the docs Pragma statements supported by SQLite)
  • write your own error handling code that will retry writes/transactions if they fail (see mine below)
  /// Generic function to retry a query that might fail due to
  /// [SqliteException] e.g. when database is locked.
  Future<T> retry<T>(Future<T> Function() computation) async {
    var retryCount = 0;
    var delay = 300;

    while (retryCount < 5) {
      try {
        final rs = await computation();
        if (retryCount > 0) {
          _log.i('Successfully recovered from database access issue');
        }
        return rs;
      } catch (e, s) {
        if (e is DriftRemoteException || e is SqliteException) {
          if (retryCount == 5) {
            _log.e('Did not manage to recover the database despite retrying');
            Error.throwWithStackTrace(e, s);
          }
          if (retryCount == 4) {
            // Last chance to fix the access issue, let's close the connection
            // and try to reconnect
            _log.w('Closing database ${_bgDatabase.hashCode} to recover from access issue');
            final dbReference = _bgDatabase;
            _bgDatabase = null;
            await dbReference?.close();
          }
          retryCount++;
          delay = delay + 100;
          _log.w('Problem with accessing the database, retry $retryCount after $delay ms', e);
        } else {
          Error.throwWithStackTrace(e, s);
        }
      }
      await Future<void>.delayed(Duration(milliseconds: delay));
    }
    throw Exception('Did not manage to recover the database despite retrying');
  }

We have quite successful setup of several SQLite databases, some of them are accessed from multiple isolates in Flutter (including workmanager callbacks), other are accessed from a single isolate in Flutter but also written to from native processes in Kotlin/Swift (which may happen at the same time as Flutter related queries), some data gets copied/processed between these databases etc.

In general we are super happy with what SQLite and drift can offer although it may take weeks if not months to discover all the caveats related to isolates access, encryption, migrations, concurrent reads and writes.

3 Likes
5

I have been using sqlite_async for a new project and have had a great experience with it so far. All ops execute off the main isolate by default, and read txns can run concurrently. I also find the API to be very ergonomic.

The package description provides a good overview of how it operates, and links to a blog post with more details.

1 Like
6

Thanks Dominik, that’s really useful information.

It does look rebuilding or augmenting my db layer with something like Drift or the underlying sqlite3 package is the way to go.

7

Thanks, I’ll checkout sqlite_async. :slight_smile: