Test driven development for SQL code

Question

Have you applied test driven development to purely sql scripts? if so what has been your experience. Is it worth it? What are the rewards? disadvantages ? etc.

Answer 1

I have played around a bit, to be honest I would rather generate my regular DB code. I was this awhile ago and thought it was interesting. http://sourceforge.net/apps/trac/tsqlunit/

Answer 2

Most (all?) of my database "scripts" are generated, not hand-written. And, I avoid stored procedures and views. I basically treat my database as a file. The testing and logic stays in the application layer (where it belongs, IMO).

This approach works pretty well for me and the kinds of applications that I develop. It might not work so well in other situations.

For me the answer to your question is "not applicable".

Answer 3

SQL was the vehicle for one of my very first TDD collaborations. This was in a setting where I was an application developer (C++, I think, but it's been a while) and we had a DBA with responsibility for all queries. I wouldn't choose to go that route again, but that's another story. The time came when I needed a new query, so I wrote up some test data and expected results and sent it to the DBA; he wrote the script and thanked me for making the requirements so clear and precise.

TDD as it's usually practiced doesn't fit well with SQL (or maybe the other way 'round), but it's not really all that hard to adapt the practice to work well with the language. One-button testing may be a little harder to bring into the mix, but it's rarely hard to run a query.