PHPUnit: how can I remove test data from my database after the tests are complete?

Question

I have a Zend Framework application (version 1.11) that uses Doctrine 2. I've got PHPUnit set up to run tests on my models and forms and whatnot. The tests work great, but there's one problem: they leave the test data in the database once they are done. Here's a basic sample of one of my tests:

class VisaTypeEntityTest extends ModelTestCase
{
    public function testCanSaveAndRetrieveVisaType()
    {
        $addVisaType = new \Entities\VisaTypes();
        $addVisaType->setEnglishName('Test Visa Type')
            ->setJapaneseName('試し')
            ->setKanaName('タメシ')
            ->setDescription('Description of the test visa type');

        $this->em->persist($addVisaType);
        $this->em->flush();

        $getVisaType = $this->em->getRepository('\Entities\VisaTypes')
            ->findOneByEnglishName('Test Visa Type');

        $this->assertEquals('Test Visa Type', $getVisaType->getEnglishName());
    }
}

Obviously the data has to actually be entered into the database in order to make sure everything is kosher. But I don't want all the test data gumming up the database every time I run a test, nor do I want to go and manually remove it.

Is there something I can do, such as using the tearDown() method to get rid of the test data once the test is complete? And if so, is it possible to "roll back" the auto increments in the tables' id fields to what they were beforehand? I know it really shouldn't matter if there are gaps between ids, but if there is some way to get Doctrine to reset the auto increment value that would be great.

Which database are you using? Mysql? If so, which storage engine? MyISAM or InnoDB? — Michael Osl, Jun 30 '13 at 08:17

score 10 · Accepted Answer · edited May 23 '17 at 12:25

10

1. Regarding "cleaning up":

Because you are using InnoDB you can use transactions to restore the DB to the same state as it was before the test started:

So in setUp() you would add $this->em->getConnection()->beginTransaction(); and in tearDown() $this->em->getConnection()->rollback(); This restores the database to the previous state. Also have a look at MySQL handbook on "The InnoDB Transaction Model and Locking" to make sure that this does not interfere with any other data in your application (keyword: isolation level).

2. Regarding rolling back auto increment ids:

As far as I know this is not (easily) possible. There is a thread about it on SO in which it is stated that your application should not care if there are gaps between the auto increment ids. If you are using a test database (which is highly recommended) this should be even a lesser concern.

edited May 23 '17 at 12:25

Community

1
1

answered Jun 30 '13 at 09:20

Michael Osl

2,720
1
21
36

That makes a lot of sense. Thanks! – blainarmstrong Jun 30 '13 at 10:15
Now another question: what's the best way to go about using a test database? Is there some kind of functionality that creates a "mirror" of the current database for testing, or do I have to create one by myself? – blainarmstrong Jun 30 '13 at 10:19
1

There are several options, much depending on the type of data and how large the database is. For instance you could either use a db schema file that creates an empty test database or regularly create a dev/test database out of backup files – Michael Osl Jun 30 '13 at 10:40
Thanks for the advice. I'll do some digging and see what works. – blainarmstrong Jun 30 '13 at 13:53

PHPUnit: how can I remove test data from my database after the tests are complete?

1 Answers1