When developing a software project that makes use of a database, a major topic is how the database schema is updated as the project evolves.

This article will show you how to run a database in a Docker container and how to use Flyway to automatically update (migrate) the database schema.

With Flyway, each modification of the database schema is called a “migration” and consists of an SQL file. The file name looks like “V20190526_1430__addingBacklinksForProducts.sql” and contains the version of the migration, in this sample “20190526.1430”; everything following the two underscores is a comment. The version is used to track which migrations have already been applied to the database and it also defines the order in which migrations are executed. When using Docker, Flyway is usually executed upon the start of a container.

Our projects usually have a Dockerfile that looks like this:

# Build docker image for our wonderland web application

FROM php:7.3-apache

WORKDIR /var/www/html/

# Preparations for flyway

# a directory must be created to prevent the error
# update-alternatives: error: error creating symbolic link '/usr/share/man/man1/rmid.1.gz.dpkg-tmp': No such file or directory
# during the installation of openjdk-8-jre-headless
#
# For more information see:
# https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=863199
RUN mkdir --parent /usr/share/man/man1

RUN apt-get update  \
    && apt-get install -y --no-install-recommends \
            # to execute java
            ca-certificates-java \
            openjdk-8-jre-headless \
            # to get flway
            wget \
            # to execute flyeway
            mysql-client \
            # needed for PHP: curl
            libcurl4-gnutls-dev \
            # needed for gd
            libpng-dev \
            # nneded for intl
            libicu-dev \
            # needed for xmlrpc
            libxml2-dev \
            # needed for libxslt-dev
            libxslt-dev \
        # basic smoke test
        && java -version \
        && apt-get clean

# Install flyway
RUN wget https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/4.1.1/flyway-commandline-4.1.1-linux-x64.tar.gz \
    && tar zxvf flyway-commandline-4.1.1-linux-x64.tar.gz \
    && mkdir /flyway \
    && mv flyway-4.1.1/* /flyway \
    && rm -r flyway-4.1.1/ flyway-commandline-4.1.1-linux-x64.tar.gz

# flyway is set up, now install everything for your application
# For instance:
# Copy application files and also php.ini file
COPY ./ /var/www/html/

RUN chown www-data:www-data -R /var/www/html/ \
    && mv ./flyway_migrations/* /flyway/sql/

EXPOSE 80

# Copy docker scripts
COPY ./docker-container-scripts/*.sh /
RUN chmod +x /*.sh

CMD ["/docker-entrypoint.sh"]

With this Dockerfile, first the prerequisites for Flyway and Flyway itself are installed. Later, the web application code is installed.

From the directory “docker-container-scripts” some scripts are copied to the Docker image. One of them is “docker-entrypoint.sh” which will be executed on container start:

#!/bin/bash

# fail on first error
set -e

echo "> Running start script..."

pushd .

echo "> Wait for database on $DATABASE_SERVER to be ready..."

/wait-for-it.sh $DATABASE_SERVER:3306 --timeout=120

echo "> database seems to be ready..."

echo "> Creating flyway config file..."

cd /flyway/conf/
touch flyway.conf && rm flyway.conf && touch flyway.conf
echo "flyway.url=$FLYWAY_URL" >> flyway.conf
echo "flyway.user=$FLYWAY_USER" >> flyway.conf
echo "flyway.password=$FLYWAY_PASSWORD" >> flyway.conf

echo "[client]" > /var/tmp/mysql.conf
echo "user = $DATABASE_USER" >> /var/tmp/mysql.conf
echo "password = $DATABASE_PASS" >> /var/tmp/mysql.conf
echo "host = $DATABASE_SERVER" >> /var/tmp/mysql.conf
echo "default-character-set = utf8" >> /var/tmp/mysql.conf

# Recommended: create a database backup now!

echo "> Migrating database with flyway..."

cd /flyway/
/flyway/flyway info
/flyway/flyway migrate
/flyway/flyway info

popd

echo ""
echo "> Running apache2-foreground to start web application"
echo ""

apache2ctl -D FOREGROUND

This container startup script first waits for the database service to be ready, using the wait-for-it.sh.

When the database is ready, the script creates some config files, containing the credentials Flyway and MySql will use to connect to the database. The environment variables DATABASE_SERVER, DATABASE_USER, DATABASE_PASS, FLYWAY_URL, FLYWAY_USER and FLYWAY_PASSWORD are set in the docker-compose.yml file (and in the credentials environment file wonderland-credentials.env respectively).

After the configuration files are created, Flyway is executed several times:

First to print some information about the database to be migrated. Then the database is migrated. To check the result, the info-command is used again.

After the migration of the database, the web application is started up.

By using this mechanism, the database of your dockerized application can easily be migrated.

You can use the following docker-compose.yml file to start the docker application group:

version: '2'
services:
    wonderland-app:
        depends_on:
            - db
        links:
            - db
        env_file:
            - wonderland-credentials.env
        environment:
            # config variables
            DATABASE_SERVER: db
            DATABASE_DB: wonderlanddb
            DATABASE_USER: wonderlanddbuser

            # Used to create flyway config
            FLYWAY_URL: jdbc:mysql://db/wonderlanddb
            FLYWAY_USER: wonderlanddbuser
        ports:
            - "127.0.0.1:8081:80"
        restart: always
    db:
        image: mysql:5.5.60
        env_file:
            - wonderland-credentials.env
        environment:
            MYSQL_DATABASE: wonderlanddb
            #            MYSQL_ROOT_PASSWORD: RootPwd
            MYSQL_RANDOM_ROOT_PASSWORD: "true"
            #            a random password for the server's root user is generated when the Docker container is started.
            #            The password is printed to stdout of the container and can be found by looking at the container's log.
            MYSQL_ROOT_HOST: %
            MYSQL_USER: wonderlanddbuser
        volumes:
          - ./data/mysql:/var/lib/mysql
        restart: always

We use a separate environment-file to store the passwords:

# Replace the aaaa placeholders with your actual credentials
DATABASE_PASS=aaaa
FLYWAY_PASSWORD=aaaa
MYSQL_PASSWORD=aaaa