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

Leadin

the linked presentation is no longer available. :( – Pentarchy

@SDC: the presentation PDF is available in a non-styled link right below the slideshare box. (rant: and that's why I say that a link should look like a link) – Aymara

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).

Ferrol

Thanks for your clear answer. The problem we have is that we are trying to unit test as much as possible (e.g. Every new bug fix/enhancement should have a unit test associated with it). Is this reasonably possible to do when the bug fix/enhancement is simply a new and improved SQL query? What is the standard procedure for testing these kinds of fixes (without using the SQLite method) if DbUnit is not recommended for Mysql? It seems a bit overkill to port everything to SQLite. – Leadin

Also, why do unit tests need to be fast? We have no current requirements to optimize our unit testing process, and I'm unable to see why this would be critically important? (I am a noob at TDD, so any enlightenment is appreciated) – Leadin

As you run them a lot. Every time you commit you should run as many of your automated test as you can. Ideally the lot (and a "Build" is not a build until all the automated tests run and pass). If that takes less than 10 minutes then it is irritating but acceptable but above ten minutes and it stops being acceptable. – Project

@user1027562: see my edit, essentially what mlk said stands correct; you run tests a lot, and execution time shouldn't discourage developers from running them. – Ferrol

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.

Unbar

Answer 3

I use DbUnit to test my models (speaking of MVC, including ORM-models), because they are closely related to database. For everything else (Controllers mostly) I use mock objects.

Basic idea of unit testing is to test just one unit of application code (which is a class) at a time, so it is better avoid using DB unless the code directly working with it.

And of course, performance matters. For example, I have ~500 test for models, almost all of them use DB and fixtures. It takes about 30-40 sec to execute all of these tests on a pretty fast computer. Code coverage report generation takes about a minute.

Physicochemical