Automating Database Migrations with Flyway and GitHub Actions

Introduction

When building an application, the database is an integral part of the overall architecture. Over time, as requirements change and new features are added, database schema changes are inevitable. As a result, it becomes crucial to have a reliable and efficient way to manage database schema changes. This is where database migration tools come in.

This blog post will cover how to set up automated database migrations using Flyway and GitHub Actions.

Prerequisites

Certainly! Before getting started with Flyway and setting up automated database migrations using GitHub Actions, it's important to ensure that you have all the necessary prerequisites in place. Here's some more information on each of the prerequisites mentioned in the original post, along with some additional resources to help you get started:

Database

A relational database management system such as MySQL or PostgreSQL.

Flyway

Flyway is an open-source database migration tool that simplifies database schema management. It is a Java-based tool that can be easily integrated into your application's build process or deployed separately. Flyway tracks the evolution of your database schema over time, making it easier to manage changes and track updates.

It provides a simple yet powerful framework for managing database changes, allowing you to version and execute database scripts in a reliable and repeatable manner.

You can download Flyway from the official website: https://flywaydb.org/download/.

Here are some additional resources to help you get started with Flyway:

Flyway Documentation

Getting started

To begin, create a new directory with any name of your choosing, then create two sub-directories within it called conf and sql. This will serve as the working directory for your Flyway migration.

Example

mkdir flyway-migrator

cd flyway-migrator

mkdir conf sql

Setting up Flyway

After successfully downloading and installing Flyway, the next step is to configure it with your database connection details. This can be achieved by creating a configuration file named flyway.conf in the conf directory that you previously created.

To set up Flyway, follow these steps:

Open the flyway.conf file using a text editor of your choice.
Configure the following properties in the flyway.conf file:

Parameter	Mandatory	Description
`flyway.url`	Yes	the JDBC URL for your database
`flyway.user`	Yes	the username for your database
`flyway.password`	Yes	the password for your database
`flyway.schemas`	Yes	the schema(s) to apply migrations to
`flyway.baselineVersion`	No	the baseline version to use for existing databases

For more detailed information about the configuration options available in Flyway, you can refer to the official Flyway documentation at here.

Here's an example flyway.conf file:

flyway.url=jdbc:postgresql://localhost:5432/mydatabase
flyway.schemas=public
flyway.user=myuser
flyway.password=mypassword
flyway.validateMigrationNaming=true

Note that you'll need to replace mydatabase, myuser, and mypassword with the appropriate values for your database setup.

Once you've configured Flyway with your database connection details, you're ready to start using it to manage your database migrations. You can run Flyway using the flyway command line tool and specify the configuration file using the -configFiles option, which we will see in the next steps.

Running the migration

After you have configured Flyway with your database connection details and placed your migration scripts in the sql directory, you can run the migration using the flyway migrate command. However, to ensure that Flyway uses the correct configuration file, you need to specify the path to the flyway.conf file using the -configFiles option.

From the root directory of the cloned repository, execute the following command in the terminal:

flyway -configFiles="./conf/flyway.conf" migrate

This command will trigger Flyway to execute all the migration scripts located in the directory specified in the configuration file. Flyway will keep track of which scripts have already been executed and will only execute the new ones. Once the migration is complete, Flyway will update the schema version in the database to reflect the latest version of the schema.

Automation

After successfully setting up and configuring Flyway for local database migration, the next step is to automate this process. One way to achieve this is by utilizing GitHub Action workflows. With workflows, you can easily automate the migration process by defining a set of actions to be executed automatically, based on specified events, such as a commit or a pull request. By automating the migration process using workflows, you can ensure that every new version of your database schema is deployed consistently across all your environments. This eliminates the risk of manual errors and streamlines the deployment process. Additionally, workflows can be configured to run on a specific schedule, ensuring that the migration process is executed at the desired frequency.

GitHub Action

We will be utilizing the workflow_dispatch event, which allows for the manual triggering of a workflow directly from the GitHub Actions tab. This provides a convenient way to run workflows without needing to push any changes to the repository.

After adding the migration script to the sql directory in this repository, you can easily run the GitHub Action to execute those migrations in any location you specify.

The workflow allows the user to specify environment variables, such as the environment name, database host address, port number, and type of database (MySQL or PostgreSQL) as input parameters (You can customize these inputs as per your specific requirements.).

Example workflow:

name: Flyway Migrations

on:
  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:
    inputs:
      env:
        description: 'Environment name'
        required: true
        default: 'dev'
        type: choice
        options:
        - dev
        - stage
        - prod
      dbHost:
        type: string
        description: 'Database host address'
        required: true
        default: 'localhost'
      dbPort:
        type: string
        description: 'Database host port number, i.e. MySQL 3306, PostgreSQL 5432'
        required: true
      dbType:
        description: 'Database type (MySQL/PostgreSQL)'
        required: true
        default: 'postgresql'
        type: choice
        options:
        - postgresql
        - mysql

The env section declares environment variables used throughout the job. The FLYWAY_VERSION specifies the version of Flyway to download, and DB_NAME specifies the name of the database to connect to.

