Testing the database layer is a fundamental step during application development. It allows us to ensure that all data operations behave properly, without data loss or corruption. In this article, we are going to describe a simple way to test the database layer of our applications, using PHPUnit database classes, Zend Framework extensions and some custom reusable code to facilitate the process for all the domain models. This article will walk you through, step by step, for each of the tasks needed to achieve this.

A Database Test Case will allow us to verify that each read operation retrieves the correct data and each write operation affects the database as expected. As for the write operations, we will consider the dataset as a whole, to ensure that the database layer is not corrupting the data, by modifying the wrong table for instance. The application data might reside on several different storage systems. However, we can identify four generic functions: Create, Read (or Retrieve), Update and Delete, usually referred as CRUD. Here we focus on CRUD operations on databases, though the following concepts would also apply to any sort of persistent data store.

Before getting started, let’s consider the testing environment for a moment. Running tests on a live database is strongly discouraged because other running processes (such as CRM interactions, e-commerce transactions, users registrations etc.) would affect the test case; we also do not want our tests interfering with live transactions. Although we will need to test concurrent reads/writes and data coherence, we will always require complete control of these processes and exclusive access to the data, so we will run all the tests on an isolated environment that includes dedicated storage not affected by any external agent.

Database testing challenges

Creating database test cases can be a long and complex process, but in short the steps are as follows:

  1. Identify the dataset for each domain model
  2. Replicate the production data schema in the testing environment
  3. Create data for the tests, since we don’t want to use real data such as emails, credit cards numbers, images etc.
  4. Prepare the expected dataset to compare our test results
  5. Rebuild the dataset before each test, since multiple tests could conflict and cause our test cases to fail

 

We will visit each of the steps needed to create the database test cases, automating most of the work and describing the best practices for the tasks. With further development it would also be possible to speed up the creation of the data required (2 and 3), and eventually focus only on writing tests and assertions.

Database Testing Checklist: What To Test

Before starting, we need to identify what we intend to test with our Database Test Case. Database testing focuses on the database access layer, where the application communicates with the database.

A domain object model encapsulates some defined logic and usually requires some information to be stored in properties. Often, some of this information is stored in the persistence layer with a schema that differs from the way the object is represented. It may be stored in one or more tables or documents or files, depending on the software and the relationships. A data mapper object encapsulates the logic to store this information, and this is the object that we want to test.

To simplify, we assume we have an object model with a database schema represented by a single table, however the same approach would work for more complex scenarios. In order to show how to test for data corruption and to explain how to validate the entire dataset, our sample database will contain a second table that should never be affected. The ideal is to write one test case for each model, testing all read and write operations. Each test case should contain tests for the following units:

  • Create:
    • Create one instance of the model in the database (create one new record)
    • Verify that the database is in the expected status (check all database content)
  • Read:
    • Retrieve one instance of the model from the database (read one record)
    • Verify that the returned data match the expected result
  • Update:
    • Update one existing instance of our model in the database (update one record)
    • Verify that the database is in the expected status (check all database content)
  • Delete:
    • Delete one existing instance of our model from the database (delete one record)
    • Verify that the database is in the expected status (check all database content)

 

We will focus on these four tests for now. Later, we could build more specific tests to verify constraints, referential integrity, transactions, stored procedures, triggers, or any other database operation.

What Not To Test

We are testing the database access layer, so we will not test specific values. This means that we do not test the empty strings, null values, password length, email validity or other elements that belong to the business logic implemented in the domain models. Neither are we going to test data sharding, replication or other specific storage features as they belong to the database model.

Provision for Database Testing in Zend Framework Using PHPUnit

PHPUnit provides an easy way to use seed data to initialise a database in a known state, execute the database-affecting code, and ensure that the expected data is found in the database. Zend Framework extends PHPUnit classes adding support for Zend Datasets and Rowsets, allowing us to create tests for classes that manipulate datasets, for example we can write assertions on query results. Since PHPUnit 3.5 the database testing functionality (provided by the DbUnit extension) and the respective DatabaseTestCase class have been moved to a separate component, however this does not affect the way we write tests.

Guide to Writing Tests

Since we are testing the database layer we need to identify the units which operate on the database. Following Zend documentation on how to write Models, we have three classes:

  1. Application_Model_User: business logic in the domain layer
  2. Application_Model_UserMapper: responsible for transferring data from the application to a storage model
  3. Application_Model_DbTable_Users: data source model; in our example we extend Zend_Db_Table_Abstract but we could use any (and even multiple) data sources

 

