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.
Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like:
- Main.dbproj - actual production code and reference data
- Main.UnitTests.dbproj - unit test code + references to tSQLt etc
The unit tests have a “Same Database” reference pointing to the main project so it is as if my tests were in the same project.
To deploy the unit tests I deploy the unit test project and use option to include composite projects, this then also deploys the production database. When I go to other environments I can deploy the production code without the unit tests. This works really well except when you deploy like this, only the pre/post deploy scripts from the dacpac explicitly being deployed are used. In this case, the scripts in the unit test project are run but not the ones in the main project but when the main project is deployed by itself the scripts are deployed.
To fix this issue what I do is to have an msbuild copy task in the unit test project:
What this means is that the script from the main project is always copied into the test project and so I know that it is always run.
If you do this you need to ensure that people don’t use the script in the test project as the changes are overwritten every build, people don’t normally make that mistake more than once!
It is a little clumsy but a pretty simple solution.
There are cases where this doesn’t work and more complicated things you can do include:
- Before you deploy, iterate through all the dacpacs and use the .net packaging api to pull out the pre/post deploy scripts then order them as you want and write them to your main dacpac.
- Write a deployment contributor to do it but there are challenges
I have put a sample on github:
Hopefully it helps someone else.