← Blog

Migration of a dockerized MySQL database with SQLAlchemy and Alembic

---

The data models in a database evolve with the development of a codebase. Hence, applying changes to these data models is a common task of developers. While tools like SQLAlchemy and Alembic come in handy to accomplish these tasks, using Docker for the development process adds another layer of challenges on top. In the following, I showcase the migration of a dockerized MySQL database in Python with the aforementioned tools.

Setup

The following tutorial is based on this minimal GitHub repository. The GitHub repository contains a dockerized MySQL database and a backend written in Python.

Database

The setup of the database is quite simple using the following Dockerfile:

On startup, I seed the database with an exemplary data model to store cars:

Besides the data model, I also alter the authentication mechanism of developer. This is necessary for reasons that I explain later.

Backend

To get started, I initialize Alembic in a folder called migrations:

and configure Alembic by editing alembic.ini to use the appropriate connection details for the database:

Migration

With the application set up, I get to define the actual migration with Alembic. To continue with the above example of cars, I go ahead and add a new column Horsepower to the table Car which stores a car's horsepower:

At this point, Alembic created a new revision template at backend/migrations/versions/9669e7426172_add_car_s_horsepower.py where I need to define the actual code that Alembic executes during the migration:

Docker

Without using Docker, I would go ahead and run the migration through a terminal against the database. However, with Docker, the database is only available from the Docker container which may not be easily accessible (e.g., through a lack of permission). Therefore, it is reasonable to run the migration as part of the Docker container, i.e., at its startup.

To do so, I define a script in the backend's Dockerfile that runs on the startup of the Docker container:

where startup.sh is:

In startup.sh, it's necessary to wait until the database is ready to accept connections as the migration would fail, otherwise. For that purpose, I use the command-line tool mysqladmin which is part of mariadb-client installed with the backend's Docker container. To coordinate this interaction between the backend and the database, I use Docker Compose. The corresponding configuration defines the dependency between the backend and the database:

Application

To run the application, execute the following command in a terminal:

where Docker should generate an output similar to the following:

The output

confirms that the migration ran successfully. You can also verify this by connecting to the database and inspecting the columns of the Car table.

Caveats

Since MySQL 8, the default authentication mechanism is caching_sha2_password instead of mysql_native_password. However, mysqlclient, which SQLAlchemy and Alembic use to execute the migration, does not support caching_sha2_password. Therefore, I change the default authentication mechanism for the executing user:

I place this command in the script executed on the creation of the database Docker container. Otherwise, it would also be possible to execute this command manually against the database.

Coupling with Container Startup

This approach couples the migration to the Docker container startup which can turn out to be problematic.

Itamar Turner-Trauring explains this downside in more detail in their post Decoupling database migrations from server startup: why and how.