Tag: Flyway

In a previous post, I got Flyway installed as a CLI utility (command line interface). This post will look at the first connection to a database.

If you need to install Flyway on Windows, see my previous post.

Concepts

Flyway is a command line executable that takes various parameters to control what it does. Essentially, these are command verbs and decide what action is running. The previous post looked at the version command.

There are also parameters that can be included before the command. There are lots of parameters.

However, much of the way Flyway works is controlled by the flyway.conf file, which is inside a conf folder where you installed Flyway. If this file is found in the current folder, then Flyway will use configuration parameters from this file.

Note, the inline parameters should override those in the conf file.

Configuration

I find it much easier to use the configuration files. While you can specify this with the configFiles parameter, I often ensure that I run Flyway from within a particular folder that has a flyway.conf file. This has worked well.

Copy your flyway.conf from your install folder (under conf) to a new folder where you will experiment. For me, I set up a new folder called “smoketests” where I’m doing a little experimenting. As you can see, I only have two files in here:

The default conf file can get a little confusing. It’s full of many options, which are somewhat documented. My default looks like this when I open it:

Most everything is commented out, which makes it harder to figure out what to do.

The thing to remember is that you uncomment those settings that you want to set. For example, the drivers section is extensive. Some of theses are included, some you need to download. Since this are all Java based JDBC drivers, it can look strange to a SQL Server person.

The thing that gets lost in here for me is that I don’t comment out the line that starts with SQL Server. Instead, I need to copy and paste the jdbc part to the flyway.url= line. My valid connection would look like this:

Let’s try connecting with the info command. When I run Flyway info, I get asked for a user and password. Annoying, but not that bad. However, I then get an error:

This is a secure connection error. I do have TCP/IP enabled, which I know Java needs. This is more about a change the SQL Server team made, which requires secure connections.

Let’s add this to our connection string: ;trustServerCertificate=true

This gives me this string:

flyway.url=jdbc:sqlserver://aristotle\SQL2022;databaseName=FWPOC_1_Dev;trustServerCertificate=true

Now when I connect, things work. I still have to enter a name and password, but I can connect and I get back something.

I see some info on flyway versions. There’s a new version available. I’m also licensed for Flyway Enterprise.

Then I see the filesystem folder, sql, is missing. This is where the migrations are stored, so if I want to add migration scripts, I need to add a folder.

Next, I get the complete connection string. Lots of options here, of which most are defaults.

Then I see the schema is empty. Flyway works off schemas, which is what many platforms require. We get lazy in SQL Server and often just use dbo, but most other platforms want schemas set up.

Finally I see that there are no migrations run in this database, and I see an empty status table.

That’s a lot, and I connected to the database. Nothing changed in the database, and no flyway_schema_history table was added. This essentially let me know Flyway was working.

The last thing I’ll do is add this to my connection string: integratedSecurity=true

This lets get away from the name and password on Windows, but using Windows Auth for SQL Server.

That’s it. I’ll keep working on different commands in Flyway and getting to know the CLI.

One of the things I’ve been trying to do is dig in more deeply to the Flyway command line (CLI) as part of my work with Redgate. While Flyway Desktop is amazing, it’s a wrapper, and my inclination is to ensure I understand the underlying technology.

When I first downloaded Flyway, it wasn’t obvious what needed to be done to connect in different ways, so I decided on a short tutorial that would help me remember how and teach others a few things. This post goes over downloading Flyway and getting started.

Setup

The setup for Flyway is easy. The Community version is free, though there are also Teams and Enterprise editions.

Flyway Community works on many platforms. I’ll choose Windows below.

The Windows version is a zip file. Click this, and it will download.

Then unzip this. I have a c:\Utilities folder where I keep stuff and I’ve added a flyway folder there. I unzip the latest into this folder.

If you care about versioning, you might drop Flyway into a version named folder, but I typically don’t on this machine. For production deployments, I would be more careful.

You then need to add this to your PATH. If you are on Windows 10 or 11, then in the Control Panel (Windows+I), search for environment. Edit the variables for your account.

Once the new dialog appears, find the PATH variable and click Edit.

Now add an entry for the folder where you extracted the Flyway files. For me, this is c:\utilities\flyway.

That’s it, Flyway is installed.

We can check at the command line with “flyway version”.

If you have entered a license key, then you’ll see that message.

I have been doing some testing with Redgate’s Flyway Desktop as a new way of managing code for databases. However, just like Git, I appreciate clients, but I want to know how the CLI (command line interface) works. I spent time learning git add, git push, git checkout and more. Now I have more comfort understanding how SourceTree or GitKraken work.

I wanted to do the same thing with Flyway, just to be sure that I know what the options, switches, and behavior for Flyway operations would be.

The Scenario

I had an existing database, and I wanted to play around with adding this to a DevOps flow. I was looking for a basic experiment, and decided to create a new repo. I copied the default flyway.conf file into this folder and changed it.

The only thing I did was alter the Flyway conf file in my folder to work with SQL Server. I copied the connection string into the flyway.url parameter and set it as follows:

flyway.url=jdbc:sqlserver://aristotle:1433;instanceName=SQL2017;databaseName=AdventureWorks2017;integratedSecurity=true

When I ran the info command, it failed.

When I ran the same command with a different database, it worked:

I was highly confused. I tried a number of different databases, and some of them worked, not I couldn’t see a pattern.

I checked a number of things, including the database owners, a few of which I changed. I thought it might be some permissions and dropped my sysadmin account and added it back.

I tried connecting with SSMS and with sqlcmd. Both of those tools seemed to work.

I was really stumped.

A Small Conflict

Finally, after a bit of back and forth with a few developers, someone noted that I shouldn’t need the port included in the string. Sure enough, when I removed it, things started working.

Apparently, the JDBC documentation notes the issue. I kept looking at Flyway docs, but they just pass things along to the JDBC driver from the various parameters and environment variables.

There is a note that says provide the port number to stop a round trip to the browser to determine the port number for a named instance. If the port number and name are included, the port takes precedence.

I have two instances, some of which have the same databases on each. The databases that worked were on a different instance (which responds to 1433). The ones that didn’t, weren’t on that instance. I kept examining the \SQL2017 instance, but that wasn’t the one I was logging into with my string.

A silly mistake, but a good one to note. The port is a higher priority than the instance in a Java connection string.

I can’t find a priority in the docs for OLEDB or the native client, but they do all say the address takes precedence over the address parameter.