Database Testing

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

Unit testing database

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?

Why bother?

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 infor mation, 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?  

You need [multiple ] databases

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):

1. The production database . Live data. No testing on this database.
2. Your local development database , which is where most of the testing is carried out.
3. A populated development database , possibly sh ared by all developers so you can run your application and see it work with realistic amounts of data, rather than the hand full of records you have in your test database. You may not strictly need this, but it's reassuring to see your app work with lots of data (i.e., a copy of the production database's data).
4. A deployment database , or integration database, where the tests are run prior to deployment to make sure any local database changes have been applied. If you're working alone, you may be able to live without this one, but you'll have to be sure any database structure or stored procedure changes have been made to the production database before you go live with your code.

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."