Section 1 - Introduction

To make the most of Flyway, and the migrations model it offers, it is often desirable to implement Flyway within a CI/CD pipeline. However, with such a wide variety of CI/CD tools out there, it can be difficult to know where to start.

This article has therefore been created to work through three commonly asked questions:

• How do I run Flyway commands inside a pipeline?
• How can I setup a starter GitHub pipeline, using Flyway?
• How can I use Docker alongside my Flyway deployments?

Section 2 - Redgate Documentation

Before we get started, it’s valuable to note that a lot of the content discussed in this document can also be found on various Redgate Documentation websites. See below for helpful resources:

• https://documentation.red-gate.com/flyway/quickstart-how-flyway-works
• https://www.red-gate.com/hub/university/courses/flyway

Therefore, if you require any additional knowledge around the topics discussed in this article, it is strongly advised to review the helpful guides provided above.

Section 3 - Getting Started

During this article, the below example YAML pipeline will be referenced:

name: 'PostgreSQL GitHub DevOps Pipeline'

on:
  push:
    branches:
      - main
      
env:
    SQLFLUFF_DIALECT: postgres
    DOCKER_IMAGE: redgate/flyway
	SCHEMAS: public

jobs:
  Continuous-Integration-Build:
    name: Continuous Integration Build
    runs-on: ubuntu-20.04

    steps:
      - name: Checkout
        uses: actions/checkout@v3.0.0
               
      - name: Continuous Integration Flyway Clean Migrate
        run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_BUILD_URL }}"
          -user="${{ secrets.DB_BUILD_USERNAME }}"
          -password="${{ secrets.DB_BUILD_PASSWORD }}"
          -cleanDisabled=false clean migrate -schemas="${{ env.SCHEMAS }}"
       - name: Flyway Info
        run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_BUILD_URL }}"
          -user="${{ secrets.DB_BUILD_USERNAME }}"
          -password="${{ secrets.DB_BUILD_PASSWORD }}" 
          info -schemas="${{ env.SCHEMAS }}"

      - name: Flyway Info Production
        run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_PROD_URL }}"
          -user="${{ secrets.DB_PROD_USERNAME }}" 
          -password="${{ secrets.DB_PROD_PASSWORD }}"
          info -schemas="${{ env.SCHEMAS }}"

       - name: Flyway Check
        run: >-
          docker run --rm \
            --volume ${{ github.workspace }}/migrations:/flyway/sql:ro \
            --volume ${{ github.workspace }}/reports:/flyway/reports \
            -e ${{ secrets.FLYWAY_LICENSE_KEY }} \
            "${{ env.DOCKER_IMAGE }}" \
            -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}" \
            -url="${{ secrets.DB_PROD_URL }}" \
            -user="${{ secrets.DB_PROD_USERNAME }}" \
            -password="${{ secrets.DB_PROD_PASSWORD }}" \
            -baselineOnMigrate=true check -changes -drift -dryrun -schemas="${{ env.SCHEMAS }}" -outOfOrder=true -connectRetries=10 \
            -check.buildUrl="${{ secrets.DB_BUILD_URL }}" \
            -check.buildUser="${{ secrets.DB_BUILD_USERNAME }}" \
            -check.buildPassword="${{ secrets.DB_BUILD_PASSWORD }}" \
            -reportFilename="/flyway/reports/report.html"
            
      - name: Upload report
        uses: actions/upload-artifact@v3
        with:
          name: Database Report
          path: reports/

        
  Deploy-Test:
    name: Deploy Test
    needs: Continuous-Integration-Build
    runs-on: ubuntu-20.04
    steps:
      - uses: actions/checkout@v3.0.0
      
   
      - run: >-    
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_TEST_URL }}"
          -user="${{ secrets.DB_TEST_USERNAME }}"
          -password="${{ secrets.DB_TEST_PASSWORD }}"  
          info -schemas="${{ env.SCHEMAS }}"
       - run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_TEST_URL }}"
          -user="${{ secrets.DB_TEST_USERNAME }}"
          -password="${{ secrets.DB_TEST_PASSWORD }}"   
          migrate -schemas="${{ env.SCHEMAS }}" -outOfOrder=true 
      - run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}"
          -url="${{ secrets.DB_TEST_URL }}"
          -user="${{ secrets.DB_TEST_USERNAME }}"
          -password="${{ secrets.DB_TEST_PASSWORD }}"   
          info -schemas="${{ env.SCHEMAS }}"

    Deploy-Prod:
    name: Deploy Prod
    needs: Deploy-Test
    runs-on: ubuntu-20.04         
    steps:
      - name: Checkout
        uses: actions/checkout@v3.0.0
      
      - name: Flyway Check
        run: >-
          docker run --rm \
            --volume ${{ github.workspace }}/migrations:/flyway/sql:ro \
            --volume ${{ github.workspace }}/reports:/flyway/reports \
            -e ${{ secrets.FLYWAY_LICENSE_KEY }} \
            "${{ env.DOCKER_IMAGE }}" \
            -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}" \
            -url="${{ env.DB_PROD_URL }}" \
            -user="${{ env.DB_PROD_USERNAME }}" \
            -password="${{ env.DB_PROD_PASSWORD }}" \
            -baselineOnMigrate=true check -changes -drift -dryrun -schemas=public -outOfOrder=true \
            -check.buildUrl="${{ secrets.DB_BUILD_URL }}" \
            -check.buildUser="${{ secrets.DB_BUILD_USERNAME }}" \
            -check.buildPassword="${{ secrets.DB_BUILD_PASSWORD }}" \ 
            -reportFilename="/flyway/reports/report.html"
            
      - name: Upload report
        uses: actions/upload-artifact@v3.0.0
        with:
          name: Database Report
          path: reports/
          
      - run: >-           
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}" 
          -url="${{ secrets.DB_PROD_URL }}"
          -user="${{ secrets.DB_PROD_USERNAME }}"
          -password="${{ secrets.DB_PROD_PASSWORD }}" 
          info -schemas="${{ env.SCHEMAS }}"
       - run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}" 
          -url="${{ secrets.DB_PROD_URL }}"
          -user="${{ secrets.DB_PROD_USERNAME }}"
          -password="${{ secrets.DB_PROD_PASSWORD }}" 
          migrate -schemas="${{ env.SCHEMAS }}" -outOfOrder=true
      - run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          "${{ env.DOCKER_IMAGE }}"
          -licenseKey="${{ secrets.FLYWAY_LICENSE_KEY }}" 
          -url="${{ secrets.DB_PROD_URL }}"
          -user="${{ secrets.DB_PROD_USERNAME }}"
          -password="${{ secrets.DB_PROD_PASSWORD }}"
          info -schemas="${{ env.SCHEMAS }}"

