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:

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:

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!

Comments:

Hendra Saputra

February 19, 2017 - 02:10

VSIX Installation

Hi,

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 (C) with your NuGet package installed in it?

Thanks

Ed Elliott

February 23, 2017 - 12:56

Hi Hendra,

Did you add a runsettings file with a valid connection string in it?

Hendra Saputra

March 1, 2017 - 12:07

Hi Ed,

Yes I copied SQL Server connection string from existing project which points to locally installed SQL Server instance

Thanks

Steve Jones

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.

Ed Elliott

March 7, 2017 - 22:52

Hi Steve - thanks, not sure i

Hi Steve - thanks, not sure i’ll have a look!

gaurav sahariya

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.

gaurav

March 16, 2017 - 21:17

HI Ed,

HI Ed,
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.

Ed Elliott

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?

sriram

May 9, 2017 - 10:01

Discover Tests failing

Ed,
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?

Ed Elliott

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:

c:\solutionRoot\project\tests\test1

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.