It’s important to run SQL Server database unit tests to ensure that any changes made will not “break” the database or in other words violate the integrity, relationships or the functionality of the database itself. Ideally, many issues can be found in development, even pre-QA, if unit test coverage is created and run early and often. So even though we may understand that running unit tests against database changes is important, we may also experience the fact that creating and running SQL Server database unit tests manually can be time consuming, especially for lager databases which are under active development.
The goal then is to use unit testing but to automate the process. Running Unit tests automatically will save time and will provide a stepping stone to any continuous delivery process that is implemented by the team.
To create and run SQL Unit tests automatically we’ll use ApexSQL Unit Test, a tool specifically created for SQL Server database unit testing, in conjunction with the tSQLt SQL Server database unit testing framework.
Setting up ApexSQL Unit Test
First of all, to be able to execute unit tests, tSQLt must be installed for the desired database. To do this, open the SQL Server Management Studio and connect to the database engine. Right click on the desired database, find the ApexSQL Unit Test menu and click the Install tSQLt option.
Once this is clicked, select which version of tSQLt will be installed and click OK.
Once tSQLt is installed the process of creating and running SQL Unit tests can be started. Right click the database for which the tSQLt is installed and navigate to the ApexSQL Unit test menu again and select the corresponding option.
If there are no unit tests created for this database, select the New test option, so we can create a new test container including unit test name and class name. Now enter a name for this test and select a valid class for it.
A “class” in the context of SQL Server unit testing is basically a schema which is used for storing and organizing unit tests.
If there is no class already created, click the New class button, enter a name for that class and click OK.
SQL Server database unit tests, in the context of tSQLt, exist as stored procedures. So once the test container is created, a new query window will appear where the new SQL Server unit test can be scripted and created as a stored procedure. You can use the query editor to create unit test classes based on your requirements.
Now, let’s assume that the user has already created a variety of unit tests. In this case, the next step would be to run them. In the ApexSQL Unit Test menu from the object explorer tab, click the Run all option and the Unit Test explorer will appear in SSMS.
In the Unit test explorer, all created test will be displayed. In this menu the Unit tests can be run, and each test can be edited if needed.
Now we can finally run the desired Unit tests. Select the folder which contains the tests and click the Run button.
Once the tests are executed and finished, the results will be displayed in the lower part of the Unit Test explorer. Also, the icons of the individual unit tests will change to indicate success or failure.
Automation
Now that we’ve built this manually, all of this can be automated by using simple scripts to run the process via the command line interface aka CLI of ApexSQL Unit Test. Automating SQL unit testing can even be integrated as a part of the continuous delivery process.
To begin automating this process, we will create a PowerShell script as an example. In this script we will use a simple combination of switches for running Unit tests and generating a report upon completing the operation:
& 'ApexSQLUnitTest' /s: ALEN - PC /db: Pubs_QA /cl: PubsTests / rc / f / v
‘ApexSQLUnitTest’ – is the console application of ApexSQL Unit test and assumes that the application directory is the current directory. If not, specify the full path explicitly
/s:ALEN-PC – specify the server name
/db:Pubs_QA – specify the database name for which the unit test needs to be executed
/cl:PubsTests – specify the specific unit test class or else tests will be executed for all classes
/rc – the return codes switch outputs all return codes to console
/f – the force switch for overriding any existing files with the same file name
/v – the verbose switch used for displaying messages about the Unit test execution process in the console
In this example we used the Windows Authentication method for connecting to the server. This way, we can run the process unattended.
|
Quick tip: There are other ways for handling login credentials which involves running the process even unattended if needed. To read more about this, read this article: “4 ways of handling database/login credentials during automated execution via the CLI”. |
When the script is created, save it with a .ps1 file extension and execute the script to see what happens.
When the Unit test execution is completed, a detailed report is displayed and you can see the results of executed Unit tests.
|
Quick tip: If the report results need to be saved to a Text document for example, an additional switch, /rn, can be used. In this case the script needs to look like this: |
The /report_name switch is used for specifying the name and the location of the report. In this case a Text document report.
Useful links:
- Create an Empty SQL Server Unit Test
- Creating and Running a SQL Server Unit Test
- Using a Custom Test Condition to Verify the Results of a Stored Procedure
- Write a SQL Server Unit Test that Runs within the Scope of a Single Transaction
October 12, 2016
