DEV Community

Cover image for Migration of a dockerized MySQL database with SQLAlchemy and Alembic
Daniel Fürst
Daniel Fürst

Posted on • Edited on • Originally published at liman.io

Migration of a dockerized MySQL database with SQLAlchemy and Alembic

#mysql #database #docker #python

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:

GitHub logo dnlfrst / migrate-dockerized-database

Minimal repository to showcase the migration of a dockerized MySQL database with SQLAlchemy and Alembic in Python.

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:

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
Enter fullscreen mode Exit fullscreen mode

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`)
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

# ...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

# ...

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


def downgrade():
    op.drop_column('Car', 'Horsepower')
Enter fullscreen mode Exit fullscreen mode

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"]
Enter fullscreen mode Exit fullscreen mode

where startup.sh is:

#!/bin/sh

while ! mysqladmin ping -h"database" --silent; do
  sleep 1
done

alembic upgrade head

# Start the backend...
Enter fullscreen mode Exit fullscreen mode

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:

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"
Enter fullscreen mode Exit fullscreen mode

Application

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

docker-compose -p mysql-migration up --build
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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. Therefore, I change the default authentication mechanism for the executing user:

ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '3AMTf?DcXxpczeaUPx2_?tTethw4aFmF';
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Read next

cloudforce profile image

Effortless Web Hosting: Build and Run an NGINX Server with Docker

Matthew M Tarfa -

abdikhafarhub profile image

Advanced Python

Abdikhafar -

cereal84 profile image

Hacktoberfest from a maintainer's point of view

Alessandro Pischedda -

dm8ry profile image

Getting Started with MongoDB Compass: A GUI for MongoDB

Dmitry Romanoff -