Obtain all active connections in the pool

[js2702]

We are currently in the process of testing PowerSync to migrate one of our apps. It makes heavy use of ATTACH/DETACH during runtime and we've been previously using just a single connection on a background isolate with Drift. So now with sqlite_async we have at least two connections. Making it harder to run the ATTACH when needed.

We were wondering how feasible it would be to add a way to obtain a list of connections so that we can run the ATTACH query on all instances.

One solution would be running the attach before using the attached db in a query, but on our app that would be unfeasible because the attach acts like a global state and all the app logic relies on that attachment existing.

Also, one of our attached databases is pretty large so ideally we would prefer to open the read connections only once and don't tear them down. Is that how it currently works, or are the read connections spawned/teared down dynamically?

[rkistner]

Read connections do stay open until you explicitly close the pool.

Just to make sure, you need to do the ATTACH dynamically at runtime, and not just when connections are opened?

A solution for this would also need to cater for the case of the of a new connection being opened after the ATTACH call - will have to think about ways to handle that.

[js2702]

Yes, we have both situations, we attach a database on open and we need to be able to attach databases dynamically on runtime.

[davidmartos96]

@rkistner We are currently holding the global state of "current active ATTACHes" in a file, that is read inside the openFactory open synchronously and attaching all that is necessary when new connections are opened. It works but we would like to get rid of the file and just keep that information in memory. For that we are thinking about asking the main isolate about the current ATTACHes, but that would be async, so no way to do it in the factory. Could there be a way to do async work when setting up a connection via the open factory? It might open other use cases.