3 ms·
Database Unit Testing: It’s Tricky
- deleted 16y ago[deleted]
- msluyter 16y agoI 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#PL.2FSQL http://en.wikipedia.org/wiki/List_of_unit_testing_frameworks... ) but, IMHO, many of them are fairly weak, and/or, suffering from bit rot.
- zenocon 16y agoIf 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 http://zenoconsulting.wikidot.com/blog:8