Database Testing
for Fun and Profit

A Portal-themed parody of a British WWII propaganda poster: 'Keep Calm and Continue Testing'

Matthew Turland

(Use arrow keys to navigate between slides.)



"Good way of ensure worst bug is escape in
production is have 100% test coverage."

There (Will) Be Slides

Publications section of my web site at

Publications section of

I Co/Wrote These

I've Contributed to These

I Work For This Company




Got Tests?

Darth Vader from Star Wars with the caption, 'I find your lack of tests disturbing.'

Database Tests != Unit Tests

Computer scientist John McCarthy with the caption, 'Programming: You're Doing It Completely Wrong'

The Right Way™

A white mug inscribed with the words, 'You're doing it right.'


Sebastian Bergmann, lead developer of PHPUnit

Database Extension

Mike Lively, author of PHPUnit's Database extension

Supported Servers

* Requires Zend Framework or Doctrine 2


Illustrations from various signs shown at the start of levels in the Portal game series

Installing the Database Extension

Easy as PEAR:

pear channel-discover
pear install phpunit/DbUnit

Or Composer with Packagist:

  "require": {
    "phpunit/dbunit": "1.3.*"
XKCD illustration: 'Tech Support'

How Database Tests Work

  1. Put the database into a known state
  2. Run code that performs database operations
  3. Verify that actual and expected states are the same
XKCD illustration: 'Stand back, I'm going to try science.'

An Example

  1. Make a table empty
  2. Run code to insert a record into that table
  3. Verify that the table now contains only that record
XKCD illustration: 'Exploits of a Mom'

Starting from a Known State

  1. Connect to the database
  2. Truncate one or more tables
  3. Populate those tables with a given data set
Fry from Futurama with his 'I see what you did there' expression superimposed over a map of the state of Florida

Database Test Case

Two guys falling off a log in a river in the forest

Call Me Maybe

Batman slap meme referencing the single 'Call Me Maybe'

Getting a Connection

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getConnection() {
    return $this->createDefaultDBConnection(new PDO('...'));

Why Connections Work Like This

Illustration of the Jackie Chan meme

Connection Tips

'Stick 'em with the pointy end' Game of Thrones t-shirt

Reusing Connections

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected $connection;
  protected static $pdo;
  protected function getConnection() {
    if (!$this->connection) {
      if (!self::$pdo) {
        self::$pdo = new PDO('...');
      $this->connection = $this->createDefaultDBConnection(self::$pdo);
    return $this->connection;

Data Sets

Venn diagram comprised of Mr. and Mrs. Pacman

File-Based Data Sets

These are used to represent expected results in assertions.

Type Pros Cons
Flat XML simple/concise has issues with NULL
XML avoids issues with NULL more complex/verbose
MySQL XML used by mysqldump --xml MySQL-specific
YAML good if you like YAML requires Symfony YAML dependency
CSV supported by most database clients limited to one table per file, doesn't support NULL
PHP Array universal support, easy to understand non-core, working manual example

Query-Based Data Sets

These are typically used to represent actual results in assertions.

Type Pros Cons
Query Good if you want a subset of the database Tedious if you want the whole database
Database Good if you want the whole database Insufficient if you want a subset of the database
Captain Jean-Luc Picard meme

Derived Data Sets

These are transformations involving data sets of other types.

Type Purpose
Composite Aggregates other data sets
Filter White/blacklist tables and columns
Replacement Replace one column value with another
XKCD strip entitled 'Useless'

Getting Data Sets

Entire database:

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getDataSet() {
    return $this->createMySQLXMLDataSet('/path/to/mysql.xml');

Multiple individual tables as a composite:

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getDataSet() {
    $dataset = new PHPUnit_Extensions_Database_DataSet_CompositeDataSet();
    return $dataset;


The Database extension provides database-specific assertions and methods to get data usable for assertions

Description Code
Row count of a table $this->getConnection()->getRowCount('table_name')
Data for one table from a query $this->getConnection()->createQueryTable('table_name', 'SELECT query');
Data for one table from a dataset $this->create*DataSet('...')->getTable('table_name'));
Data for multiple tables from a database $this->getConnection()->createDataSet(array('table1', 'tableN'));
Single table equivalence $this->assertTablesEqual($expected, $actual)
Multiple table equivalence $this->assertDataSetsEqual($expected, $actual)

Putting It All Together

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function setUp() {
    parent::setUp(); // <-- Call me. No, seriously, CALL ME.
    // ... Initialize self::$pdo with PDO instance if needed ...
    $this->foo = new Foo(self::$pdo);
  public function testInsertWithValidRecord() {
    $this->foo->insert(array('bar' => 'foobar', 'baz' => 'foobaz'));
    $actual = $this->getConnection()->createQueryTable('foo',
      'SELECT * FROM `foo`');
    $expected = $this->getDataSetFromFile('expected')->getTable('foo');
    $this->assertTablesEqual($actual, $expected);

Seeding the Database in Other Tests

class FooTest extends \PHPUnit_Extensions_SeleniumTestCase {
  protected function setUp() {
    // ... Initialize self::$pdo with PDO instance if needed ...
    $this->tester =
      new \PHPUnit_Extensions_Database_DefaultTester(self::$pdo);
  protected function tearDown() {

Upcoming Development


QR code with a link to give feedback on

Please rate my talk!

And check out the mobile apps!

That's All, Folks