Section 4 - Considerations

Before taking a look within the GitHub pipeline, consider as you go the following questions:

• What do I want my automated pipeline to look like?
• How many environments do I want to have automated and will I need any manual intervention gates?
• Which version of Linux do I want my GitHub Runners to use? Do I have a preference, or will the default suffice?

Answers to these, will help guide how you may want to setup your automated processes.

Section 5 - CI/CD Tools

Although this article has a focus on GitHub, it is fundamental to mention that Flyway as a CLI has no pre-requisite for any one individual CI/CD tool. Meaning that depending on your personal pipeline requirements, you are free to select a CI/CD tool that best fits your needs. With the knowledge that the Flyway CLI can be built into any pipeline tool, that has the capability of running a CMD/PowerShell or Bash style script. Or in the case of this article, Flyway Docker containers can be called and utilized within any CI/CD tool that has the ability to call Docker.

Additionally, Flyway has the capability of being installed on a large number of platforms. Making pipelines much more flexible, when it comes to operating system requirements. At the time of writing this article, Flyway installers can be found for the following platforms:

• https://www.red-gate.com/products/flyway/community/download/

Section 6 - GitHub

With GitHub being one of the leading SaaS platforms for end-to-end DevOps development and deployment, we often get asked if Flyway is compatible and can be used within its CI/CD pipelines? The answer to this, is a resounding yes! This is because, as mentioned in the previous section, Flyway can be installed on a range of platforms and can be interacted with in a range of fashions. Making it simple to call Flyway using your preferred scripting language, built directly into a GitHub pipeline.

