[this intelligent life]
Blog Post

Introducing SqlTest

Mark Wojciechowicz • Aug 05, 2017

A library to enable Sql and Ssis Unit Testing

One Friday, on the way home from work, it occurred to me that I've probably written some form of this SQL test framework a half a dozen times. I realized that I would save a lot of time for myself and others, who are interested in testing ETL code, if I wrote an open source library. So, I took it upon myself that weekend, to pour all my learnings, from a wealth of mistakes, into a code base that would handle all the boilerplate stuff that you need for writing tests efficiently.

My goal was to create something that was easy to use, easy to debug and easy to get started. SqlTest is meant to be used in a C# test project in conjunction with a test harness like nunit or mstest. I’m well aware that most SQL / SSIS developers do not live in .net and some are downright afraid of it. In fact, another popular testing framework, tSQLt , is an excellent option for many projects because you can write tests in native SQL, plus you get all the goodies that come with it, such as fakes. However, there were several reasons why I chose to go my own way:


  1. tSQLt wraps everything in transactions, so all test behavior is rolled back at the end of each test. This is great for database tests, but no good for SSIS tests which are going to initiate their own connections and transactions.
  2. Nunit has parameterized tests (or TestCase ). This allows you to create a dozen related tests very quickly, each with different input to the same test. This is tremendously efficient.
  3. By going the route of SqlTest and Nunit, this allows a consistent approach to testing for both the database and SSIS.
  4. Database tests in mstest are an elaborate hunk of junk.
    1. They don’t allow you to wrap a test in a transaction and roll it back, if you use Setup / tear down
    2. They have several layers of set up and tear down methods, one which executes for a given test and another which executes for all.
    3. There are 7 different windows to find a test, setup, teardown, initialize, cleanup, asserts and assert properties. Never mind that every test is in its own window. Needless to say, it is a nightmare to debug.
    4. Diffs cause eye-pain in source control
    5. But they do give you syntax highlighting, which is nice. I’m telling you all this so you know what horrors I’m sparing you from

The initial release of SqlTest contains the essentials for testing. Methods for:


  • executing any adhoc sql statement for setting up and running a test
  • fetching a scalar result from the database
  • executing a package in the catalog
  • faking a table and dropping the fake
  • faking a view and dropping the fake

There are certainly some other features which will be useful, like executing a package from the file system for those poor souls who’ve yet to migrate to the project model. And there are some things which are less useful, like asserts on a dataset, which tends to be very fragile. I will plan to add more in time – as well, I hope for some suggested features from you.

There’s documentation about how it works here: https://github.com/MarkWojciechowicz/SqlTest/wiki

I encourage you to try it out today, this is an excellent compliment for any SSDT database project. Just create a new c# project in the same solution.You can install SqlTest from nuget by opening the Package Manager Console in visual studio and typing Install-Package SqlTest.

A sample comes with the install, but below is a quick example to see how to use it.

Enjoy!

 using 
 System 
; using 
 NUnit.Framework 
; using 
 SqlTest 
; using 
 System.Transactions 
; namespace 
 mynamespace.Tests
{ public 
 class 
 UnitUnderTest 
{
        TransactionScope scope;
		SqlTest.SqlTestTarget dataWarehouseDb; [SetUp] 
 public 
 void 
 Setup 
()
        {
            scope = new 
TransactionScope();
			dataWarehouseDb = new 
SqlTest.SqlTestTarget( "DataWarehouse" 
);
        } [TearDown] 
 public 
 void 
 Teardown 
()
        { if 
(Transaction.Current.TransactionInformation.Status == TransactionStatus.Active)
            {
                scope.Dispose();
            }
        } [Test] 
 public 
 void 
 UnitUnderTest_Action_ExpectedOutcome 
()
        { //Arrange 
dataWarehouseDb.ExecuteAdhoc( $ 
 "Truncate table / Insert test data;" 
); //Act 
dataWarehouseDb.ExecuteAdhoc( $ 
 "Exec proc;" 
); //Assert 
 var 
actual = dataWarehouseDb.GetActual( "Select scalar value to test outcome" 
);
            Assert.That(actual, Is.EqualTo( "Expected Value" 
)); //The teardown method runs after every test and will rollback all actions in one transaction 
}  
 [TestCase("SomeValue", 1)] 
 [TestCase("SomeOtherValue", 2)] 
 public 
 void 
 UnitUnderTest_Action_ExpectedOutcome 
( string 
param, object 
expected)
        { //Arrange 
dataWarehouseDb.ExecuteAdhoc( $ 
 "Truncate table / Insert test data;" 
); //Act 
dataWarehouseDb.ExecuteAdhoc( $ 
 "Exec proc @param = {param};" 
); //Assert 
 var 
actual = dataWarehouseDb.GetActual( "Select to check result" 
);
            Assert.That(actual, Is.EqualTo(expected));
        }
    }
}
Share by: