Phil Factor

25 February 2021

• Flyway
• Database Dependencies
• Callbacks and placeholders

Checking for Missing Module References in a SQL Server Database Using Flyway

In SQL Server, ‘invalid object name’ errors can happen with any object that isn’t schema-bound, such as a view, table-valued function, trigger or stored procedure. A view, for example, will stop working if you subsequently change any of the tables, views or table-valued functions that it references, such as by renaming or deleting a column.

If, during development, you always alter databases to keep them up to date, rather than rebuilding them, then ‘missing references’ can gradually creep in, as you accidentally remove or rename objects such as columns that are still being referenced by a module such as a view. This can easily happen if you are doing a refactoring exercise that involves renaming columns or tidying up columns that aren’t being used. Unfortunately, the problem won’t immediately become apparent. In most database systems the “invalid object” errors will happen only when that view that referenced them is next used, which could prove very embarrassing. Therefore, as well as testing for unexpected problems during a refactoring, you really need to test for this problem after you do any migration. It is, for example, one of the checks worth running for the migration script that merges changes from several branches back into main.

More generally, doing a regular, fresh build in Flyway, by running the ‘clean’ command and then re-running the migration from the initial version (baseline), instead just running the recent migrations, will flush out these problems straight away.

Testing a database for invalid objects after a migration

It is hard to exercise every single function or procedure by using it in a test run, because they have parameters, and you can’t reliably predict valid parameter values. Luckily, all you need to do is to refresh the module so that it checks its dependencies, using a system stored procedure called sp_RefreshSQLModule. Before this existed, it used to be necessary to use SET SHOWPLAN ON, and SET NOEXEC ON, and then execute every module (meaning procedure, function, or trigger) without parameters. It is now a lot more relaxed.

Here is the version I use, within a test harness to check that it works.

This will give the result …

You might think ‘Ooh! What a nice procedure that would make!’. We can, however, leave it as a batch because we only execute it once, and a batch doesn’t alter the database at all.

Creating a Flyway ‘afterMigrate’ callback for the invalid object check

We now turn this into an ‘afterMigrate‘ callback for Flyway. In the version of the script above, we merely listed out errors, like this:

For the callback file, we need instead to trigger an error, so Flyway can, if it needs to, pass on the bad news in full:

I’ve added the script to the BuildAndFill project on GitHub so you can try it out. It is called afterMigrate__CheckModuleDependencies.sql.

Running the Flyway Deployment

Now we’ll try it out in a Flyway deployment to the AdventureWorks database. To test out the ‘invalid object’ check, we’ll add a one-line migration file migration file that introduces a small bug, due to an injudicious renaming of the MiddleName column of the Person.Person table to Initials:

And call it, well call it whatever you like as long as it starts with ‘V1.1.4__‘. I’ve called it ‘V1.1.4__InjectABugByRenaming.sql‘. Here is the scripts folder:

So now we destroy the existing database in preparation for a complete build. In my example, the target database is called AdWorks, and you can use the script CreateAdworksDatabase.sql in the project to tear down and then recreate an empty copy of the database.

When we run the PowerShell deployment script, suitably called BuildAndFill.ps1, Flyway will execute the V1.1.3_AdventuureWorks.sql migration file to build AdWorks to V1.1.3, and then we will immediately ruin our new build by breaking some of the module references, in the V1.1.4 migration.

It then runs some post-migration tasks, including our ‘invalid objects’ check. There are also afterMigrate checks to import data from BCP files and to stamp a version number into the database, as an extended property. Flyway runs these files in alphabetic order. If you haven’t created the BCP data files for AdventureWorks just rename the afterMigrate_ prefix to the file afterMigrate__ReadEveryTableViaBCP.sql to something else so that it doesn’t get executed.

Now, run the deployment. Nothing gets rolled-back but by default we get an angry red screen:

SQL State  : S0001
Error Code : 50000
Message    : SQL Module Dependency errors 
1 The view [dbo].[vTopTenSalespeople] has a reference to an Invalid column name 'MiddleName'.
2 The view [Person].[vAdditionalContactInfo] has a reference to an Invalid column name 'MiddleName'.
3 The view [HumanResources].[vEmployee] has a reference to an Invalid column name 'MiddleName'.
4 The view [HumanResources].[vEmployeeDepartment] has a reference to an Invalid column name 'MiddleName'.
5 The view [HumanResources].[vEmployeeDepartmentHistory] has a reference to an Invalid column name 'MiddleName'.
6 The view [Sales].[vIndividualCustomer] has a reference to an Invalid column name 'MiddleName'.
7 The view [Sales].[vSalesPerson] has a reference to an Invalid column name 'MiddleName'.
8 The view [Sales].[vSalesPersonSalesByFiscalYears] has a reference to an Invalid column name 'MiddleName'.
9 The view [Sales].[vStoreWithContacts] has a reference to an Invalid column name 'MiddleName'.
10 The view [Purchasing].[vVendorWithContacts] has a reference to an Invalid column name 'MiddleName'.

To fix them from the demonstration system, all we would need to do is to do a tear-down with

If this happens to you in real life, you will need to add a new version, 1.1.5, that fixed these column references (in this case there is only one). Migrations are, strictly, immutable. As the check only happens at the end of the migration chain, you’d only get the error subsequently if you opted to end the migration chain at version v1.1.4.

Conclusions

The check for module dependencies is only one of several checks that one can make on a newly built database, using Flyway’s excellent callback system. There are plenty of coding style-checks and code smells that can be checked for more easily with the live database than mere code. However, the most important things to check for at this stage are the things that would otherwise break the tests rather than just the signs of technical debt or the things that would raise eyebrows amongst hard-core database developers.

I’ve used the error channel for this because, if you use JSON rather than the default text for output, you get the error data in a form that is easy to assimilate in an automated process. Also, any migration that produces a time-bomb bug like this just has to result in a screenful of angry-looking red characters!