I am searching for days now on several forums, blogs, MSDN etc. but I was not able to find any guidance on this topic so far. I will try to explain this post in a more detailed manner because I think information and documentation of SSDT development is not well documented and there exists no best practice document like VS 2010 database projects (http://vsdatabaseguide.codeplex.com/).
I am a C# developer (no DBA) and we are in the beginning of the development phase of a green field project (10 – 15 developers) and we are currently defining our development process including handling of database development.
The technology and tool chain we want to use:
- EF 5 (model first, maybe we change this to database first because issues like views, indexes etc. are much easier to handle)
- SSDT (SQL Server Data Tools)
- VS 2012 / TFS 2012
- MS Test for automated unit / integration tests
The development process is based on test driven development and looks like this:
- Each feature is developed by one developer on a separate feature branch
- Design and implement unit tests (=feature implementation)
- If a feature requires database access then the developer has to a) create / update the EF model b) create the localDB database via EF´s „Generate database from model“ c) create / update the SSDT project via schema compare d) create the unit tests with a test initialize method that creates a fresh database and according test data for each test
- Merge the feature branch back into integration branch
- After checking in the merge the CI build executes the unit / integration tests
So there are some points I am not 100% sure about how to solve them (especially database handling with unit tests) and I would appreciate if you can put me in the right direction:
How to solve the database creation for the automated unit tests:
a) Execute the SQL database generation script (which can be manually created previously via SSDT publish feature) for each executed test method? This is the option I would prefer because each test has a clean and consistent database state for each test. Is there a performance problem creating the localdb database for each test?
b) Or use msbuild task „SQLPublish“ or „sqlPackage.exe“? I think this is not the option to go for because this would be a one time thing and I want to create a new test database for each unit test.
c) Or create the test database manually and save the *.mdf file to the root of the source control folder and create a copy for each test? But this I would not prefer because a developer A could override the file which could have changes from another developer B who checked in his changes before. And this means that the developer
How to solve test data creation for the automated unit tests:
a) Execute test specific SQL script that inserts the appropriate test data for each test. I think this also means to create a new database as mentioned in point 1. Again this is my preferred option.
b) Or using EF to create test data seems not to be a clean way, because this depends on the EF model implementation which should actually be tested implicitly through the feature unit tests.
c) Or use manually created test database files. But this would make the development process more complicate for the developer. And this could also be overriden by other developers check ins.
Maybe its good to mention what we expect from our unit tests to be.The goal of our unit tests is not to test the database schema like stored procedures and so on. We want to test parts of our application features using „code“ unit tests that can also be seen as integration tests.
So does anyone of you have a similar development process and what are your experiences? Any recommendations to improve our development process? Are there any resources out there or best practices documents on SSDT development? And the most important question for me, how did you solve the automated unit testing including proper database handling and integration tests?