Database Tests
Introduction
Database tests are a type of automated tests that do not get enough love these days. Everybody is talking about unit tests, acceptance tests, integration and regression tests.
Database tests have gotten a bad reputation because of "unit tests" which test functionality through the DB. They pretend to test some kind of model by reading from and writing to the DB. Those are not database tests (and those are not models, by the way). So what is a database test and where does it fit in?
When Are Database Tests useful?
When I have to interact with a database I want to make sure the schema of that DB is formed as expected. The design and implementation of that schema is usually not driven by tests. In test driven design, external services like databases are usually mocked, because they are not part of the application. In many organisations the schema might even be designed by a different department and may be subject to regulations. No matter where the design comes from, it might be a good idea to have tests for it.
A database test should be testing if the schema of your database is conforming to your expectations. It can be implemented on two different layers of your database abstraction. Either it uses raw SQL instruction or it uses some kind of data access object of your application. In either case it should only test simple CRUD functionality. The test are supposed to tell us if we can add, read, update and delete rows. If we rely on other database features like auto-increment indexes or triggers then we should also test if those are in place.
Such tests help us determine if somebody changed the schema without updating the code and vice versa. They tell us if Bob ‘optimized’ the database and they tell us if the schema has been adapted to Alices changes in the code. Instead of a large number of tests for business objects breaking, because they were tested through the database we now have tests that pinpoint the source of the problem.
When and How to Run Them?
Now that we have our schema tests place, when are they run? If they are reasonably fast and the database is accessible in your development environment then you can run them in your usual TDD/BDD cycle like most of your tests. If they are rather slow or access to the database is non-existent or flaky in your dev system then do not run them in that cycle. You want that cycle to be fast and reliable. Instead you could run them on your CI (continuous integration) server. They can even be run on different deployment stages to identify if database changes have been deployed as well. No more forgetting to apply your schema changes to the production database. For running them selectively you need to make sure they are set up to enable that. E.g. put them in a different folder from our other tests or mark them as DB tests if your testing framework supports that and exclude them where you do not want them to run.
Addenum October 2024
Much has changed in the last nine years. The message of this article is still true. Database tests can help you detect if your code and the database schema have diverged. What has changed is the tooling available to us. These days we can easily spin up a container with our database and have tests run against this instance. This can tell us if our migrations are in place, if our database client is wired up, and if basic assumptions about the shape of our database are correct. I even use it to check if my transaction boundaries are working as designed. Sometimes I write tests that try to provoke concurrency issues to find out if I am using the appropriate isolation levels for my transactions.