Faster Tests Using Stub Database Files

While building a larger application that has on a lot of feature tests which depends on data from the database, I found myself waiting upwards 10 minutes for my test-suite to finish, mostly due to migrations. Surely this is not an issue, and I've seen worse, but what if I could reduce that just a little bit?

By using a stub file, which I will explain below, I cut off almost 7 minutes.
I'm using SQLite for this, but any file-based database should work just fine.

What Is a Stub File?

To prevent tests writing data to the database that other tests may not expect, the database is cleared and migrated for each test. When you have many migrations, this can be a very slow process. Instead, you can use a stub database, which is basically your entire database without any data (hence migrated from scratch) and copy that into your test database for each test. Let me give you an example:

Stub database: databases/testing/stub.sqlite
Test database: databases/testing/test.sqlite

The stub is migrated and contains no data.

Now, for each test, I want to replace the stub.sqlite with my test.sqlite, instead of cleaning and migrating my test.sqlite.



class TestCase extends \PHPUnit_Framework_TestCase {
    protected function setUp() {
        // Remove old test database
        if(file_exists( '../database/testing/testdb.sqlite')) {
            unlink( '../database/testing/test.sqlite' );
        if(file_exists( '../database/testing/test.sqlite-journal')) {
            unlink('../database/testing/test.sqlite-journal' );

        // Copy stub to test database
        copy( '../database/testing/stub.sqlite', '../database/testing/test.sqlite' );

I've removed some stuff from here to make it more presentable.

Keeping Your Stub File Up-To-Date

Whenever you add new migrations, your stub file will be outdated, until you migrate it with the latest changes, so you have to keep this in mind.

In Laravel, I've setup an artisan command that migrates a fresh stub file. That command is used each time I push a new build, so I never have to think about keeping it up to date.


The Artisan command is as follows:


namespace App\Console\Commands;

use Illuminate\Console\Command;

class PrepareTestingDatabase extends Command {
    protected $signature = 'app:test-database';

    protected $description = 'Creates a SQLite stub file with migrations';

    public function handle() {
        touch( database_path( 'testing/stub.sqlite' ) );

        \Artisan::call( 'migrate:fresh', [ '--database' => 'setup', '--env' => 'testing', '--force' => 'true' ] );

Database Config

In my config/database.php file, I have added the following:

<?php return [

    // ...

    'connections' => [

        'setup' => array(
                'driver' => 'sqlite',
                'database' => __DIR__.'/../database/testing/stub.sqlite',
                'prefix' => '',

        'testing' => array(
                'driver' => 'sqlite',
                'database' => __DIR__.'/../database/testing/test.sqlite',
                'prefix' => '',

        // ...
    // ...

Faster Than Using Memory

I found this to be even faster than memory based databases, as they also have to be migrated. You might want to experiment a little, but I'm almost certain that stub files will be faster.

Show Comments