Create and Run tSQLt Tests in dbForge Studio for SQL Server
If you want to ensure that a single unit of behavior meets the business requirements and database object works as expected
prior to being integrated with other objects, Unit Testing built into dbForge Studio for SQL Server is the right solution
to achieve that. Unit Testing is a simple management tool with an intuitive interface in order to create, manage, and execute
multiple tests, visualize test results and progress. Given that the tool is based on the tSQLt framework, unit tests can easily
be written in T-SQL.
The tool focuses on testing the business logic of the database and verifies that in case of any changes,
the existing functionality keeps on working as expected. With the tool, developers can detect and fix bugs earlier in the development
cycle that, in turn, drastically saves costs, minimizes the number of errors, boosts productivity, and makes customers much satisfied.
- Improve and maintain quality of the code
- Implement SQL unit tests in isolation from dependencies
- Maintain database integrity and data consistency
- Unit testing has no impact on the actual database and no data cleanup
- Save time on automating and scheduling units tests via the command line interface
- Simulate the behavior of database objects to run unit tests with predefined tests against the database
Installing tSQLt Framework
The tool uses the open-source tSQLt framework for SQL Server to write unit tests in T-SQL. This will prevent you from switching between
different tools while working. The framework contains T-SQL tables, stored procedures, views, and functions. The tool allows you to test database
objects in isolation from other objects or their dependencies.
To run the tests, first, you need to install the tSQLt framework that will create tSQLt objects and SQL CRL object for the selected database. For this,
select the database against which you want to run the tests, click Unit Test > Install Test Framework on the shortcut menu,
and select a target database.
Creating Unit Tests for SQL Server Databases
After the successful installation, you can proceed with the creation of unit tests for the database. To create a unit test,
in Database Explorer, you need to select the database and then click Unit Test > Add New Test on the shortcut menu. In the window that opens,
specify the test attributes - a test name and a class name - and click Add New Test.
Once the tests were added, a new SQL document opens where you can create tests checking the specific unit of behavior.
The document displays the script template where you need to write T-SQL code for your test. The template is divided into three blocks: Assemble, Act, and Assert.
- Assemble: Set up the environment and declare database objects to be tested.
- Act: Execute the tests against the code and capture the results in variables or tables if any.
- Assert: Compare the value you got to the expected one.
This approach allows you to reduce cost savings and your time spent on unit testing and make sure that the output results meet the expected behavior.
Managing SQL Test Cases and Test Classes
The tool offers Test List Manager that allows you to get deeper into the tests available for the selected database and perform different actions within a single tab. Test List Manager
visualizes test cases in a hierarchical tree grouped by a test class under the selected databases. You can open the Test List Manager
in two ways:
- Database Explorer: Right-click the database and then select Unit Test > View Test List on the shortcut menu.
- Main menu: Hover over the Database menu and then select Unit Test > View Test List on the shortcut menu.
Test List Manager allows you to easily manipulate test cases and test classes:
- Add a new test for the selected test class or the database
- Run all tests grouped under the selected test class
- Delete a test or test class
- Rename a test class
- Run all tests against the selected database or multiple databases
- Open the source code of the test in a new SQL document
- Modify the test in a SQL document
In addition, you can uninstall the framework from the database. That means the tSQLt stored procedures, functions, and SQL CRL objects will be removed from the database.
Running SQL Unit Tests
You can run all SQL Server unit tests against the database either from Database Explorer or Test List Manager.
All running tests are represented as a grid in the Test Results window that displays whether the test(s) have been succeeded or failed. In the latter case,
you can view the error message stating the expected and actual results.
In the Test Results window, you can re-run all tests or the selected ones, or open a test in a new SQL document for viewing, editing, and fixing purposes.
Using Pre-Written Test Samples
The tool provides a pre-written set of unit test samples which you can use to get started with unit tests.
For that to work, you need to install a sample database: Hover over the Database menu and select Unit Test > Install Sample Database from the shortcut menu.
The successful installation will add a sample database to the list of databases in Database Explorer.
You can easily add, modify, or remove the test, run all tests or the selected one(s), update a class name, view the source code, etc.