• Flyway
• Team-based Development
• Tracking development changes

Flyway and Simple Source Control

Why bother with source control?

There are several ways that you can integrate your source control system into a Flyway database development. Your choice will be determined by the type of development, the preferences of the team and also the deliverables that you require from it. By deliverables, I mean, the contribution you want source control to make; the information and feedback it provides and the ways it can contribute to productivity, quality and visibility. Flyway itself provides “versioning” for databases. There are plenty of basic requirements of source control that it cannot meet though.

For team working, it is important to be able to track, manage and revert changes, using a tool that can provide a running history of code development and provide a way to resolve conflicts when merging contributions. It is important for database developers in a team to be able to coordinate their work, so we must provide ‘visibility’ of what is changing elsewhere in the database. For database development, it is particularly important to be aware of changes to objects (tables, views and functions) even where they are beyond the scope of the current work. Team members must also be aware of changes in dependencies, and relationships between tables.

As well as seeing the effect of migrations on the database, it is also useful to supplement Flyway’s version control by keeping a record of all the pending and committed versions of the migration code, whether they are versioned, undo or ‘repeatable’, and any changes that have occurred to these migrations. You also need a complete record of changes to all callbacks used, because these can change the database represented by a version number.

Getting stuck in with a MySQL development

We’ll explore some of the possibilities, using a MySQL development, Git, GMaster (sadly this tool was withdrawn shortly after writing this article), Flyway and a PowerShell framework I’ve developed. We’ll illustrate a simple development without branching, and look at various approaches to branching in a later article.

We start by setting up a new Flyway project called PubsFlyway, and make it a Git project too. So that we can use the extra resources from the FlywayTeamwork powerShell Framework, we set up the standard directory called ‘Resources’ and add our support files. You can find the latest copy of these repourses from my FlywayTeamwork-Pubs GitHub repo.

In the PubsFlyway directory, we put our git project. It will be the Workspace, or ‘Working Tree’ in git-speak. We also create the standard drirectores and copy in the necessary files (see What is the Flyway Teamwork Framework? for more details).

What gets stored in source control?

We’re using a network location that is accessible to everyone, so we need to decide what also gets monitored for changes by Git, and what resources we can class as reasonably ephemeral (although, in my case I’m using a very robust NAS with cloud backup). We’ll be using Git’s .gitignore file to do this.

If any of the files that Git hasn’t been asked to ignore subsequently changes, then Git will detect this and list them when they change. It is then up to you to decide if you want them staged for a COMMIT, at which point, of course, the changes are recorded within Source control. We need to commit into source control all the migration files, at a bare minimum. These are specified in the list of one or more paths in the Flyway ‘locations’ parameter.

In order to see what has changed between versions, we’ll also choose to save a full build script (just schema, not data) on every migration, and a directory of the build scripts of all the individual objects, each in a different subdirectory according to type. We well create these artefacts for every new version in its own directory. In addition, we’ll update the ‘current’ version so it always contains the latest version, and it is only the latter that we actually choose to commit to source control.

For source control, we want to store the source scripts for the current version only. We also make sure that configuration files don’t get put in source control. So we specify all this in the .gitignore file:

Step 1: Scripting Flyway migrations

The first job is to create a flyway.conf file that contains all the required project details. Here is something close to what I use. Don’t include the database password because the framework manages that for you. The placeholders are used by both Flyway and the framework.

The first bit of scripting ensures that you’ve got all the necessary details for PowerShell to do the builds, scripting and reports.

That preliminary.ps1 file must be in your current working directory because it is used to gather all the information that you’re likely to need, such as the user preferences, flyway configuration items, placeholders, environment variables and so on.

If all went well, you’ll see output that shows that you have the information you need.

Processing the default variant of Main branch of the PubsFlyway project using dbo database on MyPowerfulServer server with user Phil Factor"

The hashtable that is created by preliminary.ps1, containing all the flyway information, is called $dbDetails. You can add placeholders to pass information to the framework. The hashtable can also be used for PowerShell-scripted Flyway Callbacks that are called by Flyway Teams. This means that these scripts are never short of any configuration information, and we can also test callbacks by executing them directly.