name: Flyway Migrations

....
....
.
env:
  FLYWAY_VERSION: 9.16.3
  DB_NAME: postgres

The build job in our GitHub Action workflow consists of three steps.

First, the "Setup Flyway" step downloads and sets up Flyway on the system.
The next step, "Run Flyway Migrations", executes the Flyway migration command with the database connection details provided as environment variables.
Lastly, the "Flyway info and write result to file" step runs the Flyway info command to check the status of the migration and writes the result to a file in the migration-status directory. The filename includes the current date and time to make it unique. These steps can be modified to suit specific needs, such as adding additional migration steps or changing the format of the output file.

Here is an example of a complete GitHub Actions workflow file for automating Flyway database migrations:

name: Flyway Migrations

on:
  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:
    inputs:
      env:
        description: 'Environment name'
        required: true
        default: 'dev'
        type: choice
        options:
        - dev
        - stage
        - prod
      dbHost:
        type: string
        description: 'Database host address'
        required: true
        default: 'localhost'
      dbPort:
        type: string
        description: 'Database host port number, i.e. MySQL 3306, PostgreSQL 5432'
        required: true
      dbType:
        description: 'Database type (MySQL/PostgreSQL)'
        required: true
        default: 'postgresql'
        type: choice
        options:
        - postgresql
        - mysql

env:
  FLYWAY_VERSION: 9.16.3
  DB_NAME: postgres

jobs:
  build:
    runs-on: ubuntu-latest

    # Service containers to run with `container-job`
    services:
      # Label used to access the service container
      postgres:
        # Docker Hub image
        image: postgres
        # Provide the password for postgres
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: postgres
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          # Maps tcp port 5432 on service container to the host
          - 5432:5432

    steps:
    - uses: actions/checkout@v3

    - name: Setup Flyway
      run: |
        wget -qO- https://download.red-gate.com/maven/release/org/flywaydb/enterprise/flyway-commandline/${{ env.FLYWAY_VERSION }}/flyway-commandline-${{ env.FLYWAY_VERSION }}-linux-x64.tar.gz | tar -xvz && sudo ln -s `pwd`/flyway-${{ env.FLYWAY_VERSION }}/flyway /usr/local/bin 

    - name: Run Flyway Migrations
      env:
        FLYWAY_URL: jdbc:${{ inputs.dbType }}://${{ inputs.dbHost }}:${{ inputs.dbPort }}/${{ env.DB_NAME }}
        FLYWAY_USER: postgres
        FLYWAY_PASSWORD: postgres # ${{ secrets.DB_PASSWORD }}
        FLYWAY_SCHEMAS: flyway,public
      run: |
        flyway migrate

    - name: Flyway info and write result to file
      env:
        FLYWAY_URL: jdbc:${{ inputs.dbType }}://${{ inputs.dbHost }}:${{ inputs.dbPort }}/${{ env.DB_NAME }}
        FLYWAY_USER: postgres
        FLYWAY_PASSWORD: postgres # ${{ secrets.DB_PASSWORD }}
        FLYWAY_SCHEMAS: flyway,public
      run: |
        flyway info > ./migration-status/$(date -u +"%Y-%m-%d|%H:%M:%S")-${{ inputs.env }}.txt

    - name: Commit changes
      uses: EndBug/add-and-commit@v9
      with:
        add: './migration-status/*-${{ inputs.env }}.txt'

Finally, the Commit changes step commits the migration status file to the repository.

Here is the generated migration status file:

Flyway Community Edition 9.16.3 by Redgate
See release notes here: https://rd.gt/416ObMi

Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.2)
Schema version: 1

+-----------+---------+------------------------------+--------+---------------------+---------+----------+
| Category  | Version | Description                  | Type   | Installed On        | State   | Undoable |
+-----------+---------+------------------------------+--------+---------------------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | 2023-05-01 11:35:23 | Success |          |
| Versioned | 1       | create person table          | SQL    | 2023-05-01 11:35:23 | Success | No       |
+-----------+---------+------------------------------+--------+---------------------+---------+----------+

WARNING: A Flyway License was not provided; fell back to Community Edition. Please contact sales at sales@flywaydb.org for license information.

In addition to the explanation provided here, you can find a complete working example of automating Flyway migrations using GitHub workflows in this GitHub repository.

By exploring this repository, you can gain a deeper understanding of how to automate your database migrations using Flyway and GitHub Actions, and how to customize the workflow to fit your specific needs.

Final words

In conclusion, automating database migrations using GitHub Actions can save a lot of time and effort in managing database changes. With this approach, developers can focus on writing code instead of manually running migrations. In addition, it provides an auditable trail of all migrations and ensures consistency across all environments.

This tutorial has provided a basic understanding of how to automate database migrations using Flyway and GitHub Actions. The provided example workflow can be used as a starting point and modified according to the specific needs of your project.

As a final note, it is important to remember that database migrations are a critical component of any project and should be handled with care. Always ensure that proper backups and testing are in place before running any migration.

Blog