Voice of the DBA

Tag: Flyway

Friday Flyway Tips: Autopilot in 10 minutes
The Solutions Engineers at Redgate recently released an Introduction to Redgate Flyway Autopilot course on our Redgate University. They’ve been working on this for quite some time to help people get started with Flyway in their own environment. It’s gotten smooth and slick, so I’m going to set this up in 10 minutes in this post and video, but with a twist. I’m using my schema to show you how easy this is.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Getting Started

The course walks you through a few things. These include:
- Getting Git
- Installing Flyway Desktop
- Having an Azure DevOps or GitHub account
- Having a SQL Server or PostgreSQL server (I’ll use SQL Server)
You will also get a Redgate Token and set up a local runner. I won’t detail those steps here, but I will have them in another post. The video will also skip those steps.

Creating a Repository

I’m working in GitHub, but you can do this in Azure DevOps. Others work, but those aren’t in the course. The main thing to do is go to the official Redgate repo at: https://github.com/red-gate/Flyway-AutoPilot-FastTrack

This brings you to this site:

From there, don’t clone or fork, but use as a template. This is in the upper right corner.

When you click this, you get the Create a new repository page, that looks like this. If you’re familiar with GitHub, this looks like any other repo. Give it a name, which must be unique in your org. I added FWAutopilot as I already have an “Autopilot” repo that is public.

You can make this public or private, but just be aware of this from the standpoint of your org, especially if you add internal schemas. You can also set a description.

Once this is created, you’ll see the repo in your org. Here’s my Autopilot repo:

This is a copy of the template all set up. Now, on to Flyway Desktop.

Creating a Project

In Flyway Desktop, I’ll click the drop down by Open Project and select Open from Version Control.

Here I’ll paste in the URL of my repo. I also check that the folder for the local clone is valid. In my case, I tend to put things in Documents/Git, but you might have your own standard.

Once this clones down, you can see a repo in the path above that looks like the online repo. Flyway Desktop will also refresh the schema, which should give you this error.

This is because the databases don’t exist. As you can see, I’ve filtered to databases with “auto” in the name and I have nothing.

If I use the file | open in SSMS, I can go into the repo and into the Scripts folder, where I see this:

I want to open the CreateAutoPilotDatabases.sql script, which looks like what you see below. This creates 5 databases and adds schema objects to one of them. The goal is for Autopilot to use Database DevOps and Flyway to migrate these changes to other databases.

Run this, and I see different databases. I’ve refreshed things, and you can see Prod has nothing but Dev has objects.

Now that I have a db, let’s refresh Flyway Desktop. Now I see no changes.

Note: If you aren’t doing this on your localhost instance, then you can edit the connections to the dev database (and other databases).

Adding Our Own Schema

Don’t start deleting schema objects yet, but you can add your own. I’ll do that. I have a script that contains a schema for baseball data. The beginning is shown below, but I’ll run this in my AutopilotDev database.

After I do this, I’ll refresh Flyway Desktop again and I see my tables. This is a partial list as the full list scrolls off the screen.

I’ll select all these from the checkbox at the top next to Object Name and then click “Save to project” in the upper right. This writes the CREATE scripts for all objects to the schema model, as you can see below in the update message.

The next step (shown at the bottom) is to generate a migration script. I’ll click that.

I get the screen below, which shows me all the changes that have been made to objects. I can select one or more of these to put into a migration script (deployment script). If I don’t select them all, then I will see those I haven’t selected re-appear here and I can add them to a different script. To keep this simple, I’ve selected them all.

When I click “Generate script” in the upper right, Flyway will create a script containing all the objects I’ve selected with the appropriate create/alter/drop code inside. Here is my one large script. You can see the start of the script below. If we scrolled, we’d see the CREATE for all the tables in here.

This project is configured to automatically generate an undo script, so below the above part, there is the undo script. Again, this is just the beginning of the script. However, you can see before we drop tables, we need to remove constraints.

