Bulletproofing Database Interactions with PHPUnit Database Extension

Share this article

There’s already a great article here that discusses Test Driven Development, but did you know that you can also test the code that interacts with your database? Especially if your application is data intensive, subjecting your CRUD code to a battery of tests is good practice that helps ensure that your application is working correctly.

In this article you will be learning how to write database tests in PHP using PHPUnit and its database extension. Accompanying code can be found on GitHub, so feel free to check the source and play with it.

For this exercise you’ll need PHPUnit and the PHPUnit Database extension. You can install both using PEAR. Just follow the instructions listed in the PHPUnit documentation.

Editor Note Oct 20 2012: You can install both PHPUnit and the PHPUnit Database extension using Composer. The accompanying code on GitHub has been updated to use Composer for installing these dependencies.

Warming Up

It’s time to get your feet wet! That means, just like in any software project, we’ll first examine what we are going to program.

Imagine you are the back-end ninja of an online webzine and you’ve just received the spec for a new magazine you’ll be building:

  • The magazine will be composed of many sections or webpages.
  • Each section shows at most four feature articles which are set by the editor of that section.

For this article, we’ll only test retrieving the articles when they are going to be displayed on the page. Hence we won’t be concerned with the use case where the editor chooses the articles. We’ll assume the feature articles have already been set and are in the database. This way we are isolating the behavior we want to test so we can focus solely on it (just like how you will actually do it in practice).

Of course a necessary step here since we are writing a database test is to set up data. After all, we need something to test the CRUD operations on!

Use the database dump included in the source code section to create the database in MySQL. Here’s a bird’s eye view:

There are three ways to set up file-based test data, but for our purposes here we’ll use the XML Data Set. Create an XML file named seed.xml; the root element of the document should be dataset. Next, type in the table definitions and row data. For example, here’s how it looks for the articles table:

<dataset>
 <table name="sections">
  <column>id</column>
  <column>name</column>
  <row>
   <value>1</value>
   <value>Gadgets</value>
  </row>
 </table>
 <table name="articles">
  <column>id</column>
  <column>title</column>
  <column>description</column>
  <column>content</column>
  <column>preview_image</column>
  <column>section_id</column>
  <row>
   <value>1</value>
   <value>Android vs iOS</value>
   <value>Lorem ipsum dolor sit amet</value>
   <value>Aliquam scelerisque rhoncus porttitor. Nullam eget pulvinar magna. In vel lectus ut diam adipiscing porta vitae id nisi.</value>
   <value>android.jpg</value>
   <value>1</value>
  </row>
...
 </table>
</dataset>

I recommend you to write your own data just for practice, although you can always use the sample dataset included with the files for this article. All the tables there are filled already.

Writing the Test Class

At this point we are now actually going to code something! We need to tell PHPUnit to insert the test data into the database every time we run our test, which is done by creating a test class. You can think of the test class as just like any other class but which contains test cases for some other class being tested.

For database tests using PHPUnit, the test class extends PHPUnit_Extensions_Database_TestCase. We can tell PHPUnit how to connect to the database by overriding the getConnection() method, and then where to get our test data by overriding the getDataSet() method:

<?php
require_once "PHPUnit/Autoload.php";

class ArticleDAOTest extends PHPUnit_Extensions_Database_TestCase
{
    public function getConnection() {
        $db = new PDO(
            "mysql:host=localhost;dbname=bulletproof", 
            "root", "password");
        return $this->createDefaultDBConnection($db, "bulletproof");
    }

    public function getDataSet() {
        return $this->createXMLDataSet("seed.xml");
    }
...
}

Suppose you and the other developers agree on the following interface:

<?php
interface IArticleDAO
{
    public function getArticles($sectionId, $isHome);
}

The first test to write is also the simplest. That is, we are first coding for the case where a section is not a home page. We expect to get the articles that only belong to that section. According to my seed.xml file, one such section is the Gadgets section. It has an id of 1 and has the following articles:

  • Android vs iOS
  • Android vs Wp7
  • iOS 5

Thus, when we get all the articles for this section, the above articles should be returned. This is reflected in the test as shown below (for brevity, I have omitted selecting the other attributes).

<?php
require_once "PHPUnit/Autoload.php";
require_once "ArticleDAO.php";

class ArticleDAOTest extends PHPUnit_Extensions_Database_TestCase
{
...
    public function testGetArticlesNonHome() {
        $articleDAO = new ArticleDAO();
        $articles = $articleDAO->getArticles(1, false);
        $this->assertEquals(
            array(
                array("id" => 1, "title" => "Android vs iOS"),
                array("id" => 2, "title" => "Android vs Wp7"),
                array("id" => 3, "title" => "iOS 5")),
            $articles);
    }
...
}

