Product articles Redgate Flyway Database Testing and Quality
Running SQL Code Analysis during Flyway…

Running SQL Code Analysis during Flyway Migrations

A set of PowerShell automation script tasks for running database build and migrations tasks. This article describes the SQL code analysis task, which will check the syntax of the SQL code in your databases and your migration scripts for 'code smells'.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Flyway is the database build component in a development and deployment system, but it is only one component among many. We need a way to support the other tools that are needed as part of an automated Flyway migration or build. I’ll show how to integrate Flyway with SQL (or Oracle) code analysis, two of the tools in Flyway Enterprise, to check a Flyway-managed database for code issues. The demo in this article uses the Code Analysis for SQL Server command line component, but Oracle Code Analysis, part of Flyway Enterprise, works in the same way.

When this check is run automatically, where building a new version of the database, it will help you detect and correct issues early in the development cycle and allow the Governance and Operations team visibility into production readiness of the code.

Creating PowerShell build tasks with Script blocks

I’ll use a slightly unconventional approach to scripting. With PowerShell, I tend to like to reuse scripting components and assemble them like Lego. I use some tools that are part of Flyway Enterprise, and some that aren’t, but by sticking to certain conventions, I can then click them all together quickly.

The method I use takes advantage of a feature of .NET and PowerShell that often trips people up. This is the fact that hash tables and other large objects are generally passed by reference. You don’t copy the whole object. If you actually want to copy a hash table, you need to use the .Clone() method. Unless you want to get confused, you generally need to pretend to yourself that a hash table is read-only. However, in our case, we will use this feature of “pass-by-reference objects” to pass information down a chain of portable, anonymous functions that are just script blocks. This requires that you have a standard for the keys that you use as parameters and return values. The reason I use this method is that I can use these same PowerShell Components in Flyway Teams using a file, representing a hash table, as a parameter, and as a way to return values to the system after the script is executed. Basically, one can avoid using placeholders as parameters in Flyway callback scripts by using a JSON or XML file to pass information between build tasks.

Build tasks for Flyway migrations

These build tasks are usually done before or after a migration. In my previous article, Creating Database Build Artifacts when Running Flyway Migrations, I demonstrated build tasks, implemented as PowerShell script blocks, to generate a build script, and an object level source folder.

They generally need to know where the database is, the type of database, how to get there, and what credentials are needed. They usually need also to know the version of the database. You need to store all the problems (job-stoppers) and warnings that come up in the chain.

In this article, we’ll add in a simple build task that runs a code analysis check on a database, or the migration scripts for it We can use it to report any issues with the code within the database, each time we run Flyway to deliver a new version.

How the build task components work

The following diagram shows how this code analysis task, and various other build tasks, can be used, and what they would be able to do, in the context of a Flyway migration.

The ‘Scripted Flyway migration process’ is a PowerShell script that, when executed, uses Flyway to migrate the database to a specified version. The script then executes script blocks for each of the required build tasks, such as to generate the build script for the new version, the Source directory with its subdirectories, and a Reports folder containing a code analysis report (as indicated by the green arrow ‘Build scripts and reports’). Once the Source directory for a version is created, there is another build task that can use it to check that an existing copy of the database, purportedly at that version, hasn’t subsequently drifted. It will also provide the input for an SQL Change Automation-based script.

running build tasks during flyway migrations

These tasks must be run in the right order and any problems must halt the process. Often, there are a few other utility tasks that must be run before the required task can take place. For example, before it can run a migration task, Flyway must check user credentials and then access the database, using the supplied details and credentials. There is also a task that checks the current version of the database, either before or after a migration, and this check is often required before proceeding with any other tasks such a generating a build script or checking the database for any issues with the code.

