Flyway

Vote on what features you'd like next

GitLab Dockerized YML Pipeline Using SaaS Runner

Page last updated 13 January 2023

A working example of how-to setup a dockerized flyway pipeline within GitLab, 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 GitLab pipeline, using Flyway?
  • How can I use Docker alongside my Flyway deployments?

Reminder - This article outlines a simple working pipeline using GitLab, utilizing basic Flyway command line actions throughout. To learn more around Flyway and available parameters, navigate to the Flyway documentation website by clicking HERE.

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:

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:

Example GitLab Docker YAML Pipeline

# Global Variables Section - These will be utilized throughout the pipeline #

variables:
  RELEASE_PREVIEW: 'Release-Preview.sql'
  BUILD_NAME: 'Build'
  # FLYWAY_DOCKER_IMAGE: redgate/flyway-azure:latest-alpine                                      #https://hub.docker.com/r/flyway/flyway
  FLYWAY_DOCKER_IMAGE: redgate/flyway:latest                                       #https://hub.docker.com/r/flyway/flyway
  FLYWAY_DOCKER_CONTAINER_NAME: flyway-container                                                #The name to provide to any Docker Container created
  FLYWAY_MIGRATIONS_PATH: $CI_PROJECT_DIR/migrations
  FLYWAY_CONFIG_FILES: $CI_PROJECT_DIR/conf

  # userName and password remain environment specific because their values are evaluated at runtime
  FLYWAY_DOCKER_RUN: 'docker run --rm -v $FLYWAY_MIGRATIONS_PATH:/flyway/sql -v $FLYWAY_CONFIG_FILES/$CI_JOB_STAGE:/flyway/conf $FLYWAY_DOCKER_IMAGE -user=$userName -password=$password -licenseKey=$FLYWAY_LICENSE_KEY'

# --- End of Global Variables Section --- #

# List of stages throughout the pipeline #

stages:          # List of stages for jobs, and their order of execution
  - build
  - test
  - production-pre-release
  - production-deploy

# --- End of Stages list --- #

# Hidden definition (the prefix of . indicates the hidden aspect) for the Flyway Build and Release Runner - This can be called by future jobs, all re-using the same block of code. Reducing the locations that may require changes #

.flyway_job:
  image: docker:latest
  services:
    - docker:dind #This enables Docker in Docker mode. Which allows for the Flyway Container to be created within the G
  before_script:
    - pwd
    - ls
    - echo "${FLYWAY_DOCKER_RUN}"
    - docker pull redgate/flyway:latest
  artifacts:
    paths:
    expire_in: 1 week

# --- End of Flyway_Job hidden definition --- #

# Build Stage Definition #

build-stage:       # This job runs in the build stage, which runs first.
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: build # Indicates which stage this job is part of
  rules:
    - when: always #Change always to never to skip this job (or vice versa)
  environment:
    name: build
  variables: # Definition of job stage variables
    databaseName: 'Database_Build'
    JDBC: 'jdbc:sqlserver://localhost:1433;encrypt=true;databaseName=Database_Build'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info clean info -url=${JDBC} -cleanDisabled='false'
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Build environment"
  artifacts:
    paths: 
      - ${CI_PROJECT_DIR}/migrations # Consume all of the migration scripts used during the build process as an artifact

test-stage:   # This job also runs in the test stage.
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: test # Indicates which stage this job is part of
  rules:
    - when: never #Change always to never to skip this job (or vice versa)
  environment:
    name: build
  variables: # Definition of job stage variables
    databaseName: 'Database_Test'
    JDBC: 'jdbc:sqlserver://localhost:1433;encrypt=true;databaseName=Database_Test'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info clean info -url=${JDBC} -cleanDisabled='false'
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Test environment"

prod-pre-release: 
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: production-pre-release # Indicates which stage this job is part of
  rules:
    - when: always #Change always to never to skip this job (or vice versa)
  environment:
    name: prod
  variables: # Definition of job stage variables
    databaseName: 'Eastwind_Prod'
    JDBC: 'jdbc:sqlserver://localhost:1433;encrypt=true;databaseName=Database_Prod'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN migrate -outOfOrder=true -dryRunOutput=${CI_PROJECT_DIR}/${CI_JOB_STAGE}-${RELEASE_PREVIEW} -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Prod environment"
    - pwd
    - ls
  artifacts:
    paths: 
      - ${CI_PROJECT_DIR}/${CI_JOB_STAGE}-${RELEASE_PREVIEW} # Consume all of the migration scripts used during the build process as an artifact
    expire_in: 1 week