From now on in this example, you can use both Git and Flyway, and also run the PowerShell utilities. We place the first migration in the Migrations folder (you can find the migration files here on GitHub:

We put it to the test with a Flyway info command:

Flyway Teams Edition 8.5.0 by Redgate
Database: jdbc:mariadb://<MyServer>:3306/dbo (MariaDB 10.6)
----------------------------------------
Flyway Teams Edition (10 schemas) 8.3.0 by Redgate licensed to red-gate.com until 2024-08-24
----------------------------------------
Schema version: << Empty Schema >>
+-----------+---------+---------------+------+--------------+---------+----------+
| Category  | Version | Description   | Type | Installed On | State   | Undoable |
+-----------+---------+---------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Initial Build | SQL  |              | Pending | No       |
+-----------+---------+---------------+------+--------------+---------+----------+

So far so good. That server needed a password, but it isn’t in the code or the config file. It’s been looked after for you. Now we just execute …

Hopefully, the output looks like this:

Flyway Teams Edition 8.3.0 by Redgate
Database: jdbc:mariadb://:3306/dbo (MariaDB 10.6)
----------------------------------------
Flyway Teams Edition (10 schemas) 8.3.0 by Redgate licensed to red-gate.com (license ID c6c1a4e9-632f-4cd0-8f57-c27e360e0341) until 2024-08-24
----------------------------------------
Successfully validated 1 migration (execution time 00:00.081s)
Creating Schema History table 'dbo'.'flyway_schema_history' ...
Current version of schema 'dbo': << Empty Schema >>
Migrating schema 'dbo' to version "1.1.1 - Initial Build"
Successfully applied 1 migration to schema 'dbo', now at version v1.1.1 (execution time 00:00.671s)

Step 2: Generating database- and object-level build scripts for each version

Fine so far. However, we want to have a build script for each version, and an object-level script directory so we can easily track changes to individual objects.

While we’re about it, we’ll collect a JSON object of the metadata that we can use to quickly summarize what’s changed. We’ll also get from Flyway information about the result of the migration. You may have other routine build tasks, but that’s enough for this example.

Here’s the expected output for a successful run:

Executed GetCurrentVersion
Executed CreateBuildScriptIfNecessary
Executed CreateScriptFoldersIfNecessary
Executed SaveDatabaseModelIfNecessary
Executed CreateVersionNarrativeIfNecessary
Executed SaveFlywaySchemaHistoryIfNecessary
For the CreateBuildScriptIfNecessary, we saved the report in S:\work\FlywayDevelopments\PubsFlyway\Versions\1.1.1\Reports\V1.1.1__Build.sql
For the CreateScriptFoldersIfNecessary, we saved the report in S:\work\FlywayDevelopments\PubsFlyway\Versions\1.1.1\Source
For the SaveFlywaySchemaHistoryIfNecessary, we saved the report in S:\work\FlywayDevelopments\PubsFlyway\Versions\1.1.1\reports\ApplyInfo.json
For the SaveDatabaseModelIfNecessary, we saved the report in S:\work\FlywayDevelopments\PubsFlyway\Versions\1.1.1\Reports\DatabaseModel.JSON
in GetCurrentVersion, current version is 1.1.1, previous 0.0.0.
in SaveDatabaseModelIfNecessary, written object-level model to S:\work\FlywayDevelopments\PubsFlyway\Versions\1.1.1\model
in $GetCurrentServerVersion, current mariadb version is 10.6.3-MariaDB.
in CreateVersionNarrativeIfNecessary,  narrative for 1.1.1 isn't necessary

So, what was all that lot being saved and why? It is just there if you need it, in the version subdirectory of the project.

Here are scripts for all the tables, for example.

Step 3: Committing the new version

If we commit these scripts, as well as the migration file, making sure that we store the actual Flyway version number with it, then we’ve got a very good chance of seeing what is going on.

You should see…

[master (<MyUserID>-commit) 12df264] Version 1.1.1 by Phil Factor
 31 files changed, 672 insertions(+)
 create mode 100644  - Copy.gitignore
 create mode 100644 .gitattributes
 create mode 100644 .gitignore
 create mode 100644 Migrations/V1.1.1__Initial_Build.sql
 create mode 100644 README.md
 create mode 100644 Versions/current/Migrationinfo.json
 create mode 100644 Versions/current/Source/table/authors.sql
 create mode 100644 Versions/current/Source/table/discounts.sql
 create mode 100644 Versions/current/Source/table/employee.sql
 create mode 100644 Versions/current/Source/table/jobs.sql
 create mode 100644 Versions/current/Source/table/pub_info.sql
 create mode 100644 Versions/current/Source/table/publishers.sql
 create mode 100644 Versions/current/Source/table/roysched.sql
 create mode 100644 Versions/current/Source/table/sales.sql
 create mode 100644 Versions/current/Source/table/stores.sql
 create mode 100644 Versions/current/Source/table/titleauthor.sql
 create mode 100644 Versions/current/Source/table/titles.sql
 create mode 100644 Versions/current/Source/view/titleview.sql
 create mode 100644 Versions/current/Version.json
 create mode 100644 Versions/current/model/table/dbo.authors.json
 create mode 100644 Versions/current/model/table/dbo.discounts.json
 create mode 100644 Versions/current/model/table/dbo.employee.json
 create mode 100644 Versions/current/model/table/dbo.jobs.json
 create mode 100644 Versions/current/model/table/dbo.pub_info.json
 create mode 100644 Versions/current/model/table/dbo.publishers.json
 create mode 100644 Versions/current/model/table/dbo.roysched.json
 create mode 100644 Versions/current/model/table/dbo.sales.json
 create mode 100644 Versions/current/model/table/dbo.stores.json
 create mode 100644 Versions/current/model/table/dbo.titleauthor.json
 create mode 100644 Versions/current/model/table/dbo.titles.json
 create mode 100644 Versions/current/model/view/dbo.titleview.json

What changed between versions?

Well, none of this was very complicated, so we’ll simply repeat create each new version, by running each of the migration files in the sample project. For every migration run (step 1, above), we also create the build artifacts (steps 2) and commit to source control (step 3).

Now, we’ll see how things are going again, with Flyway info:

Flyway Teams Edition 8.3.0 by Redgate
Database: jdbc:mariadb://MyPowerfulServer:3306/dbo (MariaDB 10.6)
----------------------------------------
Flyway Teams Edition (10 schemas) 8.3.0 by Redgate licensed to red-gate.com 
----------------------------------------
Schema version: 1.1.7
+-----------+---------+-----------------------------+------+---------+----------+
| Category  | Version | Description                 | Type | State   | Undoable |
+-----------+---------+-----------------------------+------+---------+----------+
| Versioned | 1.1.1   | Initial Build               | SQL  | Success | No       |
| Versioned | 1.1.2   | Pubs Original Data          | SQL  | Success | No       |
| Versioned | 1.1.3   | UseNVarcharetc              | SQL  | Success | No       |
| Versioned | 1.1.4   | RenameConstraintsAdd tables | SQL  | Success | No       |
| Versioned | 1.1.5   | Add New Data                | SQL  | Success | No       |
| Versioned | 1.1.6   | Add Tags                    | SQL  | Success | No       |
| Versioned | 1.1.7   | Add Indexes                 | SQL  | Success | No       |
+-----------+---------+-----------------------------+------+---------+----------+

We’ll check out the migrations. Here is the last migration file that was run by Flyway (shown in Gmaster):

There is more to it, however. You need to see easily and quickly what changes have been made at an object level. This isn’t part of the migration code, but is generated by comparing the object-level build scripts we saved and updated after every Flyway migration run.

The following screenshot shows the changes made in version 1.1.3 to allow the authors to have a wider range of names and addresses. (Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr. would have been accommodated at 35 characters, even with Classic Pubs database, but probably not his address). I’m using DMaster Git-client to show the changes (no longer supported, sadly. It had excellent SQL support).

I like having the alternative of a JSON model of each object, especially as it makes it easier to see, at a glance, or search for, the child objects such as indexes, keys, and constraints. Here, I’m using GitHub Desktop to do the comparison.

Of course, Flyway provides useful information such as who originally applied the migration, and when. Here is a JSON document that tells you the Flyway version at the time of the Commit.

Here is the project in GitHub:

Once you’ve published the repository to GitHub, you can push your changes that are in your local repository to GitHub.

Next steps: automation

Naturally, because all this was run in PowerShell, it is easily automated. How you do it depends on whether you are running Flyway Community or Flyway Teams. For Community, you can automatically commit the changes to the database at the end of the migration run, once you have run the framework tasks. For Teams, you can do it all in a scripted callback.

To test the example for this article, I had to run it several times. I therefore needed to entirely automate it to the point of placing the migration files in the migrations location/folder for each migration so that Git understood that the arrival of the migration in the folder was part of that change.

I start by moving all the migration files into the directory called ‘copies’ that is excluded from GitHub’s attention in the .gitignore file. I then clear out the ‘migrations’ directory entirely. Then I made sure that old copies of the repository were removed, and a fresh repository was in place.

All this manual work done, here’s the automation code:

As you can see, it is easy to automate the push to Github, but I must admit that I prefer to use the user-interface.

Conclusions

Flyway can be made to work harmoniously with source-control systems such as Git so that it is clear what which database changes were made in which Flyway version, and so that Git records who performed the migration with Flyway.

By maintaining ‘models’ of the current version of the database, stored in a ‘current’ directory within the ‘Versions’ directory, and committing changes for every new version created, it becomes easy for Flyway’s versioning to coexist with a Source control system without any conflict. The most important point for me is that these systems should assist the database developer and not get in the way, or demand that the developer must work in a particular way.

Tools in this post

Flyway

DevOps for the Database

Find out more