Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
This tutorial has a new version, check it out!

Clearing the Database

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $10.00

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

Login Subscribe

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.

... lines 1 - 23
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.

... 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(
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.

... lines 1 - 11
class EnclosureBuilderServiceIntegrationTest extends KernelTestCase
public function setUp()
... 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.

... lines 1 - 72
* @return EntityManager
private function getEntityManager()
return self::$kernel->getContainer()
... lines 82 - 83

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

... 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().

... lines 1 - 13
public function setUp()
... 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.

... lines 1 - 47
private function truncateEntities()
$purger = new ORMPurger($this->getEntityManager());
... 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!

Login or Register to join the conversation
Default user avatar
Default user avatar Ben | posted 5 years ago | edited

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

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

An exception occurred in driver: SQLSTATE[HY000] [14] unable to open database file

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

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:

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

That did the trick for me.

1 Reply
Victor Avatar 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:

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

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


Maik T. Avatar
Maik T. Avatar 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() {

       $purger = new ORMPurger();

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


Hey Maik T.!

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



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


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!


Default user avatar
Default user avatar 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.


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....


Angelika R. Avatar
Angelika R. Avatar 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 ";").

Default user avatar
Default user avatar 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!

Default user avatar

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.


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 :).


Cat in space

"Houston: no signs of life"
Start the conversation!

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

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