• Flyway
• Database Build tasks
• Team-based Development

Creating Database Build Artifacts when Running Flyway Migrations

When you use Flyway for building or upgrading a database to a particular version, you will run “Flyway migrate”, it will execute the set of migration scripts, in the correct order, and the result is a database at the version you need. You may need to run checks, update reports or send a message. You will often also want to produce other ‘build artifacts‘ such as scripts or data.

I’ll show how you can use two of the tools in Flyway Enterprise, Flyway and SQL Compare, along with PowerShell automation, to generate these database scripts automatically, as part of a Flyway deployment. For each database version delivered by Flyway, it will use SQL Compare to generate both a build script, and a script folder containing the DDL scripts for each database object. In a subsequent article, I show how you can use this object-level script folder to have Flyway check for ‘database drift‘, before running Flyway migrate.

This will then allow you to use a Flyway project together with other the Flyway Enterprise tools such as SQL Data Compare, SQL Source Control and SQL Change Automation.

What database scripts should go into source control?

Before tackling the topic of automating various database development tasks, such as generating build and object-level scripts, I ought to explain why a development team might need to generate several different types of database scripts in source control.

In any procedural application, it is generally obvious what the source code is. Not so with relational databases. Sure, we have modules such as functions, stored procedures, constraints, rules, views and triggers that all contain procedural code, but that’s not true of tables. A user database is a dynamic structure created from system tables, not from code. Our DDL code merely places the correct values in these system tables, and there are many legitimate and productive ways of doing this, in other words of designing and evolving the tables of a database. We can use entity-relationship design tools that generate code for building or migrating databases, or we can use table-building GUIs. Like most database developers I’ll choose the quickest and most effective way of doing any task. For example, I use a single data dictionary table for documenting the purpose of tables and columns rather than embedding extended properties in a script, and I would execute it separately, but would expect to see them in the script with the object they refer to.

To give a database development team sufficient flexibility to work in the most effective way, it pays to decide on what gets stored in source control as the ‘source of truth’, or ‘canonical source’ for a deployment. When working with Flyway, your ‘source of truth’ is the migrations folder. However, whatever you choose, it greatly assists the teamwork if all the other main ways of storing source code are available as well. An object-level build script is useful in development for telling us about all the changes, who made them and when. A migration script in development will tell us how it happened. A database-level build script allows us to re-create a version quickly and easily.

To move from one release to the next, you will use one or more migration scripts. Flyway will chain migration scripts in the right order. However, if you’ve used a variety of methods to develop your database, you might just use a single migration script, either one created by a tool like SQL Compare that summarizes the changes between the two versions into a migration or ‘deployment’ script, or one created by hand.

Here is a very generalized typical developmental process that will illustrate where script generation fits into the process.

Object-level source and build scripts

The most obvious advantage of generating a database build script and object-level source scripts, for each new database version produced by Flyway, is that you can then use other Flyway Enterprise tools, such as SQL Compare, to check that a database really is at the version you think it is before you try to migrate it to a new version.

Flyway’s migration approach encourages incremental change but doesn’t protect the database from being subject to ‘uncontrolled’ changes, otherwise known as database drift. Flyway will build any database at an exact version, but then over time that database is likely to drift, due to changes that don’t originate from a Flyway-controlled migration file. I find them in my own work, mostly applied by accident or carelessness.

Migrations only work if applied to the right version of the database. Flyway can tell you what migration files it has applied to a database but can’t detect changes made in other ways. If Flyway attempts to migrate a database that is not at the version it thinks it is, due to drift, then it is more likely that something will go wrong, although you can’t predict what.

Drift detection is important for any database, but is essential for a commercial database, where all changes must be auditable. This means that what is in production use is nothing more or less than what is in source control and has been signed-off by all the players in the deployment process.

Flyway, with the help of  SQL Compare, can tell you if there have been ‘uncontrolled changes‘ to the version it is migrating that could affect the migration, security or testing. It does this by comparing the metadata of the database you wish to migrate, with the object scripts or build script for that version, which this article will show you how to generate, as part of the Flyway migration run that creates that version initially.

There are other ‘process’ advantages that are nice to have, such as being able to consolidate scripts, run a structured and automated deployment process, and get a better perspective of changes at the level of individual tables or other objects, but the check against a build script to make sure it is exactly the professed version can prevent a great deal of confusion and time-wasting in the process of developing a database.

Migration scripts

