Almost everybody understands that source code belongs into version control. Many people understand we need to test our code. Quite a few do that automatically.

But everything seems to change when it comes to databases. A lot of stuff in and around databases goes untested. Heck some of the scripts don't even live in version control. If you can't believe that, because you haven't seen it, you are a damn lucky bastard.

At least one reason for this state of affairs is obviously: It hurts. Database tests tend to be slow, interdependent and hard to maintain. Granted. But you know what: If it hurts, you should do it more often. It will teach you what exactly the things are that cause pain when testing and eventually you'll find approaches to make it less painful. Here are some ideas I found helpful when testing database related code:

The previous points where about the setup of your database and infrastructure. Lets have a look at the code:

The next points will deal with the actual tests:

  1. Recreate the schema for every test. This is slow but really safe.
  2. Do a rollback after each test. This only works when there is no commit inside the test. The mean thing is: If a test tries to rollback, but fails because there was a commit inside the test some completely different test will fail. It can be a really frustrating task to find the bad test in such a case.

We covered the testing of  database related code inside your application. But there is another kind of code we need to deal with: Scripts for deploying your application (or upgrading it)

Happy testing everybody.