Clearing the Database

With a Subscription, click any sentence in the script to jump to that part of the video!

Each time we run the test, it adds more and more entries to the database. Our test is not reliable: it depends on what the database looks like when it starts. The solution is simple: we must control the data in the database at the start of every test.

Creating a Test Database

Step one to accomplishing this is to use a different database for our test environment. Actually, this is mostly for convenience: using the same database for testing and development is annoying. One minute, you're coding through something awesome in your browser, then you run your tests, then back in the browser, all your nice data is gone or totally different! Isolation in this instance is nice.

To do that, go back to config_test.yml. In a Symfony 4 Flex application, you should create a config/packages/test/doctrine.yaml file since this will contain Doctrine configuration that you only want to use in the test environment.

Inside, anywhere, add doctrine, dbal then url set to sqlite:///%kernel.project_dir%/var/data/test.sqlite.

            
            28 lines
            
            app/config/config_test.yml
        
                                                    ... lines 1 - 23
                            
            doctrine:
        
                dbal:
        
                    url: 'sqlite:///%kernel.project_dir%/var/data/test.sqlite'
        
                                                    ... lines 27 - 28

This will override the settings in app/config/config.yml - the stuff under doctrine. With the new config, we're completely replacing this stuff and saying "Hey! Use an sqlite, flat-file database!".

Why Sqlite? It's simple to setup and you can use some tricks to speed up your tests. We'll see that in a few minutes.

Oh, and make sure you have the % sign at the end of kernel.project_dir!

Now, find your terminal and create the var/data directory:

mkdir var/data

Next, create the schema

php bin/console doctrine:schema:create --env=test

And, congrats! You are the owner of a fancy new var/data/test.sqlite file! Take good care of it.

Clearing the Database before Tests

At this point, not much has changed really. Our tests will still pass one time, but will fail each time after. We haven't actually fixed the problem yet!

How can we? The best way is to fully empty the database at the beginning of each test. This would certainly put our database into a known state: empty! Then, if we do need any data before running the test, we can manually add it in the test. It's not super fancy, but it keeps everything really clear.

Like most good things in life, there are two ways to do this. First, if you downloaded the course code, then in the tutorial/tests directory, you'll find an EnclosureBuilderServiceIntegrationTest.php file. Copy the truncateEntities() method and paste that into your test class.

            
            83 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 11
                            
            class EnclosureBuilderServiceIntegrationTest extends KernelTestCase
        
            {
        
                                                    ... lines 14 - 50
                            
                private function truncateEntities(array $entities)
        
                {
        
                    $connection = $this->getEntityManager()->getConnection();
        
                    $databasePlatform = $connection->getDatabasePlatform();
        
                    if ($databasePlatform->supportsForeignKeyConstraints()) {
        
                        $connection->query('SET FOREIGN_KEY_CHECKS=0');
        
                    }
        
                    foreach ($entities as $entity) {
        
                        $query = $databasePlatform->getTruncateTableSQL(
        
                            $this->getEntityManager()->getClassMetadata($entity)->getTableName()
        
                        );
        
                        $connection->executeUpdate($query);
        
                    }
        
                    if ($databasePlatform->supportsForeignKeyConstraints()) {
        
                        $connection->query('SET FOREIGN_KEY_CHECKS=1');
        
                    }
        
                }
        
                                                    ... lines 72 - 81
                            
            }

This is simple: pass the method an array of entities and it will empty them.

You might want to call this at the top of every test method. But another great option is to override setUp() and add it there. Let's empty all three entities: Enclosure, Security and Dinosaur.

            
            83 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 11
                            
            class EnclosureBuilderServiceIntegrationTest extends KernelTestCase
        
            {
        
                public function setUp()
        
                {
        
                    self::bootKernel();
        
                    $this->truncateEntities([
        
                        Enclosure::class,
        
                        Security::class,
        
                        Dinosaur::class,
        
                    ]);
        
                }
        
                                                    ... lines 24 - 81
                            
            }

