Look mama, no databases

I recently got a question about testing services, where this person was under the impression that when calling services you did not need to know what was going on. You just pass in data and a result is returned. How this result was given was of no importance, all you needed to test was that you got a result and it matched up with what was expected.

The only problem in this case was that he was talking about testing the business logic in the service and I replied he should perform tests on that business logic instead of just asserting the returned result. After some further discussions it was clear that he tested the service "as is", meaning the service makes a call to a database to gather its results and returns the calculation on those results.

When I state "as is", I truly mean the way it's being used in production. So the database call collects real data on which business logic is applied. You can see this is not a healthy situation, especially when you also have services that apply business logic on data and store it back into the database.

In "The Grumpy Programmer's PHPUnit Cookbook", author Chris Hartjes wrote this one sentence that says it all: "Unit test suites are meant to be testing code, not the ability of a database server to return results". And he's right, you shouldn't use database connections when your testing business rules and functional logic.

DISCLAIMER: I'm still using PHPUnit 3.4.15 for testing Zend Framework 1 projects, so all tests here should work with higher versions of PHPUnit. If not, let me know in the comments.

Say you have a service that calculates margins for sold products, it's easy to connect to a sandboxed database and retrieve the product purchase and sales prices, retrieve the commission for the sales person on top of it all and return the calculated margin. Easy enough, right? But say you have thousands of tests like this that all connect to several tables in the database. Running tests will only go slower and slower until it reaches the point developers on the team don't run them because they will take too long to run.

Example: Product.php

<?php

class Product
{
    protected $_db;

    public function __construct()
    {
        $dsn = 'mysql:dbname=test;host=localhost;';
        $user = 'testuser';
        $pass = 'test123';
        $this->_db = new PDO($dsn, $user, $pass);
    }
    public function calculateMargins($productId)
    {
        $sql = 'SELECT * FROM product
                    LEFT JOIN employee
                        ON product.employeeId = employee.employeeId
                    WHERE product.id = ?';
        $stmt = $this->_db->prepare(
            $sql, array (PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

        $stmt->execute(array ($productId));
        $result = $stmt->fetchAll();
        $margin = ($result[0]['salesPrice'] - $result[0]['purchasePrice'])
                * (1 - $result[0]['commission']);
        return $margin;
    }
}

This can be just a normal class like you must have dozens off. In the case where a test is being made to validate this business logic, this class will connect to your database, retrieves the data and performs the calculation.

Example test that will pass when the values are the following:

• purchasePrice: 299.95
• salesPrice: 399.95
• commission: 15%

<?php
require_once 'Product.php';

class ProductTest extends PHPUnit_Framework_TestCase
{
    /**
     * @backupGlobals disabled
     * @backupStaticAttributes disabled
     */
    public function testCalculateProductMargin()
    {
        $expectedMargin = 85.00;
        $product = new Product();
        $result = $product->calculateMargins(1);
        $this->assertSame($expected, $result,
            sprintf('Expected margin of %02f did not match actual margin %02f',
                $expected, $result));
    }
}

The main issue I have with this test is that I'm too depending on the database to deliver me the data. And what happens if another developer needs to modify the data in the database for his tests? Exactly, my test will fail.

How can we solve this so we can test the business logic without connecting to the database?

The first thing we need to do is to ensure we can pass in a database replacement into our Product class, our mock object. So we rewrite the Product class a little so the constructor accepts a dbAdapter as parameter.

<?php

class Product
{
    protected $_db;

    public function __construct($db = null)
    {
        if (null === $db) {
            $dsn = 'mysql:dbname=test;host=localhost;';
            $user = 'testuser';
            $pass = 'test123';
            $db = new PDO($dsn, $user, $pass);
        }
        $this->_db = $db;
    }
    public function calculateMargins($productId)
    {
        $sql = 'SELECT * FROM product
                    LEFT JOIN employee
                        ON product.employeeId = employee.employeeId
                    WHERE product.id = ?';
        $stmt = $this->_db->prepare(
            $sql, array (PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

        $stmt->execute(array ($productId));
        $result = $stmt->fetchAll();
        $margin = ($result[0]['salesPrice'] - $result[0]['purchasePrice'])
                * (1 - $result[0]['commission']);
        return $margin;
    }
}

The next thing we need to do is to modify our ProductTest class so we can set our values directly in our test, without relying on databases for providing us this data.

<?php
require_once 'Product.php';

class ProductTest extends PHPUnit_Framework_TestCase
{
    /**
     * @backupGlobals disabled
     * @backupStaticAttributes disabled
     */
    public function testCalculateProductMargin()
    {
        $data = array (
            array (
                'purchasePrice' => 299.95,
                'salesPrice' => 399.95,
                'commission' => 0.15,
            ),
        );

        $stmt = $this->getMock('PDOStatement', array ('execute','fetchAll'));
        $stmt->expects($this->any())
             ->method('execute')
             ->will($this->returnValue(true));
        $stmt->expects($this->any())
             ->method('fetchAll')
             ->will($this->returnValue($data));

        $pdo = $this->getMock('PDO', array('prepare'),
            array('sqlite:dbname=:memory'),'PDOMock',true);
        $pdo->expects($this->any())
            ->method('prepare')
            ->will($this->returnValue($stmt));

        $expectedMargin = 85.00;
        $product = new Product($pdo);
        $result = $product->calculateMargins(1);
        $this->assertSame($expected, $result,
            sprintf('Expected margin of %02f did not match actual margin %02f',
                $expected, $result));
    }
}

Yes, we need to mock out our PDO object and our PDOStatement object just to mock the behaviour of our database call, but the benefit is now we can focus on the business logic instead of expecting everything will be provided. We now have just one set of values, but we can just add more and more values to it and move it to a data provider.

<?php
require_once 'Product.php';

class ProductTest extends PHPUnit_Framework_TestCase
{
    public function marginDataProvider()
    {
        return array (
            array (299.95, 399.95, 0.15, 85.00),
            array (1200.00, 1500.00, 0.10, 270.00),
            array (1200.00, 1200.00, 0.10, 0.00),
            array (1200.00, 1000.00, 0.10, -180.00),
        );
    }
    /**
     * @backupGlobals disabled
     * @backupStaticAttributes disabled
     * @dataProvider marginDataProvider
     */
    public function testCalculateProductMargin($pp, $sp, $comm, $expected)
    {
        $data = array (
            array (
                'purchasePrice' => $pp,
                'salesPrice' => $sp,
                'commission' => $comm,
            ),
        );

        $stmt = $this->getMock('PDOStatement', array ('execute','fetchAll'));
        $stmt->expects($this->any())
             ->method('execute')
             ->will($this->returnValue(true));
        $stmt->expects($this->any())
             ->method('fetchAll')
             ->will($this->returnValue($data));

        $pdo = $this->getMock('PDO', array('prepare'),
            array('sqlite:dbname=:memory'),'PDOMock_' . uniqid(),true);
        $pdo->expects($this->any())
            ->method('prepare')
            ->will($this->returnValue($stmt));

        $product = new Product($pdo);
        $result = $product->calculateMargins(1);
        $this->assertSame($expected, $result,
            sprintf('Expected margin of %02f did not match actual margin %02f',
                $expected, $result));
    }
}

So now we can test multiple values over and over again, and if for some reason we need to test a specific combination of values, we just add it to our data provider method. In this example we added values to see what happens when we have no margin and when we have sold products below purchase price.

The end result is you have a test that will test the business requirements, isn't making an expensive database call (runs faster) and allows you to modify the test in case the scope of business logic changes (e.g. no commission is paid when sales price is equal or below the purchase price).