Adding a Boolean Column to an Existing Table with Alembic and SQLAlchemy

Ever find yourself needing to add a new boolean column to an existing table? I do!

Recently I’ve been using SQLAlchemy and Alembic to manage migrations and I frequently find myself looking up how to achieve this task. Below is a recipe for adding a boolean column to an existing table.

We want to avoid the three state boolean problem so we’ll be making this column null: false. Since we’re adding this column to an existing table we have existing rows that will have empty values for this new column. These empty values will cause the not null constraint to fail so we’ll write a statement to backfill these rows with default boolean values prior to applying the not null condition.

In our use case, our table is called session and our new column is called is_active.

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column(
        'session',
         sa.Column('is_active', sa.Boolean(), nullable=True)
    )
    op.execute("""
        UPDATE session
        SET is_active = 'f'
    """)
    op.alter_column('session', 'is_active', nullable=False)


def downgrade():
    op.drop_column('session', 'is_active')

Note that this example was run on PostgreSQL so there are a number of alternatives for the 'f' value.

There is one refactoring we could make to remove the raw SQL string. Instead of the SQL string we could write something like:

...
    new_column = table('session', column('is_active'))
    op.execute(new_column.update().values(**{'is_active': False}))
...

As always, make sure to run the migrations forwards and backwards.