Categorized | Best practices, News

How To Test Methods that Write to the Database

Posted on 11 May 2005 by Demian Turner

This question came up in a conversation with Marcus Baker, maintainer of the SimpleTest project, and he was kind enough to give the following detailed response:

Ok, there are three main strategies here. You can go all for one
strategy or mix and match. Here they are…

1) The most extreme – mock the database connection always. It means that
the mocks have to check SQL queries. Although this is a very quick way
to initially write the code, the sensitivity of these tests to changes
quickly buries you. Even changing a table name will ripple through the
test suite. What you can then do is refactor to make these tests easier,
for example using a SQL object rather than a string. I have personally
managed to make this scheme work, but it puts a lot of flexibility into
the code that you probably don’t need. And as always with mocks you
still need an integration test or two to confirm that it is all working.
Runs fast though.

2) The dumbass approach – send the objects to the database and confirm
the existence with low level calls. So you might have $object->save() in
the code and then you write assertRow(…) to confirm a table row was
written out. This has all of the test sensitivity problems of number 1
plus anyone reading the tests has to understand the mapping between the
two layers. This is pretty effective at getting you started though, so
it’s often the first test you write. Sometimes you just keep a few for
sanity checks, but otherwise refactor your way out of this pickle as
fast as you can.

3) Mathematical purity – the complete set of operations. This is my
currently prefered method. You only write your test code in the object
language you are trying to create. So if you are testing the search,
then you first save the objects using you new code. Something like…

class MyTest extends UnitTestCase {


    function testSearch() {

        $apple = &new Fruit(‘Apple’);


        $finder = &new FruitFinder();

        $result = $finder->findAll(‘Apple’);

        $fruit = &$result->next();

        $this->assertEqual($fruit->getType(), ‘Apple’);



The catch is that you need to be able to clean up before and after the
tests, so you will have to add methods like Fruit::deleteAll(), etc.
This is what I mean by a complete set of operations, coding one means
that you have to code the lot. The problem is bootstrapping this process
so that you can still develop incrementally.

My prefered approach is to start with 1 and once up switch to 3. I still
keep some ones around to simulate failure conditions.

Bookmark and Share

5 Comments For This Post

  1. Andrew Hill Says:

    This is an interesting idea, but it seems to me that Marcus’ preferred approach is only ideally suited to applications with an object persistence model.

    In many applications, writing to and reading from the database is not done in a “one-to-one” style — that is, often one part of the application writes some non-object based data to the database, while another part reads out from the same data, but in a different way (eg. a sub-set of the fields written).

    In these applications, testing the Data Access Layer (DAL, which separates the logic of reading/writing data from/to the database from the application logic) needs to take into account the lack of symmetry between reads and writes. If the DAL doesn’t have symmetry, then approach number three doesn’t really work.

    Of the other two options Marcus has suggested, I actually prefer the second option, especially when database portability is required. I realise that this means that the DAL is not actually unit tested, but rather integration tested, but it gives the developer a greater deal of confidence that the DAL to database connection works — and when the DAL is the lowest level of the application used by the developer when programming the application logic, this is very important.

    The first option is, I feel, simply too unreliable to even bother with – if your DAL needs to support multiple databases, then you can’t simply test the SQL code to make sure it is correct, because so many databases don’t even support fully-compliant SQL. Thus, using the second option, and testing the DAL code with a real database connection (on each database supported) is the only convenient way to ensure the DAL code is really doing what it should.

    Finally, there is a sub-set of SQL commands that a DAL may issue that do not conform to the standard read/write approach. For example, in a project at work, we run some SQL commands that summarise data from raw tables into summary tables, without ever “reading” or “writing” the data to/from the programming language. In this case, the only way to “unit” test these DAL methods is to put some data into the database, run the DAL methods, and then inspect what is in the database afterwards.

    So, perhaps testing with a real database connection isn’t necessarily the “dumbass” solution Marcus suggests. 🙂

  2. Marcus Baker Says:


    You are right of course, I am assuming that you are using an object model at the level at which you are testing. Even so, if you are using a data centric system, a ResultSet/RecordSet interface is still common. You can still test the mechanics of this part, even if you cannot do anything other than integration testing for funny SQL or stored procs.

    Is the situation you are describing one where some of the logic is in the database? For example special ordering of queries, removing distinct rows, etc. I’d love to hear about some specific examples to these theories further.

    yours, Marcus

  3. Patrick Ramsden Says:

    Would also suggest looking at two other places for additional thoughts. You can find the relevant links by Googling for them

    1. ObjectMother
    2. DBUnit

  4. Andrew Hill Says:

    Hi Marcus,

    Yes, I am indeed talking about cases where some of the application logic is implemented in the database, using SQL.

    As an example, take a look at Max Media Manager. In particular, the summariseConnections() method in this file.

    I can’t, at present, see any real way of testing code like this without a database connection.

    Cheers, Andrew.

  5. Andrew Hill Says:

    Hi again,

    Sorry, the system stripped out my links.

    Max Media Manager:

    The file I am refer to:

    Cheers, Andrew.

Leave a Reply



Demian Turner's currently-reading book recommendations, reviews, favorite quotes, book clubs, book trivia, book lists



PHPkitchen recommends you also check out the following sites :

Accounting for Small Businesses

FreeAgent sign-up