How to set up database-heavy unit tests in Symfony2 using PHPUnit?

tl;dr:

• If and only if you want to go the whole functional test route, then I recommend looking up Sgoettschkes's answer.
• If you want to unit test your application and have to test code that interacts with the database, either read on or jump directly to symfony2 docs
  (There is a more up-to-date version also talking about testing with fixtures for symfony5)

---

There were certain aspects in my original question that make it clear that my understanding of the differences between unit testing and functional tests was lacking. (As I have written I want to unit test the application, yet was also talking about Controller test at the same time; and those are functional test by defintion).

Unit testing only makes sense for services and not for repositories. And those services can use mocks of the entity manager. (I would even go as far as to say: If possible, write services that only expect entities to be passed into them. Then you only need to create mocks of those entities and your unit-tests of your business logic become very straightforward.)

My actual use case for my application was pretty well reflected on the symfony2 docs on how to test code that interacts with the database.

They provide this example for a service test:

Service class:

use Doctrine\Common\Persistence\ObjectManager;

class SalaryCalculator
{
    private $entityManager;

    public function __construct(ObjectManager $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function calculateTotalSalary($id)
    {
        $employeeRepository = $this->entityManager
            ->getRepository('AppBundle:Employee');
        $employee = $employeeRepository->find($id);

        return $employee->getSalary() + $employee->getBonus();
    }
}

Service test class:

namespace Tests\AppBundle\Salary;

use AppBundle\Salary\SalaryCalculator;
use AppBundle\Entity\Employee;
use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Persistence\ObjectManager;

class SalaryCalculatorTest extends \PHPUnit_Framework_TestCase
{
    public function testCalculateTotalSalary()
    {
        // First, mock the object to be used in the test
        $employee = $this->getMock(Employee::class);
        $employee->expects($this->once())
            ->method('getSalary')
            ->will($this->returnValue(1000));
        $employee->expects($this->once())
            ->method('getBonus')
            ->will($this->returnValue(1100));

        // Now, mock the repository so it returns the mock of the employee
        $employeeRepository = $this
            ->getMockBuilder(EntityRepository::class)
            ->disableOriginalConstructor()
            ->getMock();
        $employeeRepository->expects($this->once())
            ->method('find')
            ->will($this->returnValue($employee));

        // Last, mock the EntityManager to return the mock of the repository
        $entityManager = $this
            ->getMockBuilder(ObjectManager::class)
            ->disableOriginalConstructor()
            ->getMock();
        $entityManager->expects($this->once())
            ->method('getRepository')
            ->will($this->returnValue($employeeRepository));

        $salaryCalculator = new SalaryCalculator($entityManager);
        $this->assertEquals(2100, $salaryCalculator->calculateTotalSalary(1));
    }
}

No test database required for those kind of test, only mocking.

As it is important to test the business logic, not the persistence layer.

Only for functional tests does it make sense to have its own test database that one should build and tear down afterwards, and the big question should be:

When do functional test make sense?

I used to think that test all the things is the right answer; yet after working with lots of legacy software that in itself was barely test-driven developed I have become a bit more lazypragmatic and consider certain functionality as working until proven otherwise by a bug.

Assume I have an application that parses an XML, creates an object out of it, and stores those objects into a database. If the logic that stores the objects to the database is known to work (as in: the company requires the data and is, as of yet, not broke), and even if that logic is a big ugly pile of crap, there is no imminent need to test that. As all I need to make sure that my XML parser extracts the right data. I can infer from experience that the right data will be stored.

There are scenarios where functional test are quite important, i.e. if one were to write an online shop. There it would be business critical that bought items get stored into the database and here functional tests with the whole test database make absolute sense.

I have never used the PHPUnit_Extensions_Database_TestCase, mostly because for these two reasons:

• It doesn't scale well. If you set up and tear down the database for every single test and you have a application which relies heavily on the database, you end up creating and dropping the same schema over and over again.
• I like to have my fixtures not only within my tests but also within my development database and some fixtures are even needed for production (initial admin user or product categories or whatever). Having them inside an xml which can only be used for phpunit doesn't seem right to me.

My way in theory...

I use the doctrine/doctrine-fixtures-bundle for fixtures (no matter what purpose) and set up the whole database with all fixtures. I then execute all tests against this database and make sure to recreate the database if a test changed it.

The advantages are that I don't need to set up a database again if a test only reads but don't change anything. For changes I have to do drop it and create it again or make sure to revert the changes.

I use sqlite for testing because I can set up the database, then copy the sqlite file and replace it with a clean one to bring back the original database. That way I don't have to drop the database, create it and load all fixtures again for a clean database.

...and in code

I wrote an article about how I do database tests with symfony2 and phpunit.

Although it uses sqlite I think one can easily make the changes to use MySQL or Postgres or whatever.

Thinking further

Here are some other ideas which might work:

• I once read about a test setup where before you use the database you start a transaction (within the setUp method) and then use the tearDown to rollback. That way you don't need to set up the database again and just need to initialize it once.
• My setup described above has the drawback that the database is set up every time phpunit is executed, even if you only run some unit tests with no database interaction. I am experimenting with a setup where I use a global variable which indicates if the database was set up and then within the tests call a method whcih checks this variable and initializes the database if it didn't happened yet. That way only when a tests needs the database the setup would happen.
• One problem with sqlite is that it doesn't work the same as MySQL in some rare cases. I had an issue once where something behaved different in MySQL and sqlite causing a test to fail when in with MySQL everything worked. I cannot remember what it was exactly.