Here’s the current list of available tasks for running deployments with Flyway and other Enterprise edition tools, though I’ll be adding more over time. In this article we’ll be running the code analysis checks, either on the database ($CheckCodeInDatabase), or the set of migration scripts ($CheckCodeInMigrationFiles), but to do that we’ll need a few of the preliminary tasks too.

  1. $FetchAnyRequiredPasswords This checks the hash table to see if there is a username without a password. If so, the task asks for it in a query window and stores it, encrypted, in the user area it. If the user already has the password stored for this username and database, it uses it.
  2. $GetCurrentVersion This contacts the database and determines its current version by interrogating the flyway_schema_history data table in the database. It merely finds the highest version number recorded as being successfully used. If it is an empty database, or there is just no Flyway data, then it returns a version of 0.0.0.
  3. $FetchOrSaveDetailsOfParameterSet: This allows you to save and load the shared parameters for all these script blocks, under a name you give it. You’d choose a different name for each database within a project but make them unique across projects If the parameters include the name, but the vital information is missing, it fills it in from the last remembered version. If it has the name and the vital information, then it assumes you want to save it. If there is no name, then it ignores the hash table. It does not save problems and warnings.
  4. $CheckCodeInDatabase This runs SQL code analysis on the scripted objects within the database such as procedure, functions and views and saves the report and reports back any issues in the $DatabaseDetails hash table. It saves the reports in the designated project directory, in a Reports folder, as a subfolder for the supplied version (for example, in Pubs\1.1.5\Reports), so it needs $GetCurrentVersion to have been run beforehand in the chain of tasks.
  5. $CheckCodeInMigrationFiles This, where necessary, runs SQL code analysis on all the migration files in the folder and saves the report and again it saves the reports in the designated project directory, each one in a Reports folder, as a subfolder for the supplied version.
  6. $IsDatabaseIdenticalToSource: This uses SQL Compare to check that a version of a database is correct and hasn’t been changed. To do this, the $CreateScriptFoldersIfNecessary task must have been run first. It compares the database to the associated source folder, for that version, and returns, in the hash table, the comparison equal to true if it was the same, or false if there has been drift, with a list of objects that have changed. If the comparison returns $null, then it means there has been an error. To access the right source folder for this database version, it needs $GetCurrentVersion to have been run beforehand in the chain of tasks
  7. $CreateScriptFoldersIfNecessary: this task checks to see if a Source folder already exists for this version of the database and, if not, it will create one and fill it with subdirectories for each type of object. A tables folder will, for example, have a file for every table each containing a build script to create that object. When this exists, it allows SQL Compare to do comparisons and check that a version has not drifted. It saves the Source folder as a subfolder for the supplied version, so it needs $GetCurrentVersion to have been run beforehand in the chain of tasks.
  8. $CreateBuildScriptIfNecessary: produces a build script from the database, using SQL Compare. It saves the build script in the Scripts folder, as a subfolder for the supplied version, so it needs $GetCurrentVersion to have been run beforehand in the chain of tasks.
  9. $ExecuteTableSmellReport: This script block executes SQL that produces a report in XML or JSON from the database that alerts you to tables that may have issues

All the build tasks listed in 4-9 will run the task only once per version. They start by checking to see if the output, at the required version, has been done already. If so, then it is safe to assume that it doesn’t need to be redone as in normal circumstances old migration scripts can’t be changed, so a database at a particular version can’t change.

If the Flyway migration sequence is altered, or any of the migration files are cleared, then all the existing files in the Scripts directory must be cleared too. If a file of any type, such as a generated build script, must be redone, then the old one must be deleted beforehand, because the routines check for the existence of the file first and will only script out a version if the script no longer exists in that directory. If any file in a Scripts or Source folder must be refreshed, then the entire directory must be deleted. This will need to be part of a flyway 'clean' action if you are scripting at the same time.

The code analysis build task

This task is implemented using the Code Analysis for SQL Server command line component (a.k.a. SQL Code Guard) in Flyway Enterprise. It is very easy to run SQL code guard. You tell it the task, either to check the source database or the scripts for code smells, you give it the details such as the database or the location of the scripts folder and let it generate an XML file of the results that you can then prettify to taste.

