Database Testing with PHPUnit

Disclaimer

This post is mainly meant for my personal future reference, but I thought it may be useful to other people too. So it’s not an extensive guide for using DBUnit, but more of a step-by-step list of how to get started using it.

Introduction

I am currently working on a small PHP project using the Silex framework, or mini-Symfony if you like. It consists of three parts: a RESTful web service (based on Silex), a cron job (also based on Silex) and a JavaScript heavy front-end.┬áThe RESTful service is what I’m currently working on and for which I needed some more extensive database testing.

The problem

The RESTful service project consists of some controllers, which are responsible for handling the available actions that the service is able to perform. For the actual work there is a Service layer and the controllers delegate the heavy lifting to the services in that layer. So even though there are some simple tests for each of the actions in the controller, the services are the most difficult to test since they are data-heavy.

For some of the methods a simple mock db adapter returning some mock data was sufficient, but when you really want to test methods that rely on more complicated queries this isn’t enough.

The solution

Luckily we have the PHPUnit extension called DbUnit, which makes our lives much easier when it comes to this kind of use case. The DbUnit documentation is pretty good and it got me going real quick.

The basic steps I took are:

  • Install the DbUnit extension if you don’t have it yet:
    pear install phpunit/DbUnit
  • Create a test database in MySQL. You could also use an in memory SQLite database if you like. This is explained in the DbUnit documentation.
  • Extend your phpunit.xml configuration file or create it if you don’t have one yet. The important part here is the database configuration settings in the <php> element:
    <?xml version="1.0" encoding="UTF-8"?>
    <phpunit backupGlobals="false"
             backupStaticAttributes="false"
             colors="true"
             convertErrorsToExceptions="true"
             convertNoticesToExceptions="true"
             convertWarningsToExceptions="true"
             processIsolation="false"
             stopOnFailure="false"
             syntaxCheck="false"
    >
        <testsuites>
            <testsuite name="WebService Test Suite">
                <directory>./tests/</directory>
            </testsuite>
        </testsuites>
    
        <php>
            <var name="DB_DSN" value="mysql:dbname=webservice_test;host=localhost" />
            <var name="DB_USER" value="ws_test" />
            <var name="DB_PASSWD" value="xxx" />
            <var name="DB_DBNAME" value="webservice_test" />    
        </php>
    </phpunit>
  • Create the test DataSet. I just used mysqldump to create an XML dataset, but there are several formats DbUnit can work with (YAML, XML (two flavors), CSV, PHP arrays).
    mysqldump --xml -t -u ws_test --password=xxx webservice_test > ./tests/_files/ws_db.xml
  • Test classes using DbUnit have to extend the abstract PHPUnit_Extensions_Database_TestCase class and implement getConnection() and getDataSet(). In the documentation they suggest to create your own base class for this purpose. My base class is almost the same as the one described in the docs, except that I extended it with a getDbal() method to retrieve a Doctrine DBAL connection. I needed this, because I’m using Doctrine’s DBAL in my project.
    <?php
    use Doctrine\DBAL\DriverManager;
    
    abstract class Generic_Tests_DatabaseTestCase extends PHPUnit_Extensions_Database_TestCase
    {
        // only instantiate pdo once for test clean-up/fixture load
        static private $pdo = null;
        static private $dbal = null;
    
        // only instantiate PHPUnit_Extensions_Database_DB_IDatabaseConnection once per test
        private $conn = null;
    
        final public function getConnection()
        {
            if ($this->conn === null)
            {
                if (self::$pdo == null)
                {
                    self::$pdo = new PDO( $GLOBALS['DB_DSN'], $GLOBALS['DB_USER'], $GLOBALS['DB_PASSWD'] );
                }
                $this->conn = $this->createDefaultDBConnection(self::$pdo, $GLOBALS['DB_DBNAME']);
            }
    
            return $this->conn;
        }
    
        /**
         * @return PDO
         */
        protected function getPdo()
        {
            return $this->getConnection()->getConnection();
        }
    
        /**
         * @return \Doctrine\DBAL\Connection
         */
        protected function getDbal()
        {
            if (self::$dbal === null)
            {
                self::$dbal = DriverManager::getConnection(array('pdo' => $this->getPdo()));
            }
    
            return self::$dbal;
        }
    }
  • My test class extends this Generic_DatabaseTestCase class:
    <?php
    require_once __DIR__ . '/../../vendor/autoload.php';
    require_once __DIR__ . '/../Abstract/Generic_DatabaseTestCase.php';
    
    use WebService\Service\StatisticsService;
    
    class StatisticsServiceTest extends Generic_DatabaseTestCase
    {
        /**
         * @return PHPUnit_Extensions_Database_DataSet_IDataSet
         */
        public function getDataSet()
        {
            return $this->createMySQLXMLDataSet(dirname(__FILE__) . '/../_files/ws_db.xml');
        }
    ...
  • Another thing I just noticed is that DBUnit uses the setUp() and tearDown() methods to populate the test database with the fixtures, so if you implement those in your own test class make sure to call the parent implementations, like so:
    public function setUp()
    {
         parent::setUp();
    ...
  • The setUp() method truncates the tables first before populating it with the fixture data. If your test dataset consists of more than just one table and has foreign key relationships it might be a good idea to turn the foreign key checks off while setting up the fixtures. I accomplished this by just turning it off, calling the parent’s setUp and then turning them back on, like so:
            public function setUp()
    	{
    	    $this->getConnection()->getConnection()->query("set foreign_key_checks=0");
    	    parent::setUp();
    	    $this->getConnection()->getConnection()->query("set foreign_key_checks=1");