Voice of the DBA

Tag: Redgate

Creating a Flyway Desktop Shadow Database in PostgreSQL

In order to generate migrations, we need to configure Flyway to use a shadow database. This post looks at that process.

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

Configuring the Shadow

This is an empty database where we run the migration scripts to verify them. Since a user might edit or create their scripts, we want to ensure there are no problems with the syntax or execution with other scripts. This is also a place where we keep the “previous state” of your development database and use this to detect the changes you’ve made.

This database gets cleaned, meaning objects get dropped, regularly, so you configure a space for this. It can be a separate database, or just a schema (more Oracle focused).

For me. I’m going to create a new database in postgreSQL to support this. As you can see below, I use the simple CREATE DATABASE syntax.

Once I do this, I go back to FWD and click the “Generate Migrations” tab. The first time I do this (and only the first time), it asks me to configure a Shadow database.

I click this and get a connection dialog, similar to what I have for my development database. In here I enter the credentials for my shadow database, which are similar to my development ones. I test the connection and verify I can connect.

That’s it. Now I’m configured for a Shadow.

The process is similar for other platforms, just with different credentials. If you need to learn more, you can read about this in the documentation.

February 6, 2023
Flyway Desktop Projects for My PoC

In a previous post, I set up the basic databases for the PoC project I’m working on. In this next post, we’ll get the Flyway Desktop projects set up for the PoC.

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

Flyway Desktop

Flyway Desktop (FWD) is the GUI that Redgate built on top of Flyway for managing your database project. This replaces the SQL Source Control and SQL Change Automation products that we used to try and integrate into IDEs.

I like Flyway Desktop, which is standalone app for capturing code and committing it to Git. It is a project based app, so you set up a project in a folder for a particular database (and possibly schema). In my case, I’m going to set up two projects to start for my PoC.

Version Control

We work with Git, which has become the de facto VCS for most people.

On GitHub, I set up a public project where I’m putting this repository. It is located at: https://github.com/way0utwest/FWPoC

This is on my local machine as a copy at e:\Documents\Git\FWPoC

MSSQL

The first project I’m setting up is my SQL Server project, in a folder called MSSQL under the root of the git repo. This is a project folder just for Flyway Desktop. Once I create the folder, I’m going to create a new project in FWD.

I get a form after clicking New project. I’ll set the name as MSSQL, choose the root folder, and since I created the folder, I uncheck the checkbox. While I appreciate it’s good to put things in a subfolder, at times I’ve had FWD make a subfolder under the folder I choose, so I’m wary of this box. Mostly because I make mistakes.

Once this is created, I start in the Schema Model tab. I hate this nomenclature, as it’s weird. This is the list of objects whose code I’m capturing. There’s nothing here, because I need to link this to my development database.

If I click the “Link” button at the bottom, I get a dialog for the JDBC connection string. Don’t worry, you don’t need to know Java. Just fill in the boxes.

Two things in the dialog above.

One: Click Trust Server Certificate. Most new installations of SQL and driver upgrades require this. I don’t know why we don’t have this checked by default.

Two: Click “test connection” in the lower left, so if you have issues, this gets found quickly.

Once this is done, I go back to the Schema model and I see this:

I’ll select this object and save this to the project. This puts the file in the file system for this object, but doesn’t commit this to version control. We can see this in Visual Studio Code below. This is just a git repo, so if I open it in VSCode, I can see the file, the contents, and a note there are changes to be committed in the left icon.

One project complete.

PostgreSQL

My second project is for PostgreSQL. Same git repo, similar process. I’ll create a new project, but say this is a PostgreSQL project.

The process is the same. I’ll link this to a dev database. I need to specify the port and database I’m using. I also specify the schema here, as it’s not the default.

Once I get the project connected, I see the same as I did above for SQL Server. I save it, and I get a slightly different structure in the project. I see a schema below the schema-model folder. In here, I see my table, but it’s not the code, but a description.

From here, I just commit and push this stuff up to the repo. Note that commits and pushes, can push everything from both projects as they are in one repo. I did this on purpose to keep everything organized for me. However, if this were a team, I’d likely separate SQL Server and PostgreSQL into separate repos so individual developers don’t get confused.

The next step here is to get a second database to where I can deploy changes for each project. I’ve got these set up, and in the next post, we’ll work on an initial deployment.

January 30, 2023
Using SQL Data Compare from the Command Line with a Project
SQL Data Compare (SDC) is a great way to sync data among tables. It’s a software utility analogous to SQL Compare, but working with data rather than schema. I had a customer ask recently about setting up a SDC project and then calling that from the command line rather than using the GUI and clicking.

This post looks at how you can call a project from the command line. The project has a WHERE clause in it, so it uses the settings from the project.

We have the data shown here, from two different databases. There is 1 row in the first table that is not in the second table (in the second database).

I’ll build a SQL Data Compare project. In this project, I point to these two databases and the tables.

If I edit the project, I can choose the tables and views tab. Here I see my tables, and I select the dbo.RSSFeeds table.