In Section 7, which will be around setting up a basic pipeline, we will be using GitHub Actions functionality. This utilizes the concept of a YAML file, which allows users to script out their pipeline in a single markup file.

If you’re unfamiliar with any of this, please find some helpful documentation below:

• GitHub Actions Documentation - GitHub Docs

Section 7 - GitHub Runners

In order to carry out the pipeline actions, GitHub uses the concept of runners. The most common Runners used, are Runner SaaS or Self-Hosted. 

• GitHub-Hosted Runners (SaaS)
  • These runners are automatically maintained and upgraded by GitHub. However, each time you run a pipeline, a fresh virtual machine is spun up for each job in the pipeline. Once the job is complete, the virtual machine is then discarded (This means that any changes that a job makes to the virtual machine file system, such as checking out code, will be unavailable to the next job). 
  • About GitHub-hosted runners - GitHub Docs
• Self-Hosted
  • These runners are setup and managed by yourselves and is used to run jobs within the pipeline. Self-Hosted runners provide more flexibility and control around dependent software that may be required for builds and deployments. Something that is extremely valuable within a Flyway pipeline.
    • About self-hosted runners - GitHub Docs
• More information
  • About GitHub-hosted runners - GitHub Docs

For the remainder of this article, I will be presuming SaaS runners are in use. One great advantage of using these runners, is that docker comes pre-installed, making it simple and easy to harness the power of docker alongside a Flyway specific image. This means there are no pre-requisites that need to be installed outside of what the pipeline will carry out.

Section 8 - GitHub Pipeline Setup

Although the example YAML pipeline contains all the relevant steps to get started with Flyway, it is FUNDAMENTAL that the pipeline is reviewed and the variables updated before use. This is due to the highly configurable nature of Flyway and it's ability to connect to the databases and folder locations in your environment.

The easiest method for updating the YAML pipeline, is by using the GitHub editor. This is because it analyses the file as you edit, checking for syntax issues and helping to ensure it's structured correctly, which we describe how to do in the following steps.

After reviewing the pipeline file, you'll notice that there are 3 distinct stages. These stages are Build, Test and Production-Deploy; designed to mimic a standard customer environment. With any good pipeline, security should always be a pertinent consideration. Which is why, the pipeline utilizes two types of variables throughout the process. The first being 'Action Secrets', which provides a secure method of adding pipeline information, without the inherit risk of using plain text values. The second being pipeline specific variables, which contain non-sensitive information for use throughout the pipeline. The setup of both of these are fundamental to the running of the pipeline. 

Therefore, please follow the instructions below.

Step 1 - Create the necessary Actions secrets

Actions secrets are used in this pipeline to add an extra level of security around the more sensitive pieces of information. For example, licenses, urls, usernames and passwords. 

To setup the Actions secrets of your project, from the main menu. Click 'Settings'

Then select 'Secrets > actions' from the left hand menu. 

For the pipeline to function, it is necessary to create 10 variables. In order to create these, simply select the 'New repository secret' button and see the details below: 

The following Variables are required: -

For the URL variables required a valid JDBC connection string will need to be provided, so that the Flyway CLI can connect to the relevant stage database. If you're unsure how to setup and test your JDBC connection details, start by following the Flyway Redgate University courses:

• • • https://www.red-gate.com/hub/university/courses/flyway
    • Tip - Flyway Desktop can be a great tool for building and testing your JDBC connection strings.

VARIABLE 1: FLYWAY_LICENSE_KEY = This will be populated with the Flyway CLI License key

The pipeline requires 3 environments

• Build -  This is a CI stage where we clean (deletes all data and schema) and rebuild the database from scratch as a deployment test
  • VARIABLE 2: DB_BUILD_URL = jdbc:postgresql://hostname/address:port/database
  • VARIABLE 3: DB_BUILD_USERNAME = database username
  • VARIABLE 4: DB_BUILD_PASSWORD = database password
