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).
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
UPDATE) it have to be tested. More complex stuff, like
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
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
[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 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 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
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
It is very convenient to work like that.