tSQLt

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