[Question] Sharing databases between dart and native/ffi code

[svtv]

Hi, I'm just starting to get to know the drift, so I'm sorry if I'm asking the wrong questions. I need to use the database from the dart and native/ffi code at the same time. And the dart part will only read from the database and the native/ffi will only write to it. Updates (writes) may occur periodically. It would be great if dart could get notifications from native about database updates. Could you please suggest how I can solve such a problem?

[simolus3]

You can generally share the database by using the same file path for it in Dart and the native side.

Unfortunately, reacting to updates is not easily possible. Stream queries are a drift feature not directly supported by sqlite3, and there is no general way to dispatch these updates. The only way would be to use platform channels or receive ports with the native side posting about the tables it's changing. Upon receiving such notification in Dart, you can use notifyUpdates on your database to update stream queries.

[svtv]

Thanks for the quick response. As I can see now, notification of updates is not so easy to implement. I'll try your receipt or maybe use other synchronization methods. Regarding the first question. I would kindly appreciate for an example of such a sharing of, for simplicity, an in-memory db. Can an in-memory database be used in this way at all? Must it be open in the single isolate or could it occupy several isolates?

[simolus3]

You can't have them share an in-memory database unless they both use the same instance of the sqlite3 library. On some platforms (macOS and iOS) where we link sqlite3 statically, this is always the case. On others (especially Android), sharing an in-memory database only works when the native code runs in the same process and uses the same copy of sqlite3 as package:sqlite3 in Dart. If you can make that work, sharing updates is actually fairly easy as well. If you can't, the best option is using a temporary file which is opened by both your Dart code and the native layer.

[svtv]

We can pass a pointer to an open db from drift/dart to native/ffi code. And then periodically update the db from the native code. Will this scheme work, do you think? Will the updates be visible on the drift/dart side?

[simolus3]

Right, that will actually make this a lot easier. This allows you to open a database like this:

  final rawDatabase = sqlite3.open(':memory:'); // or alternatively, a file path
  final sqlite3_struct = rawDatabase.handle; // is a sqlite3* and can be used as such

  final db = MyAppDatabase(NativeDatabase.opened(rawDatabase));

Here, sqlite3_struct is a Pointer<Void> which can be cast to a sqlite3* in C and used as a database. The best thing about sharing the underlying database is that you can listen for updates on most tables:

  rawDatabase.updates.listen((update) {
    db.notifyUpdates({
      TableUpdate(
        update.tableName,
        kind: switch (update.kind) {
          SqliteUpdateKind.insert => UpdateKind.insert,
          SqliteUpdateKind.update => UpdateKind.update,
          SqliteUpdateKind.delete => UpdateKind.delete,
        },
      )
    });
  });

That way, updates made natively should also update stream queries in Dart.

[svtv]

Thank you for a brilliant solution! It's exactly what I was looking for.