Can we deploy sql code on Multiple database in parallel using flyway

Question

I have server which contains multiple databases, now i need to deploy the code on all the database in parallel, Is it possible in flyway to achieve this because for each database i need to have separate config file. So i wanted to know how to achieve in such way all the config will trigger in parallel.

Regards, Adarsh

Answer 1

Why do you need a separate config file for each database? You can override most/all the data in the config file using the various Flyway command switches documented here:

http://flywaydb.org/documentation/commandline/migrate.html

For example, you can use the -URL switch to override the JDBC connection string in the config file. This allows you to have a single config file but to run the upgrade against different target databases.

It is generally preferable to use these switches as it avoids duplication of code. (You won't need to maintain so many config files.) It also means you can avoid putting stuff like passwords into your source code.

Next step is to create a script that runs flyway migrate against each of your target databases. For example, you could write a script that does something like:

flyway migrate -url:jdbc:mysql://<host>:<port>/<database1>
flyway migrate -url:jdbc:mysql://<host>:<port>/<database2>
flyway migrate -url:jdbc:mysql://<host>:<port>/<database3>
flyway migrate -url:jdbc:mysql://<host>:<port>/<database4>

Now when you run this script each of your databases will be updated in sequence.

Alternatively, if you need the updates to run in parallel rather than in sequence, you need to find a way to schedule for each line to run at the same time. One way you could achieve this is to use an automation tool like Octopus Deploy to orchestrate your deployments.

If you want to use Octopus Deploy you may find this step template useful. This step template also includes a "Drift-Check" feature to ensure that your databases are in sync:

Flyway migrate step template for Octopus Deploy

If you plan to use any other tool you may find this PowerShell script useful (copied from the link above) where $locationsPath, $targetUrl, $targetUser and $targetPassword are all variables.

# Executing deployment
Write-Host "*******************************************"
Write-Host "Executing deployment:"
Write-Host " - - - - - - - - - - - - - - - - - - - - -"
    $arguments = @(
        "migrate"
        "-locations=filesystem:$locationsPath",
        "-url=$targetUrl",
        "-user=$targetUser",
        "-password=$targetPassword"
    )
Write-Host "Executing the following command: & $flywayCmd $arguments"

& $flywayCmd $arguments

Regards, Alex

(Open disclosure: I am a pre-sales engineer at Redgate Software. I wrote the step template mentioned above and worked with a team to build FlySQL, a tool that helps MySQL Flyway users to build their projects more efficiently.)

Answer 2

In the Flyway documentation it describes a switch do specify a different config file. This way you

"To use an alternative configuration file use -configFiles=path/to/myAlternativeConfig.conf"

This way you should be able to use the same sql folder and yet apply different config settings.