• Flyway
• Team-based Development
• Automation and workflow

What is the Flyway Teamwork Framework?

Overview of the framework design

The original framework was designed more for single-developer use, and perhaps the end-to-end developer for whom database development was an occasional journey outside their natural comfort-zone. It was intended to get over the major irritations for any user, such as the difficulty in knowing the current version in a script, the lack of information supplied to callback scripts, and the problem of insecure passwords in Flyway Community. It also provided ways of getting the development artefacts such as build scripts and file-based database models that we database developers need.

After a bit of thought, and a few discussions with people adopting Flyway, I realised I had to do more. I had to make it relevant to larger enterprises working in teams, perhaps even doing CI and adopting DevOps practices. The result was the current framework, which is backward-compatible with the first. Hopefully, I can say I’ve simplified the framework enormously for the user, alongside making it far more supporting of branch-based database development work.

I’d summarize my main aims, in the continuing development of the framework, as follows:

• Keep routine spadework in the background – Any PowerShell script that a flyway user creates for using Flyway needs to be focused on the Flyway operations, without the distractions of the details of handling credentials or finding the current version of the database. These details should be dealt with, automatically, by ‘background’ tasks. I wanted it to be possible to start work on a development branch merely by making a shared directory your current working directory and then a running a prepared PowerShell script or working interactively in PowerShell as you would at the command prompt.
• Support a whole library of added functionality – the framework includes a raft of scriptblock tasks each of which does a useful task such as generating build scripts, object-level scripts, detecting drift and more, which you simply execute as required. You just list the tasks that you want to be performed and they are executed in the order you specify. These tasks can be run within PowerShell scripts, or within script callbacks by Flyway Teams. By running them in callback scripts you can guarantee that they are run consistently at the right point in the development work.
• Support development branches, and ‘variants’ as easily as possible – independently of any particular source control system, but compatible with all
• Support the basic requirements of all the major Relational databases – not just SQL Server. The idea is that anyone, whatever the RDBMS used, can make scripted SQL calls, do database build scripts (metadata ‘dumps’), and create JSON models of the database at any point. Obviously, this is going to take time, but I’m working through the list!
• A single resource within a project for SQL scripts and PowerShell utilities – stored in just one place. These are loaded automatically into PowerShell when you start a script or callback. These scripts are required for the general running of the framework and include cmdlets that are used by the scriptblock-based tasks.
• A single object-based parameter. I wanted a single ‘table’, with the values of all Flyway configuration items, placeholders and custom placeholders, along with all the project settings, to be available to any script, however it was executed. This hashtable objects is used by all the scriptblocks.

I use the ‘Flyway Teamwork’ PowerShell framework to illustrate all my articles. It works with Flyway Community but is also designed to make Flyway Teams much easier to use. I’ve always tended to learn better with examples, so each article, including this one, provides an example project and scripts on GitHub, which you can download and play about with to get hands-on experience.

Why bother?

Flyway’s great strength is that it is generally possible to apply a migration without one’s heart missing a beat. If it works, then fine. If it doesn’t, it rolls back all the changes up to that point in the script and leaves you with a nice clean database at the correct version. Well, it does in DB2, PostgreSQL, Derby, EnterpriseDB, SQL Server and any other database that supports transactional DDL. That doesn’t include MYSQL or MariaDB. Instead, you are faced with the message that you should revert to your backup. Well, evidently, you should have taken one. In a DevOps environment, you make sure that this is done automatically. It’s not cool to require hidden knowledge.

It doesn’t take much more thought to realize that, for a migration-oriented approach to database development, source control requires other artefacts besides migration scripts. It is likely to also require creation scripts for each database object, and the details of the migration, such as who did it and when, using which database. As well as these essentials, it is nice to do code-checks, and some integration testing to make sure that all is well. You’ll want automatic ways of recording what you changed, maybe a notification message or two. You might be surprised at what you need for effective teamwork in a database development.

The easier it is to work on different branches, do routine checks, and see what your co-workers are doing, the more likely it is for all these tasks to be done, and the more effective the teamwork.

Working with FlywayTeamwork projects

To start using the framework, you need a shared network directory visible to the whole team. This isn’t a directory squirreled away on a workstation. In my example, this is called FlywayTeamWork-Pubs. Within it is a Resources subdirectory, with the current version of the framework, plus subdirectories (as many as you need) for each Flyway database project.

We can start very simply this way. As a database project grown larger, more important and has more people working at the development, it needs to expand. This framework expands like a concertina to fill these requirements.

The root level of a project folder represents the “main” (production) branch of development. It, like all branches, contains are a standard set of directories to store migration scripts, routines and version reports, as well as to support both branches and variants. There are also a standard set of files, per branch, required for running, maintaining and configuring each branch of development in Flyway Teamwork projects. Every branch must be supported by one database (if more than one developer is working on a branch, they both work on the same database).

