The Importance of Database Testing

Basic Mistakes in Database Testing

There is a huge number of books about TDD. Those books usually concentrate on using tests for units of work. Units of work are understood in many different ways, usually it means a class.

Those books usually say:

Write a lot of tests, make code in such a way thats the tests pass.

All external resources should be mocked so you can test only the one unit.

That’s cool and there is nothing wrong in this TDD approach. Unfortunately in many cases all of the testing stops at this moment. There are some queries (written by hand or generated by some ORMs) but they are not tested, usually.

Some programmers test those using integration tests - tests which connect to a real database and perform real queries. This usually means testing only the happy path.

I’ve got my ORM – it takes care of everything.

I don’t need to test it.

I don’t need to think.

PostgreSQL Versioning Policy

I was explaining the PostgreSQL version naming convention to my colleague a couple of days ago. The differences between e.g. 8.3 and 8.4 and what is different between 8.4 and 9.0 and what should be used during database migration to another version. I knew all that just because somebody told me that many years ago (I think it was depesz - thanks).

Unit Test Your Database.

I observe the endless war about TDD. On one side there are those who claim that TDD is the best thing ever. 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:

• we should use TDD for DAO
• we should use TDD for ORM objects
• we should use TDD for queries

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

How to Ensure There is One Row... part 2

Some time ago I wrote about ensuring that there is only one row with a specific value in a column.

Today a very similar problem: there is a table like this:

    CREATE TABLE test (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL,
      value TEXT NOT NULL
    );

We need to be sure that there is only one row withing specific name with value='a'. There can be more rows with this name, but only one can have value='a'.

What Is The Real PostgreSQL Triggers Ordering?

During the last database training that I led, I was talking about selecting rows from database and sorting them. Including some problems with collations. Later I was talking about triggers. For this post I will assume that there are only row level triggers.

When you perform e.g. INSERT in the PostgreSQL database, there are fired triggers (if they exists, of course). At the very beginning there are fired all BEFORE INSERT triggers. Then INSERT is made, and later all triggers AFTER INSERT are fired.

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name.

– trigger-definition

And then suddenly I was asked:

- OK, that’s clear, but which collation is used for ordering the triggers?

Good point, I really had no idea. So let’s check that.