SQLite connection pool isn't

[coderanger]

The SQLite dialect appears to open and close connections on every statement. This is causing sqlite3.OperationalError: unable to open database file during periods of heavy write activity (probably because there's a short window after opening a connection when another can't be opened?). Also this just seems wasteful as it throws away the statement cache and generates lots of unneeded I/O load.

[MRuecklCC]

Another issue with the current sqlite behaviour is that when an sqlite://:inmemory: is used, every connection uses a fresh in memory database. This essentially renders the whole sqlite inmemory functionality useless, as the persistent state that should be maintained during the application lifetime just gets truncated.

[MRuecklCC]

Maybe the way to go is to have a single instance of the connection behind an async mutex. Then this connection can be handed out to the users (locking the mutex) and once done put back (unlocking the mutex).

[aminalaee]

I'll try to take a look into this, if you find a solution feel free to create a PR for it though.