For this method to work, we need a getEntityManager() method. At the bottom, add private function getEntityManager(). Then, copy our logic from above, paste it here, and add return. And since you know I love auto-completion, I'll add a @return EntityManager.

            
            83 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 72
                            
                /**
        
                 * @return EntityManager
        
                 */
        
                private function getEntityManager()
        
                {
        
                    return self::$kernel->getContainer()
        
                        ->get('doctrine')
        
                        ->getManager();
        
                }
        
                                                    ... lines 82 - 83

This makes truncateEntities() happy! And we can even use getEntityManager() above.

            
            83 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 24
                            
                public function testItBuildsEnclosureWithDefaultSpecifications()
        
                {
        
                                                    ... lines 27 - 31
                            
                    $em = $this->getEntityManager();
        
                                                    ... lines 33 - 48
                            
                }
        
                                                    ... lines 50 - 83

Oh, and it's really important that we call self::bootKernel() before we try to access any services. The best thing to do is remove it from the test method and add it to setUp().

            
            83 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 13
                            
                public function setUp()
        
                {
        
                    self::bootKernel();
        
                                                    ... lines 17 - 22
                            
                }
        
                                                    ... lines 24 - 83

Done! Try the tests:

./vendor/bin/phpunit --filter testItBuildsEnclosureWithTheDefaultSpecification

We got it! We can run them over, and over, and over again. Always green!

Using Data Fixtures

This was the more manual way to clear the database, and it gives you a bit more control. Another option is to use Doctrine's DataFixtures library.

First, install it:

composer require "doctrine/data-fixtures:^1.3" --dev

When this finishes, we can delete all the logic in truncateEntities()... because now we have a fancy "purger" object: $purger = new ORMPurger() and pass in the entity manager.

Then... $purger->purger(). And yea... that's it! We can remove the $entities argument and stop passing in the array.

            
            64 lines
            
            tests/AppBundle/Service/EnclosureBuilderServiceIntegrationTest.php
        
                                                    ... lines 1 - 47
                            
                private function truncateEntities()
        
                {
        
                    $purger = new ORMPurger($this->getEntityManager());
        
                    $purger->purge();
        
                }
        
                                                    ... lines 53 - 64

This loops over all of your entity objects and deletes them one by one. It will even delete them in the correct order to avoid foreign key problems. But, if you have two entities that both have foreign keys pointing at each other, you may still have problems.

But, this works! The tests still pass.

Before we move on, I want to show you one cool, weird trick with integration tests: I call it "partial" mocking.

Leave a comment!

Ben | posted 5 years ago | edited

Somehow for me the url key was not working, as I got the following errors:

[Doctrine\ORM\Tools\ToolsException]
Schema-Tool failed with Error 'An exception occurred in driver: SQLSTATE[HY000] [14] unable to open database file' while executing DDL: CREATE TABLE dinosaurs (id INTEGER NOT NULL, encl
osure_id INTEGER DEFAULT NULL, length INTEGER NOT NULL, genus VARCHAR(255) NOT NULL, is_carnivorous BOOLEAN NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_B1DE6E91D04FE1E5 FOREIGN KEY (enclos
ure_id) REFERENCES enclosure (id) NOT DEFERRABLE INITIALLY IMMEDIATE)

[Doctrine\DBAL\Exception\ConnectionException]
An exception occurred in driver: SQLSTATE[HY000] [14] unable to open database file

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000] [14] unable to open database file

[PDOException]
SQLSTATE[HY000] [14] unable to open database file

In the end I resolved it by replacing the url key by path, and removing the sqlite:// part. As in:


doctrine:
  dbal:
    driver: pdo_sqlite
    path: '%kernel.project_dir%/var/data/test.sqlite'

That did the trick for me.

1 Reply

