Best practices for database testing with PHPUnit [closed]

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.