

Database Unit Testing: It’s Tricky - gnosis
http://www.hackinghat.com/index.php/database/database-unit-testing-its-tricky

======
msluyter
I work in database centric (Oracle) organization and we've never attempted
unit testing at the database or even pl/sql level, for many of the reasons
mentioned. Additionally:

1\. It's hard to achieve any form of dynamicism in the database. Yes, you can
use "execute immediate" in PL/SQL, but that introduces other problems. We have
some packages that cobble together long complex queries to be executed via
execute immediate, and they're very difficult to understand.

2\. Functionality is often quite dependent on the underlying data. In a dev
environment where tables or schemas may be dropped or rebuilt, this can become
painful.

3\. Tests tend to be very tightly coupled to the underlying design. Compare
testing a java method whose underlying code could be swapped out without
failing a test if its functionality remained unchanged with, say, a test that
must verify if certain rows have been written to a table. In the case of the
latter, changing the data model may also break the test.

4\. Side effects: the database layer tends to be side effect heavy (triggers,
child tables, log tables, etc...) Testing for these is painful.

There are some PL/SQL testing frameworks for Oracle, (
[http://en.wikipedia.org/wiki/List_of_unit_testing_frameworks...](http://en.wikipedia.org/wiki/List_of_unit_testing_frameworks#PL.2FSQL)
) but, IMHO, many of them are fairly weak, and/or, suffering from bit rot.

------
zenocon
If you're on a Java stack, use DbUnit; furthermore, the combo of Spring Test &
DbUnit, and staying away from putting too much logic in stored procedures /
triggers means you can unit test against the database fairly well -- leaving
zero side effects, and with speed/efficiency. I wrote up a blog entry on the
technique I use: <http://zenoconsulting.wikidot.com/blog:8>

