Database Unit-Testing. What is the best database testing approach for enterprise solution running CI, having branches and heavy databases?

Question

So situation is following: We have some very old product, that has dozen of solutions. Many of them run different unit tests, some MSTest, some NUnit. There are lot of tests that test database, also some depend on data. In other words they execute code to check if customer Andriy has orders with products A and B. I know that this is wrong, but I as said this product was developed by many teams in different times.

Currently many teams are continuing doing changes to projects and changes are addressed to different branches. Build environments are established in CI manner. So we face issue with maintaining those tests. We spent too much time and efforts on keeping tests running. Because databases with test data are really heavy we cannot have separate database for each branch, but we can keep clear sample databases (200Mb) for each of branches, but in this case many tests will fail.

I already thought about this and have some preliminary plan like:

1) Getting rid of data-dependent tests, by either removing them or (if possible) rewriting to insert data and rollback immediately after test executed.

2) Move all database-specific tests in separate projects - i.e. separating Integration tests from usual Unit Tests

3) Create sample database per each branch and run integration tests separately.

I would love to hear from you how similar situations were resolved within your projects. Did you separate integration tests and did you ever have data specific tests. Is it recommended to have data-dependent tests at all? Any suggestions will be appreciated.

Answer 1

For our current project we've separated CI- from Unit-tests, exactly the way you describe and it's working well. By moving to NHibernate we are free to use an in-memory database (SQLite) to test data-dependent code that can't easily be mocked*, but we also have some integration-type tests running against the test-server-local SQL Server. All those tests specify the MbUnit Rollback2-attribute and both insert the data they need and query it in the same round, so that the database doesn't get modified by the tests.

NUnit + SharpTestEx are used for the unit tests.

*Such as the test that test collection cascading mappings and sets, IEquatable and that the legacy-table schema is correctly mapped.