Configuring and running a FlywayTeamwork project

We’ll start with a simple, practical example. I’ve just added one NorthwindPG directory, for PostgreSQL, to the root directory of the FlywayTeamwork framework.

Now we go to the NorthwindPG directory. This will be our main branch, representing our releases. We create a few empty directories.

We’ll start by creating a Northwind database for our main branch. I’ve given a few tips on setting all this up in a separate article (Getting Started with Flyway Migrations on PostgreSQL).

Here, I’m using HeidiSQL to create the empty database on our PostgreSQL server:

We’re now going to set up the FlywayTeamWork framework to manage development of this database. We create a Flyway.conf file in the main directory and copy a preliminary.ps1 and DirectoryNames.json from the root Resources folder:

In the branch flyway.conf we put the following text (you need to put your own values in the fields with angle-brackets):

In a Flyway.conf in your user area, you should have …

We should now be ready to go! In this example, I’m running Flyway Community, executed within a PowerShell script. It’s a single script, but I’ll step through it in parts, to check everything is configured correctly, and then run the migration followed by post-migration tasks to create a build script and JSON ‘model’ for the new version Flyway created.

This will require use of the pgdump and psql utilities, so you’ll need to set the correct paths to these in the ToolLocations.ps1 file in the root Resources directory.

Checking the set up

The first section of code runs the preliminary set up and then checks that all the project and server details are as expected:

You should see the current version of the framework followed by details of the branch you’re working on, its database, and the user connecting to the PostgreSQL server:

scriptblocks and cmdlet loaded. V1.2.86
Processing the default variant of Main branch of the NorthwindPG project using northwind database on localhost server with user postgres"

If you’ve misnamed a directory, put a directory in the wrong place or you’ve put preliminary.ps1 in the wrong location than you’ll get an error. The message should make sense. If all is well, you can check that all your details have been picked up by highlighting (or typing) $dbDetails and executing it.

Executing Flyway

So now we can run our Flyway commands:

The info command will check that the flyway system is working, and it tells you the version of the database and what migrations have, and haven’t, been applied. In this example, there is just one versioned migration script (V1.1) in the Migrations folder, to create the Northwind tables and load them with development data:

PS <path to>\FlywayTeamwork\NorthwindPG> flyway info
Flyway Edition 8.3.0 by Redgate
Database: jdbc:postgresql://<myServer>:5432/NorthWind (PostgreSQL 13.3)
----------------------------------------
----------------------------------------
Schema version: << Empty Schema >>
+-----------+---------+----------------------------+------+--------------+---------+----------+
| Category  | Version | Description                | Type | Installed On | State   | Undoable |
+-----------+---------+----------------------------+------+--------------+---------+----------+
| Versioned | 1.1     | ClassicNorthwindJustTables | SQL  |              | Pending | No       |
+-----------+---------+----------------------------+------+--------------+---------+----------+

If you get an error, it could be that you made a mistake in the URL, you got the username wrong or maybe the password. If it was your first time using this server, you’ll have been prompted for the password. If you get it wrong, you’ll have to delete the encrypted password file in your user directory. It is also possible to get the SQL file encoded wrongly or that you didn’t realize that Flyway’s parameters are case-sensitive (see Flyway Gotchas).

If all has gone well, however, we can now build the initial database for the main branch, simply by running its first migrate:

If all goes well, you should see:

Database: jdbc:postgresql://<myServer>:5432/NorthWind (PostgreSQL 13.3)
----------------------------------------
Flyway Teams Edition (10 schemas) 8.3.0 by Redgate licensed to red-gate.com (license ID c6c1a4e9-632f-4cd0-8
f57-c27e360e0341) until 2024-08-24
----------------------------------------
Creating schema "dbo" ...
Creating Schema History table "dbo"."flyway_schema_history" ...
Current version of schema "dbo": null
Migrating schema "dbo" to version "1.1 - ClassicNorthwindJustTables"
Successfully applied 1 migration to schema "dbo", now at version v1.1 (execution time 00:04.276s)

So far, the only difference we have from an ordinary Flyway operation is that you never had to put the password into a Flyway.conf file (a great security risk). Your passwords are now stored individually encrypted in your home folder on your workstation, for each server, username and RDBMS. They are placed there the first time you access that server, via a dialog box.

Creating a build script and JSON model

Now, though, we’ll execute our post-migration tasks to create a build script and a JSON database model for the new version, as well as for the current database. In Flyway Teams, we can execute these tasks in a script callback but here we must remember to just execute it in the same script

We see…(hopefully):

