As the pressure for faster delivery cycles begins to accrue, product companies and enterprises need to adopt accelerated software development life cycle. It includes embracing change quickly with agile methodologies, development to deployment best practices, test-driven development, and continuous integration across application development.

In an SDLC, unit testing has been a crucial part of the testing procedure. The accurate execution of unit testing leads to defect detection at an early stage, reducing the overall project cost and ensuring code stability.

What is Unit Testing? How Does it Impact SDLC?

Unit testing refers to the testing of a self-contained (unit) piece of code. Any code that can produce a result by itself based on optional parameters is a unit piece of code. In T-SQL, stored procedures and functions are unit pieces of code. They produce results (in tabular or scalar format) based on the parameters passed to them, without any dependencies on other parts of the code.

For most software development teams in companies and enterprises, unit testing is an essential toolkit. However significant the unit testing be, the process is yet manual. The tedious and cumbersome manual nature of this process brings with it a host of challenges. Let us go through some of these challenges.

 

Challenges in Adoption of (Manual) Unit Testing

Decentralized Repository for Test Cases

In unit testing, there is no central repository to store your unit test cases. To develop any module, developers write test cases and store them in spreadsheets or some other format. These documented cases are attached to the ticket for reference. This type of repository for a specific module is not ideal for test cases. Whenever a task comes up in the same module later, such storage will creep additional challenges in the combination of test cases and its execution.

Repetitive Test Data Creation

In unit testing, the creation of test data is usually stressful and repetitive. Many software development professionals work on the same environment creating their test data for every task. The newly created test data would overlap with the test data of other developers who would then get undesired results. Additionally, when you test in a different environment, you will have to create the test data once again.

Manual Execution of Test Cases

In addition to a decentralized repository, repetitive test data creation, the seamless execution of these test cases is a hiccup. The execution of most of the test cases is manual and time-consuming. There is no alternative to executing multiple unit test cases with a single click of a button.

How Challenges Impact Businesses?

The challenges mentioned above impact product companies and enterprises in their software development.

First and foremost, the unit testing process is tedious and inefficient. For urgent and high priority tasks with strict deadlines, it gets challenging to evaluate and write all the possible unit test cases. Lack of testing best practices may lead to the ignorance of some critical unit test cases resulting in regression bugs. In worst-case scenarios, it becomes a formality that would defeat the purpose of unit testing; this is verifying the behavior of the small piece of code without leading to bugs and low-quality code.

Introduction to tSQLt – Database Unit Testing for SQL Server

tSQLt is an open-source database unit testing framework for SQL Server. It allows us to create test classes, write test cases in T-SQL, and uses CLR (Common Language Runtime) under the hood. Third-party software like SQL Test – Redgate and dbForge Unit Test for SQL Server use this framework prominently.

In tSQLt, stored procedures are written for every test case. All the stored procedures/test cases for a module are created within the schema of the particular module.

Why Companies Should Choose tSQLt Database Unit Testing?

tSQLt is an open-source framework, which means it’s free to use and the budget will never be a constraint. In addition, it has the following advantages in comparison with the other frameworks.

1. tSQLt is entirely dependent on SQL Server. Software development teams need not switch between applications for its execution.
2. The database unit testing framework provides software development teams with the mocking concept. Mocking allows you to mock database objects inside the test case and avoid any dependency with the actual object. It also isolates the test case from other test cases.
3. We can extend the framework for custom requirements. For example, in a new test case run, tSQLt clears the results of the previous test cases. We can create custom tables and stored procedures that would archive the previous test results and use them for reporting purposes if needed.

Benefits of Using tSQLt Database Unit Testing

The Centralized Repository

As software development teams create stored procedures and schemas in a database, a central repository gets created. This repository can be easily accessed to run the execution later whenever required. The reason being all the test cases related to the module are combined and present under the same schema.

No Repetition of Test Data Creation

Within stored procedures (test cases), the test data is created. This data gets created at the start of the test case and gets dropped upon completion of the execution. Every execution is an independent transaction that helps isolate the test case from other test case executions created by fellow developers. Software development teams can also create a stored procedure called “Setup Procedures” within every module, which would include only data creation statements. These procedures are automatically called when you run a test case and the repetitive nature of test data creation is avoided.

Automation of Test Execution

Automation of test case execution is possible by including the tSQLt.RunAll stored procedure call in a daily job or build scripts. These stored procedures don’t take any parameters and would execute all the test cases created in a particular database.

For individual execution of test cases, we can call a stored procedure tSQL.Run with the test case name as a parameter. All test cases in a module are executed by calling the same stored procedure with the schema name as a parameter.

Using tSQLt Database Unit Testing for a Client

Xoriant used tSQLt database unit testing framework for a leading client involved in networked crisis communication.

Challenge

The client’s engineering team had a “stored procedure” that searches for users based on multiple parameters. The procedure is used by many modules, including:

• User Viewer Page (A page to display the list of users to view/edit)
• Alert Publishing Page (A page to list users, with messages or alerts to be sent to them)
• Alert Summary Page (A page to list users who have been sent a particular alert) etc.

With any change in this “stored procedure,” we found ourselves writing the same test cases again. This process was repetitive, time-consuming, and affected the code quality.

Xoriant Solution & Contribution

Xoriant’s engineering team decided to use a tSQLt database unit testing framework to avoid this repetition. This framework ensured a centralized repository with the automation of test execution. tSQLt proved to be a cost-efficient solution for the client.

The users are stored in a table “Users” along with some attributes like UserId, UserType, StartDate and so on. Most of the attributes, like UserName, FirstName, LastName, HomLocation, etc. are stored in another table called “UserAttributeValues”. These are the two of the most commonly used tables in the entire application.

The User Search module takes parameters to filter the number of users returned. These filters are based on the attributes which can be text (FirstName, LastName, etc.), dates (StartDate, SubscriptionDate, etc.), geographical location (HomeLocation, WorkLocation, etc.).

Our engineering team created a test class called “UserSearch” and put all the test cases related to the user search module under it. Some of the test cases included:

• To check if the text-based filtering returns correct results
• To check if all the users return when no filters are applied
• To check if the paging feature works correctly
• To check if the sorting feature works correctly etc.

Post the set up of these primary test cases, any change in the user search module would require the creation of additional test cases based on the change. tSQLt framework was used for easy and efficient unit testing.  As explained below,

1. We had all the test cases (previous and current) in the same test class. Running the test cases was efficient, as we just had to call tSQLt.Run ‘UserSearch’ and all the test cases within the test class would be executed.
2. We had created a “Setup” procedure to create the data required for every test case at the start. We didn’t need to create the test data repeatedly.
3. The mocking concept (with use of FakeTables) allowed us to isolate other processes. The two tables which get heavily used were completely isolated from our test execution.
4. We created some stored procedures to archive test results before the next text execution for reporting purposes. Besides, we created some reports using SSRS to analyze the results of automated test executions over time.

Impact on QA practitioners

With tSQLt Unit Testing, the code written will have high quality. This framework will in no way reduce the importance of manual QA practitioners but will help them in focussing on complex regression and integration test cases that need manual intervention.

Summing it Up

We have seen an excellent framework for T-SQL unit testing. It is open-source and its execution is independent of any external tools other than SQL Server. In the beginning, it might seem to be a tedious process to create stored procedures for every test case and schemas in every module. But once you invest valuable effort at the start, your company will reap the benefits by achieving good quality code with the automation of unit testing in the long run.

Talk to our experts to learn how you can leverage the tSQLt framework in your software development lifecycle.

References

• https://tsqlt.org/
• https://tsqlt.org/full-user-guide/