Richard Dallaway notes about database unit testing inspired me the realization of the DbUnit framework. I think this is a very good text about this subject. Hopefully he gave me the permission to present excerpts from his notes here.
The original text is much longer and I strongly suggest you to read it as well. See resources for reference to it. - Manuel Laflamme
These are my notes on how I've gone about unit testing database functionality.
The problem is this: you have a SQL database, some stored procedures, and a layer of code sitting between your application and the database. How can you put tests in place to make sure your code really is reading and writing the right data from the database?
I'm guessing some, if not a lot, of database development goes like this: set up database, write code to access database, run code, do a SELECT to see if the records showed up in the database. They did? Good, then we're done.
The problem with visual inspection is this: you don't do it often, and you don't check everything every time. It's possible that when you make changes to a system, maybe months later, you break something and some data will go missing. As a coder you may not spend much time checking the data itself, so it may take a while for this mistake to surface. I've worked on a web project where a mandatory field on a registration form was not being inserted into a database for the best part of a year. Although marketing had protested that they needed this information, the problem wasn't spotted because the data was never ever looked at it (but don't get me started on that).
Automated tests painless tests that run often and test lots reduce the chances of your data is going missing. I find they make it easier for me to sleep at night. (Tests have other positive features: they're good examples of how to use code, they act as documentation, they make other people's code less scary when you need to change it, they reduce debugging time).
[B]ut how do we manage the testing data in the database so that it doesn't "mess up" live data?
Some thoughts: A good test set is self-sufficient and creates all the data it needs. Testing can be simplified if you can get the database in a known state before a test is run. One ways to do this is to have a separate unit test database, which is under the control of the test cases: the test cases clean out the database before starting any tests.
Deleting and inserting data for every test may seem like a big time over head, but as tests use relatively little data, I find this approach to be quick enough (especially if you're running against a local test database).
The downside is that you need more than one database - but remember, they can all run on one server if necessary. The way I'm testing now needs four databases (well, two at a pinch):
With multiple database you have to make sure you keep the structure of the databases in sync: if you change a table definition or a stored procedure on your test machine, you'll have to remember to make those changes on the live server. The deployment database should act as a reminder to make those changes."