tldr: I have written a free vs package to deploy files quickly from SSDT to sql server, to speed up the red-green tdd cycle.
I find that writing code for sql server using TDD can be a little frustrating, the process I have is that I use SSDT to manage and deploy database projects including tests written using tSQLt, using the standard red-green cycle:
- In SSDT, Visual Studio 2013 currently, write a tSQLt test
- In SSDT I write an nunit test to call tSQLt.Run passing in the name of the new test.
- I then deploy the tSQLt test and run it and expect it to fail (Red)
- I then write the code which satisfies the test
- I then deploy the code and watch for the test to pass (Green)
- If the test doesn’t pass, I either change the code and re-deploy or change the test and re-deploy that
- The cycle continues until the test is passing correctly.
The frustrating part, for me is that building the project and deploying, although I have automated it takes quite a long time (45 seconds to 1 minute for a reasonably sized project).
To help this process I have written the Sql Tdd extension for visual studio. If you install it from:
When you right click on a .sql file, you are given two new options, the first option is to “Generate Deploy Script” which creates a .sql file which includes a drop and create script.
The second option, generates the script in the background and deploys it to your test sql server (there is a configurable connection string).
The two options replace any $(Sql Cmd Variables) that have been configured in the project using the default values.
This isn’t intended to replace the build + publish but supplement it in that you can quickly deploy single files to help speed up the red-green cycle.
Once you have the test working I would certainly recommend, building, re-deploying and re-running your tests to verify that the whole project is correct but the time you spend waiting for ssdt to update it’s internal model and deploying is greatly reduced.
To compare working with ssdt with and without this plugin, here is a comparison.
To deploy a file in ssdt:
- Write the changes
- Build the solution
- Fix any build errors, anywhere in the solution
- Perform a schema comparison
- Decide what you want to include in the update
- Update the destination
- Re-perform the schema comparison
You could just have done a publish at step 4 but that wouldn’t have been as compelling!
To deploy a file with this plugin:
- Write the changes
- Build the solution if you want to validate the model or just save the files
- Right click the files in solution explorer and choose “deploy file”
This is a really simple plugin, so don’t expect bells and whistles like only activating on certain projects I am afraid (maybe a later version) – all messages are sent to the output window so check that if you want to see what is going on.
This is published on github (http://github.com/GOEddie/SSDTExtension), if you like it and want it to do more, either ask me or fork it and do it!
There are a few articles and blogs on SSDT but if you have any tips on making the most of it, please please do share with everyone!
Disclaimer: I run it on my work machine and home machine and seems to work, please don’t cry if you get any issues, let me know and I will try to fix them!