Best practices for database testing with PHPUnit

Question

PHPUnit's own manual has some as-yet-unwritten sections entitled "Operations" and "Database Testing Best Practices."

What are best practices for testing a database with PHPUnit, particularly in MySQL?

Answer 1

When I'm doing database testing with PHPUnit I load my MySQL dump at the start of the first suite which contains any of the information I'm assuming to be true across all tests. When each test starts I use a setupDatabase method. This method deletes all the rows from the tables I know have changed then it loads a flat XML dataset containing the data I need to hold true. After this is done I run whatever code I'm testing. Finally, I use a collection of simple methods to select rows from the database to assert the changes I made were done correctly.

I wouldn't say this is a best practice but's worked pretty well for me. The only problems I've run into are having to do a find-and-replace on the XML datasets every time the schema changes and the tests run slowly as a result of all the deleting and inserting.

The Zend Framework has an interesting library for PHPUnit that allows tests to compare a database table to a flat XML dataset but I haven't had a chance to use it yet.

Answer 2

Some unordered thoughts:

It's good to have fixtures (with or without db structure), loaded into db in startUp() of every test. It can came ie. from JSON or XML files, one for each table. If you couple it with functions like getNthFixture($sTable, $nIndex) or countFixtures($sTable), you can easily test your queries. Even further, you can use [LINQ][1] to get expected results from set of fixtures with little or no difference between DB & fixtures query. I find it quite easy to adapt at early prototyping / developing stage, when db structure is changing very often. Adding assertion to compare directly LINQ query result with db query result makes creating tests pure pleasure ;)

Another hints: db should be reinitialised before every testing method, not before test case. Ideally you should drop the base and rebuild it from complete set of fixtures.

And, if you can, try make tests which works with different databases (some things, of course are not portable, but majority is). Use at least sqlite aside of mysql/postgres/other_big_rdbm.

If you are testing framework or other complex system, you should probably mock up database access singleton. If some hardcoded stuff is buried deep in not-so-flexible-orm, it can be pain in the, let say, neck.

Good idea is to log all queries which didn't passed test, and/or to display them in fail messages. Also for db error messages. If you are testing big databases when performance is a concern, try to log slow queries at the same time.

More magical and maybe a little bit harder, is to automate testing whether all columns used in where / having / joins are indexed. Is maybe something which should belong to Jointed Php / Database Code Sniffer (tm) instead of unit tests, and is not very simple to implement, but once used greatly assures quality of code.

Another good advice, going from sad personal experience: always add tests for veryfing charsets, specially if you work with many different languages. ISO-8859-1 world is very small ;)

[1]: http://phplinq.codeplex.com/ LINQ