alembic-postgresql-enum 0.1.3a2

Alembic autogenerate support for creation, alteration and deletion of enums

alembic-postgresql-enum

Alembic autogenerate support for creation, alteration and deletion of enums

Alembic will now automatically:

• Create enums that currently are not in postgres schema
• Remove/add/alter enum values
• Reorder enum values
• Delete unused enums from schema

Usage

Install library:

pip install alembic-postgresql-enum

Add the line:

# env.py
import alembic_postgresql_enum
...

To the top of your env.py.

Examples

Creation of enum

class MyEnum(enum.Enum):
    one = 1
    two = 2
    three = 3


class ExampleTable(BaseModel):
    test_field = Column(Integer, primary_key=True, autoincrement=False)
    enum_field = Column(sqlalchemy.Enum(MyEnum))

This code will generate migration given below:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    sa.Enum('one', 'two', 'three', name='myenum').create(op.get_bind())
    op.add_column('example_table', sa.Column('enum_field', sa.Enum('one', 'two', 'three', name='myenum'), nullable=False))
    op.add_column('example_table', sa.Column('third_field', sa.Float(), nullable=True))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('example_table', 'third_field')
    op.drop_column('example_table', 'enum_field')
    sa.Enum('one', 'two', 'three', name='myenum').drop(op.get_bind())
    # ### end Alembic commands ###

Deletion of unreferenced enum

If enum is defined in postgres schema, but its mentions removed from code - I will be automatically removed

class ExampleTable(BaseModel):
    test_field = Column(Integer, primary_key=True, autoincrement=False)
    # enum_field is removed

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('example_table', 'enum_field')
    sa.Enum('one', 'two', 'four', name='myenum').drop(op.get_bind())
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    sa.Enum('one', 'two', 'four', name='myenum').create(op.get_bind())
    op.add_column('example_table', sa.Column('enum_field', postgresql.ENUM('one', 'two', 'four', name='myenum'), autoincrement=False, nullable=True))
    # ### end Alembic commands ###

Rename enum value

In this case you must manually edit migration

class MyEnum(enum.Enum):
    one = 1
    two = 2
    three = 3 # renamed from `tree`

This code will generate this migration:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'three'], [('example_table', 'enum_field')])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'tree'], [('example_table', 'enum_field')])
    # ### end Alembic commands ###

It is fine if you do not have any data in your database. But in most cases it is not.

So adjust migration like that

def upgrade():
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'tree', 'three'], [('example_table', 'enum_field')])
    op.execute("""UPDATE example_table SET enum_field = 'three' WHERE enum_field = 'tree'""")
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'three'], [('example_table', 'enum_field')])


def downgrade():
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'tree', 'three'], [('example_table', 'enum_field')])
    op.execute("""UPDATE example_table SET enum_field = 'tree' WHERE enum_field = 'three'""")
    op.sync_enum_values('public', 'myenum', ['one', 'two', 'tree'], [('example_table', 'enum_field')])

Expand old values with new one, update all old values with new one, remove old enum value