Victor | SFCASTS | Ben | posted 5 years ago | edited

Hey Ben,

Hm, I don't see any issues, it should work, actually, Symfony demo uses the same path: https://github.com/symfony/demo/blob/e45b5d59f8b253531e66b0ec8ac91dbd5559934b/.env.dist#L15 . Well, only one tip for you in case you're on Symfony 4 and use environment variables, you should not forget about resolve filter to handle the "kernel.project_dir" parameter, i.e. do this:


doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

Anyway, I'm glad you found a working solution. And thanks for sharing it with others!

Cheers!

Maik T. | posted 1 year ago | edited

I use Symfony 5 with php 8.1 and having problems to use the ORMPurger.

in the moment i call $puger->purge() i got a null reference error.

has there anything changed what is is not listed?


class MyIntegrationTest extends KernelTest 
{
    public function setUp() {
       self::bootKernel();

       $purger = new ORMPurger();
       $purger->purge();
    }
}

<blockquote>Error : Call to a member function getMetadataFactory() on null</blockquote>

weaverryan | SFCASTS | Maik T. | posted 1 year ago | edited

Hey Maik T.!

Hmm. Are you passing the entity manager into ORMPurger? If that's not passed, then it would cause this exact error.

Cheers!

akshit | posted 1 year ago

What changes do I have to make incase I'm using PostGRE SQL.

Victor | SFCASTS | akshit | posted 1 year ago

Hey Akshit,

If you're using this project for learning purposes and data lose is not a problem for you - the easiest way would be to create a DB and schema with next commands:
$ bin/console doctrine:database:create
$ bin/console doctrine:schema:create
$ bin/console doctrine:fixtures:load

And that's it, you should have the valid DB schema now, you can check it with:

$ bin/console doctrine:schema:validate

If all green - great, you're ready to go :)

For production - it's more complex, you would need to manually create the migration for any changes you made in the schema, you can leverage the "bin/console make:migration" command.

I hope this helps!

Cheers!

RyderRyder | posted 2 years ago

What do you say to the argument that one doesn't need to test against the database, since it such tests you're basically just testing that the entity manager works (which you can safely assume without needing to test it). Instead, you should mock the EM and write unit tests. It's not a position I'm advocating, but I can't think of a good response to it.

MolloKhan | SFCASTS | RyderRyder | posted 2 years ago

Hey there!

That's a good question, there are times when you need to test the interaction with the database, for example when you're writting a custom repository method, or, when you're writing an integration test. In general, if you can mockout a service, you should, you want to narrow down your tests as much as possible and make them to run fast as much as possible too. You may find this article interesting https://martinfowler.com/ar....

Cheers!

Angelika R. | posted 2 years ago

If you get "[Doctrine\DBAL\Exception\DriverException] An exception occurred in driver: could not find driver", simply uncomment extension=pdo_sqlite in php.ini in your php installation folder (by deleting ";").

gawandulph | posted 3 years ago

I have a question about testing APIs. Say I have a Symfony 5 app, which serves a number of endpoints. I want to test that when I hit endpoint `cats/x/fleas` I get a list of fleas for cat number x. So in my test, i:

1. Create a cat, persist it to the db, and get its ID (e.g. 5).
2. Use HTTPClient to hit `https://localhost/cats/5/fleas`.

My problem is, in step 1 I am using my test database, i.e. in my `test` environment. When I hit the endpoint with my HTTPClient, I am hitting the `dev` environment (which uses a different, `dev`, database). So it can't find the cat I created in the test database. One way to solve this is to set my local dev environment to `test`. But that's not ideal. Any ideas how to get around this? Thanks!

gawandulp | gawandulph | posted 3 years ago

I've found a solution. Do not use the symfony HTTP Client package, instead extend `WebTestCase` in your test class, and use its built in client. When you create the client you can define which environment you want it to use when making requests, so I create it and ask it to use the `test` environment.