Executed GetCurrentVersion
Executed CreateBuildScriptIfNecessary
Executed SaveDatabaseModelIfNecessary
For the CreateBuildScriptIfNecessary, we saved the report in S:\work\postgresql\FlywayTeamwork\NorthwindPG\V
ersions\1.1\Reports\V1.1__Build.sql
For the SaveDatabaseModelIfNecessary, we saved the report in S:\work\postgresql\FlywayTeamwork\NorthwindPG\V
ersions\1.1\Reports\DatabaseModel.JSON
in CreateBuildScriptIfNecessary, Written PG build script for NorthwindPG 1.1 to S:\work\postgresql\FlywayTea
mwork\NorthwindPG\Versions\1.1\Reports
in GetCurrentVersion, current version is 1.1

Now we’ll see if those useful build artifacts were really put there. Here’s the JSON model for V1.1 of Northwind:

That seems fine. What about the build script?

It all looks good, so we’ll now run another migration.

Getting a narrative of changes between versions

Let’s try adding the views to Northwind. It is a bit like an archeological dig, as the dates are from the 1990s.

We run Flyway. It detects the new file and runs the migration.

Successfully validated 3 migrations (execution time 00:00.196s)
Current version of schema "dbo": 1.1
Migrating schema "dbo" to version "1.2 - ClassicNorthwindViews"
Successfully applied 1 migration to schema "dbo", now at version v1.2 (execution time 00:00.344s)

OK. So, that’s easy. Well, in truth, I made a few mistakes along the way, but these resulted in rollbacks, so it was all pretty relaxed. Then it is time to create the new build script and the database model. We’ll also add in a new task, to do an analysis of the database model and compare it to the model in the previous version to see what has changed.

And we should see…

Executed GetCurrentVersion
Executed CreateBuildScriptIfNecessary
Executed SaveDatabaseModelIfNecessary
For the CreateBuildScriptIfNecessary, we saved the report in S:\work\postgresql\FlywayTeamwork\NorthwindPG\V
ersions\1.2\Reports\V1.2__Build.sql
For the SaveDatabaseModelIfNecessary, we saved the report in S:\work\postgresql\FlywayTeamwork\NorthwindPG\V
ersions\1.2\Reports\DatabaseModel.JSON
in CreateBuildScriptIfNecessary, Written PG build script for NorthwindPG 1.2 to S:\work\postgresql\FlywayTea mwork\NorthwindPG\Versions\1.2\Reports
For the CreateVersionNarrativeIfNecessary, we saved the report in S:\work\postgresql\FlywayTeamwork\NorthwindPG\Versions\1.2\Reports\VersionNarrative.MD
in GetCurrentVersion, current version is 1.2, previous 1.1.

And lo! The directory for v1.2 is there.

And as if by some strange magic the V1.2. files are there, including change reports and a version narrative (in markdown).

The current directory stores the database model and build script for the current branch database, meaning that these files are overwritten with the latest version, each time a migration is applied. Why would we also want that? It makes it easy to ensure that the changes that happen with each branch, every time a migration is applied, can be committed to source control. It would make sense to do it automatically after each successful migration. With Git, for example, each branch would commit the ‘current’ build script, along with a version number as a tag.

The $SaveDatabaseModelIfNecessary scriptblock just saves what is in the database at each version, So if you were to unwisely make untracked changes directly to the database, in HeidiSQL perhaps, these will be included in the model and will be detected because they will show up in the comparison done by $CreateVersionNarrativeIfNecessary with the previous version.

Limitations of comparing build scripts between versions

One way to check what changed between versions by comparing the build script for the latest version to the build script of the previous version using a file comparison utility. However, this is only of limited use because a build script doesn’t build database objects in the same order.

We ought to give an example. Here we compare the build script of V1.1 with the build script of V1.2. The first screenshot shows a change I had to make to a V1.1 table “Order Details“:

This looks fine, but unfortunately, it has scripted the objects in a different order between the two versions so that the other comparisons have broken down and made a file comparison of the entire script unusable.

In fact, those tables haven’t changed at all. I’ve just added views. Fortunately, we can do better with the narrative, generated by comparing JSON models.

The version narrative

There is a markdown file called VersionNarrative, that lists the objects by type and a consistent database-determined order. This allows a better comparison. Here is the narrative for the changes made by version 1.2, to version 1.1, viewed in Typora. You’ll see that it handles the alteration in the table column and correctly described the added views.

Summary

In this article, I’ve tried to briefly summarise some of the ideas behind the FlywayTeamwork framework that I’m currently using to demonstrate ways of using Flyway for team-based development work.

The objective is to hide all the tiresome details such as handling passwords, working out the current version and immediately previous (penultimate) version of the database, seeing what’s changed between versions, saving build scripts, and so on. Instead, I’m aiming to let you get as close as possible to the attractive simplicity of the system.

This is the first time I’ve used PostgreSQL in this framework, but I’ve extended the range beyond SQL Server, and I’ll add other database systems in time or when asked.

When this framework is used with Flyway Teams, it makes scripted PowerShell callbacks far easier to develop, manage and coordinate, because it delivers all the information you need, and removes any differences between Flyway callbacks and ordinary scripts.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more