using database with flutter background service

[laitooo]

In the documentation you explained clearly how to use the database with isolates , but i don't know yet if is it possible to use the database with the flutter background service plugin?
i prefer to use this plugin it since it's compiled to native services.

[simolus3]

I don't have a lot of experience with the background service package, but this stands out:

The code will executed in isolated process, you can't share reference between UI and Service. Use sendData and onDataReceived to communicate between service and UI.

This means that, by default, you can't share a moor database between the UI and a background service. You can open the database separately of course. The only real downside is that real-time stream queries wouldn't work.

If you also need stream queries, you can implement a moor remote database over the communication primitives provided by that package. This would allow you to use everything moor has to offer across that database. You can use moor over any two-way communication channel (isolates, web workers, TCP sockets, ...) by representing it as a StreamChannel. Since it sounds like that package provides primitives for communication in real-time, it should be possible to use moor over that too.

[simolus3]

Something else that I notice, is that if I got a "SqliteException(5): database is locked, database is locked (code 5)" exception once, then no matter how many times I close the db, stop the isolate(foreground service) and start it again to open a new connection this error is returned to any request

That doesn't sound right, I'll try to reproduce this with something simpler and see what can be done about it. Even without closing a database retrying should probably be enough.
Just to be sure that I got your current setup right, you're essentially opening two independent databases on different isolates here, right?

I believe I have seen a SO discussion where was mentioned that will close the connections on the other isolates too

Moor's isolate implementation is essentially a client-server architecture. There's a one fixed isolate managing the underlying database connection. Other isolates essentially act as clients, making rpc-style request to the server isolate. The server distributes updates, manages transactions across multiple clients, etc.
Since the server isolate is responsible for managing the underlying database connection, it going away means that all other connections are closed too. The only "real" database connection lives on the background isolate.

I didn't understand quite good the closing process of the DB, currently I close the DB with DB.close() from each background isolate. However from the apps isolate I don't do any closing as I can not detect killing the app in most cases and assume the connection will be stopped with the process, is that correct?

Yes. If the whole process is stopped the isolates go down with it of course. So that should stop the database connection.
Even if all connections are closed on client isolates, the server isolate continues to run and keeps the connection open (because there could be another connection coming in in the future). shutdownAll stops the server.

I think you might be able to use something like an IsolateNameServer to detect if a moor isolate is already running and start it if necessary. If the server isolate is stopped while there's a client active (for instance because it was started by the UI which in turn was outlived by a long-running service), the service could restart the isolate once it goes down. That way you would always have a single moor isolate when it's in use.

Sending stream updates only over isolate ports could work as well. That's pretty much what moor's doing already, you can check the implementation for the client here and for the server here. I hope that answers most of your questions, please let me know if I missed something.

[2math]

That doesn't sound right, I'll try to reproduce this with something simpler and see what can be done about it. Even without closing a database retrying should probably be enough.
Just to be sure that I got your current setup right, you're essentially opening two independent databases on different isolates here, right?

Yes, I open two independent databases on different isolates. On first is writing data like every 10 milliseconds and on the other is querying the same data for every 10 mills.
I have tried to write a retry function which tries up to 3 times if the exception is SqliteException and the main code is == 5, but with same result.
Btw I haven't migrated the project to null safety yet and here are my Moor dependencies from the pubspec

  #DB
  moor: ^3.4.0
  moor_ffi: ^0.8.0
  moor_db_viewer: ^2.0.3
  #DB end

I must mention that yesterday I have managed to connect to the database from another isolate(the foreground service) instead of creating one 🥳️ . It is so easy and don't know why I was complicating it so much before 🤔.
I first check if there is a registered port from the UI and try to connect from it, else is creating the DB as was before.
What was interesting is that when I kill the app(swiped out from recent apps) and the foreground service continues to work, it still has a working connection to the DB. I was afraid that the connection will be lost since the main isolate(UI) was down. The only way to stop it manually was with a force stop from Logcat(same like from OS app settings force stop).
Another interesting thing is that when the app is killed and the foreground service is working I see in Logcat the same process is a live. Once the service is finished and stopped, the process is still alive. I guess some memory leak appears and hopefully will be cleared by the OS. In fact same thing happens when you exit the app by the back button from last screen in the Navigator, I see the D/DART/NATIVE(21835): onDetachedFromEngine message, but the process is still alive. I guess the OS is keeping it alive until it needs resources(probably something new from Android 11)

