Migration of a dockerized MySQL database with SQLAlchemy and Alembic
published on 2021-01-02
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 abackend written in Python.
Database
The setup of the database is quite simple using the following Dockerfile:
FROM mysql:8.0.22 ENV MYSQL_DATABASE cars ENV MYSQL_USER developer ENV MYSQL_PASSWORD 3AMTf?DcXxpczeaUPx2_?tTethw4aFmF ENV MYSQL_RANDOM_ROOT_PASSWORD TRUE COPY ./seed.sql /docker-entrypoint-initdb.d/seed.sql EXPOSE 3306
On startup, I seed the database with an exemplary data model to store cars:
ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '3AMTf?DcXxpczeaUPx2_?tTethw4aFmF'; CREATE TABLE `Manufacturer` ( `ID` INT NOT NULL AUTO_INCREMENT, `Name` TEXT NOT NULL, PRIMARY KEY (`ID`) ); CREATE TABLE `Model` ( `ID` INT NOT NULL AUTO_INCREMENT, `Name` TEXT NOT NULL, PRIMARY KEY (`ID`) ); CREATE TABLE `Car` ( `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `Manufacturer` INT NOT NULL, `Model` INT NOT NULL, FOREIGN KEY (`Manufacturer`) REFERENCES `Manufacturer`(`ID`), FOREIGN KEY (`Model`) REFERENCES `Model`(`ID`) );
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
:
alembic init migrations
and configure Alembic by editing alembic.ini to use the appropriate connection details for the database:
# ... sqlalchemy.url = mysql://developer:3AMTf?DcXxpczeaUPx2_?tTethw4aFmF@database/cars # ...
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:
alembic revision -m "Add Car's Horsepower"
Generating backend/migrations/versions/9669e7426172_add_car_s_horsepower.py ... done
At this point, Alembic created a new revision template atbackend/migrations/versions/9669e7426172_add_car_s_horsepower.py
where I need to define the actual code that Alembic executes during the migration:
# ... def upgrade(): op.add_column('Car', sa.Column('Horsepower', sa.INTEGER)) def downgrade(): op.drop_column('Car', 'Horsepower')
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:
FROM python:3.9 COPY . / RUN apt-get update && apt-get upgrade -y && apt-get install -y mariadb-client RUN pip3 install -r requirements.txt CMD ["./startup.sh"]
where startup.sh
is:
#!/bin/sh while ! mysqladmin ping -h"database" --silent; do sleep 1 done alembic upgrade head # Start the backend...
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 ofmariadb-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:
version: "3.8" services: backend: build: context: ./backend container_name: mysql-migration_backend depends_on: - database database: build: context: ./database container_name: mysql-migration_database ports: - "3306:3306"
Application
To run the application, execute the following command in a terminal:
docker-compose -p mysql-migration up --build
where Docker should generate an output similar to the following:
Terminal
database_1 | 2021-01-02 15:10:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.22-1debian10 started. database_1 | 2021-01-02 15:10:21+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' database_1 | 2021-01-02 15:10:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.22-1debian10 started. database_1 | 2021-01-02 15:10:21+00:00 [Note] [Entrypoint]: Initializing database files database_1 | 2021-01-02T15:10:21.128699Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.22) initializing of server in progress as process 46 database_1 | 2021-01-02T15:10:21.134926Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. database_1 | 2021-01-02T15:10:21.710354Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. database_1 | 2021-01-02T15:10:23.162961Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. database_1 | 2021-01-02 15:10:26+00:00 [Note] [Entrypoint]: Database files initialized database_1 | 2021-01-02 15:10:26+00:00 [Note] [Entrypoint]: Starting temporary server database_1 | 2021-01-02T15:10:26.486959Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 91 database_1 | 2021-01-02T15:10:26.531262Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. database_1 | 2021-01-02T15:10:26.855056Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. database_1 | 2021-01-02T15:10:27.008074Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock database_1 | 2021-01-02T15:10:27.264832Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. database_1 | 2021-01-02T15:10:27.265078Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. database_1 | 2021-01-02T15:10:27.268176Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory. database_1 | 2021-01-02T15:10:27.315866Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22' socket: '/var/run/mysqld/mysqld.sock' port: 0 MySQL Community Server - GPL. database_1 | 2021-01-02 15:10:27+00:00 [Note] [Entrypoint]: Temporary server started. database_1 | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. database_1 | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it. database_1 | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. database_1 | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it. database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: buH0nai4ahz5ahdoh2phiXah7Chasha1 database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: Creating database cars database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: Creating user developer database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: Giving user developer access to schema cars database_1 | database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/seed.sql database_1 | database_1 | database_1 | 2021-01-02 15:10:31+00:00 [Note] [Entrypoint]: Stopping temporary server database_1 | 2021-01-02T15:10:31.155633Z 14 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.22). database_1 | 2021-01-02T15:10:32.783543Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22) MySQL Community Server - GPL. database_1 | 2021-01-02 15:10:33+00:00 [Note] [Entrypoint]: Temporary server stopped database_1 | database_1 | 2021-01-02 15:10:33+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up. database_1 | database_1 | 2021-01-02T15:10:33.394614Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 1 database_1 | 2021-01-02T15:10:33.402355Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. database_1 | 2021-01-02T15:10:33.602351Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. database_1 | 2021-01-02T15:10:33.711959Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock database_1 | 2021-01-02T15:10:33.787515Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. database_1 | 2021-01-02T15:10:33.787710Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. database_1 | 2021-01-02T15:10:33.790638Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory. database_1 | 2021-01-02T15:10:33.815348Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL. database_1 | mbind: Operation not permitted backend_1 | INFO [alembic.runtime.migration] Context impl MySQLImpl. backend_1 | INFO [alembic.runtime.migration] Will assume non-transactional DDL. backend_1 | INFO [alembic.runtime.migration] Running upgrade -> 9669e7426172, Add Car's Horsepower mysql-migration_backend_1 exited with code 0
The output
backend_1 | INFO [alembic.runtime.migration] Running upgrade -> 9669e7426172, Add Car's Horsepower
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 iscaching_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:
ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '3AMTf?DcXxpczeaUPx2_?tTethw4aFmF';
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.