In a previous blog post Laravel test learnings (2022), I’ve mentioned that parallel testing is a great way to speed up your tests. Laravel will spin up separate processes to run multiple tests at the same time.

./vendor/bin/sail test --parallel

In this blog post, I’m going to tackle the problem of running parallel tests on a codebase using multiple database connections. This solution addresses a MariaDB/MySQL setup.

😱 The problem

Illuminate\Database\QueryException: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'messages' already exists

Running parallel tests with DatabaseTransactions will result in a “table already exists” error on any table which is not on the default connection.

🕵️ How does ParaTest work?

Laravel’s parallel testing expects to create a new database for every test process. This means that if you are running 8 processes, there will be 8 separate databases created for testing in parallel.

You can follow this logic in Laravel’s ParallelTestingServiceProvider and TestDatabases (particularly TestDatabases::bootTestDatabase()) —

ParaTest sets up each process with each process being assigned a token (if you have 8 processes running, the token is simply the process number from 1-8)
When each process is set up (once at the beginning of the test suite) —
1. Laravel will tear down any database previously associated with the current process token (this database will be recreated later)
When each test case is set up (for every test in the test suite) —
1. Laravel will create the parallel test database if it doesn’t already exist. When using DatabaseTransactions, the database will only be created once per process per test suite execution.
2. The created database name is the name of the configured database with _test_{$token} appended. For example, if in your configs you have named your testing database testing, process 1 would create a testing database for parallel testing named testing_test_1 (going up to 8).
3. Laravel will reach into the database config (defined in config/database.php) and update it to instead use the newly created test database for the current process. After this step, the database config will be pointing towards the new parallel database.
```
// dd(config("database.connections"));
"mysql" => [                                                                                                                                                                                                        
	  "driver" => "mysql"
	  "host" => "db"
	  "port" => "3306"
	  "database" => "testing_test_8" 
    ...                                                                                                                                                                                                      
]
```
4. After updating the config, Laravel will run migrations if they haven’t already been run.
After the test suite has completed running —
1. Laravel will tear down the database associated with the current process.

In summary, every process expects a separate database to be created and migrated at the beginning of the process for dedicated use, and then removed once all tests have been run.

😣 Expected vs actual behaviour

Knowing this, inspecting the testing database during test execution makes the issue apparent.

Databases in db (the default connection):

testing
testing_test_1
testing_test_2
testing_test_3
testing_test_4
testing_test_5
testing_test_6
testing_test_7
testing_test_8

Databases in db-legacy (the legacy connection):

testing

Laravel’s native parallel testing behaviour only creates databases on the default connection, and ignores all secondary connections. This immediately causes an error on step 3D described above, due to trying to run the migrations multiple times on the same database.

This is more clear when reading through TestDatabases — the logic will only bother checking and setting the configurations on the default connection.

/**
 * Switch to the given database.
 *
 * @param  string  $database
 * @return void
 */
protected function switchToDatabase($database)
{
    DB::purge();

    $default = config('database.default');

    $url = config("database.connections.{$default}.url");

    if ($url) {
        config()->set(
            "database.connections.{$default}.url",
            preg_replace('/^(.*)(\/[\w-]*)(\??.*)$/', "$1/{$database}$3", $url),
        );
    } else {
        config()->set(
            "database.connections.{$default}.database",
            $database,
        );
    }
}

🤔 Solution A: Shared connection?

The easiest solution I could think of was to simply discard the concept of non-default connections while testing.

Even if different connections are required for the production environment, as long as database names are distinct and the connections all use the same driver, it’s possible to just populate all of these onto the same connection for testing.

Off the top of my head, I could think of two ways to do this —

Instead of hardcoding the connection for each model, make the connection configurable. When testing, point all models to the same testing connection.
Or, we can add a callback to the process setup and copy the DB connection settings into the DB-LEGACY configuration before the migrations are run (this ensures that DB-LEGACY will always reach into the same database as DB).

Pros —

