Altering an Enum field using Alembic
I decided to try to follow the postgres approach as directly as possible and came up with the following migration.
from alembic import opimport sqlalchemy as saold_options = ('nonexistent_executable', 'signal', 'success', 'timed_out')new_options = sorted(old_options + ('output_limit_exceeded',))old_type = sa.Enum(*old_options, name='status')new_type = sa.Enum(*new_options, name='status')tmp_type = sa.Enum(*new_options, name='_status')tcr = sa.sql.table('testcaseresult', sa.Column('status', new_type, nullable=False))def upgrade(): # Create a tempoary "_status" type, convert and drop the "old" type tmp_type.create(op.get_bind(), checkfirst=False) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status TYPE _status' ' USING status::text::_status') old_type.drop(op.get_bind(), checkfirst=False) # Create and convert to the "new" status type new_type.create(op.get_bind(), checkfirst=False) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status TYPE status' ' USING status::text::status') tmp_type.drop(op.get_bind(), checkfirst=False)def downgrade(): # Convert 'output_limit_exceeded' status into 'timed_out' op.execute(tcr.update().where(tcr.c.status==u'output_limit_exceeded') .values(status='timed_out')) # Create a tempoary "_status" type, convert and drop the "new" type tmp_type.create(op.get_bind(), checkfirst=False) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status TYPE _status' ' USING status::text::_status') new_type.drop(op.get_bind(), checkfirst=False) # Create and convert to the "old" status type old_type.create(op.get_bind(), checkfirst=False) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status TYPE status' ' USING status::text::status') tmp_type.drop(op.get_bind(), checkfirst=False)
It appears that alembic has no direct support for the USING
statement in its alter_table
method.
I used a bit simpler approach with less steps than the accepted answer, which I based this on. In this example I will pretend the enum in question is called 'status_enum', because in the accepted answer the use of 'status' for both the column and enum confused me.
from alembic import op import sqlalchemy as saname = 'status_enum'tmp_name = 'tmp_' + nameold_options = ('nonexistent_executable', 'signal', 'success', 'timed_out')new_options = sorted(old_options + ('output_limit_exceeded',))new_type = sa.Enum(*new_options, name=name)old_type = sa.Enum(*old_options, name=name)tcr = sa.sql.table('testcaseresult', sa.Column('status', new_type, nullable=False))def upgrade(): op.execute('ALTER TYPE ' + name + ' RENAME TO ' + tmp_name) new_type.create(op.get_bind()) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status ' + 'TYPE ' + name + ' USING status::text::' + name) op.execute('DROP TYPE ' + tmp_name)def downgrade(): # Convert 'output_limit_exceeded' status into 'timed_out' op.execute(tcr.update().where(tcr.c.status=='output_limit_exceeded') .values(status='timed_out')) op.execute('ALTER TYPE ' + name + ' RENAME TO ' + tmp_name) old_type.create(op.get_bind()) op.execute('ALTER TABLE testcaseresult ALTER COLUMN status ' + 'TYPE ' + name + ' USING status::text::' + name) op.execute('DROP TYPE ' + tmp_name)
This runs without problems:
from alembic import opdef upgrade(): op.execute("COMMIT") op.execute("ALTER TYPE enum_type ADD VALUE 'new_value'")def downgrade(): ...