Using environments in Flyway commands

Deploying database code using pipelines in various CI/CD tools is a common practice among our customers who use Flyway. To enhance security and avoid hardcoding sensitive information like connection strings, they frequently employ variables and secrets in the pipelines.

Recently, Redgate introduced a new way to configure target database connections with Flyway CLI 10 called environments. In this article, I’ll demonstrate how to use environments inside your pipeline commands both with and without creating an environments section in the toml configuration file. There are several ways to accomplish this, but I’m covering how to do it in Azure DevOps using Pipeline Library variables to avoid adding usernames, passwords, and even connection strings in the configuration file.

Traditional Flyway commands

Before explaining the new environments feature, review the traditional way to specify Flyway commands. The MIGRATE command, for example, requires a JDBC url (and a username and password for SQL authentication):

When using the CHECK command to generate a change or drift report, a buildUrl (and a buildUser and buildPassword for SQL authentication) in addition to the target url is required.

Most CI/CD tools provide a way for you to store variables and secrets instead of hardcoding. This is how the variables look in Azure DevOps.

When accessing the variables, your commands would look something like this (variable syntax is dependent on the CI/CD system):

Environments

Version 10 of the Flyway command line introduces the new toml configuration files and the concept of environments. Environments allow you to set several values inside the toml file about each database target. A typical environment section might look like this:

NOTE: The traditional check.buildUrl, check.user, and check.password parameters required for change and drift reports are all deprecated, so you do need to learn how to use environments!

In your Flyway command, you can just specify the environment name instead of the URL if the environment is defined in the toml file.

The environments section allows the following parameters to be defined:

• Connect Retries
• Connect Retries Interval
• Driver
• Init SQL
• JDBC Properties
• Password
• Provisioner
• Resolver
• Schemas
• URL
• User

When defining your check database environment, that “disposable” database used for change and drift reports, you can add a section telling Flyway which environment to use. You must have an environments.check section in your flyway.toml file with the required information, and the names are case sensitive.

Your command can then look like this:

There is no need to tell Flyway about the check database since that’s been defined in the toml file with the [flyway.check] section.

Defining environments in the command

The problem arises when you must include sensitive information that you don’t want in a configuration file: the username, password, and maybe even the URL. It’s possible to configure system  environment variables on the agent or runner and refer to those variables inside the environments sections, but more on that in a later post.

It’s a bit tricky to “wire up” pipeline variables to the toml file, but, fortunately, there is another way to include the pipeline variables and secrets to work right in the command line beginning with  Flyway CLI version 10.5.0. You can create a drift report by defining that environments section values in the command. In this example, the target database is defined in the toml file, and the check database is defined in the command, but you could also define the target in the command as well:

By defining the check environments section in the command, Flyway will ignore the environments.check section in the toml file if it’s there.

It’s easy to change the hardcoded value for pipeline variable and secrets:

You don’t have to name your build environment “check.” For example, you could name your check environment “foo” or anything at all:

Conclusion

You have a lot of flexibility when using Flyway in your deployment pipelines. The new Environments functionality provides several new properties should you need them. You can include the environments in the toml file, but you can also define the environments in the actual command. This is one way to use variables in your pipeline, but stay tuned for more posts describing other methods.

 

 

Tools in this post

Flyway

DevOps for the Database

Find out more