One of the neat enhancements made to Flyway was the addition of state-based workflows and tooling. A lot of people have loved SQL Compare or SQL Source Control for deployments. As I’ve worked with customers, they’ve asked for this, and the v11 version of Flyway includes this for the Enterprise edition.
This post will look at using these two commands to deploy a change to a database from the command line instead of Flyway Desktop.
If you want to know more about Flyway, all my posts are together in one feed.
My Current Environment
I have Flway v11.0 installed and I have two databases: FWState_1_Dev and FWState_3_QA. These two databases are in my flyway.toml file, where I see them configured as development and qa.
I also created a “deployments” folder under my project, which is where I want deployment scripts. I’ll do this manually, but in a pipeline, I’d do this in an automated fashion. As you can see, this is an empty folder.
In Flyway Desktop (FWD), you can see that I have a change made in my dev db that’s ready to deploy. I could click “deploy” and run this from FWD, but I want to experiment with automation here, so I’m practicing at the command line.
I can verify this change isn’t deployed by checking the database.
Let’s get started.
Flyway Prepare
The prepare command is designed to create the deployment script. It can use a variety of sources, but in this case, I’ll use the schema model that I’ve saved in my git repo. This is where I (in general) want to pull from, using PRs and branches to manage work. In this case, I’ll pull from main, where I’ve got that change.
To get my changes, I’ll use a few parameters with Flyway. One thing to note, each of these parameters includes a namespace. I think this will go away at some point, but it’s good to be explicit in any case as we have lots of “source” parameters in different namespaces.
Second, use a single hyphen. That somewhat offends my unix/linux’y self, as the parameter names are more than one character. I’m used to 2 hyphens, which generate an error in Flyway.
The basic parameters I’m using are:
- prepare.source – the location of my source. In this case, the schemaModel defined in my flyway.toml file as “schema-model” in the file system.
- prepare-target – the target to look at to build the script. For me this is an environment preconfigured (see above) as qa, so “env:qa”
- prepare.scriptFilename – the default is D__deployment.sql, but I want it in a subfolder, so I’ll explicitly put this in.
My CLI call is this:
flyway prepare -prepare.source=schemaModel -prepare.target=”env:qa” -prepare.scriptFilename=”deployments\FWState__deployment.sql”
When I run this in my project folder, I see these results:
If I look in my deployments folder, I see the file:
The contents of the file are here:
This looks like what I’d get in FWD if I clicked “Deploy”.
This step just created the file. I need another call to deploy this.
Flyway Deploy
The Flyway Deploy command will execute a deployment script against a target. The idea here is that one script is created from a state-based project and deployed with this command.
As with prepare, I’ll use a few parameters here. The ones I need are:
- scriptFilename – the file to deploy. I’ll use the same one as above.
- environment – the name of the environment where I am deploying, in this case, no need for the prefix.
- executeInTransaction – this should default to true, but I’ll be explicit as I might change this in different platforms (postgreSQL, Oracle, etc.) and so if someone copies this pipeline, I want this to be clear.
This gives me this command:
flyway deploy -scriptFilename=”deployments\FWState__deployment.sql” -environment=qa -executeInTransaction=true
When I run this, it works.
I can verify this in SSMS.
I’ve deployed changes, and if I look at the FWD deploy tab, I see no changes.
I could put these two commands in a pipeline and have them work in an automated fashion.
If you do that, use variables in your pipeline, or ensure people commit (and merge) changes to the flyway.toml file that is used for the various options.
Summary
This post showed a quick way to start automated state-based deployments in Flyway, using the new prepare and deploy commands. A few options were used to control these deployments from a command line.
There are more options that you can include to control deployments and I’ll look at some other ways of doing this in the future. For now, this gets my deployments working easily.
Flyway is an incredible way of deploying changes from one database to another, and now includes both migration-based and state-based deployments. You get the flexibility you need to control database changes in your environment. If you’ve never used it, give it a try today.
Voice of the DBA 