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:
<Target Name="BeforeBuild">
<Message Text="Copying Post Deploy Script" Importance="high"/>
<Copy SourceFiles="$(SolutionDir)\DeployScripts\Deploy\PostDeploy.sql"
DestinationFiles="$(SolutionDir)\DeployScripts.UnitTests\Deploy\PostDeploy.sql" OverwriteReadOnlyFiles="true"/>
</Target>
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:
https://github.com/GoEddie/PostDeployCopy
Hopefully it helps someone else.
Ed