• Test - This is a test environment where we can QA the new database changes before deploying to live 
  • VARIABLE 5: DB_TEST_URL = jdbc:postgresql://hostname/address:port/database
  • VARIABLE 6: DB_TEST_USERNAME= database username
  • VARIABLE 7: DB_TEST_PASSWORD = database password
• Prod -  This is the production deployment
  • VARIABLE 8: DB_PROD_URL = jdbc:postgresql://hostname/address:port/database
  • VARIABLE 9: DB_PROD_USERNAME= database username
  • VARIABLE 10: DB_PROD_PASSWORD = database password

The 10 variables added are already configured for use within the YML pipeline, therefore once everything is setup in this location, they are ready to be used.

Step 2 - Create a new GitHub action

To start the process, it is first necessary to create a GitHub actions, to do this within your repository in GitHub, click on the Actions Icon (pictured below)

The following page will appear, click on the configure button for a simple workflow.

This will present you with a example basic action, delete the contents and paste in the the example workflow provided at the start of this document.

At the top of the editor, replace the work blank with the appropriate name for your workflow (pictured below)

Step 2 - Personalise the YAML contents

At the top of the pipeline is a name which should be customised to meet your needs:

name: 'PostgreSQL GitHub DevOps Pipeline'

Throughout the YAML file, variables are used to make it simple and easy to configure how the pipeline functions. The first group of these are found at the top of the pipeline file and can be considered as pipeline variables. This means that the variables may be utilized anywhere throughout the pipeline, rather than be scoped at a stage level. 

The two variables used in this pipeline are 

• SQLFLUFF_DIALECT: This is used for Code Analysis and the dialect should be set to the flavour of relational database the GitHub action the pipeline is being used with, currently only SQL Server, Oracle and PostgreSQL are supported. MySQL is currently is in preview.  Please delete this variable if you are not working with one of these database types. 
• DOCKER_IMAGE: This is the docker image used within the pipeline to run flyway. 
• SCHEMAS: If you want to specify a specific schema these can be done here, currently this are set to the public schema.

env:
    SQLFLUFF_DIALECT: postgres
    DOCKER_IMAGE: redgate/flyway  #https://hub.docker.com/r/redgate/flyway
	SCHEMAS: public

Within the pipeline other settings are used which may need to be modified as appropriate depending on your setup, these are: -

• --volume: This is used to map repositor locations to the corresponding locations on the flyway docker image.  These should not need to be changes if your flyway migrations and reports folder are located in the root of your GitHub repo, if you have sub folders these paths would need to be modified. 
• -check.reportFilename: This is the name and location of the Redgate change report

Step 3 - Commit the GitHub Action file

Once you have changed the pipeline as you require, you can commit the new pipeline by pressing the start commit

Add a description and appropriate commit message and press 'Commit new file'

Section 9 - Pipeline Testing

It is not uncommon to have to review and amend the GitHub Action a few times to get it running as you expect, go to the Actions tab within your repository and you will see the newly committed workflow running in your repository. 

Although this example pipeline has been designed to be simple to stand-up and configure. There may be a need to run the pipeline through a few times, in order to iron out any configuration kinks that may be present. One tip to make this process as simple as possible, is to first test your JDBC connection details using Flyway Desktop. Where it can help build the JDBC connection string syntax, porting this over to the YAML file once complete. If you're unable to access Flyway Desktop, you could also use the Flyway CLI in your preferred Shell to test out the connection details outside of the pipeline.

Section 10 - Conclusion

Flyway is an extremely flexible migrations-first engine, with the CLI able to be built into most modern CI/CD tools. In this article, it showcased how Flyway could be built into a GitHub YAML pipeline. In addition to utilizing GitHub SaaS runners with Docker Containers, to speed up deployments and provide a more cost effective way of working. However, this just scratches the surface around how Flyway could be incorporated in almost any pipeline tool to match your business requirements.