Automated Unit Testing SQL Server Code
I have never been a fan of automated unit testing of SQL code mainly because previous frameworks / tools, which I came across, all seem to miss the point of SQL code which is either retrieving or manipulating data.
Some of the frameworks would only test SQL scalar functions which limited code coverage. Others would test stored procedures but only the output parameter not the data being returned which limited the usefulness of the test. The methods used to determine if a test pass or fail were also limited. As some would only check the return value; the correct number of rows were returned; a single row of data appeared in the dataset or within a row and column a value is returned. This meant that you could write code that would satisfy the test and could still be functionally incorrect as the rest of the data not checked could be inaccurate.
Another problem that some of the previous frameworks forced upon you to solve was how to make the test repeatable. As data has state which is maintained through the code that you are testing. If you rerun a test it may not be against the same data state as the previous test run. This could cause test to fail because the expected results were out of date or due to constraints violations as previous run weren’t removed. Also getting the data into a starting state could be time-consuming and error prone because of identity properties; referential integrity and table constraints.
However I recently came across a unit test framework called tsqlt which has changed my mind about automating unit testing against my SQL code. The framework supports the testing of any executable SQL code and offers approaches to isolate embedded stored procedures; triggers and functions. There are a range of asserts the test to use to make sure that the code is functionally correct ranging from data compare, structure compare, the existence of objects and calls were made to embedded code.
The framework helps with repeatable unit tests by wrapping the execution of tests within database transactions and then rolling back once the execution has completed. Also the framework offers tools for getting the database into a particular test state by faking the database tables and applying selective constraints, this is similar to .net mocking objects, which makes inserting data easier. More details of how to use and get the tsqlt framework can be found at the following URL: http://tsqlt.org/.
I would like to share some of my methods I use to make using the framework a little bit easier.
Creating the set up stored procedure within each test class helps reduce the amount of repeatable code that each unit test would have to contain. The set up stored procedure is automatically called before each unit test, within the test class, is executed. The code I put in my Setup procedures was the faking of the required tables and inserting of any initial data required.
To create a set up stored procedure just create a stored procedure call SetUp e.g.
I found by grouping my unit tests based upon C.R.U.D operations help to get the most out of the setup stored procedure. As I found my insert operations needed the tables to retain their identity columns and defaults to ensure that the insert stored procedures worked. Whereas the updates only required the defaults and the reads only required a fake table.
I found putting my test data into table value functions / views reduced repeating code. Also this helped me in creating my actual data as I was able to apply filters and case statements to get the required results for the particular tests.
For timestamp column you can emit them from the expected results so they are not compared with the actual table. However my method for dealing with my timestamp columns was within my setup stored procedures / unit tests and application code.
Within the setup stored procedures I would alter the defaults to a static date and time, which was completely different to the initial data, before faking the table. Then use the same static value in my expected table within the unit test. Within my application code I change my approach to updating my timestamp columns. Instead of directly updating the column by using a call to the SYSDATETIME() function I would update the column by using the DEFAULT keyword e.g.
further reading:
Some of the frameworks would only test SQL scalar functions which limited code coverage. Others would test stored procedures but only the output parameter not the data being returned which limited the usefulness of the test. The methods used to determine if a test pass or fail were also limited. As some would only check the return value; the correct number of rows were returned; a single row of data appeared in the dataset or within a row and column a value is returned. This meant that you could write code that would satisfy the test and could still be functionally incorrect as the rest of the data not checked could be inaccurate.
Another problem that some of the previous frameworks forced upon you to solve was how to make the test repeatable. As data has state which is maintained through the code that you are testing. If you rerun a test it may not be against the same data state as the previous test run. This could cause test to fail because the expected results were out of date or due to constraints violations as previous run weren’t removed. Also getting the data into a starting state could be time-consuming and error prone because of identity properties; referential integrity and table constraints.
However I recently came across a unit test framework called tsqlt which has changed my mind about automating unit testing against my SQL code. The framework supports the testing of any executable SQL code and offers approaches to isolate embedded stored procedures; triggers and functions. There are a range of asserts the test to use to make sure that the code is functionally correct ranging from data compare, structure compare, the existence of objects and calls were made to embedded code.
The framework helps with repeatable unit tests by wrapping the execution of tests within database transactions and then rolling back once the execution has completed. Also the framework offers tools for getting the database into a particular test state by faking the database tables and applying selective constraints, this is similar to .net mocking objects, which makes inserting data easier. More details of how to use and get the tsqlt framework can be found at the following URL: http://tsqlt.org/.
I would like to share some of my methods I use to make using the framework a little bit easier.
Creating the set up stored procedure within each test class helps reduce the amount of repeatable code that each unit test would have to contain. The set up stored procedure is automatically called before each unit test, within the test class, is executed. The code I put in my Setup procedures was the faking of the required tables and inserting of any initial data required.
To create a set up stored procedure just create a stored procedure call SetUp e.g.
1: CREATE Procedure [MyTestClass].[Setup]
2: AS
3: EXECUTE [tSQLt].[FakeTable]
4: @TableName = N'[MyAppSchema].[MyAppTable]'
5: , @Identity = 1
6: , @ComputedColumns = 0
7: , @Defaults = 1
8: Retutn 0
I found by grouping my unit tests based upon C.R.U.D operations help to get the most out of the setup stored procedure. As I found my insert operations needed the tables to retain their identity columns and defaults to ensure that the insert stored procedures worked. Whereas the updates only required the defaults and the reads only required a fake table.
I found putting my test data into table value functions / views reduced repeating code. Also this helped me in creating my actual data as I was able to apply filters and case statements to get the required results for the particular tests.
For timestamp column you can emit them from the expected results so they are not compared with the actual table. However my method for dealing with my timestamp columns was within my setup stored procedures / unit tests and application code.
Within the setup stored procedures I would alter the defaults to a static date and time, which was completely different to the initial data, before faking the table. Then use the same static value in my expected table within the unit test. Within my application code I change my approach to updating my timestamp columns. Instead of directly updating the column by using a call to the SYSDATETIME() function I would update the column by using the DEFAULT keyword e.g.
1: UPDATE [AppSchema].[AppTable]
2: SET [AppColumn1] = @pValue1
3: , [AppColumn2] = @pValue2
4: , [AppColumn3] = @pValue3
5: , [LastUpdated] = DEFAULT
6: WHERE [KeyColumnId] = @pKeyColumnId
further reading:
Comments
Devops Training in Chennai
Devops Training Institute in Chennai