Flask MySql Migration

Having developed and tested the database and application changes against SQLite, it is time to deploy to the MySql database running in a Docker container.

I ran this simple upgrade many times in development before I got it right. By using a SQLite database this was an easy task; I just had to restore the backup version of the file.

When making similar changes to a standalone database such as MySql, increased care has to be taken to avoid putting the database into a invalid state. Recovery can be difficulty if a backup is not available.

One of the problems with using SQLite for development and MySql (eventually) in production is that I cannot be sure that a migration that works in one will work in the other. There are subtle differences in dialect, such as not being able to alter an existing table in SQLite, that may only show up when moving from one environment to another.

At the moment I am calling the docker image the production environment. In theory, when I deploy this to a server somewhere it will look exactly the same as the image running on my machine. Nevertheless I want to be sure that each time I run a migration that I test it in a system as close in set up to the real production system.

I can create a backup of the MySql database with the following command from my local machine:

docker exec <CONTAINER> /usr/bin/mysqldump -u root --password=<PASS> <DATABASE> > <BACKUP FILE>

For me this looks like this:

docker exec estimator-app-db /usr/bin/mysqldump -u root --password=rootpass estimator > backup.sql

Restoring is like this:

cat backup.sql | docker exec -i <CONTAINER> /usr/bin/mysql -u root --password=<PASS> <DATABASE>

Effectively this script will drop all tables, re-create them, and then insert all rows. Depending on the size of your database, this might be overkill.

It will work just fine for me in this stage of development, where I have only a single table with no data of consequence.

Stuck migrations

If the upgrade function has a problem, you may leave the database in a state where some parts of the upgrade are completed, but others are not. Depending on where you are in your application development you may need to consider different approaches.

For example, if you have a database holding records from thousands of users, you should take extreme care when performing an upgrade.

It may be better to add exception handling to the code to correctly recover from mistakes or problems. Structural problems can be tested for in advance, but issues with data may only show up in production.

The downgrade function needs equal care. If the upgrade succeeds without issues but the application has a bug, you might want to quickly roll back to the previous state. An untested downgrade function could be a disaster for your application.

Remember, that while it is only data that you are losing, your application’s reputation will be tarnished by any loss of service.

Running the migration against the docker cluster

The docker-compose containers will not automatically get the changes we made. The first step is to build the environment again. Take a backup of the database as shown above, and then shutdown the containers.

docker-compose build

It looks like this command recreates the database – there was no tables in the database afterwards. This would not be good for real instances. Better check that my backup script works.

cat ../backup.sql | docker exec -i estimator-app-db /usr/bin/mysql -u root --password=rootpass estimator

This worked. I was able to connect using bash, open MySQl prompt and check the schema with:

$ docker exec -it estimator-app-db bash
root@984f93a86af4:/# mysql -uroot --password=rootpass

mysql> use estimator

Database changed
mysql> show tables;
+---------------------+
| Tables_in_estimator |
+---------------------+
| group               |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from `group`;
+----+----------+
| id | name     |
+----+----------+
|  1 | NewGroup |
+----+----------+
1 row in set (0.00 sec)

Upgrade Just the Application

The mistake that I made was not specifying that only the web container should be rebuilt. In order to recreate this from the start, I reverted my code back to the pre-migration commit using git and rebuilt. This just got me back to where I was.

The following commands allow the redeployment of the web application only. However, stopping and starting the database will bring back up a refreshed database.

docker-compose build web
docker-compose up --no-deps -d web

OK. The code is now deployed and the database still there, ready to be upgraded. What happens if I stop and start the servers with docker-compose down and docker-compose up? Standard enough practice, I would think.

Unfortunately doing so is taking a fresh version of the database, effectively a blank database. Back to the same problem I had before.

Other options

This needs more work. I looked at using a volume to automatically copy the application code to the web container. This will ensure that it has the latest code at all times. This can be done by adding the following to the web section of the docker-compose.yml file.

   volumes:
     - .:.

The problem with this is that all the files in the source folder are copied as it does not filter using the contents .dockerignore file. Another option was to use Gunicorn to do a similar thing to keep the contents of the container up-to-date and avoid rebuilding. This suffers the same problem.

Using a Backup

So my solution for the time being will be to take a backup and then restore the database from backup every time I push new changes to the containers. That will suffice for now; probably some automation will take the pain out of this.

If anyone reading has a better solution let me know: [email protected]

The upgrade step itself was fairly easy to do. With the application and database started, the following command upgrades the database to the latest version:

$ docker exec -it estimator-app-web flask db upgrade

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 2eb89b1334ef, empty message

It’s a good idea to log in and check the structure and contents of the tables after running an upgrade, to ensure that things have been correctly applied.

mysql> use estimator
Database changed

mysql> show tables;
+---------------------+
| Tables_in_estimator |
+---------------------+
| alembic_version     |
| estimation_group    |
| user                |
+---------------------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+----------+
| id | nickname |
+----+----------+
|  1 | default  |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from estimation_group;
+----+----------+------+
| id | name     | user |
+----+----------+------+
|  1 | NewGroup |    1 |
+----+----------+------+
1 row in set (0.00 sec)