Home > Programming > How I do DB integration testing

How I do DB integration testing

I’ve tried a lot of different ways of writing testing frameworks for database interaction, but have finally settled on an approach that seems to work pretty well. I figured I’d describe it here in case it’s of use to anyone. Pretty much every project I’ve worked on in the last few years has used an ORM; for Perl I use DBIx::Class, and in Java, Hibernate. This has led me down a particular path in terms of an approach to testing, but it could probably work without an ORM, so long as you have a well-encapsulated data access layer. But for the purposes of this post, I’ll use DBIx::Class code as an example.

What I’m Testing

There are three main things I’m interested in testing at this level:

  1. That mappings, relationships, etc. are configured correctly.
  2. That any methods defined on the Schema classes work as expected
  3. That my code integrates correctly with my DB of choice

You could argue that #3 is not something I should be testing, because DBIx::Class should, in theory, abstract the DB away. That’s true, but even if it does handle things in a completely DB independent way, there may be times where I need to drop to pure SQL (usually for performance reasons).

Besides, I find this is one case where integration tests are usually less fragile and more likely to catch bugs than attempting to use mocks. In fact, in general, I try to write tests with as much of the application integrated as possible, while still making sure the tests don’t break easily, and without making it difficult to get a good set of test cases (integration tests can often have a greater range of possible inputs, and thus more edge cases).

This is probably testing heresy. But I’m not advocating trying to test everything at the “big-end”, and I still use mocks a lot. I just prefer to be pragmatic, and I find integration tests often work better (and are easier to write) than setting up complex worlds of mock.

Anyway, onto the details…

Setting up a Test DB

To do DB integration testing, we of course need a DB. I’m currently using MySQL, which (for all its faults) makes it pretty easy to dump and create schemas. I usually have a ‘dev’ database that is always up-to-date with any changes to the schema, so I just refresh from there.

I load a schema which is more or less empty. Some tables need data loaded into them, e.g. ones that have things like statuses, that don’t change very often (there’s a name for this type of data, but I can’t for the life of me remember it). All other tables (that have the “real” data) I keep empty when I refresh the schema. This ensures my tests aren’t relying on some random data to pass (that would make them fragile – if I changed the schema refresh, a lot of tests could break in ways that are hard to debug).

In my master test script (the one that runs my entire test suite), I have something like this:

# $refresh_schema is controlled by a command-line arg to the script
if ($refresh_schema) {
    print "# Refreshing schema...\n";

    system("mysqldump -d db_dev > /tmp/db_dump");
    system("mysqldump -t db_dev Status_Table  >> /tmp/db_dump");
    system("mysql db_test < /tmp/db_dump; rm /tmp/db_dump");

I only reload the schema when it changes, or if data accumulates which could affect other tests. You could in theory run it before every test run (or even every test), but it can be a little slow, and besides, it shouldn’t be necessary (for reasons which I’ll explain below).

Obviously, if you have another way of managing your schema, you could employ that here too, but I think the important thing is to keep your database relatively empty.

Base Test Class

I strongly recommend using class-based testing. For Perl, that usually means Test::Class. If you’ve never used it before, Ovid wrote a series of articles on the topic a couple of months ago, and it’s an excellent place to start.

Every test that uses the database inherits from a DB base class. This sets up a connection, for use by any testing scripts. A simplified version is below:

package Test::MyApp::DB;

use MyApp::Schema;

sub db_startup : Test(startup) {
    my $self = shift;

    my $schema = MyApp::Schema->connect( "dbi:mysql:db-test", "user", "pass", { AutoCommit => 0 }, );

    $self->{schema} = $schema;

sub roll_back : Test(teardown) {
    my $self = shift;

    if ($ENV{DB_TEST_COMMIT}) {
    else {

I connect to the DB with AutoCommit turned off. This mean tests won’t commit any of their work, and I don’t have to refresh the whole schema after every test method to avoid tests interfering with each other. Every test script starts from a blank canvas, and must setup anything it needs.

The roll_back() method is called after every test method to roll back the current transaction. But sometimes (if things are going wrong) you need to see what data a particular test is creating. So I provide an environment variable “DB_TEST_COMMIT”, which will commit the transaction, so you can use a DB viewer app to inspect the relevant tables. With DBIx::Class, turning on the DBIC_TRACE environment variable to see all the SQL that’s being executed can also be very useful for debugging.

(Note, you could probably use DBIx::Class transactions to do the above, but this approach works for me).


Since each test needs to setup most of it’s data, you can end up with a lot of boilerplate at the beginning of each test, calling create(), etc. to insert new rows. This can get complicated, as sometimes you have a lot of different tables that need data in them, and you’re only interested in a few of the fields.

My solution is to create a set of “Builder” classes, that create all the data for you, with some sensible defaults. For instance, if you were working with the classic “CD” database, and you were testing the “Artist” schema class, you might need an artist, plus several CDs by that artist created for a particular test. You could create a builder class like this:

package Test::MyApp::Builder::Artist;

use Test::MyApp::Builder::CD;

sub build {
    my $schema = shift;
    my %params = @_;

    my $artist = $schema->resultset('Artist')->create(
          artist_name => $params{artist_name} || 'Some Artist' ,

    my $cd_count = $params{cd_count} || 2;
    for (1 .. $cd_count) {
            artist_id => $artist->id,

    return $artist;

Then from your test, you just have to do this:

sub test_something : Tests {
    my $self = shift;

    my $artist = Test::MyApp::Builder::Artist->build( $self->{schema}, artist_name => 'My Artist' );
    # Now have an artist with 2 CDs setup

This, of course, can then easily be re-used in other tests.

The choice of default values shouldn’t be too important. The main idea is to ensure you’ve got enough data so that your schema’s constraints aren’t going to prevent you inserting the row. Usually, your tests shouldn’t rely on any of the default values you’re using. It’s better to be explicit about exactly what you expect to be in the DB.


I’ve found the above approach to be a pretty robust solution to testing database-backed applications. In fact, with this framework in place, I’ve found integration testing to be a lot easier. For example, instead of mocking out the DB in testing Catalyst controller methods, I use Builder classes to create the data, and allow the controller to read it from the DB. As mentioned above, you do have to be careful when using integration tests. It can be easy to over-complicate things, and try to test too much at once. Mocks are certainly necessary at times. But often integration tests are a lot easier to write.

Anyway, hopefully this wasn’t all completely obvious, and may be of use to someone 🙂

  1. December 24, 2014 at 6:20 pm

    Can I simply say what a relief to uncover someone who actually understands what they’re talking about on the web.
    You definitely realize how to bring a problem to light and make it
    important. More people have to check this out and understand
    this side of your story. It’s surprising you aren’t more popular because you definitely have the gift.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: