In this article, let’s see how we can write and execute an unit test case for a Microsoft Sql Server database object using Transact-SQL. Let’s start with some background, before getting into the details.
Background
We know that database is one of the important parts of a software system, where there are different types like Relational, NoSql, Graph, etc.
In most of the organizations, where there is dominance of .NET ecosystem, it’s very common to see the usage of Microsoft Sql Server as the first choice for relational database needs. So, in this article we are going to talk about using Microsoft Sql Server as the database, imagining that we are building a system using .NET.
Let’s also assume that, we are strictly following TDD while building this software system. ORM tools like Entity Framework Core - Code First can help us in easily unit testing database objects using c# code and popular frameworks like Xunit, Nunit, etc.
However, certain system requirements force engineers to use Transact-SQL based database source code, where .NET engineers like to make use of Visual Studio Database Project, which provides a cool designer, similar to SQL Server Management Studio.
In such cases, what I have seen so far is, application code is unit tested well, with good amount of code coverage, but database code Transact-SQL is just ignored. This increases the risk of lack of code confidence
, which raises questions like Will the stored procedure work as expected?
, Is the database table composed as expected?
, etc.
So we need to do something to reduce/avoid the risk and improve code confidence. The best way to achieve it is by writing test cases, either by using several integration/e2e tests covering all scenarios or by writing unit test cases around each objects like stored procedures
, functions
, etc.
I prefer unit tests over integration tests due to the reason that they are simpler and cheaper, so as to keep the keep the scope of integration tests to integration itself. Please refer here for more details.
There are several handy open source tools available for this purpose, but I am going to pick a tool called tSQLt which works specifically for Microsoft Sql Server
tSQLt is a wonderful tool that comes with several in-built assert functions which helps engineers writing test cases effectively and rapidly.
Here are some of the high level steps to get it accomplished.
- Setup the testing framework ( tSQLt) in the database server
- Deploy all the test cases in the database
- Execute all the deployed test cases
How are we going to execute the above steps?
Testing frameworks like Xunit, uses test runners, which executes all the available test cases in that project. So I thought of making use of it by writing a simple test class fixture to perform steps #1 & #2, and then a simple test case to execute step #3
. Alternatively, we can also perform all these steps using simple powershell scripts or any other language of your choice.
Getting Started
A complete sample project can be found here. Please follow readme documentation for usage instructions.
Prerequisites
- Latest .NET SDK for the Xunit project
- Admin previlages (
sa
permission) on the database server to execute tSQLt setup scripts. (Build/CI environment only)
Steps
Create an Xunit project named
Sample.DB.TsqlTests
, preferably use latest .NET framework. Run the commanddotnet new xunit -n Sample.DB.TsqlTests
Create a folder called
tSQLt_Framework
under the projectDowload the framework from tsqlt.org, extract and move into folder
tSQLt_Framework
. The folder contains sql files for tasks like server preperation (PrepareServer.sql
), tSQLt framework setup (tSQLt.class.sql
), test examples (Example.sql
), etc.Add the below xml element in the
.csproj
file. This will ensure that all the sql files (includes tSQLt framework and tests) are copied to thebin
folder after any successful build.<ItemGroup> <None Include="**/*.sql" Exclude="**/bin/**;**/obj/**;" CopyToPublishDirectory="Always" CopyToOutputDirectory="Always" /> </ItemGroup>
To perform steps #1 & #2, let’s create a test fixture class named
TsqltTestFactory
as below to start with. tSQLt scripts containGO
statements, so there are 2 ways we can execute/deploy these sql scripts. One way is to useSqlServerConnection -> new Server(new ServerConnection(connection))
OR the other is to useSqlConnection -> new SqlConnection()
after ignoringGO
statements and execute in chunks. The methodExecuteScriptsInChunks(string[] sqlContent)
in the below code snippet explains it.public class TsqltTestFactory : IDisposable { private SqlConnection connection; public TsqltTestFactory() { connection = GetSqlConnection(); } private async Task ExecuteScriptsInChunks(string[] sqlContent) { var sqlBuilder = new StringBuilder(); foreach (var line in sqlContent) { if (line.Trim().ToLower() == "go") { await ExecuteNonQueryAsync(sqlBuilder.ToString()); sqlBuilder = new StringBuilder(); } else sqlBuilder.AppendLine(line); } } public async Task<int> ExecuteNonQueryAsync(string sql) { using var cmd = new SqlCommand(sql, connection); return await cmd.ExecuteNonQueryAsync(); } private SqlConnection GetSqlConnection() { var sqlConnection = new SqlConnection("Server=localhost;Database=Sample;User Id=sa;Password=XXXXXXX;"); sqlConnection.Open(); return sqlConnection; } public void Dispose() { // Dispose logic here // close sql connection here } }
Add a method (as below) to perform server preperation task. For this, we need to execute the sql file
PrepareServer.sql
. This is basically an onetime operation, however doesn’t hurt if executed multiple times.Note: This needs
sa
permission.private async Task PrepareSqlServer() { var prepareServerSqlFile = Path.Combine(Directory.GetCurrentDirectory(), "tSQLt_Framework", "PrepareServer.sql"); var prepareServerSql = File.ReadAllLines(prepareServerSqlFile); await ExecuteScriptsInChunks(prepareServerSql); }
Add another (as below) method to perform configuration of tSQLt framework. For this, we need to execute the sql file
tSQLt.class.sql
.private async Task ConfigureTsqltFramework() { var tSQLtClassSqlFile = Path.Combine(Directory.GetCurrentDirectory(), "tSQLt_Framework", "tSQLt.class.sql"); var tSQLtClassSql = File.ReadAllLines(tSQLtClassSqlFile); await ExecuteScriptsInChunks(tSQLtClassSql); }
To deploy all the tSQLt test cases that we create, let’s add the below method in the test class fixture.
private async Task DeployAllTsqltTests() { var tsqltTestFiles = Directory.GetFiles(Directory.GetCurrentDirectory(), "*Tests.sql", SearchOption.AllDirectories); foreach (var tsqltTestFile in tsqltTestFiles) { var tsqltTestFileSql = File.ReadAllLines(tsqltTestFile); await ExecuteScriptsInChunks(tsqltTestFileSql); } }
To execute all the above methods in order and before even executing the tests, let’s call these methods from the constructor of the fixture class as below.
public TsqltTestFactory() { connection = GetSqlConnection(); Task.WaitAll(PrepareSqlServer()); Task.WaitAll(ConfigureTsqltFramework()); Task.WaitAll(DeployAllTsqltTests()); }
At this point we are done with execution of steps #1 & #2, which are configuring the database server and deployment of tests.
Finally, let’s create a simple test (as below), to run all the tests on the database. Make sure of the usage of the created fixture. In addition, you can capture the results from the database using a
ITestOutputHelper
. For more details, you can look into my sample projectpublic class TsqlUnitTests : IClassFixture<TsqltTestFactory> { private readonly TsqltTestFactory factory; public TsqlUnitTests(TsqltTestFactory factory) { this.factory = factory; } [Fact] public async Task ExecuteAllTsqltTests() { await factory.ExecuteNonQueryAsync("EXEC tSQLt.RunAll;"); } }
Let’s write our first test case here. For that, create a file called
Table1Tests.sql
under the test project.Create the test (as below), which verifies the composition of the table
dbo.Table1
. To learn more about writing test cases, refer here. I will be publishing another article onhow to write test cases for different types of database objects
.-- create a test class EXEC tSQLt.NewTestClass 'Table1Tests'; GO -- create a test function CREATE PROCEDURE Table1Tests.[test if Table1 table is composed of] AS BEGIN -- Arrange -- create expected table CREATE TABLE Expected ( COLUMN_NAME VARCHAR(20), COLUMN_DEFAULT VARCHAR(20), IS_NULLABLE VARCHAR(20), DATA_TYPE VARCHAR(20), CHARACTER_MAXIMUM_LENGTH VARCHAR(20) ) -- create actual table SELECT * INTO #Actual FROM Expected -- load expected table with expected table composition values INSERT INTO Expected VALUES ('Id',null,'NO','int',null) INSERT INTO Expected VALUES ('Text',null,'NO','varchar','50') INSERT INTO Expected VALUES ('Description',null,'YES','varchar','500') -- Act -- load actual table with `Table1` composition INSERT INTO #Actual SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' --Assert EXEC tSQLt.AssertEqualsTable 'Expected', '#Actual'; END;
Awesome, we have created our first tSQLt test case.
If we run this test, it will definitely fail because we don’t have a table called
Table1
created.
So, what’s next? Let’s try to make the test pass.
For this, we need to create a database project (
.sqlproj
) and try to test it using tSQLt runner we built.We can use Visual Studio Database Project template OR by using a SDK style project.
For creation and deployment of the database project (
.sqlproj
) using SDK style, please follow the steps provided in my another article How to create a dotnet SDK style SqlServer database project?There we go, we should have the database project ready and deployed successfully.
Finally, let’s verify if
Table1
composition is as expected, by running the test case by executingExecuteAllTsqltTests()
. If the test passes, we should see the result as below. UseITestOutputHelper
to stream information fromSqlConnection
as done in the sample project.
In the next article, I shall try to go a bit deep into writing tests for different database objects like table, stored procedures, constraints, etc.
Sample Project
A complete sample project can be found here, which includes..
- SDK Style database project with a sample database object (table)
- Compiling and publishing the project as
.dacpac
- Creation of Microsoft Sql Server in docker
- Deployment the project (from published artifact
.dacpac
) into the Microsoft Sql Server - Testing database objects using tSQLt
Love helping the community!
comments powered by Disqus