Migration of a dockerized MySQL database with SQLAlchemy and Alembic
Tags: mysql, database, docker, python
Published: 2021-01-02 15:50:36

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](https://www.sqlalchemy.org/) and [Alembic](https://alembic.sqlalchemy.org/en/latest/) 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](https://hub.docker.com/_/mysql) in Python with the aforementioned tools.

# Setup

The following tutorial is based on this minimal GitHub repository:

{% github dnlfrst/migrate-dockerized-database no-readme %}

The GitHub repository contains a [dockerized MySQL database](https://github.com/dnlfrst/migrate-dockerized-database/tree/main/database) and a [backend written in Python](https://github.com/dnlfrst/migrate-dockerized-database/tree/main/backend).

## Database

The setup of the database is quite simple using the following [Dockerfile](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/database/Dockerfile):

```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](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/database/seed.sql) the database with an exemplary data model to store cars:

```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](https://alembic.sqlalchemy.org/en/latest/tutorial.html#creating-an-environment) in a folder called [`migrations`](https://github.com/dnlfrst/migrate-dockerized-database/tree/main/backend/migrations):

```bash
$ alembic init migrations
```

and [configure Alembic](https://alembic.sqlalchemy.org/en/latest/tutorial.html#editing-the-ini-file) by editing [`alembic.ini`](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/backend/alembic.ini) to use the appropriate connection details for the database:

```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:

```bash
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 at [`backend/migrations/versions/9669e7426172_add_car_s_horsepower.py`](https://github.com/dnlfrst/migrate-dockerized-database/blob/587cd0428e91c0d5b7d755882d091dc2804f24e2/backend/migrations/versions/9669e7426172_add_car_s_horsepower.py) where I need to define the actual code that Alembic executes during the migration:

```python
# ...

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](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/backend/Dockerfile) that runs on the startup of the Docker container:

```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`](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/backend/startup.sh) is:

```bash
#!/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`](https://dev.mysql.com/doc/refman/8.0/en/mysqladmin.html#command_mysqladmin_ping) 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](https://github.com/dnlfrst/migrate-dockerized-database/blob/main/docker-compose.yaml) defines the dependency between the backend and the database:

```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:

```bash
docker-compose -p mysql-migration up --build
```

where Docker should generate an output similar to the following:

{% collapsible Terminal %}
```bash
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
```
{% endcollapsible %}

The output

```bash
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

## `mysqlclient `

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`](https://github.com/PyMySQL/mysqlclient/issues/442). Therefore, I change the default authentication mechanism for the executing user:

```sql
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*](https://pythonspeed.com/articles/schema-migrations-server-startup/).