How dbForge Unit Test is involved in the DevOps process

Unit tests execution is one of the key stages of the database DevOps process. It ensures that the changes made by developers to database projects will be verified and all the functionality will work correctly after deployment.

To organize DevOps unit testing, you need to have dbForge SQL Tools installed. Before including Unit Tests to the testing stage of the DevOps process, they need to be created. Unit tests can be created with the dbForge Unit Testing tool. The tool also allows performing the tests during CI.

Unit Testing in the DevOps Process

When dbForge SQL Tools is installed, you can start setting up the testing stage of the DevOps process. The necessary conditions for running unit tests are the following:

  • tSQLt framework must be installed on the database you want to run tests on.
  • Unit tests must be present in the database you want to run tests on.

tSQLt is an open-source database unit testing framework for SQL Server. To learn more about the framework, please follow this link. tSQLt framework can be installed on a database where unit tests are supposed to be run. The environment of the tSQLt framework is a set of tables, views, procedures, functions, and other objects.

Unit tests can be created right in a database to be tested or in a temporary database along with a set of scripts of their creation and subsequent use on any of tested DBs. If a set of scripts for unit tests creation is used, the set will look like a multitude of *.sql files. Some of these files are creation scripts, some are database schema creation scripts (test classes). The rest of the files are stored procedure creation scripts (test cases) within which the special methods (e.g. AssertEquals, AssertNotEquals et al.) are called from the tSQLt framework.

tSQLt envinronment

Unit Tests Creation/Modification

dbForge Unit Test provides a convenient user interface for the creation of new and modification of already existing unit tests. To learn how to install the tSQLt framework as well as how to and create/modify and run Unit Tests, refer to the Creating and Running SQL Server Unit Tests section of this documentation topic.

dbForge Unit Test helps create multiple classes and test cases for them. It also allows creating reports on passed and failed tests.

To learn how to install dbForge Unit Test, follow this link

Using dbForge Unit Test in PowerShell cmdlets for Running Tests

The Invoke-DevartExecuteScript cmdlet creates tests in the database from the following folder:

    Invoke-DevartExecuteScript -Input $UnitTestsFolder -Connection $TestDBConnection
Where:
  • $UnitTestsFolder is a path to the folder with unit test files on the disk
  • $TestDBConnection is an object for connecting to the database where tests must be installed.

The Invoke-DevartDatabaseTests cmdlet runs unit tests against a database.

Invoke-DevartDatabaseTests -InputObject $TestDBConnection -InstalltSQLtFramework
-OutReportFileName $TestReportOutputFileName -ReportFormat JUnit
Where:
  • InputObject $TestDBConnection is a connection string to the database against which unit tests are to be run
  • The InstalltSQLtFramework parameter defines the installation of the tSQLt framework before running tests
  • The OutReportFileName $TestReportOutputFileName parameter is a path to an output file with the test execution report
  • The ReportFormat JUnit parameter is the report format.
To learn more on applying and using cmdlets, refer to this documentation topic.

Conclusion

As you can see, unit testing is one of the most important steps in the DevOps process. Automated tests allow you to quickly locate all errors on early stages of development and, correspondingly, minimize risks during deployment to the production environment.

Learn about other dbForge tools involved in collaborative
database development and deployment