Running SQL Prompt’s Code Analysis Rules

You can also define the SQL code analysis rules that your team considers important using SQL Prompt and them run them automatically from a PowerShell script, using SQL code Guard, which I demonstrate in SQL Code Analysis from a PowerShell Deployment Script. This article also explains how to obtain and install code guard.

Here is the script block that I use for the $CheckCodeInDatabase task. It looks rather more complicated than it sounds, because it must make various checks and cooperates with other Lego-style tasks. It also reports back, in the $DatabaseDetails hash table, any issues it finds (note that this is applicable to SQL Server only, at the moment).

Running a quick code analysis check on the database

Probably the easiest way of using this code is to paste it into a script and then add something like this.

This should give you a list of issues in the code (this is truncated output):

Object                           code   line text                                             
------                           ----   ---- ----                                             
dbo.byroyalty                    PE009  4    No SET NOCOUNT ON before DML                     
dbo.PublishersByPublicationType  DEP021 5    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 6    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 7    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 8    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 9    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 10   String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 11   String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  ST010  13   Use alias for all table sources                  
dbo.PublishersByPublicationType  ST010  14   Use alias for all table sources                  
dbo.PublishersByPublicationType  ST010  16   Use alias for all table sources                  
dbo.PublishersByPublicationType  PE002  16   Schema name for table or view is not specified   
dbo.PublishersByPublicationType  ST010  17   Use alias for all table sources                  
dbo.reptq1                       PE009  4    No SET NOCOUNT ON before DML                     
dbo.reptq1                       MI003  6    Unqualified column name                          
dbo.reptq1                       ST010  7    Use alias for all table sources                  
dbo.reptq1                       ST010  8    Use alias for all table sources                  
dbo.reptq1                       ST010  10   Use alias for all table sources                  
dbo.reptq1                       PE002  10   Schema name for table or view is not specified   
..<etc…>

Chaining build tasks

Our simple example so far isn’t fit for real use for several reasons. Firstly, you really don’t want passwords in PowerShell scripts. Also, you need to know the version of the database so that it gets saved in the correct directory. Anyway, it is probably going to be easier for anything but the simplest jobs to use separate tasks in a chain for jobs each of which performs a single action such as finding out the current version. After all, you don’t want to execute any other tasks if one of the tasks fail.

You can get around this by chaining tasks. As described earlier, there is a handy task in this project that will save and retrieve your password from a secure file in your user area, and one that will find out what version the database is at, by reading the Flyway schema history table. To access these script block tasks, you need to first execute the file they are in. This PowerShell file, called file, called DatabaseBuildAndMigrateTasks.ps1, provides a cmdlet and a whole lot of script blocks.

Once you have this, you can pull in this file at the start of each job. This can be done in several ways, but I generally just save the calling script (JustCheckCodeInDatabase.ps1) and the file containing the script block tasks in the same directory as my Flyway scripts. This should work whatever way you call the script, maybe in the IDE, as a command-line script, or from a deployment or release tool. If you just paste in this code and run it, you’ll have to make the folder hosting your script blocks tasks file the working directory first.

Here’s the code for the JustCheckCodeInDatabase.ps1 job, which after calling the preliminary tasks to get the password and check the database version, just runs the $CheckCodeInDatabase task. You can get the code from my GitHub:

Giving …

