Enabling readPool for in-memory database causes INSERT/UPDATE/DELETE commands to not work

[alexmercerind]

I create a new database using the setup below (it is more or less a modification of Drift's own source-code):

class InMemoryDatabase extends $Database {
  InMemoryDatabase() : super(LazyDatabase(() => _InMemoryDatabase.create('/path/to/database.db')));
}

class _InMemoryDatabase extends DelegatedDatabase {
  _InMemoryDatabase(super.delegate) : super(logStatements: true);

  static DatabaseConnection create(String file) {
    return DatabaseConnection.delayed(Future.sync(() async {
      const readPool = 4; // INSERT/UPDATE/DELETE work as intended for 0.

      final receiveIsolate = ReceivePort();
      final receive = StreamQueue(receiveIsolate.cast<DriftIsolate>());

      Future<void> spawnIsolate(String kind) async {
        await Isolate.spawn(
          _InMemoryIsolateStartup.start,
          _InMemoryIsolateStartup(
            file,
            receiveIsolate.sendPort,
          ),
          debugName: 'Drift isolate $kind for $file',
        );
      }

      await spawnIsolate('worker');
      final driftIsolate = await receive.next;

      var connection = await driftIsolate.connect(singleClientMode: true);
      if (readPool != 0) {
        final readers = <QueryExecutor>[];

        for (var i = 0; i < readPool; i++) {
          await spawnIsolate('reader');
        }

        for (var i = 0; i < readPool; i++) {
          final spawned = await receive.next;
          readers.add(await spawned.connect(singleClientMode: true));
        }

        connection = DatabaseConnection(
          MultiExecutor.withReadPool(
            reads: readers,
            write: connection.executor,
          ),
          streamQueries: connection.streamQueries,
          connectionData: connection.connectionData,
        );
      }

      await receive.cancel();
      receiveIsolate.close();
      return connection;
    }));
  }
}

class _InMemoryDelegate extends impl.Sqlite3Delegate<impl.Database> {
  // The disk database file (used to restore the in-memory database).
  final String file;

  _InMemoryDelegate(this.file) : super(null, enableMigrations: true, cachePreparedStatements: true);

  @override
  impl.Database openDatabase() {
    // Create in-memory database with maximum possible performance (no concern for durability).
    final memory = impl.sqlite3.open("file:in-memory-database?mode=memory&cache=shared", mutex: false, uri: true) as impl.FfiDatabaseImplementation
      ..execute('pragma journal_mode=OFF;')
      ..execute('PRAGMA synchronous=OFF;');
    // The execute() allows to use readPool in the in-memory database.
    try {
      final disk = impl.sqlite3.open(file, mode: impl.OpenMode.readOnly);
      memory.restore(disk);
      disk.dispose();
    } catch (_) {
      print('_InMemoryDelegate: openDatabase: No disk database located at: $file');
    }
    return memory;
  }

  @override
  Future<void> runBatched(BatchedStatements statements) {
    return Future.sync(() => runBatchSync(statements));
  }

  @override
  Future<void> runCustom(String statement, List<Object?> args) {
    return Future.sync(() => runWithArgsSync(statement, args));
  }

  @override
  Future<int> runInsert(String statement, List<Object?> args) {
    return Future.sync(() {
      runWithArgsSync(statement, args);
      return database.lastInsertRowId;
    });
  }

  @override
  Future<int> runUpdate(String statement, List<Object?> args) {
    return Future.sync(() {
      runWithArgsSync(statement, args);
      return database.updatedRows;
    });
  }

  @override
  Future<void> close() async {
    await super.close();

    if (closeUnderlyingWhenClosed) {
      try {
        impl.tracker.markClosed(database);
      } on SqliteException {
        // NO/OP
      }

      database.dispose();
    }
  }
}

class _InMemoryIsolateStartup {
  final String file;
  final SendPort sendServer;

  _InMemoryIsolateStartup(this.file, this.sendServer);

  static Future<void> start(_InMemoryIsolateStartup startup) async {
    final isolate = DriftIsolate.inCurrent(() => DatabaseConnection(_InMemoryDatabase(_InMemoryDelegate(startup.file))));
    startup.sendServer.send(isolate);
  }
}

[alexmercerind]

I can't seem to find any reasoning on the internet as to why this shouldn't work.

[alexmercerind]

Actually it works correctly inside unit-tests, but not when I build for Windows.

[simolus3]

sqlite3_flutter_libs omits the shared cache option as recommended, which is why the option has no effect on Flutter builds while working in unit tests.

I can't really come up with a workaround for this except opening a temporary database on disk.

[alexmercerind]

Thanks a lot for the reply. Yeah... it seems package:drift's SQLite build strips out few things.
Turns out I'm getting good performance after enabling existing WAL & readPool setup recommended by the package. No need to have a redundant in-memory db now.