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 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.
Using an IDE for .net projects, create a new .net core c# console application named
Demo.Database. Install the dbup-sqlserver nuget package.
Scripts folder in the project with two sub folders.
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.
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
Publish.bat: when executing this file, the
DemoDbdatabase will be dropped and re-created in SQL server.
Publish.Tests.bat: when executing this file, the test cases will be created in the
DemoDbdatabase. Note that this file can only be executed after the tSQLt unit test framework has been installed.
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.
Publish.bat file. The output in the
bat file should be similar to the below.
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
Now that the database is completed, the next step is to start preparing for unit testing.
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.
TRUSTWORTHY on the
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
- Create a file named
_create_role_class.sqlwith the follow SQL statement.
exec tSQLt.NewTestClass 'Role';
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
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 |
|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 |
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.
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.
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:
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