Object              code  line text                                             
------              ----  ---- ----                                             
dbo.byroyalty       PE009 4    No SET NOCOUNT ON before DML                     
dbo.byroyalty       ST003 2    Procedure body not enclosed with BEGIN...END     
dbo.byroyalty       PE002 4    Schema name for table or view is not specified   
dbo.employee_insupd EI020 27   ROLLBACK TRANSACTION without BEGIN TRANSACTION   
dbo.employee_insupd PE009 8    No SET NOCOUNT ON before DML                     
dbo.employee_insupd MI003 8    Unqualified column name                          
dbo.employee_insupd MI003 8    Unqualified column name                          
dbo.employee_insupd ST001 10   Old-style join is used (...from table1,table2...)
dbo.employee_insupd PE002 10   Schema name for table or view is not specified   
dbo.employee_insupd PE002 10   Schema name for table or view is not specified   
dbo.reptq1          PE009 4    No SET NOCOUNT ON before DML                     
dbo.reptq1          ST003 2    Procedure body not enclosed with BEGIN...END     
dbo.reptq1          PE002 6    Schema name for table or view is not specified   
dbo.reptq2          PE009 4    No SET NOCOUNT ON before DML                     
dbo.reptq2          ST003 2    Procedure body not enclosed with BEGIN...END     
dbo.reptq2          PE002 7    Schema name for table or view is not specified   
dbo.reptq3          PE009 4    No SET NOCOUNT ON before DML                     
dbo.reptq3          ST003 2    Procedure body not enclosed with BEGIN...END     
dbo.reptq3          PE002 7    Schema name for table or view is not specified   
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       MI003 4    Unqualified column name                          
dbo.titleview       ST001 5    Old-style join is used (...from table1,table2...)
dbo.titleview       ST010 5    Use alias for all table sources                  
dbo.titleview       PE002 5    Schema name for table or view is not specified   
dbo.titleview       ST010 5    Use alias for all table sources                  
dbo.titleview       PE002 5    Schema name for table or view is not specified   
dbo.titleview       ST010 5    Use alias for all table sources                  
dbo.titleview       PE002 5    Schema name for table or view is not specified

Running SQL Code Analysis on a Flyway-managed database

So, let’s run some code analysis checks from a database that is being managed by Flyway. This time we’ll check both the database, with $CheckCodeInDatabase, and the migration files, with $CheckCodeInMigrationFiles. If you just check the database, you only check the code inside the modules such as views, procedures and functions. You miss out on checking the syntax in the migration files.

After the checks are done both on the database and all the current migration files, the script just calls Flyway with the Info task, but you can see how it could easily be your chosen set of Flyway tasks. You can choose to set it to run before a migration, or after it is done. As with the earlier version, it also uses the $FetchAnyRequiredPasswords script block task to handle passwords securely, and the $GetCurrentVersion task to check the version of the database before running the static code checks. Only if it gets a valid version can it then run the checks. If the database is empty, or hasn’t got a flyway version number, then $GetCurrentVersion assumes a version of 0.0.0. The process needs a version number to save a report of the code issues in the right place. In a subsequent article I’ll be showing how to do drift checks, and other post-migration tricks.

Saving the database details

This time, we will also use the $FetchOrSaveDetailsOfParameterSet task to save the database details and other information to a hashtable, which we’ll store on disk in the user area. All you need to do to get this to work is run the code once, providing all the database details in long form and giving the parameter set a name, like this:

Happily, you don’t have to call it ‘TheNameToGiveThisDatabaseAndProject‘: just something memorable that is unique within the project. Normally, you’d probably use the name of the database you’re working on, but you can call it after your pet cat or one of the Irish saints, if you prefer. If you provide a database as well as a name and project, the $FetchOrSaveDetailsOfParameterSet task will save the details to disk. Of course, if you don’t want your full flyway data saved every time you run the code, don’t fill in the name.

Once they are saved, you can simply retrieve those details whenever you want to use this database on this development project, rather than entering them all over again, like this…

If you use this short form, providing a name, and project, but no server or database, then the task looks on disk to find if there is a previously saved hashtable under that name. If so, it loads it. If something goes wrong, you can access the data on env:USERPROFILE\Documents\Deploy\<name of project>, and either alter it by hand or delete it. Once you have things running for your project, you won’t have to leave or change details of servers in your script. If you wish to change what is stored, you run it long-form with the name again.

