• Flyway
• Database Dependencies
• Database Deployments

Dealing with Mutually Dependent Databases in Flyway

It is both common and legitimate for database projects to include several databases on the same server that have dependencies on each other. In fact, this is how MySQL deals with schemas: they are just MySQL databases. However, without a few extra techniques, these interdependencies can introduce complications into the database development process, when you wish to work on these databases or schemas independently.

Typically, the databases will, for example, contain objects such as views that make cross-database references to each other. The RDBMS checks these references when it executes the CREATE or ALTER statement, and this will cause an error if the referenced object doesn’t exist. When there are mutual, or circular, dependencies between databases, there is no natural order of creating the objects that doesn’t break the build. Whichever you build first, there is an error.

For simple Flyway database projects, I’ll show how you can get around this using a technique that should work for any RDBMS. First, we identify the dependencies that exist between two databases (A and B). In a beforeMigrate script we create ‘stub’ objects for any referenced objects in B that won’t exist when we try to build A, and vice versa. We then replace these stubs with the real objects, as each database is migrated to the version that supports the interface. You can incorporate this technique into a standard build process, using Flyway, without the need to make any changes to the original source scripts.

This scheme works well in cases where all databases live on a single SQL Server instance. It’s simplest when working on the database in ‘synch’ (migrating them to the same version). If you’re working on databases independently, you’ll just need to ensure you create ‘stubs’ for all foreign references.

For cross-server dependencies, you’ll need a different approach, such as described in Dealing with Cross-Server Database Dependencies in Flyway Projects. However, it relies on synonyms, which makes it SQL Server-specific. The overall approach is likely to be similar for other RDBMSs, but the details different.

How cross-database dependencies cause build failures

Cross-database references are sometimes an ‘evolutionary accident’ but often the result of a design choice where, instead of using schemas to partition a database into logical security spaces, components or namespaces, you use separate databases. It can also happen with Flyway where a single database has one or more schemas in a different Flyway project.

To illustrate the problems that cross-database dependencies can cause for a standard database development process, let’s create a simple database system comprising two databases with mutual dependencies. Because Flyway is designed to manage one or more schemas on a single database, we build it as two projects.

We have two very simple migration scripts; the first one builds Hyrcanus (V1.1.1__Cross_Database_Example.sql)

And here is the migration script for Antigonus:

We now attempt to run the two very simple migrations, using PowerShell.

Ouch! That won’t work! (We leave out some of the red verbiage)

flyway : ERROR: Migration of schema [dbo] to version "1.1.1 - Cross Database Example" failed! Changes successfully rolled 
back.
Message    : Invalid object name 'Antigonus.dbo.TheThirdTable'.
Location   : .\migrations\V1.1.1__Cross_Database_Example.sql 
(S:\work\Github\FlywayTeamwork\Cross_Database\Hyrcanus\.\migrations\V1.1.1__Cross_Database_Example.sql)
Line       : 16
Statement  : --and a view that references a second database
Create VIEW dbo.TheFirstView
AS
SELECT TheSecondColumn FROM Antigonus.dbo.TheThirdTable;
Caused by: Migration V1.1.1__Cross_Database_Example.sql failed
WARNING: Stopped the migration of Hyrcanus due to an error

Try it the other way around, building the second database before the first. It won’t work either. Something must ‘give’.

In a very simplified script like this, which merely demonstrates the problem. I’ve made the example as simple as possible so that it is easy for any reader to play about with the code to try out solutions.

Tackling the problem: creating and removing temporary ‘stub’ objects

Probably the easiest way of resolving this problem of building database that have mutual dependencies is to ensure that all the objects that are referenced by another database are present as temporary ‘stubs’. A ‘stub’ is an object that has the same name as the original, and gives the same result, even if no rows are produced. The stub only needs to contain the referenced columns, rather than all of them.