I'm going to test with the Workmanager today. Here it will be a bit more complicated as at the time when the Workmanager's task is started the app may not be a live and will have to create a DB instead of connecting to existing one.
Which is fine and should work as is right now. The other 2 cases are when the workmanager is connected to the UI's db and the app is killed, and when the workmanager is with independent db and the app is started.
The last case is not possible from foreground service, because is always started from the app and it will always has a connection from the UI. However from the workmanager is a possible case and maybe for that case sending the data on a port will be the solution.

[2math]

I have some new discoveries from today :)

Even if I exit the app with the back button and start the app again from launcher icon, I can confirm that the MoorIsolate is still alive and I can connect to it from the main isolate. Actually if I don't the main isolate(UI) will spawn a new MoorIsolate and we will have a memory leak for sure.
My concern, about android holding the app alive, when I click the back button to exit, is confirmed on samsung s8+(Android 9) too.

So now the solution on my side is first to check for existing connection and try to connect or if not exist to create DB instance, no matter on which isolate.
Just from the UI I spawn a new MoorIsoalte and from the others I create an independent DB on the same isolate.
On each isolate I have a flag to indicate if the DB is connected or created, so according to this flag I can tell on my repository if should send data on the UI port or not(if we are connected the streams are working).

Now the main issue is, how to detect if the application is closed after a background isolate is done, but was connected to the UI MoorIsolate?
In that case I should call shutdownAll() connections instead of it's own.

I registered a receive port at the end of _startBackground(which creates the MoorIsolate) and listen on it for a command to shutdownAll.
I also registered on app start a receive port on the UI(main() function), which listens for a command to respond to the foreground service that is alive.
On the foreground service, before to finish I send a command to the UI to check if is alive and start listening for an answer, at the same time run a Timer for 1 second, which will close the port and send a command to the _startBackground port to shutdownAll, because the UI is gone and the service is done, but current DB was connected to remote MoorIsolate.
If I got an answer from the UI before the timer, the timer it self is stopped and the service closes it's db connection only(because the UI is alive).

void _startBackground(_IsolateStartRequest request) {
  final executor = VmDatabase(File(request.targetPath));

  final moorIsolate = MoorIsolate.inCurrent(
    () => DatabaseConnection.fromExecutor(executor),
  );

  request.sendMoorIsolate.send(moorIsolate);

  ReceivePort receivePort = ReceivePort();

  IsolateNameServer.removePortNameMapping(Constants.MoorDBPortStopAll);

  var res = IsolateNameServer.registerPortWithName(receivePort.sendPort, Constants.MoorDBPortStopAll);

  print("register MoorDBPortStopAll Port $res");
  //listen on the port to shutdownAll
  receivePort.listen((message) async{
    print("message on MoorDBPortStopAll : $message");

    if(message == DbManager.CloseAll){
      print("close all db connections!");

      moorIsolate.shutdownAll().then((_){
	        receivePort.close();
		IsolateNameServer.removePortNameMapping(Constants.MoorDBPort);
		IsolateNameServer.removePortNameMapping(Constants.MoorDBPortStopAll);
	  }).catchError((error){
        Log.error("try to shutdownAll", error: error);
      });
	  
    }
  });
}

For some reason when I send to the _startBackground port a command to shutdownAll, there is a crash as :

E/flutter ( 5835): [ERROR:flutter/runtime/dart_isolate.cc(1028)] Unhandled exception:
E/flutter ( 5835): Instance of 'ConnectionClosedException'

Which is not even received in my catchError callback.

When I checked the Exception documentations it says :

