[Feature] Connection Pools

[No767]

Description

This library is dependent on a good amount of projects that require some sort of connection pooling. From looking at the docs, there is no such feature. Ideally the functionality should be similar to the connection pools used by asyncpg

Details

• OS: Fedora 38
• Python version: 3.11.4
• aiosqlite version: 0.19.0
• Can you repro on 'main' branch? N/A
• Can you repro in a clean virtualenv? N/A

[amyreese]

tl;dr is that I'm not convinced a connection pool will do anything but add another layer of Python code to an already fast process. See my response on issue #163 for detail.

That said, if you or someone else is willing to provide a PR that implements optional connection pooling, along with a simple benchmark to validate that it's faster than just spinning up next connections, then I'd be happy to review it and make a decision from there.

[amyreese]

I would also be curious to know what projects require a connection pool rather than just a simple connector. Some bridge code may be trivial to make those projects work.

[No767]

tl;dr is that I'm not convinced a connection pool will do anything but add another layer of Python code to an already fast process. See my response on issue #163 for detail.

That said, if you or someone else is willing to provide a PR that implements optional connection pooling, along with a simple benchmark to validate that it's faster than just spinning up next connections, then I'd be happy to review it and make a decision from there.

Even though all connections are local, the amount of times one application needs to connect to the database constantly is inefficient. Ideally, a pool of connections needs to be maintained instead so a program can set it up once initialized and pull connections from there instead. In this case, it's not a matter of performance but finding a way to not constantly connect on every single call.

I will provide an PR if I have the time to implement this feature.

I would also be curious to know what projects require a connection pool rather than just a simple connector. Some bridge code may be trivial to make those projects work.

The most notable that come into my mind are small-scale Discord bots. The way for developers to work with aiosqlite with these bots is on every single command called, they have to access the database and thus creating vasts of unnecessary connections. This could be easily solved by setting up a connection pool during startup, and then just accessing the connections from the pool when a command is run.

[syedz9]

Hi, any updates as to regarding this feature? Is it currently being worked on?

[bendem]

Hi, any updates as to regarding this feature? Is it currently being worked on?

No benchmark has been provided that hint at pooling actually being useful, so my guess is, it's not going to be worked on. I agree @amyreese on this.

tl;dr is that I'm not convinced a connection pool will do anything but add another layer of Python code to an already fast process. See my response on issue #163 for detail.

Small-scale Discord bots is exactly the kind of cases where pooling doesn't make a difference. You aren't receiving thousands of commands per seconds, you are probably opening a few connections per second for the largest bots, that's not many and scaling your server is going to do much more than pooling connections to a local file.

[Tomalak]

An argument for connection pooling is that sqlite will cache database pages per connection. Page caches are scrapped when the connection object is destroyed. Pooling and re-using connections could be a way to optimize in query-heavy applications.

Discussion and sample implementation: https://codereview.stackexchange.com/questions/285730/simple-connection-pool-for-sqlite-in-python