Today I had an idea of writing a unit testing framework for stored procedures in MySQL. The full idea is written on a recent post on my blog. In short it goes like this: I want to automate my procedure testing, I want to use a standardized way to test my procedures. Unit testing is widely documented, and there are a zillion XUnit frameworks out there, why not write one for MySQL (or any other database). It would be open source of course. What do you think? It is silly, stupid, needless or what? Or another idea would be to write a general database framework in SQL. Hmm, I really want to discuss that with someone, collect thoughts and ideas.
A:
I tend to unit test the data access layer, it is always a pain in the ass because you have to setup a proper database with proper data. There are data generators out there that can help (like RedGate's Data Generator) make the setup process simpler.
My thinking behind just testing the DAL, is that you are essentially testing the stored procedures themselves with the added .Net DB code which I don't think we need to worry about unit testing. This way you can leverage all the tools and processes you already have for unit testing. It seems like a lot of effort to develop a separate framework for something that can (IMHO) be performed equally well with existing tools.
I have an open mind though. If there are benefits I am overlooking please do tell me.
Cheers, V
vfilby
2008-10-29 22:08:04
If it touches the database it's not a unit test
flukus
2008-10-29 23:31:13
Then call it an integration test. I don't think we are going to get a better answer by arguing semantics.
vfilby
2008-10-29 23:40:21
+2
A:
There's already one testing framework for Sql Server - TSQLUnit. Maybe you can get some useful info from it.
Mr. Brownstone
2008-10-29 22:12:23
A:
One of the benefits would be that the test would be written in the same envirment the stored procedures written and executed, maintaned by a specialized databse developer outside the main application. There is no need for the application developer to be a master of programming a relational database nor for the database developer to master an modern application developing language. You now have test for every thing. Why not have them for the database written sql and executed outside any in house developed application.
If you are developing a multi tier application it makes sense to separate each part and test it separately.
Nikola Stjelja
2008-10-29 22:19:51
I guess in my mind, databases were primarily meant for data, I try to keep logic out of the database. Even the majority of my stored procs are relatively logic free.I see the point for DBA's, I'd like to keep them away from my unit tests :)
vfilby
2008-10-29 22:57:39
Yes they are, but they also have data manipulation capabilites. What we are usaually doing is providing bussiness logic in our software mixed with calls to the database, but what we really need is to pass some paramteres and expect a result in one call. When we do that, we need to automate tests.
Nikola Stjelja
2008-10-30 05:37:51
A:
There shouldn't be enough logic in the database to make testing worthwhile.
flukus
2008-10-29 23:32:50
Why not? A database is a powerful tool which in most cases sits unused to the full. Why not use it?
Nikola Stjelja
2008-10-30 05:33:48
+1
A:
Yes, great idea. I've been having a fair bit of success with pgTAP. I've used it in a number of projects, both for test driven database development and to write tests for existing procedures in order to be able to effectively refactor them.
I have often been asked if there's something like it for MySQL. Maybe you've written something by now?
Theory
2010-06-25 22:59:29