• Flyway
• Team-based Development
• Integrating with Flyway

Flyway and SSDT: Merging Work from a Branch

It is reasonably easy to integrate a SQL Server SSDT project into Flyway. The only significant change is that instead of using a ‘state-based’ approach to deploying changes, comparing a source and target database to generate a synchronization script, we now use Flyway migration scripts. Flyway changes a database from one known version to the next, by running a series of versioned migrations.

However, in this Flyway-SSDT hybrid system we still need to be able to accommodate any team member who, having worked in an isolated branch, wants to continue to provide a DACPAC of the new branch version, rather than a Flyway migration script.

SSDT development workflow

In a typical SSDT development workflow, a developer will begin by updating their local database from a DACPAC of the current development version. One of the advantages of working with DACPACs is that you can provide developers with access to the latest development database, without needing to hand out logins, since you don’t require a login to run comparisons to a DACPAC.

Imagine that a developer now starts work on a new search function to help an application do a ‘Google-style’ search. The developer can use all manner of tools such as an ER diagrammer, and table builder to do the work faster, or even a build script or a migration.

Whichever way development proceeds, once the search function has been developed and tested, the developer delivers the resultant database as a DACPAC. In this file (actually a .zip file of several directories) is a ‘model’ that is the bare source code and an XML model of the objects and relationships.

Imagine that it is now your role to integrate this into the development branch of the project, and eventually through to release. If feel lucky (aka reckless) you can get one of the SSDT DAC tools to publish the changes directly to the development database. However, this is unlikely to end well. Instead, you have to generate a script that you can inspect and test before applying…

Getting an incremental update script from a DACPAC

Whatever method you use, the process of merging changes is, of necessity, a process that requires manual intervention, so it has to be done via a synchronization or migration file (or ‘incremental update’ in DAC terms) at some point. This script is usually called an ‘artefact’ and must be easily reviewed by the team. A DACPAC isn’t a good choice as an ‘artefact’ for merging because it is difficult to inspect quickly and easily.

It is relatively easy to create an ‘incremental update’ script using your existing SSDT tools, such as SQLPackage.exe. Simply use the ‘script‘ action, defining a source and target DACPAC for the comparison. The command-line of SqlPackage will look something like this:

This will create a Transact-SQL incremental update script for SQLCMD.EXE that would update the schema of the ‘target’ database to match the schema of the DACPAC, the ‘source’ database.

If the source DACPAC is the latest development version then you can use this command to update your SSDT isolated database with the current changes. Conversely, if the source is the branch DACPAC and the target the latest development database, then the script will make the schema of the development database match that of the branch database.

However, we still potentially have a lot of work to do before we have a script that will seamlessly merge in the developer’s new search function.

Potential difficulties with merges

The first difficulty with the incremental update script, produced by comparing the branch DACPAC as source to the latest development database as target, would be the danger of overwriting someone else’s work that has been merged since our developer branched a copy of the database. The generated script will not only create any new branch objects but will also remove any objects others added to the development branch, in the meantime, and undo any alterations.

Instead, we need to create a script that only makes the incremental changes for the database objects related to these branch changes but doesn’t overwrite or delete other changes. Not only must we ensure that existing data in the database isn’t changed or lost, but we must also avoid test data accidentally getting into a migration file because at some point down the release pipeline, you will need to apply the changes to production. If it is an existing system, you won’t be popular if you overwrite the business’s data.

Let’s see how we incorporate Flyway can help overcome some of these issues.

SSDT-Flyway hybrid workflow

When we introduce Flyway into an SSDT development, the only real difference is that the ‘artefact’ for delivery changes becomes the Flyway versioned migration script. We apply changes to the development database, and any subsequent databases in our release pipeline, by executing a run of migration files in the correct version order. This means that a migration script must be generated from the DACPAC that we received by comparing it to the database and used as a basis for a versioned migration file.

In the Flyway-based system I like to use when developing with SQL Server, every version of the database has an associated DACPAC, which is version-stamped and stored with the other artefacts for the version, such as the object level directory that is usually required for Source Control.

My article, Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database, demonstrates the $ExtractFromSQLServerIfNecessary script block that I’ve provided that will create the version-stamped DACPAC for each new Flyway version. If these are in place, you should find it easy to use the command shown earlier to do SSDT ‘incremental update’ scripts using your existing SSDT tools such as SQLPackage.exe, for any version of the database, not just the current one. However, the ‘artefact’ is now a Flyway migration script, so we still have some work to do to convert the SSDT incremental update into a Flyway-compatible script.

Getting a first-cut Flyway migration script

I’ll provide a PowerShell function that does as much of the groundwork as possible in the process of converting the SSDT incremental update, from a DACPAC, into a Flyway migration script. I’ve previously described all of the potential difficulties associated with using a DACPAC as a source from a Flyway migration script, and this function deals with as many of them as possible, but it can’t do them all.

For a start, Flyway Migrations shouldn’t contain transactions because Flyway likes to cleanly rollback the entire script of a failed migration, so the whole script is, where possible, executed within a transaction. You can still run incremental updates with transactions in them, but Flyway may have to run them without the protective transaction that it adds, which will leave a mess to clear up if there is an error anywhere in the script. There’s no easy way to automate this so stripping transaction code out of the converted file is a manual step.

Secondly, the ‘incremental update’ script is designed to run in SQLCMD.exe, with parameters, so in order to get a Flyway-compatible script we’ll need to process the SSDT-generated file to strip out all the SQLCMD statements and any SQLCMD parameter definitions.

A migration script mustn’t touch the Flyway schema history table, and it mustn’t create schemas. They’re both Flyway’s stamping ground, not part of a script. Because the metadata of the Flyway schema history table is never altered, it will not likely appear in incremental update scripts.

Finally, the output files need conversion, and we need to sort out how we react to various errors.

So, here is the function. By default, it will take the DACPAC for the current version of the database and will compare it with the DACPAC of the previous version, to give you a ‘forward’ migration script. It should, logically, work the other way, to give you the UNDO migration as well. Sadly, at least in the version of SqlPackage I used, it doesn’t always do this successfully. To do this you’ll need access to SQL Compare (which is included in the Enterprise edition of Flyway).

I’ve provided a script block called $CreatePossibleMigrationScriptFromDACPAC that calls this function to generate the migration file. It’s designed to be used within a callback that automatically creates incremental update scripts for each version.

From my own tests, I’d advise that you check the generated file very carefully, as my version of SQL Package sometimes doesn’t script out the actions to make the changes, presumably where it believes that data would be lost or changed.

These scripts will only be used to change the metadata but, like Flyway migration files, shouldn’t be used to apply changes to development test data. These data changes should, instead, be applied to the migration manually when they are required, but with the warning that, with Flyway, unlike SSDT, the same migration files that are used for a merge are used right through to production so only ‘static’ data such as days of the week or names of countries are legitimate data in a build script. This prevents any embarrassing leakage of test data into production systems.

Creating a merge migration script from a DACPAC

Let’s now use this function to try generating a script that we could use to merge a branch back into its parent, usually development. I’ve provided, as an example, a sample project with a Search branch that provides search functionality for finding notes on customers.

At the point of creating the Search branch, the parent branch (the development branch) was at version 1.1.12. This version has an associated DACPAC, as well as other artifacts such as a build script and the object-level source.

I created the isolated, branch database at v1.1.12 using the build script. I then developed the search functionality on top of that (I originally developed it for an article that demonstrated how to do testing in Flyway). Having produced the new version (V1.1.12.1), I now need to merge it back into the parent branch.

We use the Export-ChangeScriptFromDACPACS function to create a first-cut merge migration, using a DACPAC of the branch database (V1.1.12.1) as the source and the DACPAC for V1.1.12 as the target. I’m using my Flyway Teamwork framework to run this example:

So, now we have a first-cut merge migration file. We check it to make sure that it hasn’t got transactions in it and that the SQLCMD stuff is taken out and that there are no batches in there that just add data. A batch leaves no trace in the metadata, but SQL Server allows them (other RDBMSs use temporary procedures which again leave no trace).

The development database is now at version 1.1.14 because we’ve added in some accounting functionality in the meantime. For the merge, we wish to add the search functionality in to take it to version 1.1.15.

First though, we check it with the parent at the point of merge by comparing the current parent DACPAC for 1.1.14 (source) with the branch DACPAC for 1.1.12.1 (target). This way, we can see easily see any objects created or altered in the parent in the meantime and make sure there are no name-clashes and no changes to objects we depend on.

Now we add the migration to the existing migration chain in the develop branch, as V1.1.15 and we are now merged…

Migrating schema [dbo] to version "1.1.15 - SearchFunctionality" 
Executing script callback: afterVersioned - ExtractAsADacpacIfNecessary
Executing powershell S:\work\github\FlywayTeamwork\Pubs\Branches\develop\.\migrations\Callbacks\afterVersioned__ExtractAsADacpacIfNecess
ary.ps1 
FlywayTeamwork framework  loaded. V1.2.620
Executed GetCurrentVersion
Executed ExtractFromSQLServerIfNecessary
in ExtractFromSQLServerIfNecessary, The DACPAC has already been created for Pubs 1.1.15
in GetCurrentVersion, current version is 1.1.15, previous 1.1.14.
Successfully applied 1 migration to schema [dbo], now at version v1.1.15 (execution time 04:37.291s)
Executing SQL callback: afterMigrate - Add Version EP
WARNING: DB: Recording the database's version number - 1.1.15 (SQL State: S0001 - Error Code: 0)
Executing SQL callback: afterMigrate - ApplyTableDescriptions
WARNING: DB: Adding the descriptions for all tables and columns (SQL State: S0001 - Error Code: 0)

In this case, we hit only minor problems, which were due to my own foolishness in using a batch within the migration file to fill the search (inversion) tables. It worked surprisingly well but that was because I was careful not to alter existing tables that might have been changed in different ways in the interim.

Applying a DACPAC contribution using SQL Compare

Of course, if you have access to SQL Compare, you can generate forward and backward migrations as and when required. SQL Compare has several advantages. The most obvious one is that it is a UI tool that allows you to see the changes between source and target. Unlike SSDT, you can include or exclude named objects as well as object classes, so it is much easier to fine-tune the synchronization script for a merge.

There are options that let you create a migration script. SQL Compare reads object-level script directories, which you need anyway for source control. The DACUnpack.exe command will take the DACPAC you specify and write out an object-level script when the contents are unpacked.

With SQL Compare, then, the best approach is to extract an object-level directory as the ‘source’, from the DACPAC that represents the branch work, and another as ‘target’ from the DACPAC at the point of branch. I like to play safe and use the DACPAC that created the isolated database that represented the branch at its start. By comparing source with target, you will get the first-cut migration script. From that point on you can check the old target with the parent at the point of merge to check that there are no conflicts.

Conclusion

For an existing SSDT development, Flyway provides a simple means of creating a new version of the development database by running one or more scripts that make the changes. With this in place, there is less confusion about different versions of the work, and it is easier to keep all databases updated.

Some developers may prefer to make changes via a migration file, but I’ve come across many database developers who don’t. Whichever way the work is done, the delivery ‘artefact’ is likely to be a DACPAC. Curiously, it is not much harder to deal with than a migration file when it comes to merging isolated work into the development database. After all, it needs the same check for potential conflicts, and if the developer has kept an eye on what the rest of the team is doing, these are unlikely to happen.

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