Voice of the DBA

In my previous post, I set up the Flyway Desktop projects for SQL Server and PostgreSQL. I also added a table to each platform for development. In this post, I’ll look at how I let Flyway know what already exists in my system with a baseline.

This is part of a series of working through Flyway and Flyway desktop to demo database changes. Disclosure: I work for Redgate Software

Baseline v Flyway Baseline

I find these terms to be slightly confusing, especially when I look at Flyway vs. Flyway Desktop. In Flyway, there is a “baseline” verb, which you can run at the CLI. This will mark the state of your database at a level and adds the flyway_schema_history table to the schema in which you are working. This baseline causes Flyway to ignore all migrations up to the baseline level.

In my testing, when I run this on my database, it defaults to V1 for the first migration script. That’s usually fine, but as I wrote, this can cause issues.

There is also the concept of a baseline migration, which is a Bxx script, and this contains the definitions of all the objects that already exist in your target databases. This ensures that as FW and FWD track and deploy changes, they don’t try to redeploy those migrations that are at a level lower than the baseline numbering (the xx).

Creating a Baseline

My development database for SQL Server looks like this:

There is an object in here, but it’s not in any other environment. Both Integration and QA (and the others) have no objects.

In this case, I don’t need a baseline script, because I want this table to deploy to the downstream databases.

I do, however, need a baseline. I need the baseline marker in my databases to note that we have a base version. This will give me a starting point, but also ensure that FWD creates migrations that are numbered higher than my baseline.

I’ll add this in two ways. One with Flyway Desktop and one with the Flyway CLI.

The Flyway Baseline

For SQL Server, I don’t need to worry about any objects in downstream databases, so I’m just going to run the Flyway CLI. From a command line, I’ll run this code:

flyway baseline -url="jdbc:sqlserver://localhost;instanceName=SQL2022;databaseName=FWPoC_1_Dev;encrypt=true;integratedSecurity=true;trustServerCertificate=true"

This is run from my project location, though I’m passing in the connection string from Flyway Desktop as I don’t have a flyway.conf file configured for this project. Things work from the FWD gui, but not the CLI.

This works, and I see these results. Note the flyway schema history table is created at the bottom, and the version of the database is set to 1.

Now when I run Flyway info with that URL, I get this. There is an entry in the version tracking for this table:

I can also see this table in my Object Explorer:

Flyway Desktop and PostgreSQL

I’m going to use FWD for my PostgreSQL project. This will do some of the work for me and give me the option for a baseline script.

Note: I set up a shadow database first.

I click “Create baseline” and this asks me for a target. After all, I’m trying to ensure I don’t deploy anything to prod that’s already there.

When I click Add target database, I get a connection dialog. I fill this in with the credentials for prod. This returns me to this screen below, where I see my prod database, which is at this port with this name.

I click Baseline and it goes to work. There’s nothing there, so this returns back to the blank, Generate Migrations tab.

However, there is no baseline or schema tracking table. I didn’t have a poc schema, so perhaps that’s an issue, but that’s OK. We can fix this.

In the Migrations tab, I see this:

That configures this tab to look at (and work with) this database.

In general, I know we won’t be able to see production, but this is a PoC. However, this is something that I, in general, don’t want to do. I want to work with dev/test environments, so let’s do that.

I’ll configure my QA environment. I click “configure target database” and I get this screen. These are all the databases for my project. Here I’m going to click “delete” for production and then I’m going to click the Add and configure my QA database. Once I do that, I’ll see this:

Baseline added for PostgreSQL.

Success.