Using a test database with Drupal unit tests

In my last few posts I used Test Driven Development (TDD) to write a very simple Drupal module and showed how TDD helped to keep my custom code decoupled from the Drupal framework. This time I want to take a closer look at the biggest headache I ran into while using TDD with Drupal: handling the test data. When I started to write unit tests for my example module I ran into trouble creating test data inside each test, since I found that PHPUnit stopped executing the test each time there was a failing assertion, meaning that test data weren’t cleaned up after a test failure. I was able to avoid this problem by creating and deleting the test data before and after each test was run using the setup/teardown methods from PHPUnit. But this solution brings along different problems with it:

  • In setup() I need to create all of the test data that every test will use since it is called every time, which becomes a performance problem as the number of tests increases.
  • Teardown() still won’t be called if there are any PHP syntax errors in my test or production code, which happens a lot if I’m really using TDD.
  • There’s no way to create different test data for different tests
  • Worst of all, any existing data in my Drupal database might cause the tests to fail, and vice-versa: the test data might interfere with my development work.

We need a better approach for handling test data. Rather than reinventing the wheel, let’s take a look at the Ruby on Rails framework for some inspiration and see if we can emulate the way Rails handles test data using PHP and Drupal. How does Rails handle test data? First of all, each Rails application has multiple, different databases setup: one for development, one for production, and a third for testing at a minimum. Every time you run a unit test in Rails, the test database is manipulated as follows:

  • Rails deletes the existing contents of the test database, if any.
  • Rails loads your test database with an empty copy of your application’s database schema (tables, columns, indices, etc.).
  • Finally Rails runs each of the unit tests targeting this empty test database, by default each test within a separate database transaction (more on this in my next post).

How can we do this with Drupal? If you take a close look at the SimpleTest module, you’ll see that it uses some tricks to create a test copy of the Drupal schema using the “database prefix” feature of Drupal. While this works fine, I decided to see if I could directly follow the Rails pattern of having a completely separate MySQL database to use for testing. Let’s use PHPUnit directly on Drupal from the command line again as I did before. Here’s a very simple PHPUnit test:

<?php
require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
class TestDataExampleTest extends PHPUnit_Framework_TestCase
{  
  public function create_test_blog_post()
  {
    $node = new stdClass();
    $node->title = "This is a blog post";
    $node->body = "This is the body of the post";
    $node->type = "Story";
    $node->promote = 1;
    node_save($node);
    return $node;
  }
  public function test_there_is_one_post()
  {
    $this->create_test_blog_post();
    $this->assertEquals(1, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
}
?>

As I explained in December, we have to run the unit test from the Drupal root folder as follows (replace “modules/test_data_module/TestDataExampleTest.php” with the path to the test file):

$ cd /path/to/my-drupal-site
$ phpunit TestDataExampleTest modules/test_data_module/TestDataExampleTest.php

The “test_there_is_one_post” unit test will create a test blog post record in the node table, and then count the number of nodes in the database and assert that there is exactly one. Obviously this will fail if there are any existing node records in my Drupal database, or if I even just run the test more than once:

$ phpunit TestDataExampleTest modules/test_data_module/TestDataExampleTest.php
PHPUnit 3.2.21 by Sebastian Bergmann.
F
Time: 0 seconds
There was 1 failure:
1) test_there_is_one_post(TestDataExampleTest)
Failed asserting that <string:6> matches expected value <integer:1>.
/Users/pat/htdocs/drupal4/TestDataExampleTest.php:19
FAILURES!
Tests: 1, Failures: 1.

This failure is actually good: this test is intentionally dependent on the contents of the test database. Later if we can get this test to pass then we know we have properly initialized the contents of the database without resorting to the setup/teardown solution from last time.

Let’s get started by creating a real test database using the MySQL command line:

mysql> CREATE DATABASE drupal_test DEFAULT CHARACTER SET utf8
       COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

Now, how can we get Drupal to use this database instead of the normal one? Let’s try converting the $db_url value in settings.php into an array, like this:

$db_url["default"] = 'mysql://user:password@localhost/drupal;
$db_url["test"] = 'mysql://user:password@localhost/drupal_test';

Here I’ve renamed the original $db_url variable to $db_url[“default”], and created a new entry for the test database. Now Drupal can run against either the original database, or the test database as we wish.

The next step is to load the test database with an empty copy of the Drupal database schema. In the Rails world, there are Ruby functions that export the development database schema, and then reload it into the test database. In Drupal, the database schema is created automatically by PHP functions when you install the application for the first time. The SimpleTest module also does the same thing before running its tests. Looking at code in install.php from the Drupal installation process, and also in drupal_web_test_case.php from SimpleTest I came up with this solution:

function create_test_drupal_schema()
{
  include_once './includes/install.inc';
  drupal_install_system();
  drupal_install_modules(drupal_verify_profile('default', 'en'));
  $task = 'profile';
  default_profile_tasks($task, '');
  menu_rebuild();
  actions_synchronize();
  _drupal_flush_css_js();

  variable_set('user_mail_status_activated_notify', FALSE);
  $account = user_load(1);
  $merge_data = array('name' => 'admin', 'pass' => 'test', 'roles' => array(),
                     'status' => 1);
  user_save($account, $merge_data);  
}

Here drupal_install_system() and drupal_install_modules() will create most of the empty tables we need just the way they do when you install Drupal. The other calls I took from DrupalWebTestCase->setup() in SimpleTest to create some initial data Drupal requires to function properly, like the menu for example. The last few lines I wrote to setup the admin user properly, and to avoid sending emails to the admin during this process.

The last piece of the puzzle is to find a way to clear out the test database before each test run. To do that, I wrote this code to iterate over all of the tables in the test database and drop them:

function drop_test_tables()
{
  each_table('drop');
}
function each_table($table_callback)
{
  global $db_url;
  $url = parse_url($db_url['test']);
  $database = substr($url['path'], 1);
  $result = db_query("SELECT table_name FROM information_schema.tables
                      WHERE table_schema = '$database'");
  while ($table = db_result($result)) {
    $table_callback($table);
  }
}
function drop($table)
{
  db_query("DROP TABLE $table");
}

To put it all together we just need to call db_set_active(“test”) and call all of this code before our test runs:

db_set_active("test");
drop_test_tables();
create_test_drupal_schema();

Here db_set_active(“test”) tells Drupal we want to use the test database instead of the actual database. After switching to the test database we drop any existing tables that may exist there, and then create a new, empty Drupal schema.

To avoid cluttering my PHPUnit test file, and to be able to reuse this code in many PHPUnit tests, I moved the test database setup into a new include file called: phpunit_setup.inc. I also added some validation code to phpunit_setup.inc to perform a sanity check so you don’t accidentally drop all of the tables in your main Drupal database, and to make it easier to avoid mistakes with $db_url in settings.php. The code requires that "test" be present in the test database name. I also added code to enable database transactions in the test database, which I will discuss in my next post.

To try this out on your system, just download phpunit_setup.inc and then include it at the top of your PHPUnit test file, like this:

<?php
require_once './includes/phpunit_setup.inc';
class TestDataExampleTest extends PHPUnit_Framework_TestCase
{  
  public function create_test_blog_post()
  {
    $node = new stdClass();
    $node->title = "This is a blog post";
    $node->body = "This is the body of the post";
    $node->type = "Story";
    $node->promote = 1;
    node_save($node);
    return $node;
  }
  public function test_there_is_one_post()
  {
    $this->create_test_blog_post();
    $this->assertEquals(1, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
}
?>

The require_once statement above assumes you downloaded phpunit_setup.inc into the includes folder. If you put it somewhere else, just update require_once as necessary. Now the test passes every time:

$ cd /path/to/my-drupal-site
$ phpunit TestDataExampleTest modules/test_data_module/TestDataExampleTest.php
PHPUnit 3.2.21 by Sebastian Bergmann.
.
Time: 0 seconds
OK (1 test)

One important detail I’ve glossed over here is that the test only passes because it is the only test I’m running at all in this database. If there were a second test with it’s own test data and assumptions about what data were present, then there would be failures depending on what data each test expected, and which test ran first. Next time, I’ll show how Rails solved this problem using database transactions and show how to use them with Drupal unit tests.