prod-deploy: 
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: production-deploy # Indicates which stage this job is part of
  when: manual
  environment:
    name: prod
  variables: # Definition of job stage variables
    databaseName: 'Database_Prod'
    JDBC: 'jdbc:sqlserver://localhost:1433;encrypt=true;databaseName=Database_Prod'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Prod environment"

Reminder - The above pipeline contains example logic for setting up and using Flyway with GitLab. Therefore, please be aware that these scripts are EXAMPLES and should be treated as such. Meaning these scripts will need to be reviewed and tested, by yourselves, before being executed against any environments.

Section 4 - Considerations

Before taking a look within the GitLab 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 GitLab 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 GitLab, 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:

Tip - If you're interested in seeing how this same process could work in Azure DevOps, click HERE

Section 6 - GitLab

With GitLab 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 GitLab pipeline.

In Section 8, which will be around setting up a basic pipeline, we will be using GitLabs’ 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:

Section 7 - GitLab Runners

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

  • Runner SaaS
    • These runners are automatically maintained and upgraded by GitLab. 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 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.
  • More information

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 7 - GitLab 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. GitLab requires the filename to be '.gitlab-ci.yml' by default, therefore use this naming structure to avoid potential issues. Ensure the new file is then pushed to the remote repo, which in this example will be a repo within GitLab. Further details around setting up the YAML file can be found here - https://docs.gitlab.com/ee/ci/quick_start/

Step 2 - 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.

Reminder - Every environment is different, therefore please ensure the pipeline script is reviewed and updated accordingly before running.

The easiest method for updating the YAML pipeline, is by using the GitLab editor. This is because it analyses the file as you edit, checking for syntax issues and helping to ensure it's structured correctly. To start, navigate to your project and go to CI/CD > Editor from the left-hand menu.

After reviewing the pipeline file, you'll notice that there are 4 distinct stages. These stages are Build, Test, Production-Pre-Release and Production-Deploy; designed to mimic a standard customer environment. Within the stages themselves, specifically Production, the stages have been split into 'Pre-Release' 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 'Project Variables', 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 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. Which will be discussed in the next section of this article.

Pipeline Variables

variables:
  RELEASE_PREVIEW: 'Release-Preview.sql'
  BUILD_NAME: 'Build'
  FLYWAY_DOCKER_IMAGE: redgate/flyway:latest                                       #https://hub.docker.com/r/redgate/flyway
  FLYWAY_DOCKER_CONTAINER_NAME: flyway-container                                                #The name to provide to any Docker Container created
  FLYWAY_MIGRATIONS_PATH: $CI_PROJECT_DIR/migrations
  FLYWAY_CONFIG_FILES: $CI_PROJECT_DIR/conf

  # userName and password remain environment specific because their values are evaluated at runtime
  FLYWAY_DOCKER_RUN: 'docker run --rm -v $FLYWAY_MIGRATIONS_PATH:/flyway/sql -v $FLYWAY_CONFIG_FILES:/flyway/conf $FLYWAY_DOCKER_IMAGE -user=$userName -password=$password -licenseKey=$FLYWAY_LICENSE_KEY'
  • 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.
  • FLYWAY_DOCKER_IMAGE
    • The name of the docker image that will be used throughout the pipeline. By default, the latest version is utilized. However, more details can be found here - https://hub.docker.com/r/redgate/flyway
  • FLYWAY_DOCKER_CONTAINER_NAME
    • The name given to any docker container created. The default behavior deletes all containers after use, therefore there are no issues with each container being named the same.
  • FLYWAY_MIGRATIONS_PATH
    • This variable is used to define the path on the GitLab Container where the flyway migration scripts can be found. By default, this is looking within the 'migrations' sub-folder within the standard GitLab 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 GitLab Container where the flyway config file can be found. By default, this is looking within the 'conf' sub-folder within the standard GitLab 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. 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:

Build Stage Variables

 build-stage:       # This job runs in the build stage, which runs first.
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: build # Indicates which stage this job is part of
  rules:
    - when: always #Change always to never to skip this job (or vice versa)
  environment:
    name: build
  variables: # Definition of job stage variables
    databaseName: 'Eastwind_Build'
    JDBC: 'jdbc:sqlserver://azure-se-sql-database.database.windows.net:1433;encrypt=true;databaseName=Eastwind_Build'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info clean info -url=${JDBC} -cleanDisabled='false'
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Build environment"
  artifacts:
    paths: 
      - ${CI_PROJECT_DIR}/migrations # Consume all of the migration scripts used during the build process as an artifact

Test Stage Variables

