• History

From Open Source Bridge attendee wiki 2009

Given that the database, as the canonical repository of data, is the most important part of many applications, why is it that we don’t write database unit tests? This talk promotes the practice of implementing tests to directly test the schema, storage, and functionality of databases.

Speaker: David Wheeler

Return to this session's details

Contents 1 Contributed notes 2 Contributed notes (notbenh) 2.1 WHY!? 2.2 TDD 2.3 pgtap 2.3.1 it's easy to see about validity, but what about maintenance? 2.4 now convince your DBA's 3 Q/A 3.1 can you test triggers

Contributed notes

Contributed notes (notbenh)

WHY!?

There are many excuses for why not to test, but there all wrong as testing is a good thing, it makes sure that you code really does work in the same way. Testing is not hard and its not just for finding bugs, good tests are there to prove that the code does indeed work in the same

TDD

• step 1: write test
• step 2 : write code to make test pass
• step 3 : go-to 1

pgtap

Gets a system that allows you to provide test stored procs in the same way that you would test code.

http://pgfoundry.org/projects/pgtap/ http://pgtap.projects.postgresql.org

pgtap gives you a boatload of assertion functions, if you want to test something then it is quite likely that there is something there just for your case.

it's easy to see about validity, but what about maintenance?

Sure: because you can test for the current state you can prove that you mantain the current state when you refactor.

EXAMPLE:

 current table uses VARCHAR for a month feild (why not INT?)
 the code is checking for the existance of a 0 in the string

build a test that will pass, so you know that you have coverage for the current behavoir. (be sure that you use the same data set that is currently used, not what you would like to use)

Now you can refactor.

code untill all tests pass, then you know that you have maintained state.

now convince your DBA's

start writing tests, show that they work, show that it saves time in the long run.

Be free from gunky situations.

Q/A

can you test triggers

sure.

• check for the existance of the trigger
• build tests for the function that the trigger calls
• build tests for the data pre trigger
• build tests to preform an action that will trip the trigger
• build tetss to check the post trigger state

horray!