Migration of a dockerized MySQL database with SQLAlchemy and Alembic

The logos of Docker, SQLAlchemy, and Alembic

Migration of a dockerized MySQL database with SQLAlchemy and Alembic

SQL

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:

database/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:

database/seed.sql

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:

backend/alembic.ini

# ...

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:

backend/migrations/versions/9669e7426172_add_car_s_horsepower.py

# ...

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:

backend/Dockerfile

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:

backend/startup.sh

#!/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:

docker-compose.yaml

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.