The testGetArticlesNonHome() method first instantiate a new instance of the article data access object (DAO) just as if this were being used in production code already. The DAO is responsible for encapsulating and abstracting the calls to the database. Then the DAO’s getArticles() method is called. This is the method we want to test, so the results are placed in the $articles variable for inspection.

Once we have the results of the method call in $articles, they are compared against something that is expected. In this case, we expect an array of the three articles that the Gadgets section has. The assertEquals() convenience method that comes with PHPUnit is used to compare equality.

Passing the Test

In TDD, failure is the first step. If you run the test now it will fail because we haven’t written the implementation yet.

jeune@Miakka:~/Bulletproofing$ phpunit ArticleDAOTest.php
PHPUnit 3.6.7 by Sebastian Bergman.

PHP Fatal error:  Class 'ArticleDAO' not found in /home/jeune/ArticleTest/ArticleDAOTest.php on line 18
PHP Stack trace:
...

The next step is to write the code that will let us pass the test (that is, get the articles from the database according to the business logic). Let’s start a class that implements the interface agreed on earlier with the other developers:

<?php
class ArticleDAO implements IArticleDAO
{
    public function getArticles($sectionId, $isHome) {
        $db = new PDO(
            "mysql:host=localhost;dbname=bulletproof", 
            "root", "password");

        $result = $db->query("SELECT a.id, a.title FROM features f LEFT JOIN articles a ON f.article_id = a.id AND f.section_id = 1");
        $articles = array();
        while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
            $articles[] = $row;
        }
        $result->closeCursor();

        return $articles;
    }
}

Don’t forget to include the above class into the test class’s file.

Now run the test again and you should see a passing test:

jeune@Miakka:~/Bulletproofing$ phpunit ArticleDAOTest.php
PHPUnit 3.6.7 by Sebastian Bergman.

.

Time: 0 seconds, Memory: 6.50Mb

OK (1 test, 1 assertion)

Fixing Bugs

You’ve passed the test, committed your code, and sent it off for QA. You give yourself a pat on the back and start bumming around at 9gag. But wait, QA detects a bug!

When a section doesn’t have any articles set, it returns articles from nowhere! To confirm, you need to write your seed.xml to have a section with no articles.

<dataset>
 <table name="sections">
  <column>id</column>
  <column>name</column>
  <row>
   <value>1</value>
   <value>Gadgets</value>
  </row>
  <row>
   <value>2</value>
   <value>Programming</value>
  </row>
 </table>
...
<dataset>

Account for the new bug by writing a new test for the case where a section has no articles:

<?php
class ArticleDAOTest extends PHPUnit_Extensions_Database_TestCase
{
...
    public function testGetArticlesNonHomeNoArticles() {
        $articleDAO = new ArticleDAO();
        $articles = $articleDAO->getArticles(2, false);
        $this->assertEquals(array(), $articles);
    }
...
}

True enough, the implementation fails when we run the test:

jeune@Miakka:~/Bulletproofing$ phpunit ArticleDAOTest.php
PHPUnit 3.6.7 by Sebastian Bergman.

F.

Time: 0 seconds, Memory: 6.75Mb

There was 1 failure:
1) ArticleDAOTest::testGetArticlesNonHomeNoArticles
Failed asserting that two arrays are equal.
--- Expected
+++ Actual
@@ @@
 Array (
+    0 => Array (...)
+    1 => Array (...)
+    2 => Array (...)
 )

/home/jeune/ArticleTest/ArticleDAOTest.php:33

FAILURES!
Tests: 2, Assertions: 2, Failures: 1.

The error message says we expected an array with no contents but the method call returned an array with contents. That is obviously a bug! Try to pass the new test by correcting the code.

Summary

In this article we’ve seen how to write a database test in PHP using PHPUnit’s Database Extension which makes it easier to ensure that code that interfaces with the database is correct. More importantly, it gave us confidence to make changes when fixing bugs and when improving/refactoring our code. Software is subject to a lot of changes, just like anything else in life. Writing automated tests can help you adapt your code to those changes more efficiently and confidently.

Image via Tatiana Popova / Shutterstock

Jeune AsuncionJeune Asuncion
View Author

Jeune has a fascination for building and solving things. Right now, he is interested in data mining, algorithms, performance, and software design. When not in front of his laptop, he cooks mean Asian dishes or is out kicking ass on the football (soccer) field.

Advanced
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week