However, we need to be careful. We can’t alter a particular version from what is produced by the migration chain that produced it. This would be against the Flyway principle of ensuring the integrity of a version, even though you can work around it if you feel lucky. Also, Flyway versioned migration should really work on a single database at a time (although before… and after… callbacks can work on multiple databases).

The solution I propose, for Flyway, uses a beforeMigrate callback to create any referenced objects in each of the databases as temporary stubs, if the actual objects don’t already exist. These stubs mustn’t stay in the databases, so we need to delete them afterwards. To me, this sounded like a high-risk strategy, so in the beforeMigrate callback I mark all the stubs with a comment (a “DeleteMe” extended property in SQL Server) that marks it out for deletion at the end of the migration. We then have an afterMigrate callback that identifies any remaining temporary stubs, in any of the databases, and removes them.

Finally, any versioned migrations scripts that create objects that are the target of cross-database references must account for the fact that temporary stub objects of the same name may already exist, and drop them if they do, before creating the real objects.

Running Flyway migrations on mutually dependent databases

Here’s what the whole process looks like for our demo databases, when building both database from “empty”, with Hyrcanus first:

When we execute flyway migrate, the beforeMigrate callback creates temp stubs for the referenced objects in each database: TheFirstView and TheSecondTable on Hyrcanus and TheThirdTable on Antigonus. The V1.1.1 migration then runs on Hyrcanus, dropping stubs if they exist then creating all the objects. Finally, the afterMigrate cleans up any remaining stubs (in this case TheThirdTable on Antigonus). Now we can immediately tun the Antigonus migration to take it to the same version. The same before and after callbacks will run again but this time effectively won’t have anything to do.

This technique means that, even if we don’t have the full source of each database, we can build the database we are working on and use a set of stubs or mocks to represent the objects we access in each of the other databases.

I’ve provided a sample Cross-database object on GitHub, with the required migrations scripts and callback scripts. In the Migrations folder for each database, you’ll need the migration script for that database, and then copies of the before and after callbacks. When we run the migrations on each database (I started with Hyrcanus in this example). It all works, leaving no trace of any stubs:

FlywayTeamwork framework  loaded. V1.2.215
Successfully dropped pre-schema database level objects (execution time 00:00.011s)
Successfully cleaned schema [dbo] (execution time 00:00.272s)
Successfully cleaned schema [dbo] (execution time 00:00.169s)
Successfully dropped post-schema database level objects (execution time 00:00.015s)
Successfully validated 1 migration (execution time 00:00.099s)
Creating Schema History table [Hyrcanus].[dbo].[flyway_schema_history] ...
Executing SQL callback: beforeMigrate - AddAnyNecessaryStubs
WARNING: DB: Changed database context to 'Antigonus'. (SQL State: S0001 - Error Code: 5701)
WARNING: DB: Changed database context to 'Hyrcanus'. (SQL State: S0001 - Error Code: 5701)
WARNING: DB: creating stub for dbo.TheFirstView on Hyrcanus (SQL State: S0001 - Error Code: 0)
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version "1.1.1 - Cross-DatabaseExample"
Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:00.167s)
Executing SQL callback: afterMigrate - TidyUpTemporaryObjects
WARNING: DB: Changed database context to 'Antigonus'. (SQL State: S0001 - Error Code: 5701)
+----------------------------------------------------+
| Actions executed to Temporary Objects on Antigonus |
+----------------------------------------------------+
| DROP TABLE dbo.TheThirdTable;                      |
+----------------------------------------------------+
WARNING: DB: Changed database context to 'Hyrcanus'. (SQL State: S0001 - Error Code: 5701)
+---------------------------------------------------+
| Actions executed to Temporary Objects on Hyrcanus |
+---------------------------------------------------+
|                                                   |
+---------------------------------------------------+ 
Successfully dropped pre-schema database level objects (execution time 00:00.016s)
Successfully cleaned schema [dbo] (execution time 00:00.218s)
Successfully cleaned schema [dbo] (execution time 00:00.216s)
Successfully dropped post-schema database level objects (execution time 00:00.026s)
Successfully validated 1 migration (execution time 00:00.099s)
Creating Schema History table [Antigonus].[dbo].[flyway_schema_history] ...
Executing SQL callback: beforeMigrate - AddAnyNecessaryStubs
WARNING: DB: Changed database context to 'Antigonus'. (SQL State: S0001 - Error Code: 5701)
WARNING: DB: Changed database context to 'Hyrcanus'. (SQL State: S0001 - Error Code: 5701)
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version "1.1.1 - Cross Database Example"
Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:00.194s)
Executing SQL callback: afterMigrate - TidyUpTemporaryObjects
WARNING: DB: Changed database context to 'Antigonus'. (SQL State: S0001 - Error Code: 5701)
+----------------------------------------------------+
| Actions executed to Temporary Objects on Antigonus |
+----------------------------------------------------+
|                                                    |
+----------------------------------------------------+
WARNING: DB: Changed database context to 'Hyrcanus'. (SQL State: S0001 - Error Code: 5701)
+---------------------------------------------------+
| Actions executed to Temporary Objects on Hyrcanus |
+---------------------------------------------------+
|                                                   |
+---------------------------------------------------+

