Speed Up Laravel Tests 7x with PDO::exec Schema Load

Originally published at recca0120.github.io

As migration files pile up, test speed keeps getting slower -- even with SQLite In-Memory Database, because migrations run one file at a time.

schema:dump Doesn't Support In-Memory Database

Laravel 8 introduced php artisan schema:dump, which merges all migrations into a single SQL file. However, after checking the source code, SQLite In-Memory Database doesn't support this command.

Some people online suggest using DB::unprepared(file_get_contents("path/file.sql")) to load it manually. It works but is actually slower.

Load Schema Directly with PDO::exec

The key is to use PDO's exec instead of DB::unprepared:

namespace Tests;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\TestCase as BaseTestCase;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication;

    protected function setUpTraits()
    {
        // Must run before parent::setUpTraits
        $uses = array_flip(class_uses_recursive(static::class));
        $schema = database_path('schema/sqlite-schema.dump');
        if (isset($uses[RefreshDatabase::class]) &&
            $this->usingInMemoryDatabase() &&
            File::exists($schema)
        ) {
            DB::connection()->getPdo()->exec(File::get($schema));
        }

        parent::setUpTraits();
    }
}

In practice, this reduced test time from 2:21.979s to 18.457s -- 7x faster.

Extract Into a Reusable Trait

namespace Tests\Traits;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

trait RefreshInMemoryDatabase
{
    public function loadSchemaToInMemoryDatabase(): array
    {
        $uses = array_flip(class_uses_recursive(static::class));
        $schema = database_path('schema/sqlite-schema.dump');
        if (isset($uses[RefreshDatabase::class]) &&
            $this->usingInMemoryDatabase() &&
            File::exists($schema)
        ) {
            DB::connection()->getPdo()->exec(File::get($schema));
        }

        return $uses;
    }
}

namespace Tests;

use Illuminate\Foundation\Testing\TestCase as BaseTestCase;
use Tests\Traits\RefreshInMemoryDatabase;

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication;
    use RefreshInMemoryDatabase;

    protected function setUpTraits()
    {
        // Must run before parent::setUpTraits
        $this->loadSchemaToInMemoryDatabase();

        parent::setUpTraits();
    }
}