Alembic: How to Add a Non-Nullable Field to a Populated Table

Photo by Jeremy Manoto on Unsplash

It is quite easy to add a non-nullable field to an empty table. Any migration tool, including Alembic, can automatically generate a migration file for you that is immediately ready for use, with no modifications required.

The problem arises when you have a table that is populated with data. You specify that you want a new field that doesn’t accept null values, yet, you already have a host of rows on the table created prior to this new addition. These rows won’t have values for the new field so they would default to null. Alembic still generates a migration script but you will need to add some code of your own to successfully make the schema change. You want to add code to the migration script that sets a default value on the field as Alembic doesn’t handle this automatically.

Say you wanted to add a new boolean field: is_admin.

class User(db.Model):
    ...
    is_admin = db.Column(db.Boolean, default=False, nullable=False)

Alembic would generate the migration script below

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '635d30762810'
down_revision = 'ad4b629ace65'
branch_labels = None
depends_on = None
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        'users', 
        sa.Column('is_admin', sa.Boolean(), nullable=False)
    )
    # ### end Alembic commands ###
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('users', 'is_admin')
    # ### end Alembic commands ###

If you try to apply this migration as is, you are going to get an error saying

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "is_admin" contains null values
 [SQL: 'ALTER TABLE users ADD COLUMN is_admin BOOLEAN NOT NULL'] (Background on this error at: http://sqlalche.me/e/gkpj)

This error is reporting that we have null values which the nullable=False constraint doesn’t permit. We can fix this by doing the following:

1. Add the new column: is_admin with nullable set to True
2. Then update the value of the field on all existing rows to false
3. Then update the column is_admin to be non-nullable

Here is what the updated migration script looks like. The updated portions have been emphasized.

from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '635d30762810'
down_revision = 'ad4b629ace65'
branch_labels = None
depends_on = None
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        'users', 
        sa.Column('is_admin', sa.Boolean(), nullable=True)
    )
    op.execute("UPDATE users SET is_admin = false")
    op.alter_column('users', 'is_admin', nullable=False)
    # ### end Alembic commands ###
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('users', 'is_admin')
    # ### end Alembic commands ###

We only need to make this change to the migration script, we do not need to make any changes to the User model.

When you try applying the migrate command, you should see that the operation is executed successfully this time. You can verify that all existing users have the is_admin column set to false. At this point, you can update the column on selected rows to true. In this piece, we assumed that the bulk of the users would be non-admin users.

And that is all it takes to add a non-nullable field to an already populated table. This is a recurring theme when using Alembic, you would find yourself having to manually craft your own migration commands. As you have seen in this tutorial, it can be quite straightforward to make modifications to the auto-generated script. A little knowledge of SQL is all you need to write the exact migration script that fit your use case.