If a database is already in production and being modified, the indispensable artefact produced by the development team is the script, or chain of scripts, that migrates the current production version to the new release, whilst preserving the data. It is easy to forget this, because while ‘build’ can refer generally to creating a database version from scratch, teams will often use the term ‘build’ differently during the deployment process, from Testing, User Acceptance Testing (UAT), through to Staging and onwards to Production.

What they mean by build is ‘the artifact that will establish the required version of the database’. Unless the database doesn’t already exist, ‘the build’ in these environments is in fact a migration, and this will modify the existing database to the new version, while preserve existing data, rather than ‘tearing down and starting again’. Once you have in place a strict policy of applying a version to databases and preventing ‘drift’ from that version, then all you need in the deployment process is the well-tested migration script. If that release candidate fails somewhere along the route, the deployment process must start again with a new version.

All this reinforces the need for different types of scripts during the development cycle, whatever you choose to represent the source of truth in source control. If, for example, you can painlessly generate both the build script and the script folder as you use Flyway, then you will always have an artifact that SCA or SQL Source Control can use. If, conversely, you like to work predominately from object-level scripts, you can always generate a migration script from a ‘static’ object-level folder, allowing Flyway to be used in a mixed approach.

Creating build tasks in PowerShell using script blocks

Each database product has its own solution. For Oracle, or SQL Server, we can use SQL Compare, this is all very simple. The tool can create a build script and a script folder. Once there is a script folder for a version, it can use it for comparison as if it were a live database, and it do its best to do the script in a way that guarantees to preserve the data. If there is any doubt, SQL Compare will warn you if the script needs any tweaking to preserve data.

To persuade Flyway to produce a build script and object script folder, we need a set of reusable PowerShell Build tasks that use SQL Compare to generate them. We can also create a range of other useful, standalone build tasks such as one that gets the current version of a database, or fetches a password for a user, and

To implement these tasks in PowerShell, I’ll be using script blocks as ‘nameless’ local functions to provide whatever tasks need to be done to the databases, either before or after Flyway has completed its work. The intention is to provide ‘hooks’ for other Flyway Enterprise tools and processes. In this article, I’ll be demo’ing a hook for SQL Compare to generate the scripts (a hook for SQL Data Compare would work in a very similar manner), and a hook for SQLCMD that gets the current database version number from Flyway’s schema history table.

The main reason for using script blocks is that by stacking them up in an array, I can run as many tasks as I like or need, as a list of jobs. As I have to pass the same parameters to each, I pass in just one hash table containing all the variables I need. Any result is written back to the hash table.

You can find the code for all the build tasks in this article in the associated GitHub project.

Creating the object source scripts and a build script

Here, as an example, is a database build task that creates an object-level script folder for the version of the database specified, using SQL Compare, but only does it if the folder doesn’t already exist in the location you’ve specified.

As you can see, it takes a hash table as its only parameter. The routine checks to see that the required parameters are there. If not, it fills in a problem list in the hash table. In a working environment, I’d also do an initial check that all the key/value pairs I need are valid, but I don’t want to put any unnecessary code into this article. Normally, you call this as you would a function. I can, though, execute this easily by itself if I want, and debugging is easy.

This is how to create the folder of object scripts for v1.1.10 of the Pubs database. They will be placed in the folder \<user>\Documents\GitHub\publications\1.1.5\source.

I’ve also included in the GitHub project a very similar task that uses SQL Compare to create a build script for the database($CreateBuildScriptIfNecessary), if one doesn’t exist, by comparing the database to an empty target, like this:

Checking for drift

I’ll be going into the details of this code in a later article, but I’ve also included a build task called $IsDatabaseIdenticalToSource that will verify that the database you wish to migrate hasn’t drifted and report back. It does this by using SQL Compare to compare the object-level scripts directory (if one already exists) with the schema of the database to be migrated.

Getting the version number of the database

Most of the information that gets passed in the hash table is straightforward, but not all of it. For most operations, you need to know the version of the database. If you first execute an info action with Flyway on the database, then you get it. Otherwise, you can’t. It is possible to see what migrations were attempted, from Flyway’s output, but these do not report the state of each migration, so it isn’t clear whether they indicate success.

In this article, I use a $GetCurrentVersion PowerShell build task that uses SQLCMD to read the version, if necessary, from the flyway_schema_history table in the target database. It assumes that the table is in dbo.flyway_schema_history (you can, with the Flyway config file, put it anywhere, but if you do, then you must have Flyway Enterprise or Flyway Teams, and execute the PowerShell script from a PowerShell callback). This task is also a nice illustration of a generic way of getting a SQL Result back to PowerShell.

Dealing with passwords

