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

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’ll need 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.

Alembic would generate the migration script below

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

This error is reporting that we have null values which the nullable=Falseconstraint 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.

We only need to make this change to the migration script; we don’t need to make any changes to the User model.

When you apply 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’s 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’ve 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 fits your use case!

If you found Erika’s blog useful, check out our other blog posts for more essential insights!

Are you a developer interested in growing your software engineering career? Apply to join the Andela Talent Network today.