This is part of a series on how to take the Adventureworks database and bring it in line with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.

See the introduction: https://the.agilesql.club/Blog/Ed-Elliott/A-reference-for-creating-a-con...

Add Unit Testing

Ok so this is going swimmingly, we have our code and static data out of the database and pushed to github, wowsers.

To build a CI process we really need unit tests, in fact we need unit tests with or without a CI process but that is another matter!

I am going to show how to add tSQLt to an SSDT project but first lets talk about why tSQLt and why not some other framework.

Why tSQLt?

tSQLt is awesome, it has loads of benefits but there is one feature that sets it apart and if all it did was this then I would still recommend it! The thing that makes it so great is the ability to fake tables which makes unit testing so simple.

What is faking?

It doesn't sound much but makes a massive difference, what it does is to rename the existing table and create a new one with the exact same name without any foreign key constraints and optionally without any constraints at all.

That doesn't sound cool, that sounds the opposite of what I want!

Consider a stored procedure that uses the SalesOrderDetail table and takes a SalesOrderId and aggregates all the LineTotal values (this is an actual table in Adventureworks), without being able to fake the table, to test the procedure you would need to create a SalesOrderId, one or more ProductId's and if to create those took a another load of record creations even before we have run our stored procedure we would need to write a whole load of setup code.

In relational databases you could easily spend most of your time writing inserts for related tables rather than just inserting the data we need into the SalesOrderDetail table, namely the columns that the stored proecure uses which is likely SalesOrderId and LineTotal.

So it is you being lazy?

Yes. No. Well sort of. No it isn't it is the correct way to do unit testing, I setup the exact data I need for the test, excercise that data and then check the results, being quicker is a nice side effect.

What if a contraint is really really important

You can get tSQLt to keep constraints or don't fake it but I would question what / how you are unit testing.

Ok i'm convinced

Awesome, leave your money in the hat and move on.

How do I get tSQLt into the SSDT solution?

tSQLt is distributed as a set of t-sql script files, grab them from http://sourceforge.net/projects/tsqlt/ and then create a new database called tSQLt and deploy those scripts to it. Now the foolhardy amongst you may well think you can just export the dacpac from SSMS and you can, but it won't work because tSQLt references master so you need to add a reference from the tSQLt project to the master dacpac but SSMS will export it without adding the reference.

Instead, create a dummy tSQLt project, import the code from the database, build and either save the project or throw it away. The next time you will need it is when you upgrade tSQLt and you may as well just create a new project and throw that away again.

When you have imported tSQLt it will still fail to build so add a reference to the system database "master" and then it should build and create a dacpac you can use in your actual project.

So where are we?

We created a database, deployed the tSQLt code to it and then created a dacpac from a temporary SSDT project, we will reference this in our test instances so the dacpac will get deployed along with our tests.

We could have imported the scripts into our main SSDT project but we don't want to modify the tSQLt code we just want to use it and putting it in a dacpac and referencing that is the perfect way to do it.

Once you have added a reference it still won't build, drop the assembly and re-add it as a script from the tSQLt deployment scripts and everything shall be fine.

Now add it to the project

In the solution I have created a test folder and created a new SSDT project called AdventureworksCI.UnitTests, I have also taken that dacpac and added it to the solution - ideally tSQLt would be a nuget package but there isn't one so we need to manually add it to our solution to share it easily.

Once I have added the dacpac to the solution I add a database reference from the unit test project to tSQLt.dacpac:

The reference is added as "Same Database" so they get deployed together, don't worry they will never be deployed with the actual project. The final step is to add a database reference from the AdventureworksCI.UnitTests project to the AdventureworksCI project again marking the reference as "Same Database".

When I add the tSQLt reference to the unit test project it could no longer build as it needed a refernce to master so what we end up with is this:

Let's deploy

At this stage we should check that we can deploy the test project and when we do we end up with a database that contains, all of AdventureworksCI and tSQLt. Firstly we will need to go to the properties of the UnitTest project and set the database settings to enable Trustworthy and also enable the clr in SQL. You could add an sp_configure / reconfigure to a pre/post deploy script to do it if want to be sure it will always be enabled.

If you right click on the AdventureworksCI.UnitTests project and choose "Publish..." then enter in the connection details and click "Publish", hopefully that should publish and as well as the AdventureworksCI objects you should have tSQLt, run "exec tSQLt.RunAll" - there are no tests to run but will ensure the framework is basically working:

Done

So that is it, we now have a solution with a unit test project and framework that deploys, it is checked into github, to get this specific version go to:

https://github.com/GoEddie/AdventureworksCI/tree/v0.2

To get the latest version with everything in (so far) go to:

https://github.com/GoEddie/AdventureworksCI/