Moor: Having multiple database instances at the same time

[johrpan]

While I know that it's recommended to have only one instance of the database object, I wonder what things I would have to take care of to have two instances simultaneously. I would like to access my database from a background service (written in Dart) and as far as I know, there is no way of sharing an object between the UI code and the service (but I'm open to suggestions). The background service would never write to the database, but it would be possible for the UI code to change something, while the background service is running (and possibly listening to that change). Would I run into locks or some other problems then? I'm not really an experienced SQLite hacker, so I would be happy about some advice!

[simolus3]

The warning about having multiple database instances at the same time is mainly due to complications when running migrations in parallel. In general, sqlite will take care of synchronization when multiple threads access the same database file.

So, it's no problem two have two database class instances using the same underlying database file, except for two problems:

• Migrations aren't atomic and run like this:

  1. Read PRAGMA user_version from the database
  2. If it's lower than the schemaVersion from the database, run migrations
  3. Update PRAGMA user_version = $schemaVersion

  Of course, this causes all kinds of integrity issues when two threads open a moor database at roughly the same time. If the background service is read-only, it might make sense to just not run migrations here (add a flag to your database class and check that when constructing the MigrationStrategy)

• since the database instances are unrelated, they can't communicate table updates with each other, which breaks stream queries.

If both issues aren't much of a problem for you, you just use a regular database instance per isolate. But moor also has some utilities to share table updates and the database connection between isolates. For this, I'd suggest a setup with three isolates:

1. the main UI isolate
2. an isolate for the background service
3. a special isolate that will run writes to the database and communicate table updates

In code, this could look like this:

QueryExecutor _createExecutor() => VmDatabase(someFile);

DatabaseConnection _createRawConnection() {
  return DatabaseConnection.fromExecutor(_createExecutor());
}

Future<MoorIsolate> spawnDatabaseIsolate() {
  return MoorIsolate.spawn(_createRawConnection);
}

Future<DatabaseConnection> connectForUI(MoorIsolate runner) => runner.connect();

Future<DatabaseConnection> connectForService(MoorIsolate runner) async {
  final backgroundConnection = await runner.connect();
  return backgroundConnection.withExecutor(MultiExecutor(
    read: _createExecutor(),
    write: background.executor,
  ));
}

If you then enable the generate_connect_constructor option, you can have two database instances sharing the same database connection. The MultiExecutor for the background isolate is a performance hack: Since it will mostly read (which doesn't have side-effects), it can just do that directly and skip the isolate synchronization. Relaying reads over an isolate will introduce a bit of latency, but reduce work on the UI isolate which prevents frame drops on complex queries. For further performance enhancements, I'd recommend enabling WAL mode.

I know this is all rather complicated, so let me know if you have any more questions. If you don't want to use the MultiExecutor trick, you can also just follow the regular guide for moor isolates.

[johrpan]

Thanks a lot for your detailed answer. I decided to use an isolate for my purposes and it works as expected!