Testing the database is important. It is absolutely required then your DAL is simply a bunch of methods that do SQL against the DB. It still required to be done then you rely on some ORM (like Linq to SQL, NHibernate etc).

Testing database

By testing the database I mean unit tests that runs database operation tasks (create, update, delete, stored procedures calls) and test asserts that operation is being completed successfully.

Even the SQL is simple enough (like a SELECT or UPDATE) it have to be tested. More complex stuff, like JOIN, UNION etc. have to tested with much more care (meaning different scenarios, different input set etc.). As more complex query you have, as more complex test you should do. I personally trying to avoid complex SQL queries, because they difficult to read and support, but in many cases is the only way you can do.

Example of test case of DAL methods that runs SELECT statement:

Using ORM simplifies life a bit. You are working with objects, creation of new record in DB is just creation of new object and call InsertOnSubmit() method. In many cases you have to trust that framework does its job correctly and do not write the tests for framework, that is simple waste of time. But what usually happens is that you have a wrappers against ORM DataContext, like a Repository and the behavior of Repository have to be validated with tests (check out about repositories here). Typically repositories interface looks like that:

All interface methods and properties are covered by tests. Moreover, if you have repositories extensions that helps you to select record by Id, or do paging they are also part of testing. Please check how this IBlogPostsRepostitory is tested by this example - example.

What else about testing database you should know?

First of all, database tests might required some initial data to be put in database before each test start. It could be easily solved by using [TestSetup] and [TestTearDown] methods of Fixture. Bad side of this that you could not customize the data for particular test, because Setup/TearDown runs the same code that you could not parameterize. I prefer to use a static method (or methods) that is placed in TestFixture and called at the beginning of unit test. Please see SubmitTenBlogpostsToRepository from previous example.

Second at all, you have to care about test isolation some how. It means that previous tests results should not affect next test cases, each tests case have to leave the database in the same state as it enters it. There are different approaches how to do that, I like something like this. I’m having FixtureInit that does kind of initialization for any test case and it contains DbSetup instance in it. DbSetup itself holds DataContext as well as TransactionScope object. TransactionScope is a very nice way of handling implicit transactions. Inside DbSetup constructor we also could put some test data initialization, as it would be shared thought all tests. Dispose methods of the DbSetup disposes a transaction without committing it. Each test case body is placed in using (var fixture = new FixtureInit("http://localhost")), so any data that is placed (or deleted) from database during the tests would not affect the database then test is finished. It is simple and works good, please check out for implementation here.

The last thing - layout and naming of database tests. Database tests have to be separated from application unit tests. It a good way of doing the testing. Database tests are usually too slow and you do not need to re-run them after minor changes (except changes in DAL). That’s why it is better to place DB tests to separate assembly. The name of assembly should contain *Database.Tests. It gives a clear understanding of assembly goal, as well as it will not be re-runned every time as you do build.bat by UppercuT

Test database

Test database is database against that database tests are running.. and typically Test Database == Developer Database, meaning the same database as developer tests the application is used for database unit tests.

It it bad in several reasons.

By doing developers testing you make database really “dirty”. Putting new records in DB or deleting existing ones, doesn’t matter. I usually do a small test that if I put new object(s) I check the count of objects in table after, like Assert.That(foundTasks.Count(), Is.EqualTo(2));. But if I Tasks table will have some data before, I’m not guaranteed that count will be 2, but Assert.That(foundTasks.Count(), Is.EqualTo(countOfObjectBeforeInsert + 2)); will be running OK. It works, but I don’t really like it, because it mess up test with some not required details.

It is possible that some complex database tests, could leave database in inconsistency state during its failure (that is actually bad and you have to design the tests to prevent this happening.. but it is happening especially with no-ORM, no-Transactions DAL). Also, one bad surpise could be if database tests deletes some data required for development testing.

So, it should be Test Database != Developer Database, test database and developer database are different instances actually. Test database must be restored with save version of developers database, but in general it is empty and its state is not changed after testing complete.

I’m using RoundhousE to deploy database. As I was describing in previous post I use initdb.bat and resetdb.bat to initialize and reset developer database. I’ve just created 2 others that do exactly the same but for trackytest database, inittestdb.bat, resettestdb.bat. In the same time, app.confing of test project is pointed to trackytest (instead of trackydb before). So now tests are running on trackytest, but the application itself on trackydb.

It is very convenient to work like that.