PHPDBUnit – Testing DB interaction with PHPUnit

It’s been a while since I have put anything in the heavily neglected little site of mine so I thought I would start posting some updates on a few little projects I have been working on. I have been spending a considerable amount of time traveling for work so I am not quite as far along in these projects as I would like to be, but I have been making some major head way.

The project I would like to talk about right now is a port of Java’s DBUnit for php that will compliment the PHPUnit test suite. For those of you not familiar with DBUnit, it is a framework that allows you to easily and quickly test php code that modifies and works with database data. The whole idea behind unit testing is testing functions using well defined input and then comparing the output of that function with the values that you expect the function to produce. With database code this is somewhat difficult because you have to ensure that your database is in a known state prior to running your tests. Currenty, in order to do this properly you would find yourself adding significant amounts of code to your test to simply enter data into your database and then you will add even more code to compare the resulting database values with what your expected output. Ending up with something like this:

This is a fairly simple example and they really only get more complicated from this point forward. In order to the equivelant test using PHPDBUnit you would create a test class as follows:

You will also have to provide two xml files for the above code. This first xml file (dataset.xml) is used to seed the database before every test. The second xml file (expected.xml) is used to compare your database after the test is ran. These two files allow to get the code seeding your database out of your test cases and into their own xml files. There are also other options for generating data sets, I will discuss these options as well as the xml formats for datasets in a future post.

The database connection functionality is built overtop of PDO and support is planned for all standard pdo drivers. The big question mark for me right now is the ODBC driver. The PDO connection is wrapped by a PHPDBUnit_Database class that provides the functionality to generate datasets from the database to test your data and ensure it is correct.

The seeding is controlled by two functions that I did not choose to override in my example: getSetUpOperation() and getTearDownOperation(). These functions simply return a database ‘operation’ that is to be performed on the database connection using the data set returned by getDataSet. The default setUp operation is a clean insert. This is a truncate operation immediately followed by an insert of all data in the dataset. The default tear down operation is a null operation. You have several built-in possibilities for these operations such as Truncate, refresh, and update. You can also create your own operations if necesary.

My plan is to keep the interface of PHPDBUnit as close as possible to DBUnit while maintaining a more phpish way of doing things. I am planning on the first beta release of PHPDBUnit in July. Of course I welcome feedback on any features you would like to see. In the coming days and weeks I will continue to post more information and examples of how to use DBUnit in your tests.

10 thoughts on “PHPDBUnit – Testing DB interaction with PHPUnit”

  1. It will be integrated with phpunit. I am not entirely sure if it will be downloaded as part of the same package yet or not. Sebastian Bergmann has been kind enough to offer the phpunit svn server on which to store phpdbunit. In the event that it is not in the same package I would imagine that you would be able to install it from the phpunit pear channel.

    The PHPDBUnit_TestCase extends PHPUnit_Framework_TestCase. In order to assist in doing database testing on already existing test cases I also plan to implement a tester interface and a corresponding set of classes that will allow you to compose db tests into your existing test cases without having to change which class your test case extends from.

  2. My idea is to create a branch for DBUnit development (as a copy from PHPUnit 3.1) and ship DBUnit bundled with PHPUnit (say, as of PHPUnit 3.1.X) as soon as Mike deems it stable.

    Once DBUnit is stable, the DBUnit code will be moved from the branch into PHPUnit’s main development branch and the separate DBUnit branch will be closed.

  3. Might be nice for acceptance testing, but I don’t see why you would want to talk to a real db during unit testing. It’s just too slow, and if you need anything resembling a biggish dataset for a *unit* test, your unit tests aren’t fine-grained enough.

    Mocking the database is really where you want to go. Redirect the connection logic to give a mock connection object back that basically does string compares to pick which of a handful of precooked result sets (in-memory arrays) to return. It’ll cost you an hour or so to rig up some baseclasses, and keeping everything in memory (and not having to truncate tables) makes sure that your unit tests keep flying. Also, having your datasets in the code will force you to think about your unit tests once they get large – usually that’s a refactoring smell :)

    I used database-based unit testing once, when I just started with XP 10 years ago, and I’ll never go back there.

  4. As soon as you start trying to mock queries you begin to lose the ability to ensure that the queries you are writing are correct. In my opinion it is just as important to ensure your queries are returning what you expect as it is to ensure your functions are returning what you expect. String comparison may work for smaller non data intensive apps but if returning the correct data is vital then having code that specifically tests your database is important.

    The primary motivation for me to work on this is we have thousands of lines of code in the app I work on that query a roughly 16 GB database. It is very common for us to re-factor our queries (especially our reporting queries) with the goal of speeding them up. When we make these changes we need to be able to test and make sure that we are still returning the data we expect.

    While database unit testing might not work well for some apps or some people it is a useful tool to have in your testing arsenal.

  5. I have made an initial commit of this functionality into the dbunit branch of phpunit: http://www.phpunit.de/changeset/961

    It is by no means complete, however the basics of the functionality are there. When I get a little bit more free time and clean up a few more things I will post an announcement and some instructions on how to use it.

Leave a Reply

Your email address will not be published. Required fields are marked *