Our database test case is going to test the data mapper only. We do not need to test Zend_Db_Table_Abstract since it is part of the framework and we assume that it is already tested. Most applications based on Zend Framework use PDO db adapters for the main storage, which is usually configured in the application.ini. PDO supports MySQL, PostgreSQL, MS SQL, Oracle, SQLite and many others (see here for a full list). PHPUnit and Zend_Test_PHPUnit_db work perfectly in this scenario and we can easily swap the storage used by the live application for a SQLite database during the tests. It is also fairly easy to use different adapters using custom find/insert/update methods; to achieve this we would write extra code to compare PHPUnit DataSets with the custom DataSets and RowSets. Our tests will need some global settings such as database access details and seed files location, which we will store in the application’s configuration folder.

According to the principles of Test Driven Development (TDD), we start writing our tests, see them failing, and iteratively create the code and resources needed to satisfy the tests. The process looks something like this:

  • Step 1: Creating the test case
  • Step 2: Writing the tests
  • Step 3: Creating the seed files
  • Step 4: Preparing the testing database
  • Step 5: Writing the model
  • Step 6: Writing the data mapper
  • Step 7: The data source
  • Step 8: Preparing the expected result

 

We will work through each of these steps in turn.

Step 1: Creating the Test Case

Following Zend Framework unit testing conventions we should have a tests folder in our project (this folder is automatically created if the project is created with Zend_Tool), where we replicate our application’s folder tree. In a simple, non-modular application, models and data mappers are stored in application/models/ and the related test cases in tests/application/models/.

First of all, we prepare the ground for our tests, implementing some methods to handle the database connection and return the initial data.

Zend_Test_PHPUnit_DatabaseTestCase truncates the dataset before each test and calls getDataSet() to get the data and re-populate the database. This means that, in effect, the database is rebuilt before each test, so we can assume that it will always be in the same state and the tests will always run under the same conditions. These tests cannot be run on a live database because they would wipe the production data when the database is reset.

The methods getConnection() and getDataSet() need to be included and customised in every database test case, so we take some extra steps:

  • to avoid hard coding the database configuration in each test case, we store the testing environment settings in application/configs/tests.ini
  • to avoid repeating the same code we create a reusable abstract class

 

The configuration for the test suite should be placed in tests.ini, and should look something like this:

The testing database is defined by the settings testdb.adapter and tests.dbparams.dbname, in our example this is a SQLite3 database stored in the tests/fixtures/db folder.

The following abstract class encapsulates the reusable code. It retrieves the configuration from tests.ini and creates a database connection. The seed file name is stored in a protected property $_initialSeedFile to be set in each test case. Since PHPUnit is unable to compare rowsets and records, we add a couple of methods to convert a rowset and a record into a dataset. An extra method allows us to compare a dataset with a file. All the code used in this example is on github; take a moment to review the class Ibuildings_Test_PHPUnit_DatabaseTestCase_Abstract there.

We can now write database test cases without worrying about the database connection and the dataset creation since these are already taken care of. We will only need to prepare the database schema, the seed files, write tests and have a dataset to verify each unit test. We will begin with UserMapperTest.php, extending the above abstract class. All we have to do is to set the protected variable pointing to the initial seed file stored in tests/fixtures/models/.

Step 2: Writing the Tests

At this point, we are ready to begin creating our unit tests. The ideal process is to write one test, run the test case, fix the errors (or rather write the code files needed to satisfy the test), and then proceed with another test. For brevity we write the four CRUD tests all at once. Below is the function to test the Create function: we prepare a new user and use the data mapper to store it, then we compare the entire database with usersInsertIntoAssertion.xml.

Our testing database contains only two tables (users and administrators, but could have more of course) and we want to assert that a user creation doesn’t affect the administrators table, which is not meant to be accessed during this test. To compare the two datasets we use assertDataSetsMatchXML(), a method we derived from assertDataSetsEqual() which is provided by PHPUnit framework and is used to compare two datasets. Our tests to Retrieve, Update and Delete are very similar – check out the full class Application_Model_UserMapperTest.

If we now run the test case, the four tests will fail, complaining about the missing seed files with errors similar to: InvalidArgumentException: Could not find xml file: […]usersSeed.xml. This is expected; we now go on to create the code that is tested by this class.

As an aside: if PHPUnit complains about phpunit.xml, double check the file tests/phpunit.xml has the following minimal configuration:

The next step is to provide the initial data to set up the testing environment.

Step 3: Create the Seed Files

As described above, the seed file is imported for each test running getDataSet(). PHPUnit supports three different formats for the seed files: flat XML, XML with NULL values, or multiple CSV files. We are going to work with flat XML data, so that we can store multiple tables in one file without being too verbose. However, switching from one format to the other is very simple. Here is the initial seed file used in our tests:

 

