Using SSDT to develop T-SQL code is really nice, although in my opinion it isn’t as nice as developing C# yet, it is a great improvement on using Sql Management studio alone. In this day and age all production code should be unit and integration tested and tSQLt is a great solution to help unit test T-SQL code and the two technologies together really make an excellent pair and give us all a great way to develop quickly and with the minimum amount of bugs, if your testing is done correctly.
I find that there are often parts of a database project which naturally feel like they should be done in Visual Studio SSDT such as creating tables and stored procedures and then some parts which work better in SSMS, particularly running tSQLt unit tests with the Redgate Sql Test tool. This doesn’t need to be the case and it is possible to both write and maintain a database schema completely from within Visual Studio which, I think, is one of the best development environments available.
In this post I am going to talk about how I structure my visual studio solutions and how the different parts interact to create a modern development workflow.
The major components in the projects I create are:
- Database Schema project
- Database tSQLt unit tests project, either one for all tests and the tSQLt framework or multiple depending on the size of the project
- NUnit test runner to run the individual tests.
The NUnit test runner is not strictly necessary but I find it means that I can write and run tests from within visual studio (I would highly recommend resharper to help here) and the tests can be run the same way on the Continuous Integration server. There is a tSQLt plugin for team city but there is a NUnit test runner for pretty much every single build system out there.
Database Schema project
This is simple, create a sql server project and use it to write you schema and T-SQL code!
Database tSQLt unit tests project
We then need somewhere we can put our tests, I like to keep them separate from the production code as it means you can validate the production code and run things like the T-SQL Smells tool (http://dataidol.com/davebally/2014/01/28/tsql-smells-in-SSDT-the-monster-lives/) against them without worrying about test code. To be clear I am not saying the test code should be poorly written, just that we can ensure stricter controls over production code and ensure a separation boundary.
To get the tSQLt objects into an SSDT project I can deploy, I initially deployed them to an empty database and the used the schema compare in SSDT to bring the objects into an SSDT project:
NUnit test runner
You need to have some way to run your tests, some people include a post-deploy script which calls the unit tests but I prefer to spend the extra time writing wrapper methods using NUnit so that I can run the tests either as a whole, as a class or individually. I guess that I really prefer using NUnit because I have used it a lot in my c# development and the resharper add-in for running unit tests is really nice.
To help use NUnit I typically write a test helper in C# which creates a Sql connection and callswith the name of the test to run and ensure I read in all “InfoMessages”. When you run a tSQLt test you get some messages printed to the output window, including how many tests were run successfully which you need to check, rather than relying on there being 0 errors, there must also be 1 success. You may also get an exception which will be caught by NUnit and used to report that particular test as a failure.
The helper class looks like:
Those are the major components and now to add a test, I just need to create a new schema that will be used to store the tests. In order that tSQLt knows to treat a schema as a test class you need to add the extended property:
EXECUTE sp_addextendedproperty @name = N’tSQLt.TestClass’, @value = 1, @level0type = N’SCHEMA’, @level0name = N’HumanResourcesTests’;
You will need to change the level0name to the name of your test schema.
If the unit test and main schema are in different databases then I will also need to add a database reference and use the fully qualified name to get to the other database, this is really trivial and means you get intellisense in SSDT and can run the procs in the same database when deployed.
I am then free to add a test, the new test should be in the schema of the test class, in this case HumanResourcesTests. The test name should also begin with the word “test”:
Finally I add the NUnit test so that the test helper can be called to run the actual test:
To mock or fake a table tSQLt currently only supports tables in the same database so you need to deploy all of your code into the same database. You should ideally create a new database, insert any needed data and fake any tables you will interact with and then destroy or have some system to tidy up old databases.
To run the tests you need to deploy the main schema, then deploy the tSQLt project and your tests, when they have been deployed you can then run the NUnit tests, hopefully you are using TDD to write your T-SQL code so the test will fail, you then write the code to make it complete then re-deploy and re-run the tests and you should see little green ticks:
Including these tests into a continuous build process is quite simple, you just need to get your build system to use the dacpac files created by the SSDT projects, deploy those and then use whatever calls the NUnit tests to call these tests. Because you are using NUnit to run the tests, any failures and messages will work as if the tests were pure c# code.
I am sure there are lots of other ways of working with SSDT and tSQLt but this is how I use it and it feels really good to write, clean, unit tested T-SQL in a modern development environment (intellisense, find references etc).
I have put the sample project in this demo on github, feel free to grab it:
by Ed Elliott
Want to know more? Visit Ed’s blog https://agilesql.club/blogs/Ed-Elliott