• Flyway
• Configuration, Authentication

Splatting in Flyway to Avoid Tedious Typing

Flyway commands require both file-based configuration settings and runtime parameters. Configuration files handle long-term settings, whereas specific commands require either parameters or session-level environment variables. Some parameters such as connection details and credentials must be passed to Flyway securely.

It soon becomes tedious to type in parameters to Flyway commands. Although environment variables are useful for cutting down on typing, splatting in PowerShell, Bash, and Python allows you, in addition, to define parameter arrays and pass them as a set. This allows you to combine and reuse configuration settings that have a common purpose or perform specific tasks, providing all the information we need to Flyway while avoiding errors.

What is ‘splatting’?

Splatting is a technique for passing a collection of parameters to a cmdlet, function, or script in a structured way, reducing the need for long, complex command lines. Instead of listing parameters individually, you store them in an array, hashtable, or dictionary and pass them all together. While initially a PowerShell concept, similar techniques now exist in other scripting languages to simplify parameter management and command execution:

• PowerShell: Uses arrays (@()) for positional parameters and hash tables (@{}) for named parameters.
• Python: Uses lists or dictionaries, which can be expanded when calling a function.
• Bash: Uses arrays or indirect variable expansion to pass multiple parameters dynamically.

The term ‘splatting’ came from the nickname for the ‘@‘ character in PowerShell, from its likeness to the result of an insect hitting the windscreen.

Why splatting?

If you’re using scripting, splatting allows you to provide Flyway with all the information it needs, flexibly and with minimal typing. For anything beyond simple migration commands, Flyway needs additional details, which fall into different categories:

• Stable, project-wide settings (e.g. default schema) – best stored in project-level configuration files.
• Environment-specific settings (e.g., database URL, username, credentials) – best passed via environment variables or a separate ‘secrets’ file.
• Command-specific or volatile parameters (e.g., -add.description or -outputType=json) – best passed dynamically when executing a command.

Flyway’s design encourages you to store as much static configuration as possible in config files. It automatically reads from configuration files in the install location, user area, and current working directory. These files should provide all the application-wide, machine-wide, and user-wide settings, along with sensible default values.

Beyond this, splatting helps manage more dynamic parameters, such as those specific to certain commands, tasks, projects, or databases, where the values change frequently. Splatting also provides a structured way to supply workflow variables using placeholders that vary in permanence and scope or override default values without modifying config files, and to provide sensitive details such as user IDs and passwords, securely, reducing the risk of exposing them in command history.

Splatting with PowerShell and Flyway

Splatting works seamlessly with PowerShell cmdlets because they can natively process parameter collections from splatted arrays or hashtables. However, external CLI applications like Flyway don’t inherently understand PowerShell’s splatting mechanism. They expect their parameters to be passed as a string of arguments, and each CLI application interprets this string in its own way.

Flyway uses the syntax -parametername=parameterValue, rather than the PowerShell-style -parametername:parameterValue syntax used for passing parameters to cmdlets. Because Flyway does not natively support PowerShell splatting, all parameters must be formatted as a string before being passed to the Flyway command.

However, while the standard splatting syntax doesn’t apply directly, you can achieve the same effect with the Flyway CLI (or other CLI applications) by constructing and combining (‘splatting’) parameter arrays, which PowerShell will ‘expand’ into a single argument string when calling Flyway.

Defining and splatting arrays

One of the advantages of splatting parameter arrays is that we can group the parameters by functionality. We can have arrays that define the sensitive and target-specific parameters for each environment ($Dev, $Test, $Staging). We can also have a group of arrays for each of the Flyway subcommands and their flags (such as info with -outputType=json). We can have an array to hold general Flyway parameters ($FG), such as settings that apply broadly to Flyway’s behavior, as well as user placeholder variables.

We can combine the arrays we need for a particular task, at runtime (“the splat”), and PowerShell then automatically ‘expands’ this into a space-separated list of parameters and passes it as a single string to Flyway, which executes the command:

Here’s a simple demo of the above example:

Each of the three arrays ($Info, $Dev and $FG) contain a list of their relevant parameters and values, in the required key=value format. $Info contains the Flyway info command and its output format, $Dev holds connection details for the Dev environment and $FG has the general parameters and user placeholders.

OK, you have to generate the arrays, but only once in a session. From then on, it is just a matter of passing the parameters as splatted arrays. You can do the equivalent in Python. The above example uses the $(...) subexpression operator to concatenate the arrays but we can use various syntaxes to get the same result:

# we combine the  arrays that we need for the command in several ways
flyway  @($Info;$Dev;$FG) # the 'splat'. we can pass this as a single array
flyway  $($Info;$Dev;$FG) # or use the $(...) subexpression operator
flyway  $Dev $FG $Info # or just provide the three arrays

