Practical PHP Testing Patterns: Table Truncation Teardown

It's a fact of life that you will have to executes tests which touch some kind of database, usually on a Fake database like Sqlite to boost speed. Test isolation should be preserved across test cases: a failed test may not influence subsequent ones by leaving the database in a broken state.

Providing to each test a different connection to an in-memory database, with a whole new schema is feasible, but performance suffers greatly in this case. While a Shared Fixture may be premature optimization for in-memory objects, it's not in any nontrivial case where database istances are involved.

When I arrived on my last project, tests were correctly isolated from one another, but we had 80 tables which were recreated along with indexes and the insertion of some fixture data, for each test (some hundreds, at that time). Thus I proceeded to apply this pattern: the full suite run went from about 15 minutes to 2.

Let's reset the database

Teardown left to the single tests is complex, and results in a duplication of configuration and reset difficult to maintain. In case you're using a real database instead of an in-memory one, tests that exit will left it into an inconsistent state.

Table Truncation Teardown is a database testing pattern which maintains the same connection and database as a Shared Fixture between tests. To ensure, test isolation, it deletes everything from the tables at teardown (along with restoring common fixtures which all tests may use.) As long as your tests share the same database schema, and it is almost always the case, this pattern provides you with the equivalent of a fresh database.

Historically, the SQL TRUNCATE TABLE command was used; now it may be abstracted away by database abstraction layers. It usually avoids triggers and other active procedures from being invoked.

Implementation

There are two requirements for implementing this pattern:

• all data can be deleted: you do not have 1000 rows to restore in the new setUp(), but only an acceptable number of them.
• each runner has its own Database Sandbox, so it can do whatever it wants with it. When using Sqlite in-memory, the sandbox is not accessible at all from other processes.

The decisions to make during the implementation are multiple.

First, you need to understand how to delete the data: a TRUNCATE or DELETE command is only the start.

In Sqlite, DELETE on all tables must be followed by a DELETE on `sqlite_sequence` to make autogenerated keys start from 1 again. In MySQL, DELETE without WHERE conditions is enough.

Second, foreign key constraints are often an impediment to truncation. When you delete rows from a table, it may contain other rows which are referred to from other tables, with constraints that stops the deletion. In Sqlite, foreign key constraints are not supported, so you can just delete everything.

Third, you have to ensure your ORM's consistency: it has references to objects which do not exist anymore in its Identity Map. In Doctrine 2, the EntityManager can be reused between tests, but you must call clear() on it in order to delete everything from the Unit of Work and the Identity Map.

Normally you can execute delete queries directly from the ORM. A DELETE in Doctrine's DQL with the right cascades will delete also records which do not have a correspondent object, like rows of an user_group table for many-to-many associations.

Example

The code sample shows you how to implement this pattern with Sqlite, by running three tests on a shared connection, but with a database shared and reset between each test.

<?php
class TableTruncationTeardownTest extends PHPUnit_Framework_TestCase
{
    private static $sharedConnection;
    private $connection;

    public function setUp()
    {
        if (self::$sharedConnection === null) {
            self::$sharedConnection = new PDO('sqlite::memory:');
            self::$sharedConnection->exec('CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(255)
            )');
        }
        $this->connection = self::$sharedConnection;
    }

    public function teardown()
    {
        $this->connection->exec('DELETE FROM users');
        $this->connection->exec('DELETE FROM sqlite_sequence');
    }

    public function testTableCanBePopulated()
    {
        $this->connection->exec('INSERT INTO users (name) VALUES ("Giorgio")');
        $this->assertEquals(1, $this->howManyUsers());
    }

    public function testTableRestartsFrom1()
    {
        $this->assertEquals(0, $this->howManyUsers());
        $this->connection->exec('INSERT INTO users (name) VALUES ("Isaac")');
        $stmt = $this->connection->query('SELECT name FROM users WHERE id=1');
        $result = $stmt->fetch();
        $this->assertEquals('Isaac', $result['name']);
    }

    public function testTableIsEmpty()
    {
        $this->assertEquals(0, $this->howManyUsers());
    }

    private function howManyUsers()
    {
        $stmt = $this->connection->query('SELECT COUNT(*) AS number FROM users');
        $result = $stmt->fetch();
        return $result['number'];
    }
}