Testing DB Dependencies with PHPUnit

Part of maturing as a start-up company is learning how to migrate from a prototype/proof-of-concept development style to a disciplined development approach that allows the ever increasing code base to be modified without risking regressions.  For genius.com, one of the cornerstones of this migration was the introduction of unit testing into our development process.  Our first foray into unit testing was with our PHP code.  Choosing PHPUnit as the testing framework was a no-brainer but we knew that PHPUnit alone would not be enough to comprehensively test all of the functionality produced by the PHP code.  Being a development house that follows the Lean software development principles (in particular just-in-time design and development), we decided to incrementally improve upon our testing framework by prioritizing work based on how much pain individual limitations were causing.

As developers started writing unit tests, we almost immediately recognized a central problem: it is hard to test code that interacts with a database.

Initially, we tested against our development database but this meant hard-coding the tests to statically depend on data in the database.  Being a development environment, the data in the database is not static and does not allow reliable execution of the tests tied to it.  Quickly we realized this brittleness.

1st Fixture Framework

The initial improvement was the introduction of a fixture framework that allows database scaffolding to easily be written to reliably test database dependent code.  We created a separate test database with a schema matching our development database and one of our developers wrote a remarkably simple but immensely powerful framework to load YAML formatted fixture files.  As a developer writing a test using the framework, you:

  1. Create a YAML fixture file for each table you want to populate.
  2. In each fixture file, create one named entry for each row you want to insert.  Within each entry, define the column/value pairs to set.
  3. In the setup method of your test class, add the fixture files that you want loaded into the database for the execution of your tests.
  4. In the tear down method, any tables affected by the fixtures are truncated.

This revolutionized our unit testing.  Instead of having to rely on volatile data, test writers could specify the exact data on which their tests depend and guarantee that the correct data would exist in the database when their tests execute.  When the database schema changed, developers simply had to modify the fixture files to fix the data and ensure the build didn’t break.

2nd Fixture Framework

While the 1st fixture framework worked (and was vastly superior to the previous method for writing tests), we believe in always striving to improve not only the code we write, but also the way we write it.  Everything in our system is open for improvements and modifications.  This mindset resulted in the incremental development of our 2nd fixture framework.

We learned from the 1st framework what worked (YAML, fixture naming, use of setup/tear-down) and what didn’t:

  1. Many fixture files for each test. When testing relatively simple code related to contacts and users, we had to have upwards of 8 different fixture files to properly populate the necessary tables.
  2. Lots of overhead for testing reports. To test code that generates reports, we needed to populate many rows in many tables.  This required each row to be named even though the names were never used.
  3. No special handling of auto-increment pk/fk columns.  The initial framework required a developer to hard-code auto-increment column values and foreign-key connections.  For foreign-key columns, we had to hard code IDs defined in other files.  Following and visualizing dependencies was complicated and time consuming.
  4. Time-consuming to optimize db schema. If the columns in a single database table were split into multiple tables to improve indexing and decrease lock-waits, additional fixture files had to be created and the setup methods had to be adjusted to load and tear-down the new files.

The 2nd fixture framework attempted to address the above issues with the 1st framework.  We reworked the way that fixture files were written so that an individual fixture file could contain multiple tables.  The name of the file was no longer important to the test code beyond the setup declaration of the files to include for a test.  The YAML format was changed so that an individual named fixture could not only include multiple tables, but also multiple rows in a table.  Individual rows could be named or not, depending on use.

In addition to changing the YAML format used, we introduced a domain-specific language that can be embedded in the YAML in a database column value.  The language started out as a way to avoid hard-coding auto-increment primary keys and the foreign keys that link to them.  The idea was to avoid having to use:

        table_id: 1
        table_id: 1

So, as a result of the 2nd framework, you can instead now use:

        table_id: <<auto>>
        table_id: <<this.table.table_id>>

The 2nd framework made it much easier for developers to write tests for database dependent code.  This improvement in ease of use, in turn, had an appreciable impact on the growth of total tests.

Orange arrow is when 2nd framework was deployed

Three months before the 2nd framework until two months after the new framework. Orange arrow is when 2nd framework was deployed, the jump is the addition of tests for the framework.

