Why should I avoid using DbUnit to test MySQL?

Question

I have recently become involved with some TDD using PHPUnit. I have to test a database-driven app, and read about the DbUnit extension, which I was planning to research and implement over the coming weeks.

However, I have come across this presentation by the man himself - Sebastian Bergmann - He has a slide entitled 'Avoid testing against MySQL if you can', which has cast some doubts upon my escapade.

Can someone explain the reasons why I should not test against MySQL?

Thanks

Answer 1

Two reasons:

• it is slow (and unit tests need to be fast)
• it adds extra failure point that is beyond your control (does test really fail when DB connection fails?)

Instead, as author suggest you should test your DAL with in memory databases, like SQLite. This eliminates problems noted above.

However, this approach has its drawbacks too - you might have problems porting SQL from one database dialect to SQLite. This naturally means you won't be able to test MySQL specific parts of your DAL. As always, it is double edged sword - you get unit test speed and isolation, but you lose credibility (if we can call it this way) - if it passed on SQLite, can you be 100% sure it works on MySQL?

It might be not that bad idea to leave the core of your DAL/DAO tests to integration testing phase, where you will test them agains real DB engine you use and leave small things for unit testing; for example mappings (if you use ORM that is).

Edit: the fast is by no means strict requirement - it's just good general advice. When doing TDD your developers will run unit tests a lot (think this way; every commit to local repo/every important code change will require code base integrity check by running unit tests) - perhaps not all of them, but surely some. You want this process to be quick.

Now, having slow tests usually ends like this:

• "Man, this things runs so slow..."
• "Perhaps I can run just few of them... I'll run rest later"
• At this point we know later never comes
• "Hey, my last commit didn't break anything and I didn't run any tests at all!"
• "Why would I run them after all?"

Writing tests that are not run, pretty much kills the purpose of writing them.

_{This thing happened to a friend who works with me; his team had test suite running +/- 20 minutes (DAL tests done poorly, IoC container involved in tests), developers started running some tests, and pretty soon the "Current build breakers" emails became daily thing. They had rather large suite, but breaking test was not that bad.}

Overall, your approach seems correct - I wouldn't move tests to SQLite. Like I suggested, have database layer tested with integration tests suite (so that it can be run separately than regular, unit tests suite).

Answer 2

I totally disagree with the title of slide 33 written by Sebastian Bergmann.

(Because the message from slide could be easily misunderstood, I am sure that in his lecture he explained the real meaning)

As stated in his introduction debugging sucks but testing rocks.

If the real environment uses mySQL you must test it against mySQL. Otherwise you will find yourself debugging what are the differences between SQLite and mySQL.

I believe that his direction is addressed to developers rather than unit testers, and in that sense i would also suggest to developers to make any effort necessary to keep their code database neutral as possible. It will improve the quality of overall life cycle of project, not only unit testers. It will protect even from feature improvements of mySQL itself. (There were a lot of problems in the past).

But when testing a single piece of code that uses SQL, no matter what other testings you already did you must test it against the real thing.

My practice is to create an abstract layer of database connection and having tests to react with that layer. Subsequently i am creating different concrete classes for each db server that i want to test.

With that environment given we can test frequenlty against SQLite, but the real tests will run at least once per day.