tSQLt

Where do you install tSQLt?

  • Posted on: 19 October 2017
  • By: Ed Elliott

The question of where to install tSQLt is probably the most common question I get when I talk about unit testing T-SQL, so much so that I thought that it would be a good topic for a blog. I mention tSQLt in the title, but this covers all unit test code for T-SQL.

So to be more specific:

Where do you install unit tests and unit tests frameworks for SQL Server, which databases should have that code in them?

If we look at what databases might exist in a development process:

  • Local developer database
  • CI database on a build server
  • Test database for testers
  • QA database for user acceptance testing
  • Prod mirror or Pre-Production database for a production standby database
  • Production database

If we have tests written in tSQLt and the tSQLt framework itself then where do you install that and those tests and execute those tests?

TLDR: The answer is quite simple, in these two database types:

  • Local developer database
  • CI database on a build server

Not TLDR:

The first thing to say is, what are unit tests?

There are two top goals for unit tests:

The first is that unit tests are small pieces of code that validate that other small pieces of code work successfully.

The second goal is that unit tests guard against other developers breaking the actual pieces of code covered by the tests - you might well be the other developer in this case so I would always recommend not getting too worked up about this :).

In the list of databases, these two types are the only database types that are private and the type of databases that can typically be thrown away on a whim.

A local developer database should sit on the developer's machine and allow a developer to deploy and test their code locally without affecting anyone else. I have worked on projects with this setup, and it is easily the best to work with as a developer.

If you aren't able to have your database locally but have to either use a remote database or even worse, a shared database for testing, then you will likely get hit by other peoples breaking changes or other people debugging their code in some way blocking you from working.

When you have a shared database you have to coordinate all sorts of things like the best time to deploy and that itself is a massive time sink.

The second type of database, one for a CI build is typically only used for a single build at a time. Because a SQL Server database is, mostly, free to create on an existing server, there is no need to share CI databases. When you think about the fact that you can use Local DB to quickly and cheaply spin up a new clean, empty, fresh, happy database, why wouldn't you use this unless there was an excellent reason not to?

It is in these private databases that we can do things like deleting all the data in a table, setting all the dates of birth to a developer's favourite date or create a long-running transaction that blocks out all of the other users.

Once we move further right in our list of environments, we are less likely to want to do things such as deleting all the data in a table, and once we hit production, it is critical we don't leave test code like that in the database to be accidentally called.

Tests for a production database should be a series of smoke tests which can operate in and around the existing live production calls and data so they do not take extra transactions than the standard database does and they certainly don't modify data, other than their own.

These smoke tests are also likely to be carried out via the application, so a deploy happens and a set of application tests are typically executed to validate that the deploy has been a success - this is a long way from where we use unit tests to validate and guard code against breaking changes.

Anyway, enjoy!

tSQLt Test Adapter for Visual Studio 2017

  • Posted on: 3 October 2017
  • By: Ed Elliott

So Visual Studio 2017 has been released a while and I had created a version of the test adapter (which lets you run tSQLt tests from within Visual Studio with SSDT, ReadyRoll or just plan sql scripts using the test window but I held off on pushing it to the marketplace as it didn't work with Visual Studio 2015.

I spent quite a lot of time trying to work out how to build a single vsix that worked with VS 2015 and 2017 and in the end gave up and now there are two versions, one for 2015 and one for 2017 - I am not putting any fixes into the 2015 version so I would upgrade to 2017 if you want any updates.

I also fixed a couple of issues and one nice little problem with my .net tSQLt client where if you passed in the name of a non-existant schema or test, the tests would return success:

https://github.com/GoEddie/tSQLt-Clients/issues/2

These now report a failure and handle extra select's in the output from tests. Because the test adapter uses the tSQLt client, that gets the benefit of any fixes I make there (isn't hosting individual repos on github awesome for even sharing your own work!)

If you want to use visual studio test window to run your tSQLt tests and have the aweomsness of this:

Then grab it from inside visual studio "Extensions and "Updates" on the tools menu.

Learn how to unit test SQL Server T-SQL code

  • Posted on: 5 September 2017
  • By: Ed Elliott

A free email course on how to use tSQLt including the technical aspects of writing unit tests AND the art of writing repeatable, useful unit tests for even the most complicated T-SQL code

UPDATE: I thought that if I got a certain number by October 1st I would run the course but in two days I had three times the amount of people subscribe than my initial target so I have closed the first course, sign up if you want to join the wait list or the next course!

Unit testing helps us to write better code, make rapid changes to our code and has been generally seen as a good idea for about 10 years. Writing tests for T-SQL code is made much easier by using tSQLt but there is quite a high barrier to entry both in terms of the technical skills in getting tSQLt running and also how to approach large code bases of, sometimes, unfriendly T-SQL code and taming the code with unit tests.

