[Request]: Allow multiple connections and restrict threads to 1 per connection

[ospfranco]

What do you need?

Currently there are some limitations with the architecture of op-sqlite:

• All connections are kept alive in a single map. This limits the amount of connections one can keep alive to one per db "name". This was a simplistic design based on the limitations of the old bridge.
• There is a shared thread pool without any concurrency guarantees. This works well to offload work of the main thread and not block the UI, but as people start doing more and more tasks in parallel the risk of race conditions increases. This is clear with some issues like: Gotcha with async prepared statement execution #199. There is a way to prevent race conditions via db.transaction but this means ALL operations (including reads) need to use a transaction, which is not practical, also, hard to communicate as people don't read the docs in detail.

The plan here is to re-work the architecture of the library. To limit a thread per connection and to move the database object to a HostObject. Then the connection no longer needs to be manually managed on the C++ side of things and can just be returned to JS to be managed by the JS engine. This will not only allow to have multiple connections but also solve threading issues as then sqlite can handle the thread calls on its own by managing the connections.

One can then create a single "write" connection, do all the operations in there without fear of overwrites and multiple read connections, which sqlite guaranteeing the order of operations.