As if dockerizing a software project isn’t hard enough, it is also challenging to actually make the transition from running a non-dockerized application to run it in a Docker container. This is especially the case, if the application is using a database and you want to have an automatic database schema migration mechanism (we usually use Flyway for this task).
This article will show you how to restore a database in a container and how to prepare it for the usage with Flyway, so that future database migrations can be applied automatically.
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.
With Flyway, each modification of the database schema is called a „migration“ and consists of an SQL file. The file name looks like „V20150316_1430__addingCustomerReference.sql“ and contains the version of the migration, in this sample „20150316.1430“. This version is used to track which migrations already have 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. (See separate article for an introduction of how we use Flyway in our dockerized projects)
Flyway also has a concept how to work with existing databases, that were not migrated with Flyway before: it is called „baselining“. This command tells Flyway, that the current database schema is the same as if the migrations were all executed up to a specific one (it defines the baseline for the following migrations).
E.g. the following command informs Flyway, that the current database version is 20150316.1430; in this case referring to the migration file of our previous example:
flyway baseline baselineVersion=20150316.1430
This is simple when the database lives on a regular server where the database engine can be started before the application, which uses the database. The baseline command is executed and afterwards the application is started. That was easy, very good.
But when a legacy project is dockerized and Flyway is introduced at the same time, it requires some extra steps.
In very small deployment cases or test instance deployments we often use docker-compose, which handles the start of the services. Usually at least two services are configured in the configuration file: the database service and the application service.
The database service is using a standard image pulled from Docker Hub, like MariaDB or MySql for instance.
The application service is configured to rely on the database service and checks via Flyway if there are any pending migrations and updates the database if necessary.
If you start up the application group defined in the configuration file with docker-compose up all services are started.
Since the legacy project has never used Docker or Flyway before, you have to 1. restore the database in the container and 2. baseline the database with Flyway.
Let’s assume, we have two defined services in the „docker-compose.yml“ file:
db, which is the database service- and
webapp, which is the web application service relying on the database services and executing the migrations
Further, let’s assume we have the last backup of the current database in an SQL file named „database_backup.sql“ and the „docker-compose.yml“ file resides in the directory „wonderlandapp“.
Follow these steps to restore the database and baseline it:
First, we start only the database service, using the docker-compose up-command and specifying the database service’s name:
docker-compose up db
Copy the database backup SQL file to the docker container:
docker cp database_backup.sql wonderlandapp_db_1:/
Now we bash into the database docker container:
docker exec -it wonderlandapp_db_1 bash
Within the database container we restore the database using the mysql command line tool:
mysql --database=$MYSQL_DATABASE --user=$MYSQL_USER --password=$MYSQL_PASSWORD < database_backup.sql
After this, we exit the container:
exit
(We can use the environment variables $MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD here because they are specified in the docker-compose.yml file. See the previously mentioned article for more information.
Now we start the web app container, but not with the default command. Instead we use „bash“ as executable:
docker-compose run webapp bash
Within the webapp container we change the current directory to the folder where flyway is stored and use it to baseline the database that we just restored:
cd /flyway/ ./flyway baseline -url=$FLYWAY_URL -user=$FLYWAY_USER -password=$FLYWAY_PASSWORD exit
(Again, we can use the environment variables $FLYWAY_URL, $FLYWAY_USER, $FLYWAY_PASSWORD here, because they are specified in the docker-compose.yml file)
Now we can bring up the remaining services in the application group that are defined in the „docker-compose.yml„, including the webapp service (remember: previously we used this container to baseline the database, but started „bash“ as executable. This did not bring up our service)
docker-compose up --detach
If there are still migrations to be applied to the database, the webapp service will perform the upgrade of the database by calling Flyway during startup.