Easy, doesn't require digging into the ParaTest behaviour
Simplifies the testing database set up (this is arguable; you could say this adds complexity as it results in a testing setup that doesn't match the development or production setup)

Cons —

Introduces bugs if your database connections use different drivers
Doesn't work if there is a naming conflict of databases in different connections
Testing setup is now different from both development and production setup, which introduces a higher chance for bugs to make it through testing undetected

I wanted to see if I could write a solution that enforced multiple connections, so I moved onto solution B 👇

🚀 Solution B: Rewrite `ParallelTestingServiceProvider`

I ended up just taking the logic from ParallelTestingServiceProvider and wrapping every piece of connection-specific logic in a loop that processes it for each connection. The complete snippet is at the bottom of this post, but I’ll first step through each change piece by piece.

Specify the parallel connections that must be configured

class ParallelTestingExtendedServiceProvider extends ServiceProvider
{
    use TestDatabases;

    protected array $parallelConnections = ['db', 'db-legacy'];

On process set up

Previous

if (ParallelTesting::option('recreate_databases')) {
    Schema::dropDatabaseIfExists(
        $this->testDatabase($database)
    );
}

New

if (ParallelTesting::option('recreate_databases')) {
    foreach ($this->parallelConnections as $connection) {
        Schema::connection($connection)
            ->dropDatabaseIfExists(
                $this->testDatabaseOnConnection($connection)
            );
    }
}

On process tear down

Previous

if (ParallelTesting::option('drop_databases')) {
    Schema::dropDatabaseIfExists(
        $this->testDatabase($database)
    );
}

New

  if (ParallelTesting::option('drop_databases')) {
      foreach ($this->parallelConnections as $connection) {
          Schema::connection($connection)
              ->dropDatabaseIfExists(
                  $this->testDatabaseOnConnection($connection)
              );
      }
  }

On test set up

Previous

// Create the test database on the default connection
[$testDatabase, $created] = $this->ensureTestDatabaseExists($database);

// Switch to the test database
$this->switchToDatabase($testDatabase);

// Run migrations
if (isset($uses[Testing\DatabaseTransactions::class])) {
    $this->ensureSchemaIsUpToDate();
}

// Fire database created event to listeners
if ($created) {
    ParallelTesting::callSetUpTestDatabaseCallbacks($testDatabase);
}

New

$allCreated = [];

// For each connection
foreach ($this->parallelConnections as $connection) {
    $this->usingConnection($connection, function () use ($connection, &$allCreated) {
        // Create the test database
        $database = config("database.connections.{$connection}.database");
        [$testDatabase, $created] = $this->ensureTestDatabaseExists($database);

        // Switch to the test database
        $this->switchToDatabase($testDatabase);

        if ($created) {
            $allCreated[] = [$connection, $testDatabase];
        }
    });
}

// Run migrations
if (isset($uses[DatabaseTransactions::class])) {
    $this->ensureSchemaIsUpToDate();
}

// Fire database created event to listeners
foreach ($allCreated as [$connection, $testDatabase]) {
    $this->usingConnection($connection, function () use ($testDatabase) {
        ParallelTesting::callSetUpTestDatabaseCallbacks($testDatabase);
    });
}

Complete snippet

<?php

namespace App\Providers;

use Illuminate\Foundation\Testing\DatabaseMigrations;
use Illuminate\Foundation\Testing\DatabaseTransactions;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\ParallelTesting;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\ServiceProvider;
use Illuminate\Testing\Concerns\TestDatabases;

class ParallelTestingExtendedServiceProvider extends ServiceProvider
{
    use TestDatabases;

    protected array $parallelConnections = ['db', 'db-legacy'];

    public function boot()
    {
        if ($this->app->runningInConsole()) {
            $this->bootTestDatabase();
        }
    }

    protected function bootTestDatabase()
    {
        ParallelTesting::setUpProcess(function () {
            if (ParallelTesting::option('recreate_databases')) {
                foreach ($this->parallelConnections as $connection) {
                    Schema::connection($connection)
                        ->dropDatabaseIfExists(
                            $this->testDatabaseOnConnection($connection)
                        );
                }
            }
        });

        ParallelTesting::setUpTestCase(function ($testCase) {
            $uses = array_flip(class_uses_recursive(get_class($testCase)));

            $databaseTraits = [
                DatabaseMigrations::class,
                DatabaseTransactions::class,
                RefreshDatabase::class,
            ];

            if (Arr::hasAny($uses, $databaseTraits) && ! ParallelTesting::option('without_databases')) {
                $allCreated = [];

                foreach ($this->parallelConnections as $connection) {
                    $this->usingConnection($connection, function ($connection) use (&$allCreated) {
                        $database = config("database.connections.{$connection}.database");
                        [$testDatabase, $created] = $this->ensureTestDatabaseExists($database);
                        $this->switchToDatabase($testDatabase);

                        if ($created) {
                            $allCreated[] = [$connection, $testDatabase];
                        }
                    });
                }

                if (isset($uses[DatabaseTransactions::class])) {
                    $this->ensureSchemaIsUpToDate();
                }

                foreach ($allCreated as [$connection, $testDatabase]) {
                    $this->usingConnection($connection, function () use ($testDatabase) {
                        ParallelTesting::callSetUpTestDatabaseCallbacks($testDatabase);
                    });
                }
            }
        });

        ParallelTesting::tearDownProcess(function () {
            if (ParallelTesting::option('drop_databases')) {
                foreach ($this->parallelConnections as $connection) {
                    Schema::connection($connection)
                        ->dropDatabaseIfExists(
                            $this->testDatabaseOnConnection($connection)
                        );
                }
            }
        });
    }

    protected function usingConnection(string $connection, \Closure $callable): void
    {
        $originalConnection = config("database.default");

        try {
            config()->set("database.default", $connection);
            $callable($connection);
        } finally {
            config()->set("database.default", $originalConnection);
        }
    }

    protected function testDatabaseOnConnection(string $connection): string
    {
        return $this->testDatabase(config("database.connections.{$connection}.database"));
    }
}

I was hoping to be able to do this without having to override the ParallelServiceTestingProvider but this is intended to completely replace it.

In any case, everything’s working happily!

Screenshot of PHPUnit results displaying 2227 successful tests

😱 The problem

🕵️ How does ParaTest work?

😣 Expected vs actual behaviour

Databases in db (the default connection):

Databases in db-legacy (the legacy connection):

🤔 Solution A: Shared connection?

🚀 Solution B: Rewrite ParallelTestingServiceProvider

Specify the parallel connections that must be configured

On process set up

On process tear down

On test set up

Complete snippet

🚀 Solution B: Rewrite `ParallelTestingServiceProvider`