Once I’m happy, I can click save and this is written as a migration script (and an undo script).

I can click Verify, which essentially runs these scripts against my shadow database, but I don’t do that if I haven’t altered the scripts. You can if you want.

Now that we’ve made some changes, let’s commit those. On the right side of Flyway Desktop is the VCS blade. You can see I have 28 changes in my repo.

If I click the “28”, this opens to the commit tab. I can also click the arrow at the top and select the commit tab. In here, I see my changes and I can include all of them or some of them and write a commit message.

I’ve selected them all and written a message, so I’ll click the drop down by commit and select the combined Commit and Push.

If I check my repo, I see this commit included. You can see this altered the schema-model and migrations folders.

Now we need to keep this Database DevOps flow going and deploy our code.

Setting Up Runners

If I check the Actions tab in my repo, I see there are two workflows configured. They are the same, but one works for Windows and one for Linux. I don’t have any runs yet and I haven’t configured a runner.

If I go to Settings in my repo and the Actions | Runners area, I’ll see this. The runners are the agents that execute your code. In this case, I need to setup a new one. I’ll detail that in another post.

Once I have a runner set up, I should see something like this:

Adding Secrets

Flyway is a licensed product, so I need to tell the runner that it is licensed to use the product. If you don’t have a license, this system can get you a 28-day trial, but if you have one, you can just use that.

If you go to the token section of the Redgate portal, you should see something like this:

If you click New Token, you get a new token.

Note: I’ve deleted this token, so this code doesn’t work.

Don’t close this, but open a new browser tab for your repo. Go to the Secrets | Actions section under Settings. You should see this. Click New repository secret.

The documentation notes you need to add two secrets: FLYWAY_TOKEN and FLYWAY_EMAIL. These are essentially secret variables picked up by the automation. When I click new, I add the email like this.

I added the token in the same way, pasting in the token from the portal. When I finished, I see two secrets.

Run the Automation

Check your production database (and the test one). There should be no objects, which is what we saw above.

Now, go to the Actions section of your repo. Click the Windows workflow on the left (or Linux if you used that).

Now on the right, click Run workflow, and then Run again in the pop up.

In a minute, your web page should show this running with a yellow circle before the name.

Your CLI window should look like this as well, with a job running.

If you click then name of the run on the web page, you should then see the three tasks. Here my build completed before I could get the screenshot, but yours likely has the yellow on the build database.

If I click any of the tasks, I’ll see the logging as they run. In this shot below, I’ve clicked the running prod deploy, as that was running when I was ready for the screen shot.

The output scrolls along and can be hart to follow, but after any of these are complete, you can click on them and see the task outline. Each of these items below can be expanded by clicking on the angle bracket. You can see I’ve expanded the Migrate Test DB task.

However, most of the time we assume we have a repeatable, reliable execution of our migration, so we don’t care. The proof is in checking the databases.

Here is my refreshed AutoPilotTest database.

and here is the AutopilotProd database.

I moved code from dev –> VCS –> test –> prod without executing it anywhere past Dev. This is the way changes should be made to test them before they hit prod.

We should also have feature branches, PRs, and more, but that’s beyond the 10 minutes to get started. From here, I could easily make other changes in dev and get them deployed by clicking a button in GitHub.

Summary

This process took me ten minutes. To be fair, I’d tested it a few times, but in knowing what things are needed in the docs, it took me ten minutes, which I show in a video below.

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. It works for SQL Server, Oracle, PostgreSQL and nearly 50 other platforms.

Video Walkthrough

I’ve got a video of me doing this in 10 minutes.
February 7, 2025
Friday Flyway Tips: Chaining Commands for State-based Deployments
One of the cool things I’ve seen with the new Flyway CLI is that I can combine multiple actions together in one call, which can make the process of writing automations streamlined.

I’m not sure I love this, but I’ll show how this works.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Making a State-Based Deployment from the CLI

In a previous post, I showed how to use the new prepare and deploy verbs to create a deployment script and then apply it to a database. This is a great way to handle simpler deployments if you want to work in a state-based fashion with Flyway.

That post showed two different CLI calls, which are shown below:
```
flyway prepare -prepare.source=schemaModel -prepare.target="env:qa" -prepare.scriptFilename="deployments\FWState__deployment.sql"flyway deploy -scriptFilename="deployments\FWState__deployment.sql" -environment=qa -executeInTransaction=true
```
Easy enough, and I like this structure as I’d likely build a pipeline that looks like this, where I prepare the script, then have a manual approval step to check this before deployment:

However, you might argue that someone would check the script in a QA or other deployment, so maybe you streamline things later, or you use this as a QA/test deployment and want everything to run smoothly after a PR.

In that case …

Chaining Commands

I can run this code instead.

flyway prepare deploy …

Let’s see this work. If you look, I have a couple of changes that I can see in Flyway Desktop. There is a table alter and a new stored procedure.

I can streamline the automation of these two changes in the CLI with the code style above with this code:

flyway prepare deploy -prepare.source=schemaModel -prepare.target=”env:qa” -environment=qa -executeInTransaction=true

Here is the pre-execution look at my database:

The execution, which didn’t use a transaction. I’ll have to figure that one out:

And the post database view with the table and proc changes deployed.

I built and deployed the script in one command, and it also appeared to have added my script to the deployments folder. No idea why that happened either.

If you want commands chained, you can do it.

Summary

This post showed how you can chain your prepare and deploy commands together in one command, which might be preferable in some situations.

I don’t know if I like this, or would do it, but it’s not a bad idea. Ultimately, I’d really like this deployment script to be saved back to the repo so I could track it, but that’s going to be some work, and likely that means not chaining commands.

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.
January 10, 2025
Using Flyway Prepare for State-Based Deployments
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.
January 8, 2025
Friday Flyway Tips: Searching a Migration

This was actually a cool tip I saw internally from one of the product managers, when trying to find specific text in a migration.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

A Large Project

I’ve got a project in Flyway Desktop that I add to regularly. It doesn’t do anything amazing, but I keep slowly adding objects, with the idea that I want to build up a large project for showcasing different things to customers. You can look at the project here if you are interested.

In my list of migrations, I’ve got a number of migrations, 21 to this point. It is a little hard to read names here, but I’ve named each script to make searching easier. I’ve included a create or alter, a type, and an object name. This helps me use the search box at the top of FWD, which searches migration names.

Most of these are relatively small scripts, one or two objects. A few are a little larger. If I select a particular one (21 as shown below), the code scrolls off the screen. Even if I expand the code listing to take up the entire screen, I can’t see all the code.

More importantly, it can be hard to visually find something in the code. If you’ve ever edited a stored procedure of 100 or more lines, you know what I mean.

One of our PMs pointed out recently that you can use the standard browser CTRL+F if you’ve clicked into the script. I’ve done this below and the search widget pops up at the top of the code. I’ve entered part of an object name here, prod, and you can see this shows 6 matches.

The first one is highlighted in a pale yellow color in the code.

If I click the down (or up) arrows in the search widget, the code jumps around, as it would in a browser. You can see below I’ve jumped down to the 4th occurrence, with the 2nd and 3rd highlighted in an orange(ish) color, while the 4th also has the yellow background bar.

Many of us would perform work on this code in an IDE (SSMS, ADS, etc.), but while managing a project or perhaps evaluating something else, we might want to quickly find something in code in FWD. Perhaps reviewing a change I’m about to commit. USing CTRL+F might be a handy feature.

I do wish the search was more comprehensive at the top, and could dig through all the files in the project, but at least I can search within a file here. Having a good naming convention for migrations also helps me to find the high level purpose for a migration in the list.

Flyway Enterprise

Try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

If you use Flyway Community, download Flyway Desktop and get a GUI for your migration scripts.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

October 11, 2024