Autogenerate does not consider schema for enum type

[renedupont]

Describe the bug I am using Postgres 12, Alembic (1.7.1) and SQLAlchemy (1.4.23) and have something like this SQLAlchemy enum in my model:

class MyEnum(enum.Enum):
    one= 'one'
    two= 'two'

...

my_enum = Column(Enum(MyEnum), nullable=False)

When I run alembic revision --autogenerate

I get in my migration script:

sa.Column('my_enum',
          sa.Enum('one', 'two', name='myenum'),
          nullable=False),

I am using another schema than the default (public) one and while there is a schema arg that can be set (and I am currently changing the script manually adding it), it is not being considered by autogenerate. For stuff like tables and so on, it works perfectly and the schema is considered.

Expected behavior I'd like to see the following being autogenerated for my example case:

sa.Column('my_enum',
          sa.Enum('one', 'two', name='myenum', schema='my_schema_name'), 
          nullable=False),

Versions.

• OS: Ubuntu 20.04
• Python: 3.8.10
• Alembic: 1.7.1
• SQLAlchemy: 1.4.23
• Database: Postgres 12
• DBAPI:

[CaselIT]

Thanks for reporting.

I am using another schema than the default (public) one and while there is a schema arg that can be se

is the schema set on the MetaData?

[renedupont]

In env.py I have

from myapp.models import Base
... 
target_metadata = Base.metadata

and in myapp.models I do:

engine: Engine = create_engine(myDbUrl, pool_pre_ping=True)
db_session: scoped_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

[CaselIT]

Where is the schema defined?

[renedupont]

In myapp.models I have a base model like this where I set the schema:

class BaseModel(Base):
    __abstract__ = True
    __table_args__ = {'extend_existing': True, 'schema': 'my_schema_name'}
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

The model class that contains the enum in question is derived from this base model class.

[renedupont]

Looking at it again, I think I have to add more info of the scenario described in my opening post. The auto-generated full example looks actually more like this:

    op.create_table('my_table_name',
                              sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                              sa.Column('my_enum',
                                                sa.Enum('one', 'two', name='myenum'), nullable=False),
                              sa.PrimaryKeyConstraint('id'),
                              schema='my_schema_name'
                              )

So it has a schema at the create_table level, but when running this script, the enum which gets created as a postgres TYPE in the database is still being created in the default (public) schema. When adding the schema name to the sa.Enum(...) as mentioned in my opening post in the expected behaviour part, it is being created in the right schema.

I hope that helped explaining a bit more in case it was still confusing.

[vinayman]

Is there any more news on this request? I am encountering the exact same issue

[zzzeek]

no. please edit your files manually

[rhynzler]

I have to modify (remove) the same change in the files generated by each migration because I have multiple tables in various schemas that reference several Enums with the same names

[smiling-watermelon]

@zzzeek, by:

no. please edit your files manually

Do you mean that the fix for this will not be created, or do you mean that at the moment there is no fix, and for now, we should make manual adjustments, and in the future this will be fixed?

---

I have an even comical situation in this regard:

...
    op.alter_column(
        "entity_name"
        "status",
        existing_type=sa.Enum("INITIAL", "PENDING", "ERROR", "SUCCESS", name="status"),
        type_=postgresql.ENUM(
            "INITIAL",
            "PENDING",
            "ERROR",
            "SUCCESS",
            name="status",
            schema="schema_name",
        ),
        existing_nullable=False,
        schema="schema_name",
    )
...

And migration fails like this:

sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedObject) type "statustype" does not exist
[SQL: ALTER TABLE schema_name.entity_name ALTER COLUMN type TYPE statustype ]
(Background on this error at: https://sqlalche.me/e/20/f405)

So it doesn't see the schema of a sa.Enum object (rightfully so - it is not set, even though in my model I have set it for ENUM object explicitly), even though postgresql.ENUM does have schema specified. Or so I presume :cry:

[zzzeek]

@zzzeek, by:

no. please edit your files manually

Do you mean that the fix for this will not be created, or do you mean that at the moment there is no fix, and for now, we should make manual adjustments, and in the future this will be fixed?

the bug is opened, which means to the degree that maintenance on Alembic continues, this bug is part of what's considered available work.

that said, ENUM is a wide, sprawling issue for which I maintain only a holistic solution would be appropriate, that is, major architectural / workflow enhancements to Alembic to support this single, PostgreSQL-only datatype, so I have no plans in the near term to work on it (this has been the case for some years now)