We create the usersSeed.xml file under tests/fixtures/models as described and then run the test case again. PHPUnit will complain about a missing table, because we have not prepared the database yet. PHPUnit is trying to truncate the test tables to import the seed file, the error we get is:

Application_Model_UserMapperTest::testCreateUsers
PHPUnit_Extensions_Database_Operation_Exception: COMPOSITE[TRUNCATE] operation failed on query: TRUNCATE users using args: Array()
[SQLSTATE[HY000] [14] unable to open database file]

or, if the database exists but the table is not present:

PHPUnit_Extensions_Database_Operation_Exception: COMPOSITE[TRUNCATE] operation failed on query: TRUNCATE users using args: Array() [SQLSTATE[HY000]: General error: 1 no such table: users]

Step 4: Preparing the Testing Database

The testing database needs to be created manually, using the same schema as the production database. Depending on the testing database there are tools that can be used to automate this step. For brevity we use a sqlite3 file with a Users table created manually. We can quickly create a SQLite db from the command line using these commands:

$ cd tests
$ mkdir -p fixtures/db
$ cd fixtures/db
$ sqlite3 testing.sqlite3
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE "users" ("id" integer NOT NULL PRIMARY KEY, "username" text NOT NULL, "password" text NOT NULL, "email" text NOT NULL, "name" text NOT NULL);
sqlite> CREATE TABLE "administrators" ("id" integer NOT NULL PRIMARY KEY, "username" text NOT NULL, "password" text NOT NULL, "email" text NOT NULL, "name" text NOT NULL);
sqlite> .quit

If we run the test case now, we will receive a fatal error “Fatal error: Class 'Application_Model_User' not found” because the class Application_Model_User does not exist yet, so the next step is creating our domain model to fulfil the tests’ expectations.

Step 5: Writing the Model

The test case defines the requirements of the User model: we want a User model with five properties for id, username, password, email and name, their setters/getters, a rich constructor for a rapid instantiation and an array adapter to access all the properties at once.

This is the Application_Model_User (click to see the code from github) domain model stored in application/models/User.php

Even with this class in place, running the test case will result in another fatal error: “Fatal error: Class 'Application_Model_UserMapper' not found. The class Application_Model_UserMapper does not exist, so the next step is to create the User Data Mapper.

Step 6: Writing the Data Mapper

We create the data mapper class as described in the Zend Framework documentation, with some minor changes; it connects our domain model to a data source handling all the database operations. The full class can be found by viewing the Application_Model_UserMapper class in the github repository.

Step 7: The Data Source

Running the test case now will throw (yet another) error because we have not written the data source class yet, however we are making some good progress. In our example, the data source is an extension of Zend_Db_Table_Abstract; however, the code could be adapted to work with any data source, as applicable to your system.

The error we get is “Fatal error: Class 'Application_Model_DbTable_User' not found” so we code our simple data source class in this way:

Step 8: Preparing the Expected Result

As we can see running the test case the four tests fail due to some missing file, with the message “code>InvalidArgumentException: Could not find xml file: usersInsertIntoAssertion.xml”. We have not created the expected result yet, and that is our final step.

We need to test our database operations to ensure that the result and the database status are correct. In order to assert that a result is correct, we compare the data returned from a read operation with a predefined record set. Similarly, in order to assert that the database status is correct we compare the whole database with a data set representing the expected state.
Both the record set and the data set can be represented as xml files similar to the way we made the initial seed, so we need to create the expected result for each CRUD operation. These are the files we need to build the test case:

    1. usersInsertIntoAssertion.xml : database status after Creating one user
    2. usersRetrieveOneAssertion.xml : query result Retrieving one User
    3. usersUpdateAssertion.xml : database status after Updating one user
    4. usersDeleteAssertion.xml : database status after Deleting one user

 

We will store these files in tests/fixtures/models, and you can view them there in the github project.

      • usersInsertIntoAssertion.xml
        After creating a new user, our Users table will have three rows that will look like this: 
      • usersRetrieveOneAssertion.xmlWe will retrieve the User with id=2, it has to match the following data.

         

      • usersUpdateAssertion.xmlOur test will update the user with id=2. After that our Users table will look like this:

         

      • usersDeleteAssertion.xmlWe try deleting the user with id=1, then the Users table has to match this record set:

         

 

We can now run the test case without any fatal PHP errors. PHPUnit will run four tests and display a report, hopefully a complete success! If a test failed, PHPUnit would display all the details about the error, the actual and the expected data, allowing us to see why the test failed looking at the values in the tables. Debugging, refactoring and running the test case, eventually all the tests will run successfully, asserting that our application database layer is acting correctly.

That’s just about all we need to know to write database test cases. We can replicate the same approach to test all our Models and the Database layer.

Useful Resources