If we are using SQL Server, we can now inspect our two databases to see the cross references that are in place.

You’ll probably notice that the ‘reference’ column in the query above gives you the list of objects that need to be created as stubs for the migrations to be successful.

The beforeMigrate callback

The beforeMigrate callback script must keep in step with the database cross-references you make. It creates the stubs whenever necessary to allow referencing objects to be created. Two things will seem unusual. First, we add an extended property to indicate that these are stubs, so they can be cleaned out at the end of the migration. The other unusual code is the guard clause that makes sure that the stubs are created only when they are required: when they don’t exist.

The afterMigrate callback

The afterMigrate callback script simply looks for all comments that indicate that the attached object is a stub that needs to be deleted. It should be duplicated for every database in your collection that has stubs. We just have two databases. I have left in some extra voluble code to report what has been deleted in case you wish to experiment with it.

If you create stubs or mocks that are inter-dependent, which I reckon is a bad idea, then you need rather more complicated code to delete them in reverse dependency order.

The migration scripts

The final complication is the migration files must be able to remove any temporary stubs before the actual referenced object is created, so before the referenced object is created, the scripts check to see if they already exist and if so then delete them.

Here’s the migration script for Hyrcanus:

And here is the migration script for Antigonus:

Other Solutions

There are a couple of other ways to tackle this problem…

Install the other databases on the development Server

One alternative way around the problem, explained by Kathi Kellenberger, is to ensure that each of the development servers has full copies of all the databases, merely updating them as required. This alternative technique might be more appropriate for one-off changes of small-scale database where scripting a full build process would be overkill, or where the number and complexity of the cross-references makes the scripted approach difficult to devise and maintain.

There are two potential issues. The first is that if you are using production data, you’ll need to check that there is no personal or sensitive data there, otherwise it is likely to be illegal without the consent of the owners of the data. The other problem is that you can never do a clean build of all the databases by re-running the migrations on a new server. You will always need the other databases at sufficiently high version that all the referenced objects are in place with the right column names.

Use Synonyms

The other solution, which can only be used for SQL Server and PostgreSQL, is to use Synonyms. I’ve covered this in a separate article, which uses synonyms to deal with cross-server dependencies.

Conclusions

It is perfectly legitimate for database projects to include several databases, either on the same server instance, or on other server instances. This should present no more difficulties for development teams who wish to do a complete build or migration to one of the servers.

I’ve demonstrated a technique that simply places stubs in otherwise-empty databases, to let you develop individual databases from the code in source control, even when they are destined to be components in a larger system. You don’t need to make any changes to the migration files to accommodate potential cross-database references, but you do need to add checks in the referenced database to delete an existing stub if it exists, as I’ve demonstrated.

 

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more