Of course, the 2nd framework wasn’t perfect either.  The four issues outlined above were fixed but, with the developers writing and running fixture-based unit tests more frequently and more attention being paid to the nightly builds, three new issues bubbled to the surface that were causing significant pain:

  1. We only had one test database.  Developers were running tests before check-in which would frequently cause the runs to overlap.  These concurrently running tests would step on each other and cause false failures.
  2. Database schema changes. When the schema of the development database changed, we relied on the database engineer also manually making the change in the test database.  This was error prone and also caused false failures of tests.
  3. Testing multiple branches. Prior to release to production, we branch the code base. Bug fixes are made on the branch, and new development (for the next release) happens on the trunk.  The next release probably requires schema changes which are not compatible with previous releases.  Making the changes to the test database allows the trunk build to pass but breaks all other builds.  Not good.

3rd Fixture Framework

The 2nd fixture framework made it much easier to write fixture based tests but the setup and use of the database were still problems.  When trying to figure out how to solve these issues, we discussed many options but the more we talked, the more we narrowed in on a single comprehensive solution.  In order to allow multiple tests being run against potentially multiple database schemas, we would need the individual test runs to be smart enough to figure out what the database schema needed to be.  To do this, we maintain the current production database schema and the update scripts required to upgrade the production database to the schema required for the individual branch.  Essentially, in each branch we have a baseline schema (current production schema) and the diffs (update scripts) required to get from the baseline schema to the schema required for the given branch.

For example, let’s take a look at what the code branches looked like just before the 16 release was pushed to production.

  • 15 branch was on production
    /db/fullschema-15.sql        <---- baseline after 15 release
    /db/archive/15-update.sql    <---- update script applied with 15 release and archived when new baseline was checked in
  • 16 branch was awaiting deployment
    /db/16-update.sql            <---- update script to apply during 16 release
  • trunk had active development for the 17 release
    /db/17-update.sql            <---- update script to apply to 16 baseline during 17 release

Storing the entire schema in the repository allows us to programmatically create a new database instance with the proper schema prior to running tests.  Of course, following execution, the new database instance needs to be dropped.  Running the unit tests in different branches causes different, but related, setup to occur:

  • 15 branch
    1. new database is created
    2. fullschema sql file is run to setup the proper schema for the 15 release
  • 16 branch
    1. new database is created
    2. fullschema sql file is run to setup the proper schema for the 15 release
    3. 16-update sql file is run to upgrade the schema from the 15 release version to the 16 release version
  • trunk
    1. new database is created
    2. fullschema sql file is run to setup the proper schema for the 15 release
    3. 16-update sql file is run to upgrade the schema from the 15 release version to the 16 release version
    4. 17-update sql file is run to upgrade the schema from the 16 release version to the 17 release version

Keeping the baseline and the archive directory properly updated introduced a new wrinkle into our production push process.  Upon completion of a production release, we now export the current full production database schema, check it into the branch pushed, move the applied update script into the archive directory, then merge the changes forward.  When release 15 went out, we checked its schema into the 15 branch, moved its update script into the archive directory, and merged both changes into the 16 branch as well as into the trunk.  Thus fixing all of the previously mentioned database problems.

We’ve made additional changes to the framework, including expanding the power of the DSL.  Some of these changes will probably end up being their own posts.  Future plans include porting it to other languages we use.

We’d love to hear feedback from others who have dealt with similar issues.  Also let us know if you are interested in more in-depth info on how it works.  If there is enough interest, we’ll work on open sourcing it.

  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Bookmarks
  • DZone
  • HackerNews
  • LinkedIn
  • Reddit
  • http://www.thedeveloperday.com zilvinas

    I would more than interested to see the code how you run your unit tests. Do you use PHPunit tests to create and drop databases? Could you please send me code examples to < >.

    Appreciate it.

  • http://www.thedeveloperday.com zilvinas

    crap ;) please delete my email from the comment till crawlers catch it ;D

  • Eugene

    hi there,

    it will be interesting to see the source code – how it works for you. please keep posting how you implemented it!

  • Pingback: Database Fixtures for Isolated Testing in PHP | Team Lazer Beez Blog