Why Test Databases?

I’m going to be doing some presentations on testing for database administrators. I’ve been really excited to be working on pgTAP, and have started using it extensively to write tests for a client with a bunch of PostgreSQL databases. But as I start to push the idea of testing within the PostgreSQL community, I’m running into some surprising resistance.

I asked a major figure in the community about this, someone who has expressed skepticism in my past presentations. He feels that it’s hard to create a generic testing framework. He told me:

Well, you are testing for bugs, and bugs are pretty specific in where they appear. Writing the tests is 90% of the job; writing the infrastructure is minor. If the infrastructure has limitations, which all do, you might as well write that extra 10% too.

I have to say that this rather surprised me. I guess I just thought that everyone was on board with the idea of testing. The PostgreSQL core, after all, has a test suite. But the idea that one writes test to test for bugs seems like a major misconception about testing: I don’t write tests to test bugs (that is what regression tests are for, but there is much more to testing than regression tests); I write tests to ensure consistent and correct behavior in my code as development continues over time.

It has become clear to me that I need to reframe my database testing presentations to emphasize not the how to go about testing; I think that pgTAP does a pretty good job of making it a straight-forward process (at least as straight-forward as when writing tests in Ruby or Perl, for example). What I have to address first is the why of testing. I need to convince database administrators that testing is an essential tool in their kits, and the way to do that is to show them why it’s essential.

With this in mind, I asked, via Twitter, why should database people test their databases? I got some great answers (and, frankly, the 140 character limit of Twitter made them admirably pithy, which is a huge help):

  • chromatic_x: @theory, because accidents that happen during tests are much easier to recover from than accidents that happen live.
  • caseywest: @Theory When you write code that’s testable you tend to write better code: cleaner interfaces, reusable components, composable pieces.
  • depesz_com: @Theory testing prevents repeating mistakes.
  • rjbs: @Theory The best ROI for me is “never ship the same bug twice.”
  • elein: @Theory trust but verify
  • cwinters: @Theory so they can change the system without feeling like they’re on a suicide mission
  • caseywest: @Theory So they can document how the system actually works.
  • hanekomu: @Theory Regression tests - to see whether, after having changed something here, something else over there falls over.
  • robrwo: @Theory Show them a case where bad data is inserted into/deleted from database because constraints weren’t set up.

Terrific ideas there. I thank you, Tweeps. But I ask here, too: Why should we write tests against our databases? Leave a comment with your (brief!) thoughts.

And thank you!


Josh Berkus wrote:

... so that we can get a clear spec from the application programmers. Darn it.

Joey wrote:

So you know what you wrote is what you want.

David wrote:

Could you make an example of a something you would catch with a database test, which could not have been cought with a regression test?

Rod Taylor wrote:

I'm convinced. I have a little over 6500 pgTap based tests (4000 lines of pgtap sql).

Many check that plpgsql and trigger code works correctly. Invaluable when applying structural changes. Being able to remodel portions and coming having it come out 100% functional for 3 different applications, each using different access functions but the same DB structure, is very useful.

Some check standard security settings to ensure that junior staff cannot directly write to any structure at any time and PUBLIC has no access. Also very useful and immediately applies to new structures added.

I have one test that simply checks that sequence permissions match table permissions, and complains if they do not.

A few tests check the plpgsql functions for permissions, behaviour (volatile, stable, immutable at the appropriate time), that they have COMMENTs, that the return datatype is correct (an is_foo function should always return a boolean).

Given that regression tests are a few layers up on top of Catalyst, this allows me to test DB related stuff much quicker and is much more accurate. A Catalyst regression test could never check the results of an audit trigger since that data is not readable.

A catalyst regression test cannot tell you, easily, that a function has too many permissions.

Bob wrote:

why wouldn't you?

I'm utterly amazed of the overall lack of database folks thinking testing is not for them or being ignorant of testing the database.

Lets get to the point. Why wouldn't you test your database? I hope airplane turbine engineers have a better mind set then some in the database world when it comes to testing.

I mean if you are going to test that the plane will fly soundly wouldn't you want to test the turbine engine on it's own before doing a flight tests?

Ben Finney wrote:

Resistance to modern testing practices

I found similar resistance when I asked about the possibility of running a less-capable, in-memory PostgreSQL instance, for the purpose of unit testing the database. After the expected “PostgreSQL can't do that”, I was inundated with “why would you do that?” and staggeringly low awareness of the need for rapid unit testing feedback.

As perceptively observed in the book “Refactoring Databases”, software engineering best practice has made great progress in the last twenty years, but database practicioners have largely yet to find out about it.