Adding a Required Foreign Key to a Table That Already Has Rows: A Safe Alembic Migration

A developer faced a challenge when adding a required foreign key to a table that already contained rows. They wanted every image to belong to a user, so they added a foreign key from images to users and marked it as not null. However, before running the upgrade, they had a question: what would happen to the existing rows, and how could they change the database while it was being used?

Alembic, a migration tool, cannot create data or decide which user should own each old image. It only compares the database structure with the models and writes the shape change. In this case, adding a not null constraint to the column caused an error, as SQLite only supports certain types of ALTER TABLE operations.

The solution was to expand, backfill, and contract the migration in three steps. First, the new column was added without requiring values for existing rows. Next, every old image was assigned a placeholder value for the user_id. Finally, the not null rule and the foreign key were enforced. The specific placeholder email used in this example was "unassigned@example.invalid". In a real application, this value would be replaced with a more meaningful approach for assigning users to images.