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

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

Migration step-by-step vs Schema Dump single load speed comparison

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
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();
    }
}