Last night I read a blog entry entitled “How to Unit-Test Code That Interacts With a Database”. It stated that such testing is hard. But you still want to fully test the database interaction. The blog recommended that you connect to a separate database to do your unit testing. Each developer should ideally have their own database. In addition, the database should be reset to the state it started in prior to the unit test. The best database for testing is a small focused data set. Above all the unit test activity should not connect to the production database.On our project we conduct unit testing on new code that we write, as well as on fixes that we produce. Our main test data set is a small but interesting combination of test cases for verification. In general most of our developers have their own schema for testing. This allows you to do whatever you want without impacting other developers. It also helps in that nobody else makes changes to the data in your database. In the past I have written elaborate unit test scripts which generate delete old test data, generate new test data, execute the tests, and report on the results. This is normally done when I write code that relies heavily on database interaction.
One of the best parts of the blog post I read was the large number of comments from readers. I will share some of them here for your benefit. Multiple people pointed out that the type of testing discussed here is integration testing rather than unit testing. Unit tests are concerned with logic or combination checks. Oracle users recommended the utPLSQL framework for Oracle. You can use the ut_setup and ut_teardown functions to take care of unit test data.
Some readers commented that you could treat the database changes as one big transaction, then roll it back after testing is done. This would definitely not work for my system. We commit transactions all over the place. We even have autonomous transactions which commit themselves without interacting with any outer transactions. You can do better than getting your own schema for testing. You can get a whole database installation of your own to conduct unit tests in. Express versions of Oracle and Microsoft SQL Server are free.
It appears that unit testing is hot in the blogosphere. This seems strange to me. But I am happy about it. Nobody seems to like unit testing where I work. Most developers seem to want to code, check it in, and ship it. I know I myself could use a more rigorous unit test habit.
