1

I'm working on a Flask app using Flask-Migrate (and therefore Alembic) to manage changes to the DB. I've run into an issue early on where changing some not-nullable boolean columns has triggered an error in the underlying DB.

I am removing one boolean column, and renaming another (which amounts to two deletes and an add).

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('is_enabled', sa.Boolean(), nullable=False))
    op.drop_column('user', 'is_disabled')
    op.drop_column('user', 'is_deleted')
    # ### end Alembic commands ###

Since there is already data in the database, the add needs a default value to use for existing rows. However, the SQLAlchemy folks have already passed on adding (portable) support for server_default in Boolean columns.

Given these conditions, is there any way to do this migration and maintain database portability, or do I need to limit the code to one database, and start coding server_default values based on the underlying DB driver for boolean columns?

mpounsett
  • 1,174
  • 1
  • 10
  • 30

1 Answers1

2

Yes, you can alter the upgrade (and probably also the downgrade) function to achieve what you want. Also please note that you current upgrade doesn't maintain your existing data as you are adding a new column and deleting the old column. As alembic cannot detect a rename you need to alter the upgrade (and downgrade) auto generated functions.

To achieve what you want with backfilling the database would be:

  • Add/rename the column without the nullable constraint.
  • Back-fill the existing rows with a default value when current value is Null.
  • Alter the column to include the nullable constraint:

To solve the renaming issue you need to change that add and delete row to 1 alter row:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('user', 'is_disabled', new_column_name='is_enabled')
    op.execute('update user set is_disabled=false where is_disabled is null')
    op.alter_column('user', 'is_enabled', nullable=False)
    op.drop_column('user', 'is_deleted')
    # ### end Alembic commands ###

Also as you changed is_disabled to is_enabled you can use op.execute to toggle the boolean if required.

rfkortekaas
  • 6,049
  • 2
  • 27
  • 34