Need Help: Shared Database Isolate between applications

[Leylan24]

Hi, I have an issue where I have two apps writing to the same sqlite database. The issue is that there is two seperate singletons being created and when they both write to the database it locks the database because sqlite can have multiple reads but only single writes. Is there a way I can create a IsolateNameServer where I can share the database connection between two applications so that this does not happen?

[simolus3]

So I assume this is for a desktop machines (since applications are sandboxed on mobile and won't access the same database file in an unrestricted manner)?

If there are indeed two different processes, you can't use an IsolateNameServer for communication between them. Drift provides the general-purpose remote library, which you can use to implement DriftIsolate-like services over all kinds of communication channels. So depending on the OS you could use unix sockets or something to let the processes coordinate access to the database.

[Leylan24]

this is for android OS but it is a custom OS where we have complete control of the device. We tried to use the IsolateNameServer between the two apps but it still creates 2 Isolates. I think we can try and communicate through a network socket to allow database access that is a good idea. I was hoping there was something we weren't understanding about the IsolateNameServer and thats why it wasnt working for us

[rf-westhuizen]

I tried to simulate the above scenario using two basic Flutter applications to see if the isolate instances can be shared between them.

Here's the shared database class to be used by both applications running on the same device:

import 'dart:async';
import 'dart:ui';
import 'package:drift/drift.dart';
import 'package:drift/isolate.dart';
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'dart:io';

part 'shared_database.g.dart';

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'shared_db.sqlite'));
    return NativeDatabase(file);
  });
}

@DriftDatabase(tables: [UserTable])
class SharedDatabase extends _$SharedDatabase {
  SharedDatabase(QueryExecutor executor) : super(executor);

  @override
  int get schemaVersion => 1;

  static SharedDatabase? _sharedInstance;

  factory SharedDatabase.local() {
    _sharedInstance ??= SharedDatabase(_openConnection());
    return _sharedInstance!;
  }

  static Future<void> initialize() async {
    if (_sharedInstance != null) {
      return;
    }

    final sendPort = IsolateNameServer.lookupPortByName('drift_isolate');
    if (sendPort == null) {
      final driftIsolate = await _createDriftIsolate();
      print('runc only once');
      IsolateNameServer.registerPortWithName(
          driftIsolate.connectPort, 'drift_isolate');
      final connection = await driftIsolate.connect();
      _sharedInstance = SharedDatabase(connection);
    } else {
      final driftIsolate = DriftIsolate.fromConnectPort(sendPort);
      final connection = await driftIsolate.connect();
      _sharedInstance = SharedDatabase(connection);
    }
  }

  static Future<DriftIsolate> _createDriftIsolate() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final path = p.join(dbFolder.path, 'shared_db.sqlite');
    return await DriftIsolate.spawn(() {
      return NativeDatabase(File(path));
    });
  }
}

class UserTable extends Table {
  IntColumn get id => integer().autoIncrement()();

  TextColumn get name => text().withLength(min: 1, max: 50)();
}

For Application 1 I call the initialization method:

void main() async {

  WidgetsFlutterBinding.ensureInitialized();

  // Initialize the SharedDatabase
  await SharedDatabase.initialize();

  runApp(MyApp());
}

Then, on the dart side I run a query on the database:

            ElevatedButton(
              onPressed: () async {
                for (var i = 0; i < 5000; i++) {
                  final userId = await SharedDatabase.local().into(SharedDatabase.local().userTable).insert(UserTableCompanion.insert(name: 'User from App 1'));
                  print('Inserted user with id: $userId');
                }
              },
              child: Text('Insert Loop from App 1'),
            ),

I replicated the above logic for Application 2.
However, when await SharedDatabase.initialize(); is called in Application 2 after Application 1 has already been initialized, the final sendPort = IsolateNameServer.lookupPortByName('drift_isolate'); returns null.