Database Testing with PHPUnit and MySQL

Update 2012/01/15: I finally got around to submitting a patch to document this feature in the PHPUnit manual. Sebastian has merged it, so it will hopefully be available in the online manual soon.

Update #2 2012/01/23: I got around to checking the online version of the manual and the current build includes my patch. Enjoy.

I recently made a contribution to the PHPUnit project that I thought I’d take a blog post to discuss. One of the extensions bundled with PHPUnit adds support for database testing. This extension was contributed by Mike Lively and is a port of the DbUnit extension for the JUnit Java unit testing framework. If you’re interested in learning more about database unit testing, check out this presentation by Sebastian Bergmann on the subject.

One of the major components of both extensions is the data set. Database unit tests involve loading a seed data set into a database, executing code that performs an operation on that data set such as deleting a record, and then checking the state of the data set to confirm that the operation had the desired effect. DbUnit supports multiple formats for seed data sets. The PHPUnit Database extension includes support for DbUnit’s XML and flat XML formats plus CSV format as well.

If you’re using MySQL as your database, CSV has been the only format supported by both the mysqldump utility and the PHPUnit Database extension up to this point. My contribution adds support for its XML format to the extension. While this support was developed to work in the PHPUnit 3.4.x branch, it won’t be available in a stable release until 3.5.0. In the meantime, this is how you can use it now.

  1. Go to the commit on Github and apply the additions and modifications included in it to your PHPUnit installation.
  2. From a shell, get your XML seed data set and store it in a location accessible to your unit test cases.
    mysqldump --xml -t -u username -p database > seed.xml
  3. Create a test case class that extends PHPUnit_Extensions_Database_TestCase. Implement getConnection() and getDataSet() as per the documentation where the latter will include a method call to create the data set from the XML file as shown below.
    $dataSet = $this->createMySQLXMLDataSet('/path/to/seed.xml');
  4. At this point, you can execute operations on the database to get it to its expected state following a test, produce an XML dump of the database in that state, and then compare that dump to the actual database contents in a test method to confirm that the two are equal.
    $expected = $this->createMySQLXMLDataSet('/path/to/expected.xml');
    $actual = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
    // Specify a SELECT query as the 2nd parameter here to limit the data set, else the entire table is used
    $actual->addTable('tablename');
    $this->assertDataSetsEqual($expected, $actual);

That’s it! Hopefully this proves useful to someone else.

12 Comments

  1. Social comments and analytics for this post…

    This post was mentioned on Twitter by irfan_uygur: Database Testing with PHPUnit and MySQL http://tinyurl.com/ylnxl3l #database# #php# #unit test# #mysql#…

  2. Les says:

    This is going to help so many people – it will certainly go some way to quieten down those who use Simple Test [Ugh] that bang on about PHP Unit lacking features and functionality.

    Well done #thumbsup

  3. abcphp.com says:

    Database Testing with PHPUnit and MySQL » Matthew Turland…

    I recently made a contribution to the PHPUnit project that I thought I’d take a blog post to discuss. One of the extensions bundled with PHPUnit adds support for database testing. This extension was contributed by Mike Lively and is a port of the DbUni…

  4. [...] a new post to his blog Matthew Turland looks at a contribution he recently made to the PHPunit project to help [...]

  5. [...] a new post to his blog Matthew Turland looks at a contribution he recently made to the PHPunit project to help [...]

  6. [...]  Database Testing with PHPUnit and MySQL (0 visite) [...]

  7. Lyle Ruller says:

    Appreciate this. Very educational entry.

  8. Murilo says:

    Hi there,

    I’m trying to get your modifications to phpunit at http://github.com/elazar/phpunit/commit/fad913fd84720f889e1d3415e775f68304e76f52

    But the link doesn’t exist. This is a smart way to work and I want to use it.

    Cheers,

    Murilo

  9. I’ve fixed the link. I’d dropped the repository after my contribution was pulled in (under the same commit identifier) to Sebastian Bergmann’s account and forgotten to fix the link here. Appreciate you letting me know, thanks!

  10. Joaco says:

    I find this all very interesting, but is it possible to test the results of a complex query, such as a select statement with multiple joins? It would be nice to be able to confirm that the logic used to generate a query is correct as well.

  11. @Joaca To my knowledge, the only way to handle that would be to use a SELECT INTO OUTFILE query (or mysql -e ‘SELECT’ > dump.sql if you’re accessing a remote server) to dump the result set to a CSV file and then use the PHPUnit class for CSV data sets. I don’t believe any of the standard MySQL client utilities offer a way to dump a data set like that into the same XML format that mysqldump does for individual tables.

    If you wanted to use the XML data set class, you’d need to write something to convert from CSV to that XML format. In fact, before I developed MySQL XML support for PHPUnit, I had written a PHP CLI script to convert from the MySQL XML format to the DBUnit format that PHPUnit did support at the time.