Reading · liman.io · RSS Amplifier

SQL

published on: Jan 2, 2021

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:

database/Dockerfile

_10

FROM mysql:8.0.22

_10

ENV MYSQL_DATABASE cars

_10

ENV MYSQL_USER developer

_10

ENV MYSQL_PASSWORD 3AMTf?DcXxpczeaUPx2_?tTethw4aFmF

_10

ENV MYSQL_RANDOM_ROOT_PASSWORD TRUE

_10

COPY ./seed.sql /docker-entrypoint-initdb.d/seed.sql

_10

EXPOSE 3306

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

database/seed.sql

_10

ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '3AMTf?DcXxpczeaUPx2_?tTethw4aFmF

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

_10

# ...

_10

sqlalchemy.url = mysql://developer:3AMTf?DcXxpczeaUPx2_?tTethw4aFmF@database/cars

_10

# ...

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 at backend/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

_10

# ...

_10

def upgrade():

_10

op.add_column('Car', sa.Column('Horsepower', sa.INTEGER))

_10

def downgrade():

_10

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

_10

FROM python:3.9

_10

COPY . /

_10

RUN apt-get update && apt-get upgrade -y && apt-get install -y mariadb-client

_10

RUN pip3 install -r requirements.txt

_10

CMD ["./startup.sh"]

where startup.sh is:

backend/startup.sh

_10

#!/bin/sh

_10

while ! mysqladmin ping -h"database" --silent; do

_10

sleep 1

_10

done

_10

alembic upgrade head

_10

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

docker-compose.yaml

_14

version: "3.8"

_14

services:

_14

backend:

_14

build:

_14

context: ./backend

_14

container_name: mysql-migration_backend

_14

depends_on:

_14

- database

_14

database:

_14

build:

_14

context: ./database

_14

container_name: mysql-migration_database

_14

ports:

_14

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

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

_10

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.

Migration of a dockerized MySQL database with SQLAlchemy and Alembic

Setup

Database

Backend

Migration

Docker

Application

Caveats

Coupling with Container Startup

Comments