Why has check.buildUrl been deprecated?

ColinSmit

With the introduction of check.buildEnvironment, we now have to configure the environment in the Flyway.toml file.  This leads to the following consequences:
JDBC, User Name and Password are in clear text unless you use a secret manager, which you have to pay for.

Previously, with check.buildUrl, we could pass a variable to this parameter, which is our preference as we dynamically create the build database using build.buildID, this saves time as the database does not need to be cleaned before the build.  Now I'm forced to hard code the value in the Flyway.toml file.  Please bring back the option to use variable substitution when reading these values.  This will allow us to continue using the DevOps variable groups and using the built in secrets functionality.

We would need to rewrite all our pipelines to use static build databases, losing 3 or so minutes on every build due to clean.  We have 16 projects currently and this is just going to keep growing so this will require a huge amount of effort to rewrite.

I don't understand why the deprecation of check.buildUrl, check.buildUser and check.buildPassword was done in favour of having to hard code these values using check.buildEnvironment.  We should be working smarter, not harder!

Ideally, we want to use the following in Flyway.toml:

[flyway.check]

majorTolerance = 0

buildEnvironment="build"

[environments.build]

url = "$(JDBC)"

user = "$(DatabaseLogin)"

password = "$(DatabasePassword)"

AlistairW

Sorry that this is proving painful for you. I hope I can clear some of this up.

We've deprecated the previous build parameters in favor of environments as these are more consistent and configurable - basically whatever you can configure with your migration URL (e.g. security, initialization) you can also use in the check command. It also supports project configuration from Flyway Desktop which can makes life easier.

You don't have to specify everything in the TOML file, the environment variable resolver should work in the case you have outlined (no secrets manager required although they are available):

[environments.build] 
url = "${env.JDBC}" 
user = "${env.DatabaseLogin}" 
password = "${env.DatabasePassword}"

Or if you prefer, you can do the same thing by specifying the environment on the command line (although it gets a but verbose):

flyway <some verbs and flags> -check.buildEnvironment=build -environments.build.url=$JDBC -environments.build.user=$DatabaseLogin -environments.build.password=$DatabasePassword

The documentation has fallen short in this case and we'll make some changes to help find the right way to wire this up.

Does what I've outlined solve the problem, is it good enough ? Please let us know.

ColinSmit

Thank you very much, that was the information I was looking for!  However, when the password value in the variable group is saved as a secret the command fails with:

Suppressed: org.flywaydb.core.api.FlywayException: Unable to resolve environment variable: 'DatabasePassword'

But if the password value is stored in clear text everything works as intended.  Could you check to see if this is a known issue or am I missing something?

[flyway.check]

majorTolerance = 0

buildEnvironment="build"

[environments.build]

url = "${env.BuildJDBC}"

user = "${env.DatabaseLogin}"

password = "${env.DatabasePassword}"

Variable group has the variable all 3 variables as above.

ColinSmit

Good day Alistair

Since you have not responded to my post in some time, I thought I'd update this with some new findings.

In an effort to get away from username and password (since the password variable cannot be obtained) I have made use of integrated security, where the VSTS agent uses an MSA Account, and set integrated security to true.  Added the MSA Account to all the relevant DB's with sa rights which works like a dream.

The problem I am encountering remains with the -check parameter, I can set the build DB URL via a variable and it is resolved, but I cannot set the check DB URL via a variable as it does not get resolved.  I can set the check DB URL to the actual JDBC of the check DB and it works.

- script: '${{parameters.FlywayCommand}} -X check -changes -drift -code -environment="Dev" -environments.Dev.url="$(JDBC) -environments.check.url="${{parameters.CheckJDBCConnectionString}}" -check.buildEnvironment="check" -reportFilename="$(System.DefaultWorkingDirectory)\${{parameters.Environment}}-${{parameters.DriftandChangeReportOutputName}}"'

- script: '${{parameters.FlywayCommand}} -X check -changes -drift -code -environment="Dev" -environments.Dev.url="$(JDBC) -environments.check.url="${CheckJDBC}" -check.buildEnvironment="check" -reportFilename="$(System.DefaultWorkingDirectory)\${{parameters.Environment}}-${{parameters.DriftandChangeReportOutputName}}"'

- script: '${{parameters.FlywayCommand}} -X check -changes -drift -code -environment="Dev" -environments.Dev.url="$(JDBC) -environments.check.url="$(CheckJDBC)" -check.buildEnvironment="check" -reportFilename="$(System.DefaultWorkingDirectory)\${{parameters.Environment}}-${{parameters.DriftandChangeReportOutputName}}"'

None of the above works as the -check parameter url is never resolved, however; substituting the actual JDBC:

- script: '${{parameters.FlywayCommand}} -X check -changes -drift -code -environment="Dev" -environments.Dev.url="jdbc:sqlserver://Dev13.directtransact.corp:2433;encrypt=false;databaseName=DIRENT_YOCO_DEV" -environments.Check.url="jdbc:sqlserver://Dev13.directtransact.corp:2433;encrypt=false;databaseName=DIRENT_YOCO_DEV_Check" -check.buildEnvironment="Check" -reportFilename="Report.html"'

This works perfectly.

I believe there may be a bug in the software that has not yet been identified or attended to, please can you confirm whether I am missing something or a bug does exist.

Regards

AlistairW

I think there is something strange with your shell variable substitutions, I've had a go on linux and powershell and following work for me pulling information out of an environment variable (called CheckJDBC):
Linux:

./flyway check -changes -environments.check.url=$CheckJDBC -check.buildEnvironment="check"

Powershell: 

./flyway check -changes "-environments.check.url=$env:CheckJDBC" "-check.buildEnvironment=check"

I'm not sure what VSTS uses but I'd expect powershell ??

As an aside, if you're using check then you have an enterprise license and so can access our helpdesk - we keep an eye on the community helpdesk but as you've seen, it can be sporadic. The helpdesk is likely to know more about nuances of particular CI platforms

ColinSmit

Alistair

This does not work., you have inverted comma's around the whole parameter, was this intentional?

Either way, I tried it as you have suggested and it does not resolve the variable.

ERROR: Unexpected error

org.flywaydb.core.api.FlywayException: Unsupported database type for changes

Build url: $env:CheckJDBC

AlistairW

I put inverted commas around the whole parameter for PowerShell because otherwise the shell attempts to split the parameter up using periods in the parameter name rather than give it all to Flyway and that doesn't work.
From what you have posted, it looks like the shell is not substituting the environment variable value so Flyway is being passed the entire string "$env:CheckJDBC" instead of the value.
I'd have a look at how environment variable substitution works on the platform you are using, I had a guess it was like Powershell but I don't know ...