When I select the row with dbo.RSSFeeds, I can then click the “Where clause” option and get a dialog where I can filter data. Here I can enter the where clause I used in the first query above. I also have the”use the same WHERE Clause” box checked.

Now I can save that project. I’ll then execute this from the command line. Note that I don’t have the SQL Data Compare install in my path, so I qualify both of these files, the executable and the project file. The call for me is:
```
"C:\Program Files (x86)\Red Gate\SQL Data Compare 14"\sqldatacompare /project:"C:\Users\way0u\OneDrive\Documents\SQL Data Compare\SharedProjects"\DLM_Demo_RSS.sdc
```
You can see this being run below:

I can see there is a single row in the DB! that needs to move to DB2, which is the result I saw in the first queries above and in the SQL Data Compare gui.

If I add the /synchronize option to this call, SQL Data Compare will deploy the changes. Once I do that, I can query the two tables and see the data is the same. At least the data matching the WHERE clause.

Some of this is documented, but not worked through in an example, so I wrote this post to help myself and anyone else looking to work with SQL Data Compare from the command line. This is a great way to sync data easily between systems, if you have a repeatable set of data that you need to move.

SQL Data Compare is a very handy tool for checking and moving data between tables that needs to be synched. All sorts of lookup or reference data can be managed with SQL Data Compare. If you haven’t tried it, grab an evaluation and give it a try.

Disclosure: I work as an advocate for Redgate Software.
January 25, 2023
No Flyway Baseline No Migration
In my experiments with the Flyway CLI (fwcli), I’m finding some interesting behavior, some of which is catching my by surprise.

This post looks at the baseline command and the issues with not having one. I also cover a naming issue. This is a bit long, but I wanted to document what happens as I experiment. I’ll condense down what a baseline does in another post (or two or three).

The Scenario

I created a FWTest database and put a small table in it. I can see in my Object Explorer (OE) that there is just one table. Assume there are no views, functions, etc.

My flyway.conf file points here, and if I run Flyway Info, I see the results below. The only important part is the “schema version”, which is empty, and the table, which shows one versioned migration called getone.sql.

The migration is in my SQL folder, which is below the location of the flyway.conf file. The configuration file is in the smoketests folder and it has only two lines uncommented: 1 for the SQL Server connection string and one for the location of the migrations, which is the SQL folder. The relevant lines are:

That’s the basic start. I’ve got a database, and I created a new script. The script in the file is shown here:

Running Flyway

Now, I have a migration script I want to apply to a new database. What happens if I run flyway migrate. Will this create my procedure? Let’s see.

The output shown below runs and give me a green line and a red like. One success, one error.

The success is that the script was named correctly, so it passed validation.

The error is that there is no flyway_schema_history table. This is where all Flyway activity is tracked inside the database. Without this, there’s nowhere to stick the data on script execution.

The error does note that we need to run flyway baseline or set the baselineonmigrate option to true. The default for this is false.

Adding a Baseline

Let’s do the baseline thing. The documentation for baseline is very poor (as of Dec 2022) in my opinion. I’ve sent a few notes around the company, as I think this needs to be cleaned up.

In any case, this will do a couple things. First, it creates the flyway_schema_history table (under dbo for SQL Server) as the place to store history for Flyway. Next, it will add a row as the baseline version for this database. I don’t have a baseline script, a “B” script, but that’s OK. I don’t need it for now.

Let’s try this. I’ll run this on my database and we see the results below. I’ve captured the text, ignoring the licensing and connection string part. The results are really here:
```
Creating Schema History table [FWTest].[dbo].[flyway_schema_history] with baseline ...

Successfully baselined schema with version: 1
```
This shows me we have created the table and added a baseline of version 1. If we look in the database, we see this: the new table, but no proc.

If I run flyway info, I see these results (again, ignoring the licensing and connection stuff).

What I see in here is that my database version 1 is baselined with no scripts. However, the getone.sql script is noted as a versioned script but ignored because of the baseline.

The baseline is supposed to be a level that includes all scripts up to that number. It’s not well explained in the docs, but this means that any scripts up to the baseline are assumed to have been executed in the database. This is the baseline. The baseline.sql script also is supposed to include the contents of all previous migration scripts, but as my test showed, I don’t need that.

The Problem with Baselines

The thing I have to know here is that this baseline version means no scripts at this version or lower will be executed. Since my script was a v1 script, it gets ignored, as you see below:

Why this says 2 migrations, I don’t know. There’s only one script. I assume this is counting the non-existent baseline script.

However, nothing migrated, and my procedure isn’t created.

Be aware, that you want baseline scripts and other scripts to have discrete numbering.

Summary

Getting started with Flyway means we need a baseline to get going. We can do this without a script, but we do need to run flyway baseline, or set an option. I’ll look at those two items in a future post.

I also need to be careful with naming of scripts, as a script that matches the numbering of the baseline will not get executed.
January 16, 2023