sqlacodegen generates base sqlalchemy.ARRAY for PostgreSQL text[], causing .contains() NotImplementedError (should use sqlalchemy.dialects.postgresql.ARRAY) #441
Description
Things to check first
-
I have searched the existing issues and didn't find my bug already reported there
-
I have checked that my bug is still present in the latest release
Sqlacodegen version
3.2.0
SQLAlchemy version
2.0.44
RDBMS vendor
PostgreSQL
What happened?
When generating models from a PostgreSQL schema that contains an array column (e.g. test.tags text[]), sqlacodegen outputs from sqlalchemy import ARRAY and uses ARRAY(Text()) for the column type.
In SQLAlchemy 2.0, the base sqlalchemy.ARRAY does not implement PostgreSQL-specific array operators such as .contains(), so calling Model.tags.contains([...]) raises:
NotImplementedError: ARRAY.contains() not implemented for the base ARRAY type; please use the dialect-specific ARRAY type
This makes the generated model unusable for common PostgreSQL array queries unless the import/type is manually adjusted to sqlalchemy.dialects.postgresql.ARRAY.
Database schema for reproducing the bug
- Create a PostgreSQL table with an array column:
CREATE SCHEMA IF NOT EXISTS db;
CREATE TABLE db.test (
id text PRIMARY KEY,
tags text[]
);
- Run sqlacodegen (declarative generator), e.g.:
sqlacodegen postgresql://USER:PASSWORD@localhost:5432/DB --schemas db --generator declarative - Observe generated code includes base ARRAY import:
from sqlalchemy import ARRAY, Text
# ...
tags = mapped_column(ARRAY(Text()))
- Run a query expression using .contains():
from database.models.models import test
Test.tags.contains(["hello"])
Raises:
NotImplementedError: ARRAY.contains() not implemented for the base ARRAY type; please use the dialect-specific ARRAY type
For PostgreSQL array columns (text[], int[], etc.), generated code should use the dialect-specific type:
from sqlalchemy.dialects.postgresql import ARRAY
...
tags = mapped_column(ARRAY(Text()))
so that .contains() (and other PostgreSQL array operators) work.
Environment
Python: 3.13
SQLAlchemy: >=2.0.44
sqlacodegen: >=3.2.0
Database: PostgreSQL
Column: Test.tags text[]