asyncio-compatible dialect

[cancan101]

SQLAlchemy 1.4 now supports Python asyncio. In order to take advantage of this, the dialect must be "asyncio-compatible". It would be great to have a version of the base Dialect that can be used for this.

A discussion for what that entails: https://github.com/sqlalchemy/sqlalchemy/discussions/7854.

Currently the APSWDialect class subclasses SQLiteDialect, which is not async. There is a SQLiteDialect_aiosqlite that could potentially be used. The goal here is not necessarily to have async operations vis a vis sqlite but rather to allow async operations when connecting to the APIs.

This might be tricky as you would want this to be an async iterator: https://github.com/betodealmeida/shillelagh/blob/97197bd564e96a23c5587be5c9e315f7c0e693ea/src/shillelagh/backends/apsw/db.py#L221 and then likewise have the get_rows call be an async iterator: https://github.com/betodealmeida/shillelagh/blob/97197bd564e96a23c5587be5c9e315f7c0e693ea/src/shillelagh/backends/apsw/vt.py#L439-L441

I filed this upstream though I am not 100% sure this is the right way to solve the ask: https://github.com/rogerbinns/apsw/issues/325

[betodealmeida]

Oof, this sounds hard. I'm going to try to implement this in another dialect (https://github.com/DataJunction/datajunction/blob/main/src/datajunction/sql/sqlalchemy/dialect.py) first to understand better what it entails.

[cancan101]

Yea this might be a pipe dream . Not sure it's possible in SQLite. Might be possible using postgres as engine with fdw