I also use a task called $FetchAnyRequiredPasswords, to get the password for the user for this database server. It encapsulates into a build task the code I’ve included in other articles (see for example Customizing Database Deployments using Flyway Callbacks and Placeholders) to keep passwords in a secure place within the user folder, and avoid having to supply it repeatedly on the command line.

Running PowerShell build tasks during automated Flyway migrations

Let’s now demo a script that uses these tasks. It is designed for just working on a single database.

This script takes a database that you specify and migrates it to the latest version. I’m using the migration Scripts folder in the GitHub project, as I’ve done in previous articles.

Before running the migration, it runs build tasks to get the current version ($GetCurrentVersion), and then to verify that the database hasn’t drifted ($IsDatabaseIdenticalToSource). It will report back via the $databaseDetails array whether it is OK to proceed. The whole purpose of checking for database drift is to check on what the database should be by comparing it with an existing source code directory. If you’re starting with an empty target database, and no build script or source folder, then there is obviously no way to check for drift, and you’ll get a warning, but the migration will proceed (nervously).

It then does the migration and checks to sees whether it is necessary to create a build script for the new version ($CreateBuildScriptIfNecessary), and an object-level source folder ($CreateScriptFoldersIfNecessary), and if so, does both, using schema comparison (SQL Compare). In this script we save the script folder only once for each version. This build artifact should be considered immutable so that it remains the ‘canonical representation’ of the database schema, for each database version.

Listing 1

Running database build tasks interactively

As well as doing running these tasks in a scripted process, we can use the PowerShell IDE to walk through some of them interactively. In the PowerShell ISE, we can work interactively with these objects even when you need extra parameters.

First let’s clean up all existing objects in the database:

Successfully dropped pre-schema database level objects (execution time 00:00.004s)
Successfully cleaned schema [dbo] (execution time 00:06.020s)
Successfully dropped post-schema database level objects (execution time 00:00.046s)

Now, we just migrate to a version …

Successfully applied 5 migrations to schema [dbo] (execution time 00:24.496s)
Executing SQL callback: afterMigrate - Add Version EP
WARNING: DB: Recording the database's version number - 1.1.5 (SQL State: S0001 - Error Code: 0)

…and we can see what happened:

We can check out our routine to get the version from the live database, using the $GetCurrentVersion task:

PS C:\WINDOWS\system32> $GetCurrentVersion.Invoke($DatabaseDetails);$DatabaseDetails.Version
1.1.5

As you can see from the script, we can easily find out the version, and, if necessary, create both the build script and the object-level source folder, after we do a migration.

We can stack up our script blocks and execute them in order, checking for problems after each execution.

If we hit a problem, the list of tasks stops executing. If we execute that successfully, we can see the result. We have a directory for the 1.1.5 version.

Inside this directory is a Scripts directory with our build script for that version…

And a source folder…

And in the Tables subfolder, we have our object scripts:

So, let’s take migrate the database to the next version:

Now if we rerun out stack of script blocks…

We now have a folder for each version:

Now we have all the various scripts in place.

We can compare script directories, build versions from scratch and have an object-level script for each version. We are likely to be able to satisfy almost any configuration-management person with our record-keeping.

With the object level scripts folder in place, there is no problem with interfacing with SQL Change Automation, as it and SQL Compare can use a source scripts folder as easily as an actual database. If you have a good reference version as a script, as would be the case if you created a database from scratch, then you can check a database that you need to upgrade to make sure that it hasn’t drifted by having un-versioned migrations applied directly.

Keeping a library of build and migration tasks

The script blocks that perform a range of tasks are designed to slot into a list like Lego blocks. To allow this, they take as a parameter a hash table. In this table are all the required inputs and some useful strings such as the name of the database that are used in file paths, with all ‘illegal’ characters replaced. To have outputs in the hash table is slightly odd, but useful. There are other ways of arranging this, but I like to keep things simple where possible.

All the scripts are kept in GitHub and you are welcome to submit pull requests.

Conclusions

Nowadays, it is good to work with a system like flyway that works across a whole range of databases. When using Oracle, PostgreSQL or SQL Server, it is a dish which is improved with a side-helping of other database tools, with a garnish of automation.

I’ll be continuing an ‘automation’ theme because it is important for me. Whereas the reader may be blessed with a virtuoso precision on the keyboard and the patience of a mediaeval monk, I’m not, so I automate development and maintenance chores. In subsequent articles, I’ll be demonstrating various ways of integrating other tools with Flyway. After getting the generic requirements out of the way with the features in Flyway Community, I’ll explain how Flyway Teams can take this to the next level, using PowerShell scripting in Callbacks.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more