In this post, you will learn how to set up PHPUnit to test the database layer of your application. Unit tests are usually
focused on testing object models, but they can also be used to test interactions with the database to prevent
regressions in data persistence. If you have a complex application that involves a database, it is very important to
validate that it is working properly so you can be more confident in your deploys.
The examples used in this post are based on a sample “library” application. You can find all source code for the app
on Github. The sample has an accompanying
Vagrantfile for creating an ubuntu VM with the sample database initialized, and uses Phinx for
managing the database migration. Follow the README for steps to initialize.
The application is a basic example of a library system. There are two tables: authors and books.
Each record in the books table represents a book in the library. Each book has an ISBN number, a title and an author,
which is stored using an author_id. The author_id in the books table is a foreign key to the id in the authors
In the sample database library, there are four books:
A Short History of Nearly Everything - Bill Bryson - 9780767908184
The Life and Times of the Thunderbolt Kid - Bill Bryson - 9780767919371
The Road to Little Dribbling - Bill Bryson - 9780385539289
The Kite Runner - Khaled Hosseini - 9781594480003
The code structure of the application is shown below.
The source code is located in the src directory, and the corresponding tests are located in the tests directory.
In a fully tested application there should be a corresponding tests/Unit folder at the same level as the tests/Persistence folder,
but this sample application only covers the Persistence (database) testing requirements so the unit tests have been left out.
The BookMapper class is the database mapper class that queries the database table and maps the resulting records
into Book objects. There are 5 functions in the class that will be tested:
fetches all books in the books table, with author name from the authors table
fetches zero or one book in the books table, matching the provided ISBN number
creates a new record in the books table
updates a record in the books table
deletes a record in the books table
Each of these functions will be tested using the BookMapperTest.php class.
Setting Up The Tests
The first thing to do when setting up the tests is to install PHPUnit. The best way to do this is
Ensure that both phpunit/phpunit and phpunit/dbunit are included in the require-dev section of the composer.json
Next create a file phpunit.xml.dist at the root of the project to define the bootstrap for the project, the database
connection details for the tests, and any specific test suites that you require. In the case of this sample, there
is only a persistence suite:
Finally create a bootstrap.php file at the root of the tests directory to initialize the tests for phpunit, and create
an empty tests/Persistence directory.
At this point, assuming you have not yet created any test files, you should be able to run the phpunit suite with no
Creating The Test Classes
First, create a file BookMapperTest.php in the tests/Persistence folder. The file should look something like this,
with a test function for each of the functions that exist in the BookMapper.php file.
Note two things about this file:
the getDataSet function returns an empty dataset (this will be filled in later)
the class extends DatabaseTestCase, which you may have also noticed in the code structure image above
Create the DatabaseTestCase.php file next, in the tests/Persistence folder:
The DatabaseTestCase class extends the PHPUnit_Extensions_Database_TestCase, which is the base phpunit/dbunit test case class.
The trait used by PHPUnit_Extensions_Database_TestCase contains the following abstract functions, requiring the test
class to implement:
By creating our own abstract version of the base test class (as recommended in the
it allows you to define the database connection to be used in all of the tests. In the example we are using the main
database connection (as defined in phpunit.xml.dist), which is the same connection that the application itself uses.
This can also be adapted to use a specific test version of the database, depending on your application and it’s
Now if you run the tests, you should see 5 tests run, all incomplete:
Creating Test DataSets
There are four documented ways to create datasets for database tests run with PHPUnit: XML, YAML, CSV and Arrays. The
first three methods are natively supported, and the fourth can be supported with some custom code written. Let’s go over each.
XML datasets can be used to store the test data used in the suite, with each XML tag inside the root node <dataset>
representing a row in a database table. The tag name should be the name of the database table, and attributes should
be the column names in the table.
The XML file data is retrieved by a test class using the createFlatXmlDataSet function:
YAML datasets can also be used to store the test data used in the suite. The structure of the YAML file is very
straightforward, with headings for each table and blocks below for each row in the table:
The YAML file data is retrieved by a test class using the PHPUnit_Extensions_Database_DataSet_YamlDataSet function:
CSV datasets are slightly different in structure than XML and YAML datasets, as you must load each table individually
with the data for each table contained in a separate file. Maintaining the datasets this way, however, is much easier
if you are extracting the test data from an existing database, as the data dumps can be configured per file to match this
The CSV files are retrieved per table by a test class using the PHPUnit_Extensions_Database_DataSet_CsvDataSet function:
Array datasets can be included directly in the getDataSet function, or can be loaded using included files similar to the
methods used above. However, you need to write your own data iterator which you can find a sample of in the
PHP Manual. In the case of this example, the
dataset is loaded using the custom ArrayDataSet class:
Writing The Tests
Finally, you can now write the tests for the BookMapper class. Start with the first incomplete test function and work
your way down.
In this test, you should create a $bookMapper object, using the test adapter from the DatabaseTestCase class. Call
the fetchAll() function, and verify each of the expected properties in the resulting data against the values inserted in
the test datasets.
Now if you execute the tests, you should see that there are 4 incomplete tests and one passing test:
Continue filling in each of the remaining incomplete tests.
Finally, run the full test suite to ensure all tests are passing.