Why has check.buildUrl been deprecated?

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)"


Answers

  • AlistairWAlistairW Posts: 4 New member
    edited February 28, 2024 11:11AM
    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.
  • ColinSmitColinSmit Posts: 4 New member
    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.
  • ColinSmitColinSmit Posts: 4 New member
    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
  • 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

  • ColinSmitColinSmit Posts: 4 New member
    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

  • 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 ...

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file