SQLite async connection pool for high-performance

If you’re building Python async apps (FastAPI, background jobs, etc.) with SQLite, you’ll eventually hit two issues

- Opening/closing connections is fast, but not free—overhead adds up under load

- SQLite writes are globally locked

aiosqlitepool is a tiny library that adds connection pooling for any asyncio SQLite driver (like aiosqlite):

- It avoids repeated database connection setup (syscalls, memory allocation) and teardown (syscalls, deallocation) by reusing long-lived connections

- Long-lived connections keep SQLite's in-memory page cache "hot." This serves frequently requested data directly from memory, speeding up repetitive queries and reducing I/O operations

- Allows your application to process significantly more database queries per second under heavy load

Enjoy!

How does this help with the second issue, the write locks?

No idea if it applies, but one way would be to direct all writes (including any transaction that may eventually write) to a single connection.

Then writers queue up, while readers are unimpeded.

if you enable WAL mode with sqlite then readers are not blocked by writer so only writers queue up without needing any special case handling to archive it

(in general you _really_ should use WAL mode if using sqlite concurrently, you also should read the documentation about WAL mode tho)

I was running into some horrendous issues with WAL, where the WAL file would grow boundlessly, eventually leading to veery slow reads and writes.

It's fixable by periodically forcing the WAL to be truncated, but it took me a lot of time and pain to figure it out.