Summary

For one-off commands (i.e. no app is running), one would expect that the connection is released when an error occurs. MySQL and Postgresql pools relies on context managers from their respective libraries, which does close the connection if error happens, while SQLite pool implemented by mayim does not, hanging the command indefinitely until interrupted with ^C, which shows that a thread does not releases its lock:

^CException ignored in: <module 'threading' from '/usr/lib/python3.11/threading.py'>
Traceback (most recent call last):
  File "/usr/lib/python3.11/threading.py", line 1590, in _shutdown
    lock.acquire()
KeyboardInterrupt: 

Expectation

The connection is closed so that the command can end.

Reproduce

Assuming we have a table for storing user account:

CREATE TABLE account (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL
)

and the query insert_account.sql to insert new user:

INSERT INTO account (username, password) VALUES ($username, $password)

We insert the user twice to raise IntegrityError

async def main():
    executor.insert_account('test', '123')
    executor.insert_account('test', '123')

asyncio.run(main())

The IntegrityError is raised and the script hangs indefinitely.