Practical PHP Testing Patterns: Stored Procedure Test

It happened in the day before the advent of DDD and the Hexagonal architecture, that you had code that lived inside the database, such as Stored Procedures, constraints, and triggers.
Back in the day, the relational database was considered the single source of truth instead of a Domain Model written in a language like PHP or Java. Today the picture is different - but there are still scenarios where pushing code in the database make sense.

One of the reasons for having logic expressed as SQL and in other database languages is their power, and their performance. SQL operators, especially when augmented by proprietary extensions, let you declare pieces of logic that you would instead have to code by hand.

SQL that is executed directly on the database can accomplish operations too onerous to perform over a reconstituted object graph with a subsequent saving. In fact, every decent ORM include a language for batch updates that translates to SQL, like Doctrine with DQL; and also a mechanism for providing hints for the underlying database, like indexes definitions.

The problem with SQL derivates and other database-specific embedded logic is that we cannot execute it and test it in isolation - we need a real copy of the database to perform our tests. Thus the Stored Procedure Test is an umbrella term for tests that encompasses database code, even when they're not actually stored procedures. When I'll use the term stored procedure in this article, it will be to signify any database-specific code, such as complex queries, triggers and so on.

Implementation

The pattern prescribes to write unit tests for the stored procedure, to test it in isolation from the rest of application a first simplification. These tests cover nontrivial logic in database code - probably you don't need them for indexes definition, but more for queries with aggregate functions.

In the PHP world, Sqlite often suffices for testing queries - as long as you have an intermediate layer like Doctrine DBAL (part of Doctrine 2) which smooths out the differences between vendors. You use MySQL in production, Sqlite in the test suite, and you can write queries in Doctrine's DQL being confident that it will translate them to the right SQL dialect.

These tests should be executed in a sandbox - a database with just enough structure and data to test the stored procedure at hand. This sandbox should run by definition on the production dbms. The most difficult aspect of the pattern is integrating with the dbms: it should be running and listening on the right port. A sandbox should be created in setUp() or setUpBeforeClass(), and destroyed during teardown. In case the database is not available, the tests should be marked as skipped or incomplete.

Variations

In In-Database Stored Procedure Tests, the test is written in the same language as the database code. I cannot imagine something more boring for a PHP programmer.

In Remoted Stored Procedure Tests, which is the variation of interest for us, the tests are written in PHPUnit and integrated with the suite (slowing it down a bit).

The logic is that whatever SQL logic you're going to add to your application, is already encapsulated in some PHP class: for example, complex queries are encapsulated in Repositories or DAO. So it's going to be feasible to build a sandbox via PHP code, and test the stored procedure as a black box. It will be encapsulated for a unique execution, like schema creation, or for executing multiple times in case of queries.

Example

The example shows you how to test a query with a real database - supposing a surrogate database does not support all the needed functions - from inside a test suite. I thought it would be difficult to write this test, but instead it required less than a Pomodoro.

<?php
/**
 * This is a kind of integration test: we mock out the database from every 
 * other test, but still we have to hit it in one place to ensure our Gateway
 * (in this case a class containing queries) work.
 */
class StoredProcedureTest extends PHPUnit_Framework_TestCase
{
    private $connection;
    private $repository;

    /**
     * I'm using my local instance of MySQL: the only requirement is the 
     * presence of a database named 'sandbox'. You may want to parametrize 
     * everything here from database name to user and password.
     */
    public function setUp()
    {
        $this->connection = new PDO("mysql:host=localhost;dbname=sandbox", 'root', '');
        $this->connection->exec("CREATE TABLE users (name VARCHAR(255) NOT NULL PRIMARY KEY, year YEAR)");
        $this->repository = new UserRepository($this->connection, 2011);
    }

    public function testAverageAgeIsCalculated()
    {
        $this->insertUser('Giorgio', 1942);
        $this->insertUser('Isaac', 1920);
        $this->assertEquals(80, $this->repository->getAverageAge());
    }

    private function insertUser($name, $year)
    {
        $stmt = $this->connection->prepare("INSERT INTO users (name, year) VALUES (:name, :year)");
        $stmt->bindValue('name', $name, PDO::PARAM_STR);
        $stmt->bindValue('year', $year, PDO::PARAM_INT);
        return $stmt->execute();
    }

    public function tearDown()
    {
        $this->connection->exec('DROP TABLE users');
    }
}

class UserRepository
{
    private $connection;
    private $currentYear;

    public function __construct(PDO $connection, $currentYear)
    {
        $this->connection = $connection;
        $this->currentYear = $currentYear;
    }

    /**
     * We suppose AVG() cannot be correctly implemented by Sqlite or
     * another surrogate database (substitute another vendor feature
     * for the same effect). 
     * We also suppose reconstituting millions of User objects to calculate
     * their average age isn't feasible: that's why we used SQL directly.
     */
    public function getAverageAge()
    {
        $stmt = $this->connection->prepare('SELECT AVG(:year - year) AS average_age FROM users');
        $stmt->bindValue('year', $this->currentYear, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch();
        return $row['average_age'];
    }
}