News Archive
PhpRiot Newsletter
Your Email Address:

More information

Unit Testing Databases with Zend Framework

Note: This article was originally published at Planet PHP on 7 December 2010.
Planet PHP

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

Truncated by Planet PHP, read more at the original (another 44713 bytes)