Reading connection details from a secure configuration file

Of course, if the connection details are stored as config items in files on disk, either encrypted or in a secure user area, or maybe both, then you can read them into one of these arrays. This would be a good fallback way of reading connection information rather than using STDIN with the configFiles parameter, or specifying the file as a relative path that can be resolved against the working directory.

In the following example, $Test holds the connection details for the Test environment, but this time we read them from the Flyway configuration file in the user area, which can be either a CONF or a TOML format. In either case, the code will get the parameters into the correct -key=value format

Saving command-specific parameters

One of the great advantages I’ve found with this splatting technique is that you can prepare preferences separately for each Flyway command. This is especially useful for recent releases of Flyway where a lot of newly introduced commands, relating to schema comparison and script generation, have their own set of parameters. Many of them define user preferences for how a particular command should be executed.

For example, the add command creates a new empty migration script. It has many parameters that have no relevance outside this command and are used to specify the version, description and location of the script:

-add.type	The type of migration to create. Valid values: versioned | baseline | undo | repeatable. Default: versioned
-add.description	The description part of the migration name
-add.version	The version part of the migration name. If not specified, this will be calculated automatically based on the migration type
-add.location	The location to generate the migration to (See also: flyway.location)
-add.nameOnly	Only returns the name of the migration script, without creating the empty file. Default: false
-add.force	If the file already exists, overwrite it. Default: false
-add.timestamp	Add a timestamp to the calculated version if one is not already present. Valid values: always | auto | never. Default: auto

It will take some time and head-scratching to type this each time:

flyway add -add.type=undo -add.description='Undo Add Table' -add.location='C:\Migrations'.

It is much safer to enshrine it in a string array and alter it where necessary whenever you use it, just as you might fill in a form.

Defining an array to get the flyway schema version

Now we can demonstrate how to use splatting to tackle one of the perennial difficulties of Flyway, getting the database version number. You can query the Flyway Schema History table, but this isn’t always straightforward; its location is configurable, and if you’re using UNDO migrations, determining the correct version using SQL can be more complex.

We can make this much simpler if we create an $info array that stores the necessary parameters for retrieving the schema version directly from the JSON output from the info command. We save the JSON output to a file named after the project. We keep the project name in a separate array, making it easier to reuse this approach across multiple projects.

We splat this array along with the connection settings ($Dev) and any additional general Flyway parameters ($FG). We check the resulting JSON file for any errors and then extract all the bits of information we need from it: the schema version, the name of the database and the name of the schema. This is enough information to allow us to, for example, write a useful name for a backup, or a generated build script.

flyway  $Dev $FG $Info >$null
$result=Type ($Info|where {$_ -like '-outputFile=*'}|foreach{($_ -split '=')[1]})|convertFrom-JSON 
if ($result.error -ne $null)
    {write-warning "$($result.error.message)"}
else
    {
    $Schemaversion=$result.SchemaVersion
    $Databaase=$result.database
    $Schemas=$result.schemaName
    }

Benefits of creating and splatting arrays

We’ve seen that splatting can be very useful for running Flyway, especially if typing is not your primary talent. I’ve found a few distinct advantages.

• You can decrypt your credentials on the fly and have them added to the array of parameters.
• You can use Flyway in a pipeline to perform consecutive Flyway operations, just passing the separate arrays containing connection information, project information, user placeholders and credentials in a combined array that can then be splatted.
• Ability to prepare preferences separately for each Flyway command

By defining parameters in arrays, we gain a lot of flexibility. For example, we can override any parameter at runtime by placing the new value before the splatting the arrays required to run the command: If, for example, you wanted to use a different name for the JSON information file, say myPreferredName.json, you can just do this:

flyway '-outputFile=myPreferredName.json' $Dev $FCP $Info

Similarly, we can update existing parameters dynamically within an array using PowerShell’s -replace operator and specifying the key in the regex string:

Conclusions

When you are working with Flyway, there are many times, especially when debugging, when there isn’t a polished script that can be executed to do the task while you lie back in your chair and think about football. Instead, you are faced with the prospect of working interactively in the console on several different tasks.

At this point, you’ll quickly realize how useful it can be to ‘splat’ the Flyway parameters, using arrays (PowerShell and Bash) or lists (Python), each separate array or list being used for the different purposes of the parameters. There are parameters to define the server and how to connect to it. Some parameters concern the database project and others are specific to the Flyway command. Some parameters determine how you connect to the database, and maybe a host of user placeholders. You store the arrays or lists in files or maybe just snippets. Suddenly, it is back to a more restful interaction with Flyway.

 

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more