How should methods updating database tables be unit tested?

Question

I have an application that is database intensive. Most of the applications methods are updating data in a database. Some calls are wrappers to stored procedures while others perform database updates in-code using 3rd party APIs.

What should I be testing in my unit tests? Should I...

1. Test that each method completes without throwing an exception -or-
2. Validate the data in the database after each test to make sure the state of data is as expected

My initial thought is #2 but my concern is that I would be writing a bunch of framework code to go along with my unit tests. I read that you shouldn't write a bunch of framework code for unit testing.

Thoughts?

EDIT: What I mean by framework is writing a ton of other code that serves as a library to the unit testing code...not a third party framework.

Answer 1

I do number 2, i.e., test the update by updating a record, and then reading it back out and verifying that the values are the same as the ones you put in. Do both the update and the read in a transaction, and then roll it back, to avoid permanent effect on the database. I don't think of this as testing Framework code, any more than I think of it as testing OS code or networking code... The framework (if you mean a non-application specific Database access layer component) should be tested and validated independently.

Answer 2

If the update logic is complex then you should do #2.

In practice the only way to really unit test a complex calculation and update like say, calculating the banking charges on a set of customer transactions, is to intialise a set of tables to known values at the start of your unit test and test for the expected values at the end.

Answer 3

I use DBUnit to load the database with data, execute the update-logic and finally read the updated data from the database and verify it. Basically #2.

Answer 4

You must test the actual effect of the code on the data, and its compliance with the validation rules etc., not just that no exceptions are raised - that would be a bit like just checking a procedure compiles!

It is difficult testing database code that performs inserts, updates or deletes (DML), because the test changes the environment it runs in, i.e. the database. Running the same procedure several times in a row could (and probably should) have different results each time. This is very different to unit testing "pure code", which you can run thousands of times and always get the same result - i.e. "pure code" is deterministic, database code that performs DML is not.

For this reason, you do often need to build a "framework"to support database unit tests - i.e. scripts to set up some test data in the right state, and to clean up after the test has been run.

Answer 5

There's a third option, which is to use a mock database-access object that knows how to respond to an update as if it had been connected to a live database, but it doesn't really execute the query against a database.

This technique can be used to supplement testing against a live database. This is not the same as testing against a live database, and shouldn't substitute for that kind of testing. But it can be used at least to test that the invocation of the database update by your class was done with proper inputs. It also typically runs a lot faster than running tests against a real database.

Answer 6

If you are not writing to the database manually and using a framework instead (jvm, .net framework, ...), you can safely assume that the framework writes to database correctly. What you must test is if you are using the framework correctly.

Just mock the database methods and objects. Test if you are calling them and retrieving data back correctly. Doing this will give you the opportunity to write your tests easier, run them much more faster and make them parallel with no problems at all.

Answer 7

They shouldn't be unit tested at all! The whole point of those methods is to integrate with the outside world (i.e. the database). So, make sure your integration tests beat the you-know-what out of those methods and just forget about the unit tests.

They should be so simple that they are "obviously bug-free", anyway – and if they aren't, you should break them up in one part which has the logic and a dumb part which just takes a value and sticks it in the database.

Remember: the goal is 100% test coverage, not 100% unit test coverage; that includes all of your tests: unit, integration, functional, system, acceptance and manual.