test-stage:   # This job also runs in the test stage.
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: test # Indicates which stage this job is part of
  rules:
    - when: never #Change always to never to skip this job (or vice versa)
  environment:
    name: build
  variables: # Definition of job stage variables
    databaseName: 'Eastwind_Test'
    JDBC: 'jdbc:sqlserver://azure-se-sql-database.database.windows.net:1433;encrypt=true;databaseName=Eastwind_Test'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info clean info -url=${JDBC} -cleanDisabled='false'
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Test environment"

Prod Stage Variables

prod-pre-release: 
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: production-pre-release # Indicates which stage this job is part of
  rules:
    - when: always #Change always to never to skip this job (or vice versa)
  environment:
    name: prod
  variables: # Definition of job stage variables
    databaseName: 'Eastwind_Prod'
    JDBC: 'jdbc:sqlserver://azure-se-sql-database.database.windows.net:1433;encrypt=true;databaseName=Eastwind_Prod'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN migrate -outOfOrder=true -dryRunOutput=${CI_PROJECT_DIR}/${CI_JOB_STAGE}-${RELEASE_PREVIEW} -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Prod environment"
    - pwd
    - ls
  artifacts:
    paths: 
      - ${CI_PROJECT_DIR}/${CI_JOB_STAGE}-${RELEASE_PREVIEW} # Consume all of the migration scripts used during the build process as an artifact
    expire_in: 1 week

prod-deploy: 
  extends: .flyway_job # The extends command reuses configuration sections - See https://docs.gitlab.com/ee/ci/yaml/#extends
  stage: production-deploy # Indicates which stage this job is part of
  when: manual
  environment:
    name: prod
  variables: # Definition of job stage variables
    databaseName: 'Eastwind_Prod'
    JDBC: 'jdbc:sqlserver://azure-se-sql-database.database.windows.net:1433;encrypt=true;databaseName=Eastwind_Prod'
    pauseForCodeReview: 'false'
  script:
    - $FLYWAY_DOCKER_RUN info migrate info -url=${JDBC} -cleanDisabled='true'
    - echo "The job completed successfully with Flyway in the Prod environment"
  • extends
    • This is a method of using a template set of code to help structure what the stage carried out. In the case of this pipeline, it takes the information from the hidden '.flyway_job'. This is to define what each stage container should look like in a single location, making it simpler to update if required.
      • For example, '.flyway_job' defines that the GitLab SaaS runner will uses the latest docker image and have docker's dind (Docker in Docker) service enabled. Which allows for a docker container to be created within a docker container, necessary in the case of GitLab, because the SaaS runners are themselves a container!
  • databaseName
    • This is used within the relevant stage, to refer to the database name where required.
  • JDBC
    • A valid JDBC connection string will need to be provided, so that the Flyway CLI can connect to the relevant stage database. In the examples above, SQL Server is being utilized, using integrated security. However, due to Flyway being compatible with a large number of RDBMS solutions, this can be tailored to match your requirements.
      • If you're unsure how to setup and test your JDBC connection details, start by following the Flyway Redgate University courses:
      • Tip - Flyway Desktop can be a great tool for building and testing your JDBC connection strings.

Tip - If your pipeline requires additional stages to be present, I.e a Pre-Prod stage, simply make a duplicate of one of the current stages and change the variables accordingly. Also ensure that the 'Stages' structure on line 19 also indicates the new stage.

Project Variables

As mentioned earlier in the article, Project Variables 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 Project Variables navigate to the Settings < CI/CD section of your project, from the left-hand side menu. Click 'Expand' within the 'Variables' sub-section.

For the pipeline to function, it is necessary to create three variable. In this example, it presumes that the SQL Server User credentials for each environment is the same. However, if this is not the case for your setup, separate variables can be setup for each stage and consumed in the pipeline as required. If 'integratedSecurity' is enabled for your JDBC connection, which presumes Self-Hosted windows runners are utilized, the username/password variables can be ignored.

In order to create the three variables, as seen in the above image, simply select the 'Add variable' button and enter 'userName'/'password' and 'FLYWAY_LICENSE_KEY' as the Key of the three variables being created. When entering the value each variable will be associated with, you are also able to select the 'mask' variable tick box to make the value hidden within the job logs. This is extremely valuable for password variables, which best practice dictates should NEVER be stored as plain text. However, the great thing with GitLab is that the values are secured by default, with 'Mask Variable' adding an extra level of security. More information can be found here - https://docs.gitlab.com/ee/ci/variables/index.html#mask-a-cicd-variable

The three variables added are already configured for use within the YML pipeline, therefore once everything is setup in this location, they are good for use.

Section 8 - 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 9 - 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 GitLab YAML pipeline. In addition to utilizing GitLab 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.


Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal