Voice of the DBA

Tag: Flyway

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
Parsing EXE Output in PowerShell
I saw a post internally that asked this question: Anyone have a handy powershell script testing if the installed flyway version matches a specific string?

That seemed simple, but getting program output from PoSh wasn’t something I’ve tried. So I tackled the challenge and this is what happened.

Getting the Output

The first thing I wanted to do was actually figure out what the output of checking the version was from the CLI. I looked at the help and noticed a version verb. When I run that, I see a bunch of lines of output.

A lot of output. I need to parse a bunch of strings, and then find a line.

My first experiment was to run this to get a file with this output.
```
flyway version > fwversion.txt
```
Now, let’s parse this.

Parsing Content

It’s been awhile since I read stuff from a file, but I know Get-Content works to read the file. What about finding a line. I saw this post with an answer that noted Select-String can be used, so I decided to try that.

Here’s a first cut of code:

That didn’t work. However, with some experiments, I tried this code:
```
Get-content fwversion.txt | select-string 'Edition'
```
That worked.

Now, I’ll assign that to a variable with this code:

$a=Get-content fwversion.txt | select-string ‘Edition’

Next, I’ll split this string by spaces into a new variable with this:

$b = $a -split(‘ ‘)

Then I can evaluate the various element of $b. You can see below the first and third elements are what I’m interested in. Really the third. Remember, PoSh is zero-based.

That let’s me parse the output, but I don’t want to save a file. Now on to the next step.

Capturing the Output from a Program

One of the things I know you can do in a PoSh ptompt is run a program. The redirection operator allows you to move output. When I tried it, I couldn’t quite get the output I wanted, but I did find this post that helped. With that, I ran this code:
```
$a = & "flyway" --version  2>&1 | select-string 'Edition'
```
This runs Flyway, captures the output in a stream and then uses the code above to find the right line. I assign this to a variable.

Almost there.

Adding a Parameter and a Test

Since I want to call this from the CLI and pass i a parameter, I added a param() clause to my script and then a test that compares the version output from the flyway.exe to the parameter. That gest me this code:
```
param(
    [string]$versionToCheck=""
)
$a = & "flyway" --version  2>&1 | select-string 'Edition'
$b = $a -split(' ')
if ($b[3] -eq $versionToCheck)
{ Write-Output("$($b) installed")}
else {
```
Write-Output(“wrong version – $($b) installed”)
}
Now I can call this from the CLI and check things. It works well. At least for now.

I am certainly not a PoSh expert, but this short script took me about 15 minutes to write with a little research. Then a little testing and I sent it off to the requester. Haven’t heard any complaints, so I’m hoping this actually works for them.
September 18, 2024
Friday Flyway Tips: Git Integration in Community Edition

Redgate added Git integration to the free, Community edition of Flyway Desktop. I saw the announcement and decided to make this post to show how this can work for a new project.

We do need git installed, so head over to the free git download if you don’t have it. From there, install Git and you’re ready to go.

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.

Source Controlling Your Project

When you start Flyway Desktop Community, you should see the edition in the upper left, as shown here.

I’ll click Open project, and choose one of my existing projects. When I do that, I see all the migrations in the project. I can also select or add a target and run flyway commands from here.

What’s new is the right hand sidebar, which now has the VCS controls. If I click the left arrow in the upper right, I get the sidebar to expand. I can see I don’t have any changes. This bar wasn’t available previously, but now it is.

Let’s make a change. I’ll close this (click the arrow at the top) and return to the migrations screen. I’ll click the “add migration” button (the arrow points to this in the image below).

When the editor opens, I’ll add some code. I’ll also change the name. Notice there are no changes in the right sidebar.

When I save this, all of a sudden, there is a single change in the middle of the bar.

Expanding the sidebar and clicking on the middle icon, I see my one change has been added as a migration script.

I can add a comment and commit this or continue working. When I’m done committing, I can easily push my changes from here to the remote.

I’m manually managing scripts in Community Edition, but I can do it all from Flyway Desktop, including all the version control work.

Flyway Enterprise

If you want to get more from Flyway, try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today.

If you use the CLI Flyway Community, download Flyway Desktop and get a GUI for your migration scripts as well as version control.

Video Walkthrough

No video walkthrough this week as I’m on the road.

You can check out all the Flyway videos I’ve recorded.

August 30, 2024
Friday Flyway Tips–Comparison Defaults
One of the little details that I find matter more and more in enterprises is understanding why a tool behaves a certain way. OSS/home-grown ones often have limited docs, but vendor tools should have great docs. Today I learned about how to easily find Flyway comparison defaults, which is the topic of this post.

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.

The Defaults

Do you know all the SQL Compare defaults? Would you realize it if someone changed them? Do you think they’re the same in Flyway? I actually don’t know about the latter question, and I’m not digging through and comparing the options.

In any case, I saw an internal discussion recently about documentation and someone pointed out that we have this page that discusses where you change options. It’s good for that purpose, but it doesn’t list the defaults.

However, at the bottom, there are links:
- Oracle comparison options
- SQL Server comparison options
If you click through, then you get a list of default options. There is first a link to the full list of options (SQL Server, Oracle) as well as a link to SQL Compare options (SQL Server, Oracle).

The SQL Server page looks like this:

Note the sentence just below the image, which links to all the options. The second link is for the SQL Compare option explanations. Below this, you see the default options. This is a table of options, which looks like this image. Note this says certain options are set to true and all others false.

If I click through to the full list, I see this:

I can see all the settings and if they are required. I also see an example of the TOML file below this, where I can set these and store them in version control.

If you want to change behavior of the comparison engine in Flyway, or double check if someone else has changed something, this is the place to check.

Note, if you are looking to induce certain behavior, changes should be made in a TOML file for the project and flow through a PR process for approval and into a pipeline. Don’t edit these options directly, or change them in a pipeline.

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:
July 19, 2024