We want to create a migration to change the datatype of a column while keeping the data that already exists in the database. We can’t just change the column datatype in-place and (auto-)create a migration for that. That would corrupt the existing data in the database. Here is how to do the change the datatype while preserving the existing data.
Suppose we have a database table called products that has a column called serial_number.
Previously this column was defined as a string. So a serial number in the database could look like this: "1234567".
Now we would like this column to be of type integer instead.
We can start by updating our Product model and changing the serial_number column datatype from sa.String to sa.Integer.
“Wait a sec! But if we change the model how does SQLAlchemy read the existing data in the database?? Isn’t this the same as changing the datatype in-place?”
Exactly! That is why in our migration script we will create a shadow definition of the Product model. We will use this shadow model to talk to the db through our ORM. Since this new model acts as a replacement, we can fix our real Product model without worrying about its impact.
The magic happens in the migration script. Here is an overview of the steps we need to take in the upgrade() method:
Create a shadow Product model class.
Create a new column with the desired datatype. We could call it serial_number_. The name doesn’t matter as we will change it later.
Copy and convert the data from the original column to the new column.
Remove the original column.
Rename the new column to the original column’s name.
The downgrade() method follows the same process but in reverse.
Here is the migration script:
We use Session(bind=op.get_bind()) to start a new session so that we can copy and convert the data.
Remember that any constraints you had on the original column (for example unique serial numbers, etc.) needs to be recreated for the new column. You can add those before step 3. We should also take care of the edge cases for example if serial number is not convertible to an integer. This was a very simple example to show you the steps involved so I tried to keep it simple. I hope you find it useful.