If you write code to SQL Server then you might be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 300 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.
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:
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:
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”?>
<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:
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:
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.
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!
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!
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.
Of course this is open source, it will be on;
Any issues shout!
February 19, 2017 - 02:10
Can you elaborate more on installation of the VSIX package? For example I have a solution with structure:
- Database project (A)
- Test database project referencing Database project & tSQLt dacpac (B)
Installing the VSIX extension doesn’t show the tests in B project. Or should I add new dummy project © with your NuGet package installed in it?
February 23, 2017 - 12:56
Did you add a runsettings file with a valid connection string in it?
March 1, 2017 - 12:07
Yes I copied SQL Server connection string from existing project which points to locally installed SQL Server instance
March 3, 2017 - 21:24
Multiple Test Executions
Ed, First, thanks for this. It’s very cool.
Second, I’m running this in VSTS and when I do so, I get multiple executions of each test. I have a separate test project from my main project, though when I execute tests in VSTS, I see one of each. In VSTS, I see 9 of each. Any ideas?
I’m using this with the ReadyRoll project type, and since the various items execute in numerical order, I need my test class files to be named dbo.aaa_someclass.sql in order for them to execute before the tests. My issues, but thought you might like to know.
March 7, 2017 - 22:52
Hi Steve - thanks, not sure i
Hi Steve - thanks, not sure i’ll have a look!
March 6, 2017 - 22:37
tSQLt Test Adapter, searching for tests…No test Found
Can you please explain step more clearly. I tried to run this but project is unable to find any project.
March 16, 2017 - 21:17
this seems very promising. I created tsqlt dacpac and added reference into my empty database project.Then i tried to connect with local TSQLt_Example db. I wasn’t find any test. Did I missed something. Please suggest.
March 28, 2017 - 15:33
Not sure what you might have
Not sure what you might have missed - can you give more details of what you were doing?
May 9, 2017 - 10:01
Discover Tests failing
I have a project where i added runsettings file and I have also installed testAdapter. Now i have added a sql file in my project which is tsqlt example test stored procedure. tSQLT Test Adapter runs but it doesnt find any tests. Am i missing something ? What does the includepath in runsettings mean?
May 31, 2017 - 12:12
hey sriram, the include path
hey sriram, the include path is used to stop use having to parse the entire project to find tests - if you put all your tests in a path like:
then a good include path would be “tests” - it is a regex so to get it working you can try with .* but it will mean we have to parse the whole solution each time so try to use include path to just find files which might have tests in.