A database is the most crucial software component in any software application since it stores, organizes and manages large amounts of information efficiently.

Database developer tools

A number of powerful free database developer tools exist to create and manage a SQL server database. For example,

• SQL Server data tools is a plugin for visual studio providing the ability to create and manage a SQL database project. This package can also be used to create database unit tests.
• SQL Server Management Studio is an integrated environment for managing any SQL infrastructure from SQL server to Azure SQL databases.
• Azure Data Studio is a cross-platform database editor for SQL server and Azure SQL.
• Visual studio code, a text editor, to create and manage SQL scripts.
• DbUp, a .net library providing the ability to deploy changes to a SQL server database. This library also tracks which SQL scripts have already been run and only executes the scripts that have not been run.
• dbforge Studio for SQL Server express edition, a powerful IDE for SQL development.

There are many other technologies that can be used to create and manage SQL server databases, however this article will focus on creating a database using DbUp.

Unit testing

Unit testing is an important phase in any software engineering project. In many projects, unit testing of the database is completely ignored. With rapid advancements in technology in recent years, and with new practices emerging to combine software development and IT operations, that is DevOps, to encourage continuous delivery with high software quality, a database should follow best practices and should be unit tested in any enterprise software engineering project.

tSQLt is an open source unit testing framework for SQL server. This framework makes it very easy to unit test a database in a development environment. It also encourages Test Driven Development (TDD) and provides the ability to automatically run tests in a CI/CD pipeline.

This article will focus on unit testing a database using the tSQLt unit testing framework.

Getting started

Using an IDE for .net projects, create a new .net core c# console application named Demo.Database. Install the dbup-sqlserver nuget package.

Install-Package dbup-sqlserver

Create a Scripts folder in the project with two sub folders.

• src
• tests

Replace the Program.cs file with the below code base.

DbUp provides a simple fluent interface to set up the console application. The DbUp nuget package is also well documented making it very easy to follow.

In the Program.cs class above, the program requires three arguments.

• connectionString – the SQL server database connection string.
• dropDatabase – “true” to indicate that the database must be dropped before executing the scripts; otherwise “false”.
• installTests – “true” to indicate that only the scripts to create the test cases are to be run; otherwise “false”.

Finally create the following bat files. Note that the connection string indicates that a DemoDb database will be created.

Note that dotnet core 3.1 was used. If a different version of donet core is used, then the bat files need to be modified accordingly.

Based on the Program.cs arguments, two bat files are created providing the following feature when executing the bat file.

• Publish.bat: when executing this file, the DemoDb database will be dropped and re-created in SQL server.
• Publish.Tests.bat: when executing this file, the test cases will be created in the DemoDb database. Note that this file can only be executed after the tSQLt unit test framework has been installed.

The Demo.Database project should be similar to the below.

Now that all the boilerplate code is completed, build the solution. This concludes all the c# .net programming.

Creating a table

For this demo, a new schema will be created named auth, having one table named Role. This table will store a list of unique roles.

Since DbUp keeps track of all SQL scripts that have run, it is good practice to have a good naming convention for script files.

Create a file named 001_Create_auth_schema.sql. This file should have the following SQL code.

Create a second file named 002_Create_role_table.sql with the below SQL statement.

Run the Publish.bat file. The output in the bat file should be similar to the below.

The DemoDb database should be created in SQL server with the following tables.

DbUp automatically created the dbo.SchemaVersions table and managed the versioning. Below are the records contained in the dbo.SchemaVersions table.

Now that the database is completed, the next step is to start preparing for unit testing.

Installing tSQLt

Run the following script to enable CLR functionality of the SQL server. This is a once off script since it is executed on the SQL server instance.

Next, enable TRUSTWORTHY on the DemoDB.

Finally, Download tSQLt from here. Unzip the tSQLt download and execute the tSQLt.class.sql script. After tSQLt is installed, the output should be similar to the below.

Write first unit test

Unit testing for SQL databases follows the same principles as application development. The Three Pillars of a Unit Test can also be followed as a guideline to write unit tests.

In order to write unit tests for the auth.Role database table, a class needs to be created. A class will contain a number of unit tests. Follow these steps to create a class.

• Create a folder named Role in the tests folder.
• Create a file named _create_role_class.sql with the follow SQL statement.

exec tSQLt.NewTestClass 'Role';

The auth.Role table has the following structure.

From the above structure, a number of unit tests can be written. For now, a unit test will be written to prove that if a role name, that does not exist, is less than 10 characters, then it will be inserted into the auth.Role table.

The three pillars of a unit test will be used to test this feature. The first part is the unit test case.

Unit test case
What are you testing?	Role
What are the conditions?	when role name is less than 10 characters
What is the expected result?	then should insert role.

Using the above table create the following unit test in the Role class in the tests folder.

To implement the unit test, the unit test definition part needs to be addressed.

Unit test definition
Arrange	Create a role name that does not exist. Also create a variable to store the role ID after the role is inserted.
Act	Insert the new role into the auth.Role table.
Assert	Check that the new role was inserted.

Using the above table, the unit test can be constructed as follows.

Finally, the last part of the three pillars of a unit test can be addressed.

Unit test rule
Independent	The unit test is not dependent on any other unit test. It is loosely coupled and can be run at anytime providing the same output.
Readable	The code is structured neatly and is descriptive. Good naming conventions are used for variables. Expected and actual results are clearly defined.
Maintainable	The unit test is independent, readable and simple since it is only testing a single unit of the database. It can be easily maintained if the auth.Role table changes.

Now that the unit test has been created, run the Publish.Tests.bat file to create the test case in the SQL server database. The output should be similar to the below.

To run the test, execute the following SQL statement.

exec tSQLt.RunAll;

The output should be similar to the below.

That is all that is needed for this test. The project layout should be similar to the below.

The following tests can also be constructed to complete the auth.Role unit testing.

Summary

The DbUp implementation used in this article made it very easy to create a new database. Also, the DbUp package handled versioning of SQL scripts by creating a SQL table to store a list of executed scripts. Using the bat file provides enough flexibility to re-create the database whenever it is needed.

tSQLt is a powerful open source unit testing framework for SQL server. This framework makes it very easy to unit test a database. By using tSQLt to unit test a database, it ensures that quality software is produced. Also, by writing adequate unit tests, it enhances a developer’s SQL skills.

Both DbUp and tSQLt provide simple and neat documentation, thus making it very easy to use these tools.

Note that DbUp and tSQLt can be used independently. This article presented one approach that used DbUp to create the database with tSQLt as the unit test framework.

The source code used in this article can be found here.

Further information on DbUp and tSQLt can be found at the following links:

• tSQLt
• dbUp

Software versions used in this article

• Jetbrains Rider v2020.1.3
• SSMS 15.0.18333.0
• SQL Server 14.0.2027.2
• DbUp v4.4.0
• tSQLt v1.0.5873.27393
• dotnet core v3.1
• dbforge SQL tools v5.8.30