Migration downgrade doesn't drop type created by Enum

[ale7canna]

Given a model definition with an Enum, the resulting autogenerated migration creates a type in the database (Postgresql).
The relative downgrade migration doesn't drop it from the database, making it impossible to perform an upgrade (since this type is already existing).

[zzzeek]

hi -

we are not fixing any autogenrate enum issues in the near future however when you say "downgrade is impossible" I would need to know what migration operation you are referring towards. you can drop enums manually using enum.drop(op.get_bind()).

[ale7canna]

Hi @zzzeek, I'll try to be clearer. Firstly, I want to point out that I'm working on postgres.

The following class definition

class SomeType(enum.Enum):
    foo = 'foo'
    bar = 'bar'


class SomeTestTable(BaseTable):
    __tablename__ = 'some_table'
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    some_type = Column(Enum(SomeType))

generates the following migration

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('some_table',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('some_type', sa.Enum('foo', 'bar', name='sometype'), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('some_table')
    # ### end Alembic commands ###

What I see on the database is the creation of a new type sometype performed with this query:

 CREATE TYPE sometype AS ENUM ('foo', 'bar')

If I downgrade this migration, the table is correctly dropped, but not the type. This make impossible to upgrading back to head, since the upgrading operation will try to create again the sometype type.

[CaselIT]

This make impossible to upgrading back to head, since the upgrading operation will try to create again the sometype type.

As mentioned in the downgrade comment, you can adjust the downgrade function.
If you add sa.Enum('foo', 'bar', name='sometype').drop(op.get_bind()) after the drop_table it will work correctly

[ale7canna]

Thanks guys for your answers. I'll do as you suggest.
Can you confirm this is something you're not going to address in the next releases?

[CaselIT]

Can you confirm this is something you're not going to address in the next releases?

this will not be worked on for the next release.

[zzzeek]

enum is a long term issue that requires a full end-to-end approach be devised as there are many complications to the problem, including different database backends, SQLAlchemy's awkward constraints that it generates, and the fact that PostgreSQL wont let you change the elements of an ENUM inside of a transaction. if we make any API decisions or behaviors that conflict with a broader plan to support enum migrations in all forms, it would be much worse. this bug is already an example of early assumptions that don't hold up in practice (SQLAlchemy creates the "enum" for you automatically, but then alembic's "drop table" command doesn't know about the columns). im hoping after SQLAlchemy 2.0 is out and all tools are on python 3 i might have time to start thinking about an all new approach to the whole issue.

[CaselIT]

and the fact that PostgreSQL wont let you change the elements of an ENUM inside of a transaction

Another thing to consider on PostgreSQL is that emun are append only, you cannot remove values from them

[davidjb99]

I understand this is a difficult problem to solve but it would be a useful feature.

there are many ways to update/drop enums in Postgres outlined here

https://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/

[romanzdk]

I assume op.execute('DROP TYPE typename;') would also work?

[CaselIT]

sure, you can run any supported sql command by using op.execute