When I first looked into unit testing, the concept seemed simple enough.  The canonical example seemed to be the sum function.

public function sum($x, $y)
{
    return $x + $y;
}

And the test.

public function testSum()
{
    $summer = new Summer();

    $this->assertEquals(7, $summer->sum(3, 4));

    // and after awhile, trying to break the 
    // function turned out to be part of the fun.
    $this->assertEquals(4, $summer->sum(7, -3));
    $this->assertEquals(4, $summer->sum(4, null));
}

In any case, this was nice and all for arithmetic, but like in any typical web app, I soon ran into the big problem: how do I test the database?

Now, there is a chapter on database testing in the PHPUnit manual with DBUnit for PHP.  But as I read about those, I saw a lot of documentation about syntax and handling of null values and empty tables. I wondered why I couldn’t just create a real database and test it with real queries?

Enter SQLite

SQLite is a self-contained, serverless, zero-configuration, ACID-compliant RDBMS.  Since SQLite requires no setup, and runs completely in memory, it makes the perfect database to run self-contained, serverless, automated unit tests against.

Database Access Abstraction To The Rescue

Being able to test seamlessly with a SQLite database in my unit tests and run against a MySQL database in live code required some careful thought about how I went about setting up my classes.  First of all, I created a database access interface with some standard functions like query(), getRows(), startTransaction(), commit(), etc.  (You are using a database access class, aren’t you?)  Then, I implemented the interface with a MySQL implementation, and an SQLite implementation.

Since there are places in the code that takes advantage of MySQL specific features, I had to emulate these features in my SQLite driver.  For example, there was no lpad() function in SQLite.  Fortunately, thanks to the magic of createFunction(), I could create a SQLite User-Defined Function.

public function lpad($value, $numchars, $char)
{
    return str_pad($value, $numchars, $char, STR_PAD_LEFT);
}

$sqlite->createFunction('lpad', 'lpad', 3);

Some of the MySQL bells and whistles are of course missing, such as ON DUPLICATE KEY UPDATE. With some fancy query re-writing, I got those MySQL queries working with SQLite.  Here is my current SQLite database access class in its entirety.

Dependency Injection FTW!

Now, that I had a MySQL database driver, and an equivalent SQLite database driver, I needed a way to get my business objects to use the SQLite version in a test, and the MySQL version during real execution.  If only there was a pattern that let me “inject” the proper database dependency into the object.  Well, this is exactly what Dependency Injection is!

Instead of instantiating a database driver within the class, I passed the database object in the constructor.  There are other ways to pass in the object, such as with a setter method, or with a global registry, but I like constructor the best.  So the class might look like this:

class Customer
{
    private $_db;

    public function __construct($db)
    {
        $this->_db = $db;
    }
}

Writing The Test

With the database access classes in place set to be injected into my classes, I could test functions that persisted data to the database, and then actually inspect the database.  There was no XML data files.  There was no database testing DSL.  There was only plain ol’ SQL.

Here is a simplified function that saves fictional customer data, and a test for it.

public function save($firstName, $lastName)
{
    $this->_db->query("INSERT INTO customers (first_name, last_name) VALUES (?, ?)", 
        array($firstName, $lastName));

    return $this->_db->lastInsertId();
}

And the test.

class CustomerTest extends PHPUnit_Framework_TestCase
{
    private $_db;

    public function setUp()
    {
        $this->_db = new Test_Database;

        $this->_db->query("CREATE TABLE customers (first_name, last_name_);");
    }

    public function tearDown()
    {
        $this->_db->query("DROP TABLE customers");
    }

    public function testSave()
    {
        $customer = new Customer($this->_db);

        $customer->save("Mickey", "Mouse");

        // test that customer is saved to DB
        $results = $this->_db->getRows("SELECT * FROM customers");

        $this->assertEquals("Mickey", $results[0]['first_name']);
        $this->assertEquals("Mouse", $results[0]['last_name']);
        $this->assertEquals(1, count($results));
    }
}

One Last Thing

This method could get to be overkill in certain situations as you write tons of SQL to set up your tests.  But even then I find it preferable to setting up XML or CSV data.  As far as mocking out a database with some sort of Mock Object, I still maintain, why use a mock and a DSL when you can use the real thing?  This has worked well for me so far.