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
FLYWAY_PASSWORD are set in the
docker-compose.yml file (and in the credentials environment file
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