Practical PHP Testing Patterns: Database Sandbox

We set out for doing TDD and testing everything, but then we come across an SQL query and we are puzzled. We may test some logic independently, but we're bound to touch the database in end-to-end tests or in operations which involve SQL operators, like a SUM().

We may start to use our shared database on the development server for doing tests. But then our test suites cannot run concurrently (nothing worse than having to tell your colleagues that you have to run the suite, and they should not touch it.)

And what should be the schema of that database? Taken from which branch and release of the application?
A Database Sandbox fosters test isolation by providing a separate database for each person running the tests.

Ideally, for every instance of the test suite a separate database will be available. If you as a developer run two test suites simultaneously, or in the same day (maybe different subsets of tests or the same tests from two machines) you'll hit different databases.

Without a sandbox, you'll experience intermittent failures due to concurrency, or tests that cannot start because of a wrong schema deployed on the common database, or where wrong fixtures have been loaded.

We'll see later how to reset the situation so that the tests start always from the same fixture.  However, no worries for a developer with a Database Sandbox: he's free to modify its database and work on his copy of the source code.

Implementation

If you intend to use a real database in test suite, you must provide the possibility of configuration for the database used by developers locally.

I'm also assuming we are talking about relational database: these patterns have been extracted from this kind of applications.

Alternatively, you can make the fixture setup phase build a new database for each test run (which involve a database, of course). I use Sqlite for the majority of tests, via PDO it's very simple to subtitute it and the Doctrine Database Abstraction Layer (DBAL) it's capable of generating the right syntax for CREATE TABLE and INSERT queries, dependeing on the type of database you're connected to.

Even the walls in a PHP-based shop now knows that Sqlite is the way to go for test suites: free, fast, and satisfying most of the functional requirements of web applications. The schema is built at the start of test run, and kept in-memory; it is destroyed after the test run ends. It can't be sandboxed more than this.

The issue of SQLite is the lack of functionalities like foreign keys or other advanced aggregate functions. You'll still have to test somewhere with the real database: Sqlite does not give me the confidence to go in production.

Variations

Most of these variations are rendered obsolete by the first. Substitute lightweight db created on the fly is very powerful: no shared objects, the most updated schema for your code. the only problem is speed or number of licenses if you use a proprietary db, but if you use in-memory Sqlite you won't get problems. PDO and Doctrine 1/2 can subtitute it along with each self-respecting ORM.

• Dedicated Database Sandbox: lightweight databases, one for each developer/machine. Kept on local machines together with code or on a shared server for simplicity.
• DB Schema per Test Runner: an immutable schema, used only for reading, is replicated for each runner.
• Database Partitioning Scheme: data in the same database instance are used by different runners, for example you create a test user for each of them in order to make authentication work.

Examples

I show you how to create an Sqlite database with a schema and data for each test; This example trades off speed for simplicity as rebuilding the db for each test is expensive even for Sqlite (still faster than a real db like MySQL or Postgres). There are solutions (next article) for avoiding destroying the database, and recycling it between tests (but not between runs, to avoid schema conflicts.)

<?php
class DatabaseSandboxTest extends PHPUnit_Framework_TestCase
{
    private $db;

    public function setUp()
    {
        // in production the string is mysql:... or pgsql:...
        $this->db = new PDO('sqlite::memory:');
        // if you want portable CREATE statements, Doctrine 1 (deprecated) or 2
        $this->db->exec('CREATE TABLE users (id INT NOT NULL PRIMARY KEY, name VARCHAR(255))');
        $this->db->exec('INSERT INTO users (id, name) VALUES (1, "Giorgio")');
        // $this->db will be recreated for each test method: if you have many tables, this is SLOW
        // we'll see how to adjust the teardown so that we can reuse 
        // the same schema, containing hundreds of tables, between different tests
    }

    public function testMyDatabaseIsNewShinyAndPopulatedWithData()
    {
        $this->assertTrue($this->db instanceof PDO);
        $users = $this->db->query('SELECT * FROM users')->fetchAll();
        $this->assertEquals('Giorgio', $users[0]['name']);
    }
}