Separate Dialect and Connector

[grigi]

Is your feature request related to a problem? Please describe.
Doing work for a client, we requested access to a PostgreSQL database, and was provided an ODBC interface, when asked to have a direct network connection was told that it was "Against policy".
Considering the current architecture if we were using Tortoise it would have been quite the pain point.

Describe the solution you'd like
Separate the SQL Dialect and the DB Connector, so that they are loosely connected.
This way we can allow the user to mix & match them as needed.

This should make sharing connector for both GEO and non-GEO easy, and providing backup routes for db connections (e.g. falling back to aiopg in case asyncpg is not available)

We could allow a Dialect to provide a preferred connector, but allow overriding it manually:
postgres+aiopg:// or postgres+odbc://
To allow the postgres dialect to generate either asyncpg or aiopg depending on availability we should make the dialect optionally return the default connector from a callable.
We could use the same mechanism to return capabilities.

Describe alternatives you've considered
Lots of code duplication, so not much really.

Additional context
It would require a much larger test matrix, which is already getting ridiculously large. Might be useful to split test runs up by base primitive, e.g. DB-variants (on default Python) + Python-versions (doing a testall equivalent) to compress the matrix.
Right now we have 13 runs, which using the proposed compression we can reduce to 9.
If we add the hypothetical aiopg + odbc connectors, it would be 25 vs 12.

[abondar]

Hi
In case of ODBC, is it really possible to implement all features that native driver implements?
I am not really familiar with working through ODBC, but I always thought that it applies certain restrictions on how much of API you could use.

And implementing such flexible function could lead to the problem that users won't really know which driver are they using right now, but results could vary based on what backend was decided to be used. Such behaviour could lead to heisenbugs, which are worst kind of bugs 😄

Regarding using ODBC engine at all - community (or we, as part of community) could just implement ODBC backend and use it.

[grigi]

ODBC is only a common subset, so I assume that some funtionality will not work the same way.
We won't know until we actually test it though. It could be horrible, or it could just work fine.

We still need to separate dialect and connector to make it clean to add (or support adding) GIS dialects. and until that gets off the ground, we might as well test using different drivers, e.g. asyncpg and aiopg.

Then it should be possible to add odbc at a later stage. (even as an external project).

[grigi]

Could do DB capabilities as part of this, as your ODBC question raised a good point that the connector may support different features, so the capabilities may need to be different for each combination... 🤔

[reedjosh]

Hello, I would like to use ODBC as it allows async Oracle connections. Could you point me in the right direction to start work on "... the community (or we, as part of community) could just implement ODBC backend and use it."