This guideline is deprecated. Latest and greatest can be found here: https://www.red-gate.com/hub/university/courses/flyway/flyway-desktop-enterprise-implementation/preparing-for-the-poc/proof-of-concept

A working example of how-to setup a flyway pipeline within Azure DevOps, using the declarative YAML pipeline-as-code method.

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 Azure DevOps 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://flywaydb.org/documentation/getstarted/
• 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 example YAML pipeline will be referenced, this can be found below (and also on this github page to see latest):

name: docker-ADO-hosted-pipeline

trigger:
  branches:
    include:
      - master
  paths:
    include:
      - migrations/*

pool:
  vmImage: $(AGENT_POOL)

variables:
  RELEASE_PREVIEW: 'Release-Preview.sql'
  BUILD_NAME: 'Build'
  REPORTS: 'Reports.html'
  REPORTS_DISPLAY_NAME: 'Reports'
  
  FLYWAY_MIGRATIONS_PATH: $(Build.Repository.LocalPath)/migrations
  FLYWAY_CONFIG_FILES: $(Build.Repository.LocalPath)
  FLYWAY: 'docker run --rm -v "$(FLYWAY_MIGRATIONS_PATH)":/flyway/migrations -v "$(FLYWAY_CONFIG_FILES)":/flyway/conf -v "$(System.ArtifactsDirectory)":/flyway/reports $(FLYWAY_DOCKER_IMAGE) flyway -user="$(userName)" -password="$(password)" -licenseKey=$(FLYWAY_LICENSE_KEY)'
  
  # Contains FLYWAY_LICENSE_KEY, BASELINE_VERSION, FIRST_UNDO_SCRIPT, FLYWAY_DOCKER_IMAGE, AGENT_POOL
  # Make BASELINE_VERSION match the baseline version in your project
  # Make FIRST_UNDO_SCRIPT match the first undo version in your project
  # FLYWAY_DOCKER_IMAGE is currently at: redgate/flyway:latest-azure
  # AGENT_POOL here is ubuntu-latest
  # Look for images at https://hub.docker.com/r/redgate/flyway
  group: flyway_vars

parameters:
  # IMPORTANT: DO NOT ADD DEPLOYMENT STEPS TO THE BUILD STAGE - THE BUILD IS A DESTRUCTIVE ACTION
  - name: buildStage
    type: object
    default: 
      
      stage: 'Build'
      displayName: 'Build'
      variableGroupName: 'build_credentials_variable_group' #contains userName, password, JDBC, databaseName

  # This is the extensible definition of your target environments. 
  # Every parameter in deploymentStages corresponds to an environment - here it's Test and Prod.
  # Pay attention to the 'dependsOn' field - this determines order of operations.
  - name: deploymentStages
    type: object
    default: 

    - stage: 'Test'
      dependsOn: 'Build'
      displayName: 'Deploy Test'
      pauseForCodeReview: false
      variableGroupName: 'test_credentials_variable_group' #contains userName, password, JDBC, databaseName, check_JDBC
        # IMPORTANT: check_JDBC will get destroyed

    - stage: 'Prod'
      dependsOn: 'Test'
      displayName: 'Deploy Prod'
      pauseForCodeReview: true
      variableGroupName: 'prod_credentials_variable_group' #contains userName, password, JDBC, databaseName, check_JDBC
        # IMPORTANT: check_JDBC will get destroyed

stages:
  - stage: ${{parameters.buildStage.stage}}
    displayName: ${{parameters.buildStage.displayName}} 
    jobs:
    - job: ${{parameters.buildStage.stage}}
      variables:
      - group: ${{ parameters.buildStage.variableGroupName }}
      - group: flyway_vars
      steps:

        - script: |
            $(FLYWAY) info clean info -url='$(JDBC)' -cleanDisabled='false'
          failOnStderr: false
          displayName: 'Clean Build DB'

        - script: |
            $(FLYWAY) info migrate info -url='$(JDBC)' -cleanDisabled='true'
          failOnStderr: false
          displayName: 'Migrate Build DB'

        - script: |
            $(FLYWAY) info undo info -url='$(JDBC)' -target='$(FIRST_UNDO_SCRIPT)'? -cleanDisabled='true'
          failOnStderr: false
          displayName: 'Undo Build DB'
        
        - task: CopyFiles@2
          inputs:
            targetFolder: '$(System.ArtifactsDirectory)' 

        - task: PublishBuildArtifacts@1
          displayName: 'Publish Build Artifact'
          inputs:
            ArtifactName: '$(BUILD_NAME)'
            PathtoPublish: '$(System.ArtifactsDirectory)'
  
  - ${{each stage in parameters.deploymentStages}}:
    
    - stage: ${{stage.stage}} 
      displayName: ${{stage.displayName}} 
      dependsOn: ${{stage.dependsOn}} 
      jobs:
      - job: PreRelease
        displayName: Configure Release
        variables:
        - group: ${{stage.variableGroupName}}
        - group: flyway_vars
        steps:
          - task: DownloadBuildArtifacts@0
            inputs:
              buildType: 'current'
              downloadType: 'single'
              artifactName: '$(BUILD_NAME)'
              downloadPath: '$(System.ArtifactsDirectory)'
            
          - script: |
              $(FLYWAY) migrate -outOfOrder=true -dryRunOutput='reports/${{stage.stage}}-$(RELEASE_PREVIEW)' '-url=$(JDBC)' '-cleanDisabled=true' -baselineOnMigrate=true -baselineVersion='$(BASELINE_VERSION)'
            failOnStderr: false
            displayName: 'Pre-Release Deployment Report'

          - task: PublishBuildArtifacts@1
            displayName: 'Publish Release Preview'
            inputs:
              ArtifactName: 'Release-Preview'
              PathtoPublish: '$(System.ArtifactsDirectory)/${{stage.stage}}-$(RELEASE_PREVIEW)'

          - script: |
              $(FLYWAY) check -changes -drift -code "-check.buildUrl=$(check_JDBC)" "-url=$(JDBC)" "-check.reportFilename=reports/$(databaseName)-$(Build.BuildId)-$(REPORTS)" -cleanDisabled='false' -baselineOnMigrate=true -baselineVersion='$(BASELINE_VERSION)'
            continueOnError: true
            displayName: '$(REPORTS_DISPLAY_NAME)'
          
          - task: PublishBuildArtifacts@1
            displayName: 'Publish $(REPORTS_DISPLAY_NAME)'
            continueOnError: true
            inputs:
              ArtifactName: '$(REPORTS_DISPLAY_NAME)'
              PathtoPublish: '$(System.ArtifactsDirectory)/$(databaseName)-$(Build.BuildId)-$(REPORTS)'

      - ${{if eq(stage.pauseForCodeReview, true)}}:
        - job: CodeReview
          displayName: Code Review
          dependsOn: 'PreRelease'
          pool: server
          steps:
            - task: ManualValidation@0
              displayName: 'Review Change Report Prior To Release'
              timeoutInMinutes: 4320 # job times out in 3 days
              inputs:
                notifyUsers: |
                 'user@email.com'
                 'foo@foo.com'
                instructions: 'Review changes'
      
      - ${{if ne(stage.pauseForCodeReview, true)}}:
        - job: CodeReview
          displayName: Skipping Code Review
          dependsOn: 'PreRelease'
      
      - job: Deploy
        displayName: Deployment
        dependsOn: 'CodeReview'
        variables:
        - group: ${{stage.variableGroupName}}
        - group: flyway_vars
        steps:

          - script: |
              $(FLYWAY) info migrate info -url='$(JDBC)' -outOfOrder='true' -cleanDisabled='true' -baselineOnMigrate=true -baselineVersion='$(BASELINE_VERSION)'
            displayName: ${{stage.displayName}}
            failOnStderr: false

Section 4 - Considerations

Before taking a look within the Azure DevOps 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 microsoft-hosted agents to use? (Default is Ubuntu in the above YAML pipeline)

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 Azure DevOps, 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://flywaydb.org/download/community

Section 6 - Azure DevOps

With Azure DevOps 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 Build and Release 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 an Azure DevOps pipeline.

In Section 8, which will be around setting up a basic pipeline, we will be using Azure DevOps’ Declarative pipeline-as-code 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 articles below:

• https://docs.microsoft.com/en-us/azure/devops/pipelines/get-started/pipelines-get-started?view=azure-devops
• https://docs.microsoft.com/en-us/azure/devops/pipelines/create-first-pipeline

Section 7 - Azure DevOps Agents

In order to carry out the pipeline actions, Azure DevOps uses the concept of agents. The most common agents used, are Microsoft-Hosted or Self-Hosted. 

• Microsoft-Hosted
  • These agents are automatically maintained and upgraded by Microsoft. 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).
• Self-Hosted
  • These agents are setup and managed by yourselves and is used to run jobs within the pipeline. Self-Hosted agents 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.
• More information
  • https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser

For the remainder of this article, I will be presuming Microsoft-Hosted agents are in use. More specifically, the 'ubuntu-latest' version, which is using a lightweight Linux agent. One great advantage of using these agents, 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 7 - Azure DevOps Pipeline Setup

Step 1 - Create the YAML File and Push to Remote Repository

To start the process, it is first necessary to save the YAML pipeline example from Section 3 as a new file in the root directory of your preferred GIT repository. The filename can be anything, for this example I will use the name 'FlywayPipeline.yml. Ensure the new file is then pushed to the remote repo, which in this example will be a repo within Azure DevOps.

Step 2 - Create the new pipeline in Azure DevOps

Whilst in your preferred Azure DevOps organisation, click Pipelines on the left-hand menu. Followed by selecting 'New Pipeline' in the top right-hand corner:

In the following screen, choose the repository location for where the code is coming from. In this example, we are selecting 'Azure Repos GIT', as the source code is housed in the Azure DevOps repos location. However, yours may differ, depending on where the source code is currently housed. Once the code location is selected, simply choose the relevant repository on the following screen.

In the configuration tab, select 'Existing Azure Pipelines YAML file', which will indicate we want to select a YAML file from within our repo. Following this, either enter the location for the file, or select the down arrow to select one from the drop-down list.

Step 3 - Review the YAML Pipeline

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.

After reviewing the pipeline file, you'll notice that there are 3 distinct stages. These stages are Build, Test and Prod; designed to mimic a standard customer environment. Within the stages themselves, specifically Test and Prod, the stages have been split further into 'PreRelease' and 'Deployment'. This has been done to help visualize the actions taking place within the pipeline process, in addition to allowing for code review steps to be carried out where requirement (For example, before pushing anything to Prod). 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 pipeline specific variables, which contain non-sensitive information for use throughout the pipeline. The second being 'Variable Groups', which provides a secure method of adding pipeline information, without the inherit risk of using plain text values. The setup of both of these are fundamental to the running of the pipeline. Therefore, please follow the instructions below:

Pipeline Variables

Throughout the YAML file variables are used to make it simple and easy to configure how the pipeline will function. 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. Which will be discussed in the next section of this article.

 variables:
  RELEASE_PREVIEW: 'Release-Preview.sql'
  BUILD_NAME: 'Build'
  REPORTS: 'Reports.html'
  REPORTS_DISPLAY_NAME: 'Reports'
  FLYWAY_MIGRATIONS_PATH: $(Build.Repository.LocalPath)/migrations
  FLYWAY_CONFIG_FILES: $(Build.Repository.LocalPath)

• RELEASE_PREVIEW
  • This is used to define the name of the Dry Run Script that is to be produced within the pipeline.
• BUILD_NAME
  • This is used to define the artifact name, where various build and release files will be uploaded in to.
• REPORTS
  
  • The is used to define the name of the Check reports produced within the pipeline
• REPORTS_DISPLAY_NAME
  • This is used to house the display name for the ADO artifact that will contain the reports created within the pipeline.
• FLYWAY_MIGRATIONS_PATH
  • This variable is used to define the path on the Ubuntu VM where the flyway migration scripts can be found. By default, this is looking within the 'migrations' sub-folder within the standard Azure DevOps Build repository. HOWEVER, it may be necessary to update this path should your GIT repository contain additional sub-folders.
• FLYWAY_CONFIG_FILES
  • This variable is used to define the path on the Ubuntu VM where the flyway config file can be found. By default, this is looking within the 'conf' sub-folder within the standard Azure DevOps Build repository. HOWEVER, it may be necessary to update this path should your GIT repository contain additional sub-folder.

Stage Variables

As a means of making it simple to configure the Build/Test and Prod environment details, the variables related to each stage are grouped together in code blocks between lines 79 - 106. Each stage will need to be updated to match your preferred environment details, otherwise the pipeline will fail. Although the variables used within each of the three sections are similar, please find a breakdown of what they are used for below:

      stage: 'Build'
      displayName: 'Build'
      variableGroupName: 'build_credentials_variable_group'

    - stage: 'Test'
      dependsOn: 'Build'
      displayName: 'Deploy Test'
      pauseForCodeReview: false
      variableGroupName: 'test_credentials_variable_group' #contains userName, password, JDBC, databaseName, check_JDBC

    - stage: 'Prod'
      dependsOn: 'Test'
      displayName: 'Deploy Prod'
      pauseForCodeReview: true
      variableGroupName: 'prod_credentials_variable_group' #contains userName, password, JDBC, databaseName, check_JDBC

• dependsOn (Test/Prod Stages)
  • Configuring this, informs ADO that there is a dependency for another stage to be successful before starting.
    • I.e - Ensure the Build process has been successful, before attempting Test.
• displayName
  • This is the display name for the stage.
• pauseForCodeReview
  • The ability to review the database code changes before deployment, can be extremely helpful whilst getting comfortable with CI/CD and Flyway. Therefore, setting this to true, will result in a Manual Validation step being present for that stage after the 'PreRelease' steps have completed.
• variableGroupName
  • This defines the name of the 'Variable Group' to be utilized within the pipeline stage. Details on how to set these up, can be found in the next section.

Variable Groups

As mentioned earlier in the article, variable groups are used in this pipeline to add an extra level of security around the more sensitive pieces of information. For example, usernames and passwords. 

To setup the variable groups, navigate to the 'Library' tab, found within the 'Pipelines' section of the left hand navigation menu within Azure DevOps. Following this, select the '+ Variable Group' button to create your first group.

For the pipeline to function, it is necessary to create three variable groups, one for each stage. Each group will need to contain the 'userName' and 'password' information that the Flyway CLI will utilize for connecting to that stage's database. If 'integratedSecurity' is enabled, these credentials can be skipped. In addition to the databaseName and JDBC connection details. A breakdown of why we need these variables can be found below:

• databaseName
  • This is used within the relevant stage, to refer to the database name when required. For example, to be used within the name of a generated report.
• JDBC
  • 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.

In order to create the four variables, simply select the '+ Add' button four times and enter 'userName'/'password'/'JDBC'/'databaseName' as the names of the variables needing to be created. When entering the value each variable will be associated with, you are also able to select the lock icon to make the value a secret. This is extremely valuable for password variables, which best practice dictates should NEVER be stored as plain text.

Simply copy this process three times, for the three variable groups required by the default pipeline setup, ensuring that the variable group names are as follows (Unless first changed in the pipeline):

• build_credentials_variable_group
• test_credentials_variable_group
• prod_credentials_variable_group

Once complete, it is necessary to create one final variable group, this time called 'flyway_vars'. The goal of this group is to contain the flyway licence key, which can securely be provided to the Flyway CLI where required.

The only variable required in this group is the following:

• AGENT_POOL
  • This should contain the name of the Agent Pool you would like to use throughout the pipeline. The recommended value to use here is 'ubuntu-latest'. This will ensure the most up to date ubuntu VM is spun up, when required by the pipeline. 
• FLYWAY_LICENSE_KEY
  • Without passing this key/value to the Flyway CLI, only basic Flyway commands can be carried out. I.e Flyway Community actions.
• BASELINE_VERSION
  • This should contain the version number of the baseline script, present in the migrations folder of your Flyway Project.
• FIRST_UNDO_SCRIPT
  • This should contain the version number of the first script that has a corresponding undo script equivalent. This is required so that the pipeline can test the rollback behavior, during the Build stage.
• FLYWAY_DOCKER_IMAGE
  • This should contain the name of the Redgate Flyway image to be used by the pipeline. The recommended value for this is 'redgate/flyway:latest-azure'. This will ensure the latest Azure Specific Flyway image is pulled down for use by the pipeline.
    • More details can be found - https://hub.docker.com/r/redgate/flyway

Section 8 - Pipeline Permissions

Upon completion of Section 7, which involved setting up the pipeline and the relevant variables. It is now necessary to carry out your first pipeline run, which will involve setting up pipeline permissions to be able to successfully complete. To start this process, select the 'Run' button from the top-right hand side of the ADO Pipeline editing screen.

You will then be navigate to a screen similar to below, where each stage will sectioned out (According to the pipeline). The pipeline will initially be paused, due to insufficient privileges to both the Variable Groups and Agent Pools. To resolve this, simply select the 'View' button that showcases at the start of each section. Followed by 'Permitting' the permissions for each Variable Group and Agent Pool that is requested.

Should you have been viewing the pipeline from the summary view, the same action is required.

Section 9 - Pipeline Testing

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 an Azure DevOps YAML pipeline. In addition to utilizing Microsoft-Hosted agents 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.

Related articles

• Page:
  Welcome To Flyway
• Page:
  GitHub YML Self Hosted Agent (Using GitHub Actions)
• Page:
  Azure DevOps YML Self Hosted Windows Agent
• Page:
  Azure DevOps Docker YML Microsoft Hosted Agent
• Page:
  Jenkins YAML (Jenkinsfile) Windows/Linux