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