weaverryan | SFCASTS | gawandulp | posted 3 years ago

Hey @gawandulp!

Nice work figuring all of this out! Yes, the WebTestCase client is just a nicer way to do it - and you can even get the "profiler" for that request if you want to, or assert that an email was sent - cool stuff like that :).

Cheers!

While the fundamentals of PHPUnit haven't changed, this tutorial *is* built on an older version of Symfony and PHPUnit.

Symfony 3 PHPUnit 6

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.0, <7.4",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "doctrine/doctrine-bundle": "^1.6", // 1.10.3
        "doctrine/orm": "^2.5", // v2.7.2
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.2
        "sensio/distribution-bundle": "^5.0.19", // v5.0.21
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.28
        "symfony/monolog-bundle": "^3.1.0", // v3.1.2
        "symfony/polyfill-apcu": "^1.0", // v1.6.0
        "symfony/swiftmailer-bundle": "^2.3.10", // v2.6.7
        "symfony/symfony": "3.3.*", // v3.3.13
        "twig/twig": "^1.0||^2.0" // v2.4.4
    },
    "require-dev": {
        "doctrine/data-fixtures": "^1.3", // 1.3.3
        "doctrine/doctrine-fixtures-bundle": "^2.3", // v2.4.1
        "liip/functional-test-bundle": "^1.8", // 1.8.0
        "phpunit/phpunit": "^6.3", // 6.5.2
        "sensio/generator-bundle": "^3.0", // v3.1.6
        "symfony/phpunit-bridge": "^3.0" // v3.4.30
    }
}

Edit on GitHub

COURSE

PHPUnit (Legacy): Testing with a Bite

Clearing the Database

Keep on Learning!

Creating a Test Database

Clearing the Database before Tests

Using Data Fixtures

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 23
	doctrine:
	dbal:
	url: 'sqlite:///%kernel.project_dir%/var/data/test.sqlite'
	... lines 27 - 28

	... lines 1 - 11
	class EnclosureBuilderServiceIntegrationTest extends KernelTestCase
	{
	... lines 14 - 50
	private function truncateEntities(array $entities)
	{
	$connection = $this->getEntityManager()->getConnection();
	$databasePlatform = $connection->getDatabasePlatform();

	if ($databasePlatform->supportsForeignKeyConstraints()) {
	$connection->query('SET FOREIGN_KEY_CHECKS=0');
	}

	foreach ($entities as $entity) {
	$query = $databasePlatform->getTruncateTableSQL(
	$this->getEntityManager()->getClassMetadata($entity)->getTableName()
	);

	$connection->executeUpdate($query);
	}

	if ($databasePlatform->supportsForeignKeyConstraints()) {
	$connection->query('SET FOREIGN_KEY_CHECKS=1');
	}
	}
	... lines 72 - 81
	}

	... lines 1 - 11
	class EnclosureBuilderServiceIntegrationTest extends KernelTestCase
	{
	public function setUp()
	{
	self::bootKernel();

	$this->truncateEntities([
	Enclosure::class,
	Security::class,
	Dinosaur::class,
	]);
	}
	... lines 24 - 81
	}

	... lines 1 - 72
	/**
	* @return EntityManager
	*/
	private function getEntityManager()
	{
	return self::$kernel->getContainer()
	->get('doctrine')
	->getManager();
	}
	... lines 82 - 83

	... lines 1 - 24
	public function testItBuildsEnclosureWithDefaultSpecifications()
	{
	... lines 27 - 31
	$em = $this->getEntityManager();
	... lines 33 - 48
	}
	... lines 50 - 83

	... lines 1 - 13
	public function setUp()
	{
	self::bootKernel();
	... lines 17 - 22
	}
	... lines 24 - 83

	... lines 1 - 47
	private function truncateEntities()
	{
	$purger = new ORMPurger($this->getEntityManager());
	$purger->purge();
	}
	... lines 53 - 64