I have successfully unit tested T-SQL code in a number of different environments including clean greenfield environments as well as legacy projects and I have written this course to help people get started with unit testing but also help them to turn unit testing into a part of their development process that they can use everyday to improve the quality of their work and the speed at which deployments can be made.

Are you any of these people?

  • An application developer experienced with other testing frameworks for testing application code?
  • A T-SQL developer with no testing experience?
  • A -TSQL developer with testing experience in other languages or frameworks?

If you are then you should sign up (https://www.getdrip.com/forms/317903840/submissions/new) and let me help you learn tSQLt unit testing for SQL Server.

Why an email course?

I thought it would be an interesting way to provide actionable information regularly and to allow a level of assistance and feedback that I don't think is possible with blogging or writing articles.

How do I sign up?

Run over to: https://www.getdrip.com/forms/317903840/submissions/new and pop in your details.

The course is going to start on the 1st of October and as it is the first one I am limiting the amount of people who can start it. If the first one is a success then I will run it again but it won't be until at least 2018.

What will be the format?

The course will be one email a week which will include an overview of the weeks topic, some detail into the parts that need it and an excercise for the week which can be done on a demo database or any SQL Server database code you have.

and it is free?

yep, gratis. I am not open sourcing yet - maybe in the future but the course itself is free, aka "no service charge"

tSQLt Visual Studio Test Adapter

  • Posted on: 17 August 2016
  • By: Ed Elliott

tSQLt Visual Studio Test Adapter

What is this?

This lets you use Visual Studio to run tSQLt tests easily. Visual Studio has a built in framework for finding and executing tests so that if you have tSQLt tests in an SSDT project for example, although this just requires you have the .sql files in source control and does not require ssdt - you can easily see and execute your tests via the Visual Studio Test Explorer window. It is also available for use in your favorite build server via the vstest.console.exe tool or if you have vsts then you can use the default test task.

What does it look like?

Two screen shots, the first is the tSQLt sample tests in Visual Studio:

tSQLt tests are shown in the Visual Studio SSDT project

A couple of things to mention, firstly if you double click on the test in the Test Explorer window it will jump you to the test in the code (whoop). Secondly if you run a test and it fails you get a failure messages (it gives you a red cross) and clicking the test shows the tSQLt error message below.

This screen shot is the test running on vsts:

Output of tSQLt tests on vsts

Oooh that looks shiny! You see the step failed because the tests failed (I hadn't actually deployed them whoopsie). You get some nice graphs and to get this all you need to do is add the adapter to the project and configure the "Test Assemblies" task.

How do I use it?

To run tests you will need a .runsettings file and select it using "Test --> Test Settings --> Select Test Settings File" in the runsettings file you will neeed at a minimum the connection string to connect to:

<?xml version="1.0" encoding="utf-8"?>
<RunSettings>
<TestRunParameters>
<Parameter name="TestDatabaseConnectionString" value="server=.;initial catalog=tSQLt_Example;integrated security=sspi" />
<Parameter name="IncludePath" value="AcceleratorTests" />
</TestRunParameters>
</RunSettings>

If you run your tests and get an error with the connection string not being set make sure you actually have the runsettings file attached.

If you have a large project you really will want to limit the processing of tests to just test files so you can add a filter which tells the adapter to only parse files under paths that match a specific regex. To discover tests we need both the test procedure and also the schema that defines the test class so if you use a filter ensure that both are included.

Once you have your run settings, install the Visual Studio extension:

https://visualstudiogallery.msdn.microsoft.com/cba70255-ed97-449c-8c82-e...

If you want to run this on VSTS then you can either download the VSIX, extract all the files and check them into a folder in your solution or use nuget to add the package AgileSQLClub.tSQLtTestAdapter (aka https://www.nuget.org/packages/AgileSQLClub.tSQLtTestAdapter/0.59.0) to your solution. Nuget doesn't support SSDT projects so you will need at least one .net dll which can do nothing at all except reference this package. Once you have the test adapter in your project then configure a test task like:

Configuring the test task on vsts

The things to note here are you can either add a filter to the runsettings file or you can filter which .sql files are passed to the test adapter, you will need to make sure both the schemas and tests are passed in otherwise we can't work out what is a test and what is a stored procedure.

An Oddity

Because of the way the test adapter framwork runs and discovers tests and the way that us sql developers like to seperate our schema's and tests into different files it means I need to add a fake test with the name of the test class if you try to run it you will get a message "Not Run" and it won't do anything but all of the individual tests will work. I tried to make this generic so you don't need SSDT to run and ironically if I had relied on dacpac's it wouldn't have been a problem!

What else?

It isn't the full expression of what I would like to do with this, there are a couple of things I will add in the future but this is a start, these are:

  • Ability to display the result sets rather than just the outcomes
  • In SSDT I would like to deploy any changed objects that are referenced by a test so you make your changes, then run the test and the code is dpeloyed and then run - cool hey!

Minimum Requirements

VS 2015, Update 3 - if you use an earlier version you will get slots of "0 test cases found" messages - if you are desperate for an older version of vs let me know and I will take a look but I am not planning on supporting older versions unless there is a good reason.

Open Source

Of course this is open source, it will be on;

https://github.com/GoEddie/tSQLt-TestAdapter

Any issues shout!

ed

Create stub tSQLt tests in SSDT from stored procedures

  • Posted on: 17 November 2015
  • By: Ed Elliott

I have released another tool as part of the ssdt dev pack, what this does is create tSQLt classes and stub tests. If you open a stored procedure in ssdt and then do "Tools--> SSDT Dev Pack--> Create tSQLt Class" or you can create a keyboard mapping in "tools->options->keyboard", what I do on my machine is map "Tools.CreatetSQLtClass" to Ctrl+K Ctrl+S - it will create a schema with the name of your stored procedure and the correct extended property for tSQLt to register it as a test class.

If you then again either do "Tools--> SSDT Dev Pack--> Create tSQLt Test" or as I have, map it to Ctrl+K Ctrl+T - it will create a tSQLt test but with any tables referenced in the procedure added as fake tables, any parameters the procedure requires defined and an exec on the procedure.

This will build test stubs for stored procedures and table valued functions today, there is no reason not to support other things like scalar functions, I just needed those two first.

If you have the old version installed you can just install the latest vsix and it will upgrade it (https://github.com/GoEddie/SSDT-DevPack/tree/master/release). You will need to restart visual studio though :(

This doesn't add the reference to tSQLt so you will need to continue to do that as you do it today.

To build the tests I need the compiled dacpac so if you add a procedure or change it make sure you build the project before trying to add the test otherwise you won't get the correct fake tables (or anything at all).

Walkthrough

If you have this code:


create procedure [dbo].[get_customer]( @customer_id int)
as
select * from customer where Id = @customer_id
go

and this project structure:

note you do need a folder to put the tests into, they can't go into the root of the project.

Choosing "Create tSQLt Schema" with the procedure open causes this dialog to pop up:

If you choose the target folder, in this case if I choose "Tests" then it will create sub-folder under that called "get_customer" where the schema will be created. You can change the name of the schema but if you do the tests will not be created in the correct place - I will fix this in a later version or feel free to do it yourself via a pull request. To manually work around this just rename the schema of the tests when they have been created.

If you click on "OK" the new folder and schema will be created:

This is the way I like to structure my tests, I have a folder where I put the schema (class) and all the tests, it seems a logical way for me to group the tests - if someone has a different way then let me know and I will see how easy it is to incorporate it.

If you then go back to the test and choose "Create tSQLt Test" (or use the shortcut you map) then again you will get the similar dialog to the schema, choose the new folder that was created and the test will be created there for you. You can override the default test name then click OK and the new test should pop up in Visual Studio:

The test proc will look something like:


CREATE PROCEDURE [get_customer].[test get_customer does something]
AS
EXECUTE tSQLt.FakeTable 'dbo', 'customer';
DECLARE @customer_id AS INT = 0;
EXECUTE [dbo].[get_customer] @customer_id;
EXECUTE tSQLt.AssertEquals 'TRUE', 'FALSE', N'Error Not Implemented';

Every table referenced will be faked, there are probably other things we should fake but this feel like a good start to help with some of the overhead that tSQLt tests have.

I hope this is useful and I intend to keep adding to this so if you have any ideas or bugs let me know!

If anyone wants any help with testing or SSDT in general feel free to give me a shout!

UPDATE

I have released a new version of the dev pack, grab the latest one from: https://github.com/GoEddie/SSDT-DevPack/blob/master/release/SSDTDevPack....

For full details see https://the.agilesql.club/Projects/SSDT-Dev-Pack and https://the.agilesql.club/blog/Ed-Elliott/2015-11-22/Highlight-Expensive...

ed

Why is my tSQLt test not running?

  • Posted on: 10 September 2015
  • By: Ed Elliott

I wrote a test earlier and forgot one of the two cardinal rules about tSQLt so I was running my test suite and the test did not run, I re-deployed and it still didn't run so I thought I would create this handy list of things to check that mean that even when the tests exist in the database they do not run:

  • Are the tests in a schema that has the tSQLt test class extended property?
  • Does the name of the test start with the word "test"? If it doesn't then it will not actually run

Test schema extended property

You need to create a schema which is basically the test class:

CREATE SCHEMA [thingy_a_bob]
go
EXECUTE sp_addextendedproperty
@name = N'tSQLt.TestClass'
, @value = 1
, @level0type = N'SCHEMA'
, @level0name = N'thingy_a_bob';
go

If the lovel0name does not match the name of the schema then it won't actually apply to it so make sure that it is correct.

test name

The test MUST begin with the word test so this is valid:

create procedure [thingy_a_bob].[test this does something cool]
as ...

but this is invalid:

create procedure [thingy_a_bob].[this does something cool]
as ...

If this doesn't help?

If you have checked both of these things then go back to making sure that the test is actually deployed!

AdventureworksCI Step 5 Adding tSQLt to the solution

  • Posted on: 22 July 2015
  • By: Ed Elliott

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:

add db reference 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:

add db reference to tSQLt dacpac

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:

add db reference to tSQLt dacpac

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/

For better Sql code use fake tables

  • Posted on: 18 November 2014
  • By: Ed Elliott

When unit testing code there is a really powerful feature that is either called mocking or faking or sometimes using stubs and what these mean is creating objects that look like real objects but aren't.

Why would you want something that looks like something but isn't?

Unit testing means breaking your code into small chunks and testing that the small chunk does what it is meant to. Testing small chunks of code in isolation is difficult as they often call other chunks of code so we test and pretend that the other thing is correct and to validate those other small chunks, we unit test them.

What the hell does that mean?

If we look at an example, we have a schema in sql that looks like:

schema showing tables with loads of foreign keys

We want to write a unit test for a stored procedure that looks something like:

proc showing a couple of inserts we don't care about

If we run the stored procedure, it will insert data into the audit log table and the current run processing log table. The audit log insert requires a foreign key which should be individually unit tested - all we care about for this test is whether or not the stored procedure returns the id for 'Spice Girls' when called.

If we just call the stored procedure, it will run through all the code and validate all of the check constraints and all of the foreign keys, the problem is that this is:

  • Slow
  • Means we have to make sure we have all the data in all reference tables setup
  • If there is an error in one of those constraints or foreign keys, this test will start failing - instead we only want the tests that are actually covering those parts to break

This is why we create fake or mock objects. In tSQLt this is done using FakeTable and FakeFunction - you simply give it the name of the table and it effectively gives you a new table (it renames the existing one) with the following definition:

schema showing original tables and the new faked table

This means that there are no extra bits of code getting in our way and we can test what we actually need to. Instead of the setup to this test, inserting test data or checking for test data in all of the separate tables we can simply insert the values we will use in the columns we will use.

Once you have all the different items of code under their own unit test then if something breaks you can quickly see what it is and fix it.

The complete test for this procedure might end up looking something like:



Unit tests help document your code!

  • Posted on: 2 November 2014
  • By: Ed Elliott

I recently had the chance honour to spend twenty minutes talking to Boris Hristov about testing with Sql Server for his Google Hangouts series, catch the video here
One of the things I mentioned was that having a suite of unit tests helps to document the code and in this post I would like to demonstrate that. If we take the first stored procedure in the AdventureWorks2012 database to see how it is documented, if we take the procedure definition, we get:

If we look at this in terms of documentation, if we start with the name “uspGetBillOfMaterials”, I guess usp means it is a stored procedure and it gets a bill of materials. There aren’t any comments in the header. If we then look at the parameters we have StartProductID and CheckDate which help to describe how it is used.

We then have three extended properties, the first is most useful for documenting the procedure:

“Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.”

Finally we have a comment in the procedure itself:

“Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly) The CheckDate eliminates any components that are no longer used in the product on this date.”

For me, although this gives a technical description of what is happening, I have to look in three different places to get the overview and it doesn't really explain how the procedure works and what it should and should not do. If instead of extended properties and a comment inside the procedure we had a set of test stored procedures called something like:

[uspBillOfMaterials][test returns all level 1 components of a level 0 assembly]
[uspBillOfMaterials][test returns all level 2 components of a level 1 assembly]
[uspBillOfMaterials][test does not return level 2 components of a level 0 assembly]
[uspBillOfMaterials][test does not return components no longer used by CheckDate]

These tests tell us what the procedure should do and shouldn't do additionally if we always have a set of tests it makes it much easier to explore your T-Sql code. To highlight the difference a good set of tests can make, take these two screenshots - which one is clearer and documents the procedure better? When you look at both of these consider that this is a pretty small procedure, 5000+ line procedures are pretty common (unfortunately!) so if this was more complicated, there could be more tests, possibly separated into sections and there could be more comments anywhere in the code or even worse and very common is no comments or misleading comments:

list of tSQLt tests in SSMS

-or-

Hopefully this has helped explain why having unit tests helps to document code and as I said on the hangout, if you have any questions about testing with Sql Server please ask as I know we can figure out a way to use unit testing to improve the life of Sql DBA’s and Developers!

&nbsp