Phil Factor

18 April 2023

• Flyway
• Team-based Development
• Integrating with Flyway

Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database

In an SSDT-Flyway ‘hybrid’ system, I’d recommend that the individual development of the database code continues without disruption, whereas the merging of all this work into the development database, and all subsequent deployments, is done via a Flyway migration script. This quickly delivers the many advantages of Flyway’s versioning system to team development.

Each time Flyway successfully produces a new version, we can automatically generate DACPACs along with the various build artifacts that make team-based development simpler, including a build script to reproduce the new version and object-level source to verify a version and to track and investigate changes. A DACPAC is used as part of a process that allows isolated branch development work to keep up to date with other committed changes.

In this article, I’ll provide a PowerShell task to auto-extract a ‘versioned DACPAC’ from each new Flyway version. We use this task in a Flyway callback script, so that it runs automatically on the successful completion of any Flyway migration run. I’ll even demonstrate how to retrospectively produce DACPACs for older versions of the database. Teams can use these DACPACs to recreate the new version locally for local development work or upgrade older versions.

In a subsequent article, I’ll demo how to extract a Flyway-compatible migration script from a DACPAC, for use in merge operations and subsequent deployments.

Provisioning databases using DACPACs

Although they are a neat way of packaging a database, DACPACs aren’t cross-RDBMS technology. They are like an insect caught in aspic or resin, complete but lifeless, a database at a moment in time. Unlike the insect, a SQL Server database caught in a DACPAC can be revived and turned back into a functioning database just as long as all the external databases, CLR libraries, server-references and other dependencies can also be revived. As such, DACPACs are handy. You can, of course, ‘script out’ the database and possibly also the data, in a compressed form, but a DACPAC can be written or read by any SQL Server tool such as SSMS.

I tend to create DACPACs as a matter of routine (or ‘extract’ them, in the SSDT terminology). By default, we store data separately, in compressed BCP files on the server’s filesystem. However, if this is not possible, you can include data in the DACPAC, though it is likely to get quite large. You can also store the version, though it is not attached to the database in SSDT but to the server.

A team member can do an isolated install of the database from a DACPAC to try things out, as well as extract either a build script or an object-level source. A DACPAC can be ‘published’ to update a database schema to match the schema contained in the DACPAC file. If the database does not already exist on the server, the publish operation creates it. Otherwise, it is updated. This is done via a generated Synchronization script called an ‘incremental update’ script. You can either create the script, inspect it and apply it using SQLCMD.exe, or if you are feeling lucky, you can leave it to SSDT (DacFx) to do it for you.

If we create a DACPAC of a Flyway database and use that DACPAC to publish to a target database, the published target database will have a Flyway Schema History table copied over as well. If we include data in the publishing operation, the target database will be at the version of the source of the DACPAC. If you don’t want this, it will need to be deleted after the publish operation is completed.

Creating a DACPAC from the current Flyway version

I have a PowerShell Script block task ($ExtractFromSQLServerIfNecessary) that I use for extracting a DACPAC. It uses SQLPackage.exe, which is part of the SSDT tool pack. It can be used ‘isolated’ or within my Flyway Teamwork framework. Within the framework, assuming use of Flyway Teams or Enterprise Edition, it will work in a PowerShell callback so can be set to produce a DACPAC for each version automatically. It only needs to create the DACPAC once per version, so it checks first. You can override this behavior.

Here is an example of calling it in isolated mode, so you can try it out without using the Framework. Because it isn’t sharing details with a whole chain of processes, it looks a bit messy, but you wouldn’t see this when using the Script-block with the framework, because all the Script-blocks share parameters that are there already.

Executed ExtractFromSQLServerIfNecessary
in ExtractFromSQLServerIfNecessary, Connecting to database 'MyDatabase' on server 'myServer'. 
 Extracting schema 
 Extracting schema from database 
 Resolving references in schema model 
 Validating schema model 
 Validating schema model for data package 
 Validating schema 
 Exporting data from database 
 Exporting data 
 Processing Export. 
 Processing Table '[dbo].[flyway_schema_history]'. 
 Processing Table '[dbo].[employee]'. 
 Processing Table '[dbo].[jobs]'. 
 Processing Table '[dbo].[stores]'. 
 Processing Table '[dbo].[discounts]'. 
 Processing Table '[dbo].[publishers]'. 
 Processing Table '[dbo].[pub_info]'. 
 Processing Table '[dbo].[roysched]'. 
 Processing Table '[dbo].[sales]'. 
 Processing Table '[dbo].[authors]'. 
 Processing Table '[dbo].[titleauthor]'. 
 Processing Table '[BookQuery].[flyway_schema_history]'. 
 Processing Table '[BookQuery].[PhraseBanks]'. 
 Processing Table '[dbo].[publications]'. 
 Processing Table '[dbo].[editions]'. 
 Processing Table '[dbo].[prices]'. 
 Processing Table '[dbo].[TagName]'. 
 Processing Table '[dbo].[TagTitle]'. 
 Processing Table '[dbo].[Publication_Types]'. 
 Successfully extracted database and saved it to file <MyDirectory>\Pubs1.1.5-DACPAC.DACPAC'. 
 Time elapsed 0:00:44.60 
 Written DACPAC for Pubs 1.1.5 to <MyDirectory>\Pubs1.1.5.DACPAC

Here is the actual Script-block. It is messier than you might imagine because I deal with feedback, verbose information and warnings. I also check the inputs fairly thoroughly. You’ll notice that it gives you full access to all the ‘extract’ functions, including producing build script files and all the possible types of object-level directories. That makes it a lot easier to commit your work to source control in whatever format it requires. Flyway Teams and Enterprise Editions can read these object directories very easily, making integration smoother.

The current source for this is in the resources directory on GitHub, within the file DatabaseBuildAndMigrateTasks.ps1. You’ll also need the Process-FlywayTasks function that is included there.

Auto-creating DACPACs for every migration with a Flyway callback

The advantage of running this task within the Teamwork framework is that we can now create a DACPAC for every version of a Flyway project. We then have a canteen for developers who need to quickly create a database at a particular version, even on an isolated database server.

We just put the code in an AfterMigrate callback and put the path to the callback in the list of locations paths in the project-level flyway.conf file. Here is a callback script that will do the trick::

Creating DACPACs for older database versions

What if you’d like all your old versions to have associated DACPACs? Well, if you don’t mind doing a rebuild, you can use this technique, which just does a single migration on every iteration, and follows that by writing the resulting DACPAC to the Versions folder.

This is pretty draconian. Do not do this to your main development database without lots of backups and checking with the rest of the team. If you’re on your isolated workstation with a copy of the development, then you can be more relaxed.

This routine will save a script directory, a full build script and a DACPAC after every version. That way, you can be prepared for almost any source control requirement, search, bug-hunting operation, or scripting requirement.

Conclusions

I’ve demonstrated how we can bring DACPAC technology to Flyway. With the code I’ve provided, you should be able to associate a DACPAC with every version. We can now provide DACPACs for anyone who needs them. However, that is only the first stage. What do you do if developers provide a DACPAC from their branch work, and you have to create a migration file from this DACPAC, or maybe even a Flyway UNDO file? Normally, we use SQL Compare for scripting, but there is a snag: it doesn’t read DACPACs directly without help, and the live development database is at a specific version. We’ll tackle this task as a separate exercise.