Running the build tasks

As in the previous example, all the build tasks are in a separate PowerShell script file, called DatabaseBuildAndMigrateTasks.ps1, in the same directory as the script below. The Flyway migrate Scripts folder is, in my case, UserName>\Documents\GitHub\PubsAndFlyway\PubsFlywaySecondMigration, and the output of all build tasks goes to different project folder, called Pubs (<UserName>\Documents\GitHub\Pubs), which is laid out with a directory for each version.

Here’s the resulting output:

Object                           code   line text                                             
------                           ----   ---- ----                                             
dbo.byroyalty                    PE009  4    No SET NOCOUNT ON before DML                     
dbo.PublishersByPublicationType  DEP021 5    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 6    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 7    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 8    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 9    String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 10   String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  DEP021 11   String literals as column aliases are deprecated 
dbo.PublishersByPublicationType  ST010  13   Use alias for all table sources                  
dbo.PublishersByPublicationType  ST010  14   Use alias for all table sources                  
dbo.PublishersByPublicationType  ST010  16   Use alias for all table sources                  
dbo.PublishersByPublicationType  PE002  16   Schema name for table or view is not specified   
dbo.PublishersByPublicationType  ST010  17   Use alias for all table sources                  
dbo.reptq1                       PE009  4    No SET NOCOUNT ON before DML                     
dbo.reptq1                       MI003  6    Unqualified column name                          
---etc --- 

dbo.titleview                    PE002  4    Schema name for table or view is not specified   
dbo.titleview                    ST010  4    Use alias for all table sources                  
dbo.titleview                    PE002  4    Schema name for table or view is not specified   
dbo.titleview                    ST010  4    Use alias for all table sources                  
dbo.titleview                    PE002  4    Schema name for table or view is not specified   

---Flyway stuff deleted ---

(Microsoft SQL Server 13.0)
Schema version: 1.1.8
+-----------+---------+-----------------------------+------+---------------------+---------+
| Category  | Version | Description                 | Type | Installed On        | State   |
+-----------+---------+-----------------------------+------+---------------------+---------+
| Versioned | 1.1.1   | Initial Build               | SQL  | 2021-03-24 15:03:14 | Success |
| Versioned | 1.1.2   | Pubs Original Data          | SQL  | 2021-03-24 15:03:34 | Success |
| Versioned | 1.1.3   | UseNVarcharetc              | SQL  | 2021-03-24 15:03:45 | Success |
| Versioned | 1.1.4   | RenameConstraintsAdd tables | SQL  | 2021-03-24 15:03:59 | Success |
| Versioned | 1.1.5   | Add New Data                | SQL  | 2021-03-24 15:04:34 | Success |
| Versioned | 1.1.6   | Add Tags                    | SQL  | 2021-03-24 15:04:51 | Success |
| Versioned | 1.1.7   | Add Indexes                 | SQL  | 2021-03-24 15:05:04 | Success |
| Versioned | 1.1.8   | AddconditionalVersion       | SQL  | 2021-03-24 15:05:16 | Success |
+-----------+---------+-----------------------------+------+---------------------+---------+

For more elaborate ways of viewing and prettifying the codeAnalysis.xml report, see my previous article, SQL Code Analysis from a PowerShell Deployment Script.

Conclusions

I’ve shown what looks at first glance to be a rather complicated approach to scripting. Whatever your approach to scripting, the principles are very similar, and the Script block approach is surprisingly simple but takes some care to make sure that obvious mistakes in the way they are used are easily revealed to the user. It allows Flyway to participate in a deployment in a cooperative manner with other database development and deployment tools such as Redgate’s SQL Change Automation and SQL Compare. It is devised to allow development teams to provide a robust system without becoming overwhelmed by the complexity of the processes.

 

Tools in this post

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more

Redgate Flyway

DevOps for the Database

Find out more