Is it possible to support dialect as option

[shengha2]

We are generating our python code using sqlacodegen so far it works really well. But recently, we bump into an issue with only the Postgres dialect ARRAY support feature that could not be supported with sqlalchemy ARRAY.

We have (generated from generator.py)
from sqlalchemy import ARRAY

We need
from sqlalchemy.dialects.postgresql import ARRAY

Is it possible to specify a flag for generating the postgresql dialect?

[leonarduschen]

Quoting from sqlalchemy.ARRAY official documentation

This type serves as the basis for all ARRAY operations. However, currently only the PostgreSQL backend has support for SQL arrays in SQLAlchemy. It is recommended to use the PostgreSQL-specific sqlalchemy.dialects.postgresql.ARRAY type directly when using ARRAY types with PostgreSQL, as it provides additional operators specific to that backend.

@agronholm seems like we can just replace from sqlalchemy import ARRAY with from sqlalchemy.dialects.postgresql import ARRAY

[leonarduschen]

On a second thought...

Current logic for column type is pretty straightforward: choose the most generic type that still has the same compiled SQL expression (with the special case of Postgresql's float), users who wish to use dialect specific types will have to make manual changes.

Manually changing from sqlalchemy import ARRAY with from sqlalchemy.dialects.postgresql import ARRAY in the generated code is easy and IMO doesn't warrant adding an extra special case despite what the docs says above.

Adding the option to use dialect specific types instead of generic types sounds like a good idea to me though, implementation will be quite straightforward as this is already the default behavior of sqlalchemy.MetaData.reflect

[shengha2]

Thank you for the thoughtful solutions proposed here. One thing that is hard in our case is that we maintain a large project and regenerate the orm via github action. Sometimes we can do many code generations in a day. Manually changing from sqlalchemy import ARRAY with from sqlalchemy.dialects.postgresql import ARRAY would not be feasible in our case.

[leonarduschen]

Thanks for elaborating on your usecase. I'll create an issue for an option to generate dialect-specific column types.

[shengha2]

Thank you