Tricks to speed up tests with database
Not everything is worth to unit testing.
Sometimes Integration testing is better:
An integration test has the target to test the behavior of a component or the integration between a set of components. The term functional test is sometimes used as synonym for integration test. Integration tests check that the whole system works as intended, therefore they are reducing the need for intensive manual tests.
See: vogella
Components of your application like repositories which are dealing with database should be tested with real database connection. Another case is testing controllers, endpoints or CLI commands. Integration testing with real database connection is really valuable.
In many cases which I have seen, tests that work with database have very poor performance and it disturbs during development. My typical cases were with Mysql database platform and Doctrine ORM. That’s why I created examples using Mysql and Doctrine ORM. I have some tricks to speed up the tests with database and I want to present it below.
I also prepared the benchmarks to compare inefficient solution with more efficient one. Benchmarks were made on CPU i7, 16GB RAM, HDD SSD. Every example is available here: https://github.com/lzakrzewski/tests-with-database-examples. You can run it on your own machine :)
At the beginning: Why Your tests should be fast?
The tests with fast feedback are important during development. Fast feedback of the tests allows to run test suites frequently and avoids creating bugs or needles code. If we can run the tests frequently than we can stay "green" its especially useful during refactoring. Another obvious advantage of the tests which gives you the fast feedback is saving time, we all know how the time is important in the work of the developer.
Trick no 1: Don't load redundant fixtures!
A test fixture is a fixed state in code which is tested used as input for a test.
See: vogella
Very popular case which I met is loading whole fixtures before every single test:
<?php
//..
class FixturesLoadingTest extends TestCase
{
/** @test */
public function loading_whole_fixtures()
{
$this->loadWholeFixtures();
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('teapot_1')->price());
$this->assertEquals(200, $this->findItemByName('phone_1')->price());
}
private function loadWholeFixtures()
{
$loader = new Loader();
$loader->addFixture(new LoadItems());
$purger = new ORMPurger($this->getEntityManager());
$executor = new ORMExecutor($this->getEntityManager(), $purger);
$executor->execute($loader->getFixtures());
}
//..
}
Its extremely inefficient. Libraries like data-fixtures are great for filling up the database with exemplary data (for clicking), but using this component to load fixtures for testing is redundant. On the other hand this test is unreadable. For me the reason why the product name is "item_1" and why price is "50" is still unclear.
I have also created more efficient version of this test:
<?php
//..
class FixturesLoadingTest extends TestCase
{
/** @test */
public function loading_minimal_fixtures()
{
$this->givenDatabaseIsClear();
$this->add(new Teapot('brand-new-teapot', 100.0));
$this->add(new Phone('amazing-phone', 400.0));
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
$this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
}
//..
}
Its much better. Every single test should have it's own minimal database setup to reduce time of test executing. I can see clearly that previous price was 100,
after executing usecase ApplyDiscountUseCase price was discounted.
I've prepared the benchmark of 500 tests with 500 fixtures to compare 500 tests with minimal setup:
| Test method | repeated times | total execution time |
|---|---|---|
| FixturesLoadingTest::loading_whole_fixtures | 500 | 12.62 minutes |
| FixturesLoadingTest::loading_minimal_fixtures | 500 | 1.22 minutes |
Trick no 2: Purge database instead drop and create
Dropping and creating schema before every test method is another popular case which has poor performance:
<?php
//..
class EmptyDatabaseTest extends TestCase
{
/** @test */
public function drop_and_create_database()
{
$this->dropAndCreateSchema();
$this->add(new Teapot('brand-new-teapot', 100.0));
$this->add(new Phone('amazing-phone', 400.0));
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
$this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
}
private function dropAndCreateSchema()
{
$schemaTool = new SchemaTool($this->getEntityManager());
$metadata = $this->getEntityManager()->getMetadataFactory()->getAllMetadata();
$schemaTool->dropSchema($metadata);
$schemaTool->createSchema($metadata);
}
//..
}
Much faster is to just purge database before every test method:
<?php
//..
class EmptyDatabaseTest extends TestCase
{
/** @test */
public function purge_database()
{
$this->purgeDatabase();
$this->add(new Teapot('brand-new-teapot', 100.0));
$this->add(new Phone('amazing-phone', 400.0));
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
$this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
}
private function purgeDatabase()
{
$purger = new ORMPurger($this->getEntityManager());
$purger->purge();
}
//..
}
I used ORMPurger for purging. It creates sql to purify every table and executes it in right order.
And some benchmark:
| Test method | repeated times | total execution time |
|---|---|---|
| EmptyDatabaseTest::drop_and_create_database | 500 | 2.93 minutes |
| EmptyDatabaseTest::purge_database | 500 | 1.17 minutes |
Trick no 3: Test repositories or projections in single transaction
If you are testing simple component which does not commit transaction internally, then you can run every test method in single transaction:
<?php
//..
class RepositoryTest extends TestCase
{
/** @test */
public function reverting_transaction()
{
$this->givenDatabaseIsClear();
$this->getEntityManager()->beginTransaction();
$this->add(new Teapot('brand-new-teapot', 10.0));
$this->add(new Phone('amazing-phone', 400.0));
$items = ItemRepository::create($this->getEntityManager())
->paginate(1, 2);
$this->assertCount(2, $items);
$this->getEntityManager()->rollback();
}
//..
}
Benchmark:
| Test method | repeated times | total execution time |
|---|---|---|
| RepositoryTest::purge_database | 500 | 1.13 minutes |
| RepositoryTest::reverting_transaction | 500 | 25.23 seconds |
Trick no 4: Restore cached copy of database
If you are refactoring legacy app and probably you aren't able to remove whole fixtures now, there is also solution for this case. In most cases when you use Doctrine ORM with DQL queries and Mysql platform you can try to replace your database with Sqlite platform during testing:
<?php
//..
class FixturesLoadingTest extends TestCase
{
/** @test */
public function loading_whole_fixtures_from_cached_copy()
{
$this->givenSqliteDatabaseWasConnected();
$this->loadWholeFixturesFromCachedCopy();
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('teapot_1')->price());
$this->assertEquals(200, $this->findItemByName('phone_1')->price());
}
private function loadWholeFixturesFromCachedCopy()
{
$path = SqliteConfig::getParams()['path'];
$backupPath = $path.'.bck';
if (!file_exists($backupPath)) {
$this->loadWholeFixtures();
file_put_contents($backupPath, file_get_contents($path));
}
file_put_contents($path, file_get_contents($backupPath));
}
//..
}
Configuration of doctrine with Sqlite was described here: pdo-sqlite. Sqlite is the database which is storied in a file. This file could be created before executing the first test method, and then this file could be restore before every other test method.
I prefer to run the tests with the same database platform as on production, but Sqlite could be perfect in fighting with legacy code.
Another advantage of using Sqlite platform is that it has better performance while running weekly designed schema (updates without indexes etc.) see speed.
Benchmark:
| Test method | repeated times | total execution time |
|---|---|---|
| FixturesLoadingTest::loading_whole_fixtures | 500 | 12.62 minutes |
| FixturesLoadingTest::loading_whole_fixtures_from_cached_copy | 500 | 28.1 seconds |
Trick no 5: Cache purge query
Creating the query which purges the database needs to read the whole metadata of entities which takes time. It could be done only once before the first test and then it could be restored from cache:
<?php
//..
class EmptyDatabaseTest extends TestCase
{
/** @var string */
private static $purgeQueryInMemory;
/** @test */
public function purge_database_with_cached_purge_query()
{
$this->executedCachedQueryToPurgeDatabase();
$this->add(new Teapot('brand-new-teapot', 100.0));
$this->add(new Phone('amazing-phone', 400.0));
ApplyDiscountUseCase::create($this->getEntityManager())
->apply(0.5);
$this->assertEquals(50, $this->findItemByName('brand-new-teapot')->price());
$this->assertEquals(200, $this->findItemByName('amazing-phone')->price());
}
private function executedCachedQueryToPurgeDatabase()
{
$conn = $this->getEntityManager()->getConnection();
if (null === self::$purgeQueryInMemory) {
self::$purgeQueryInMemory = $this->createQueryToPurgeDatabase();
}
$conn->exec(self::$purgeQueryInMemory);
}
//..
}
Benchmark:
| Test method | repeated times | total execution time |
|---|---|---|
| EmptyDatabaseTest::purge_database | 500 | 1.17 minutes |
| EmptyDatabaseTest::purge_database_with_cached_purge_query | 500 | 32.76 seconds |
I highly recommend to take a moment to profile performance of your tests. It certainly will benefit in the future.
I also created the library which supports caching purge query and caching Sqlite out of the box. I urge to try this: https://github.com/lzakrzewski/DoctrineDatabaseBackup any feedback will be very helpful!
