General development suggestions

[vhakulinen]

Responding to https://github.com/margelo/react-native-quick-sqlite/pull/55#issuecomment-2391285662, since bloating that PR with extra hopes and dreams wouldn't be productive.

• [ ] Concurrency. The sqlite connection is currently opened in serialized mode, which means (afaik) that threadpool created for each connection is basically useless. In serialized mode only one operation can be executed at any given time, which means that only one thread in the thread pool is able to do any sql work. Lifting the serialized mode with the current design won't do any good, because you would need to duplicate all the locking work that sqlite is currently doing (so you gain nothing). Best to just drop the thread pool and use one background thread per connection.
• [ ] Only first statement is executed, rest is (silently) ignored. Excluding the silently ignoring stuff this is generally fine, but it would be really useful to have API for executing all the sql statements in a string (think of large schema migration files).
• [ ] Extra copying, part of which is mentioned in #30
• [ ] Disjoint between cpp <-> js w.r.t. the implementation. There is extra book keeping of open connections, "transactions" are implemented in js side which sounds like a leaky abstraction. Probably other things that I don't remember at the moment.
• [ ] No way to hold references to prepared statements. Not the most important thing, but has performance implications.
• [ ] General API improvements (named parameters, possibility to get returned rows as objects along with arrays, etc..?)

W.r.t. Concurrency. It would be nice to be able to have a read only connection pool and the one connection (or another connection pool) for writing. This could be a huge performance win for more complex application that has heavy database usage.

Having studied this code base (and having had my own, long abandoned venture to alternative, sqlite-jsi), I have some idea of the effort required to implement these things. While I dont have the time / resources to work on this myself (nor am I expecting anyone to do the work either!), if someone is up for the task I am more than happy to chat about the ideas / design / implementation details.

As the sqlite-jsi repo might suggest, I have interest in having a solid sqlite library for react-native - I just lack the time to work on it.