Flyway is an extremely popular application for managing SQL migrations. In this post I’ll show you how to use Docker to test Flyway migrations before applying them to your live databases.

Flyway is an open-source database migration tool that simplifies the process of managing and versioning database changes. It allows developers to keep track of database schema changes over time, making it easier to deploy and update applications in various environments. With Flyway, you can define SQL migration scripts and apply them automatically to keep them in sync with your application’s codebase.

Flyway migrations are code, written in either SQL or Java. They are simple to apply to a database with a single command: flyway migrate. Once you do so, however, any changes your migrations make are permanent unless you always write rollbacks. Also, it’s impossible to know if your migrations have correct syntax unless you run them. This creates a conundrum while you’re developing migrations: how do you know they are correct without running them? How do you run them without mutating a database?

Enter Docker. Docker is a platform that enables developers to create, deploy, and run applications inside lightweight, disposable containers. Docker can also run databases inside containers, and has pre-built images for most popular databases (mysql, postgres, etc.) We can run a temporary database server inside a container, run Flyway migrations against it, and then dispose of it in one step. Sounds like magic? Let’s dive in.

Setting up your environment

The only pre-requisite is to have Docker Desktop installed. You can also install Flyway if you wish, but we can also run Flyway with Docker.

If you’d like to see a complete example, you can download it here. Otherwise, follow along with the steps below.

Creating the directory

The first step is to create a directory to hold our project. Let’s call it flyway_docker_example. In this directory, create a folders sql and a docker-compose.yml file. It should look like this:

flyway_docker_example
|   sql/
|   docker-compose.yml

Now let’s add some example migrations to the sql/ directory.

-- sql/V1__create tables.sql
CREATE TABLE example_table (
    example_id serial PRIMARY KEY,
    example_name text NOT NULL
    example_date timestamp NOT NULL
);

-- sql/V2__add column.sql
ALTER TABLE example_table
  ADD COLUMN example_count int NOT NULL DEFAULT 0;

-- sql/V3__update rows.sql
UPDATE example_table
  SET example_name = 'foo'
  WHERE example_name = 'bar';

Normally you will run these migrations against a live database server using the simple command flyway migrate. Today, we’re just going to run against a temporary database living inside a Docker container. Now that we have some example Flyway migrations, let’s set up our docker-compose.yml file. First we’ll add a service to be our database. In this case it will be the postgres docker image.

services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: example_user
      POSTGRES_PASSWORD: example_password
      POSTGRES_DB: example_database

This simple configuration will spin up a PostgreSQL database server with an empty database named example_database. The server will have a hostname of db on Docker’s virtual network, since that’s the name of the service. Next, we’ll add another service for Flyway.

services:
  ...
  migrations:
    image: redgate/flyway
    volumes:
      - ./sql:/flyway/sql
    environment:
      - FLYWAY_URL=jdbc:postgresql://db:5432/example_database
      - FLYWAY_USER=example_user
      - FLYWAY_PASSWORD=example_password
      - FLYWAY_VALIDATE_MIGRATION_NAMING=true
    command: migrate

The FLYWAY_URL jdbc:postgresql://db:5432/example_database will connect to the db service within the virtual network created by Docker Compose. This will almost work, however since the PostgreSQL server won’t be ready until it opens a TCP port, we need to wait until that socket is ready. We’ll add a wait-for-it service in the middle.

services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: example_user
      POSTGRES_PASSWORD: example_password
      POSTGRES_DB: example_database
  wait:
    image: willwill/wait-for-it
    command: -t 60 db:5432
    depends_on:
      - db
  migrations:
    image: redgate/flyway
    volumes:
      - ./sql:/flyway/sql
    environment:
      - FLYWAY_URL=jdbc:postgresql://db:5432/example_database
      - FLYWAY_USER=example_user
      - FLYWAY_PASSWORD=example_password
      - FLYWAY_VALIDATE_MIGRATION_NAMING=true
    command: migrate
    depends_on:
      - wait

Notice we’ve added a depends_on to each container configuration. This instructs Docker to start the containers in a particular order so that the container one depends upon is ready before starting the next. And that’s our final Docker Compose file! We’re ready to spin up some containers.

Running the migrations

To run the migrations, there’s a single command to execute.

docker-compose run --rm migrations

This tells Docker to run the migrations service we defined. Since we said it “depends on” the wait service, and the wait service depends on the db service, Docker is smart and will start them in order. First it spins up the PostgreSQL db container, then the wait-for-it container will wait until the 5432 port is open on db, then finally the Flyway container will execute with the migrate command.

docker output

But alas, the flyway migrate command fails.

flyway fail

I intentionally added a syntax error to illustrate why this technique is useful. We were able to catch the error before running Flyway against a real database. Add the missing comma and the migrations succeed.

flyway succeed

We now have a running database in a Docker container with all the Flyway migrations applied. Since it was just to test the migration scripts, we can throw it away.

docker-compose down -v

The Flyway migrations are ready to run against a real database. You can follow along with the official quickstart.

Running in a Continuous Integration (CI) Pipeline

The technique above is useful to add to a CI pipeline. As long as you have Docker available for CI builds, you can run the same commands above to validate migrations on pull requests and prior to deployments.

docker-compose run --rm migrations
docker-compose down -v

This way, syntax errors and other problems won’t sneak into your mainline branches. You can catch them before they are merged without having to touch any real databases.

Conclusion

This is a great starting place for new schemas you’re creating from scratch, but can also be retrofitted onto existing Flyway migrations. It catches errors in Flyway scripts sooner and provides valuable feedback in a risk-free environment. Go try it for yourself.