Unit Test Your Database.

Author: Szymon Lipiński
Published at: 2011-11-14

I observe the endless war about TDD. On one side there are those who claim that TDD is the best thing since sliced bread. On the other side there are those who claim that TDD is just a waste of time, and is too slow.

But what about databases? Nobody writes about them too seriously.

In some books there are some wise words like:

This is very good for testing the unique, foreign and primary keys. Very good for all checks. What is missing here?

Should we use a little bit more TDD here?

Two simple examples of what should be done:

TDD Your Indexes

Imagine you have got a database filled by some ETL process, which loads huge amount of data. This means you can have many different databases with different versions of the data and structure. For one database you could notice that it’s is slow. It is too slow. Simple index could help. You check that, and it really helps. OK, good.

You just fix the ETL to create this index after loading the data. You are happy - you’ve already fixed the problem. Do you fixed the real problem?

What with the rest of the databases? Well… they need that index too. So you just create the index on all the databases/schemas you remember about.

The answer is simple: TDD.

TDD you database.

If you are using PostgreSQL, just write a simple test, use pgTap. Then it would be as simple as:

    SELECT has_index(
      ARRAY['a', 'b']

Next time when the database is slow, just run this simple test. It will tell you if the index exists or maybe you should create it.

Automate This

Automate the tests. You can use Jenkins for that with the plugin for TAP. I will write about that some time later, but Jenkins is a really great tool. With the TAP plugin you can easily combine testing your Java (or Python, or C++) project, and test database as well.

Version Your Database

Versioning can be done in many ways. This entry is about Java, so I’d use liquibase, which integrates database patches with java in a very nice way (yes, I know, there is XML, but nothing is perfect).

OK, this is not fully about the TDD. You shouldn’t use the TDD for pure versioning, but you should version the database, so you can check the version when your application starts.

Imagine an application which starts, checks the database version, it connects to. And then alerts that the version is wrong. And just quits. Some say that it could be frustrating a little bit. I think that even more frustrating is when you notice the problem after many hours or days of the application running. Suddenly it turns out that the application was using wrong database all the time. You tested app on a different database, and run it on quite a different schema. What if it caused some unmaintanable inconsistency in the data?

Data is, in most cases, the most valuable asset you have.

TDD Sucks

Short answer to all those who just say that such testing is stupid, and time is too precious (my precioussss) to spend it for writing tests.

Just calculate how much time those programmers and admins could save.

Just calculate how much time would programmers save if they wouldn’t be just managing the bug tickets. And this could be caused only because they couldn’t ensure everything works before deploying app to production.


How do you test your database or maybe your data?

The comments are disabled. If you want to write something to me, you can use e.g. Twitter.