/// Exception thrown when there are outstanding pending requests at the time the
/// isolate connection was cancelled.
class ConnectionClosedException implements Exception {

I know that there are no current requests, however probably when I killed the app, the UI isolate was currently requesting data which got held?

Now what is interesting is that on next app start, there is an existing port from the DB to connect with(because shutdownAll crashed). But when the app wants to connect to it, it hangs there. My assumption is that shutdownAll actually closed the connections and the isolate(or maybe partial), also because of the exception, was not removing the port from the IsolateNameServer.
So I had to change it and remove the port before the execution of shutdownAll, to make sure on next app start, will not stuck on the connection call.

//listen on the port to shutdownAll
  receivePort.listen((message) async{
    print("message on MoorDBPortStopAll : $message");

    if(message == DbManager.CloseAll){
      receivePort.close();
      IsolateNameServer.removePortNameMapping(Constants.MoorDBPort);
      IsolateNameServer.removePortNameMapping(Constants.MoorDBPortStopAll);

      print("close all db connections!");

      moorIsolate.shutdownAll().catchError((error){
        Log.error("try to shutdownAll", error: error);
      });
    }
  });

Now the crash is still there, but on next start the app creates a new MoorIsolate and is back to normal.
Remains the question if previous isolate(which spawn the MoorIsolate) is still alive?

I tried to experiment and at the end of the service execution simulated that the app is not returning on the port for existing check.
Then the service sent a message on the _startBackground port to close all. And there was no crash, in fact all DB connections were closed, because when tried to open a screen in the app with a request I got an error as :
"Bad state: Tried to send request (id = 200): EnsureOpen(30, null) over isolate channel, but the connection was closed!"

So something is happening when the user kills the app, as swiping out from recent apps or exit by the back button and having a foreground service(background isolate) running and connected to the MoorIsolate . Then the isolate that spawn the MoorIsolate from UI is still alive, but when you try to call shutdownAll() on it crashes with "ConnectionClosedException ".

[2math]

The workmanager has a bit different behavior. If is started while the app is alive, it connects to the DB.
On finish checks with echo on a port to the UI(the same way as from foreground service with 1 sec. timout timer).
If the UI respond, then closes it's instance only, if not sends a command to the MoorIsolate port to shutdownAll.
However it appears that if the workmanager is started while the app is running and you kill the app before the WM to complete, then the WM task is stopped as well. Because of the WM backoff policy, the task is restarted and there is no registered port from the MoorIsolate to connect to and I assume the isolate is down too.

So I can confirm the DB can connect and work correct from Workmanager and Foreground service.

The only problem that I still can not figure it out is when the app is killed and the foreground service remains, then on finish is trying to shutdownAll connections, but there is an internal crash
E/flutter ( 5835): [ERROR:flutter/runtime/dart_isolate.cc(1028)] Unhandled exception:
E/flutter ( 5835): Instance of 'ConnectionClosedException'

Before that crash I clear all connected ports, so even if a new isolate is started or the app it self will not connect to the one that crashed and I hope at some time the GC will clear it out :(

[2math]

What if, instead of moorIsolate.shutdownAll() I call Isolate.current.kill() ?

On theory that should close all connections too?

I have tested it on my side and there is no error.
Also if I do that while another isolate is connected to the DB, I can see that the connection on that isolate is closed and receives no data

[North101]

My current solution is to communicate with ReceivePort, as both isolates basically has the same functionality they check for registered SendPort from the UI and pass JSON data on it, this way on the queue screen and also some others that has a gallery inside, are listening for those changes and do the updates on it's own instead of pulling data from the DB.

The only real downside is that real-time stream queries wouldn't work.

@simolus3
I haven't really explored isolates but it sounds like you could listen for table updates and then send those tables in SendPort and have the ReceivePort trigger the tables for updates

[2math]

@simolus3
I haven't really explored isolates but it sounds like you could listen for table updates and then send those tables in SendPort and have the ReceivePort trigger the tables for updates

Even if is possible, that will probably not solve the problem with locking DB. If you have intensive work on the isolate to read and write and at the same time to notify other isolates to pull from the DB.

Unless you can send trough the port the entire changes and push on the stream those changes instead of querying the DB.
I'm currently converting data to JSON string as String is primitive that can be sent trough a port. Not sure what is the size limitation though. Also there is a small delay from the asynchronous port communication

[laitooo]

I was able to share data between the flutter background service and the app .
1- I first load the downloads into list in the app side.
2- I then listen to any download update using the onDataReceived function in the app side.
3- Update the list in the app side depending on the update type coming from the service.
4- If user pressed an edit or delete button , i send the update request to the service side and process it there.

So in conclusion the database is modified in the server side only and listens to the updates in the app side.
I published my work here.
@simolus3 thanks a lot.