Multi-database testing utilizing Laravel Multitenancy & MySQL while utilizing existing Laravel RefreshDatabase trait.

README.md

Multi-database testing utilizing Laravel Multitenancy & MySQL while utilizing existing Laravel RefreshDatabase trait.

1. Create test database service for creating and managing test databases:
2. Create phpunit Bootstrap that uses the above service.
3. Update phpunit.xml to use it instead of the composer autload:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="./vendor/phpunit/phpunit/phpunit.xsd"
         cacheResultFile="/tmp/.phpunit.result.cache"
         bootstrap="tests/Bootstrap.php"
         colors="true"
>
<!-- ... -->
</phpunit>

4. For parallel testing, create a Test Database Provider that works with the TestDatabaseProvider.
5. Update tests/TestCase.php to include both our tenant (connection tenant) and landlord (connection mysql)

abstract class TestCase extends BaseTestCase
{
    protected $connectionsToTransact = ['tenant','mysql'];
    use CreatesApplication;
}

6. Create a switch tenant task to switch tenants in a non-reconnecting way.
7. Register the switch tenant task in config/multitenancy.php:

    'switch_tenant_tasks' => [
        \App\Tasks\SwitchTenantDatabaseTask::class,
    ],

8. Create a TenantObserver that creates databases on creation of tenants, EXCEPT when testing, in which case it uses the test database service.
9. Register the TenantObserver in app/Providers/EventServiceProvider.php:

protected $observers = [
        \Spatie\Multitenancy\Models\Tenant::class => [\App\Observers\TenantObserver::class],
]

Bootstrap.php

<?php
// in tests/Bootstrap.php

// Only run this in the case of non-parallel testing. Parallel testing is handled through events registered on.
//   App/Providers/TestDatabaseProvider
if (getenv('LARAVEL_PARALLEL_TESTING') === false) {
    require('vendor/autoload.php');
    /** @var \Illuminate\Foundation\Application $app */
    $app = require __DIR__ . '/../bootstrap/app.php';
    $app->make(\Illuminate\Contracts\Console\Kernel::class)->bootstrap();
    $testDatabaseService = $app->get(\App\Services\TestDatabaseService::class);
    // Uncommen the following line to expose debug output.
    // $testDatabaseService->output = new \Symfony\Component\Console\Output\ConsoleOutput(Symfony\Component\Console\Output\OutputInterface::VERBOSITY_DEBUG);
    $app->call([$testDatabaseService, 'initTestingDatabases']);
    unset($testDatabaseService);
    \Illuminate\Support\Facades\Facade::clearResolvedInstances();
    \Illuminate\Support\Facades\App::setFacadeApplication(null); // Ditch current Application.
    unset($app);
}

SwitchTenantDatabaseTask.php

<?php
// in app/Tasks/SwitchTenantDatabaseTask.php

namespace App\Tasks;
use App\Services\TestDatabaseService;
use Spatie\Multitenancy\Tasks\SwitchTenantDatabaseTask as SwitchTenantDatabaseTaskUpstream;
use Spatie\Multitenancy\Exceptions\InvalidConfiguration;
use Illuminate\Support\Facades\DB;
class SwitchTenantDatabaseTask extends SwitchTenantDatabaseTaskUpstream
{
    protected function setTenantConnectionDatabaseName(?string $databaseName)
    {
        $tenantConnectionName = $this->tenantDatabaseConnectionName();
        if ($tenantConnectionName === $this->landlordDatabaseConnectionName()) {
            throw InvalidConfiguration::tenantConnectionIsEmptyOrEqualsToLandlordConnection();
        }
        if (is_null(config("database.connections.{$tenantConnectionName}"))) {
            throw InvalidConfiguration::tenantConnectionDoesNotExist($tenantConnectionName);
        }
        config([
            "database.connections.{$tenantConnectionName}.database" => $databaseName,
        ]);
        app('db')->extend($tenantConnectionName, function ($config, $name) use ($databaseName) {
            $config['database'] = $databaseName;
            return app('db.factory')->make($config, $name);
        });
        // This utilizes an undocumented DatabaseManager public method to grab currently live connections.
        $activeConnections = DB::getConnections();
        // If there is an active connection on our tenant, then we'll switch db's via MySQL "use"
        //    which doesn't dump our current transaction.
        if (array_key_exists($tenantConnectionName, $activeConnections)) {
            TestDatabaseService::changeDatabase($activeConnections[$tenantConnectionName], $databaseName);
        }
    }
}

TenantObserver.php

<?php
// in app/Observers/TenantObserver.php

namespace App\Observers;

use Spatie\Multitenancy\Models\Tenant;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;

class TenantObserver
{
    public function created(Tenant $tenant): void
    {
        $databaseName = sprintf('tenant_%d', $tenant->id);
        if (!App::runningUnitTests()) {
            // In Production...
            // create if database does not exist
            DB::unprepared("CREATE DATABASE IF NOT EXISTS $databaseName;");
            $tenant->database = $databaseName;
            $tenant->save();

            Artisan::call("tenants:artisan --tenant={$tenant->id} \"migrate --database=tenant --path=database/migrations/tenant \"");
        } else {
            // For unit/feature testing...
            $databaseName = App::call(\App\Services\TestDatabaseService::class.'@retrieveUnusedTenantDatabase');
            $tenant->database = $databaseName;
            $tenant->save();
        }
    }
}

TestDatabaseProvider.php

<?php
// in app/Providers/TestDatabaseProvider.php

namespace App\Providers;
use App\Services\TestDatabaseService;
use Illuminate\Support\Facades\ParallelTesting;
use Illuminate\Support\ServiceProvider;
class TestDatabaseProvider extends ServiceProvider
{
    /**
     * Bootstrap services.
     *
     * @return void
     */
    public function boot()
    {
        ParallelTesting::setUpProcess(TestDatabaseService::class.'@initTestingDatabases');
    }
}

TestDatabaseService.php

<?php
// in app/Services/TestDatabaseService.php

namespace App\Services;

use Exception;
use Illuminate\Database\Connection;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Schema\Builder;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\ParallelTesting;
use Spatie\Multitenancy\Concerns\UsesMultitenancyConfig;
use Spatie\Multitenancy\Models\Concerns\UsesTenantModel;
use Symfony\Component\Console\Output\OutputInterface;

class TestDatabaseService
{
    use UsesMultitenancyConfig;
    use UsesTenantModel;

    public null|string $grantUser = null;
    public string $grantHost = '%';
    public ?OutputInterface $output = null;
    protected string $trackingDb = 'testingdbs';
    protected string $trackingTable = 'testingdbs';
    protected string $tenantDbPrefix = 'tenant';
    protected int $testingDatabaseCount = 2;
    protected string $tempLandlordDb = 'temp_landlord';
    /**
     * @var string|null Parallel Testing Token
     */
    protected string|bool $token = false;

    // On construct, get our ParallelTesting token.
    public function __construct() {
        $this->setupToken(ParallelTesting::token());
    }

    protected function setupToken(string|bool $token = false) {
        $this->token = $token;
        if ($token !== false) {
            $this->trackingDb = 'testingDbRegistry_'.$this->token;
            $this->tenantDbPrefix = 'tenant'.$this->token;
        } else {
            $this->trackingDb = 'testingDbRegistry';
            $this->tenantDbPrefix = 'tenants';
        }
    }

    /**
     * Retrieve an unused Tenant Database for feature testing.
     *
     * @return string
     */
    public function retrieveUnusedTenantDatabase(): string
    {
        $this->setupToken(ParallelTesting::token());
        $dbConn = DB::connection($this->landlordDatabaseConnectionName());
        $dbConn->table($this->trackingTableRef())->where(['in_use' => 0])->limit(1)->update(['in_use' => 1, 'id' => DB::raw('LAST_INSERT_ID(id)')]);
        $testDbResult = $dbConn->table($this->trackingTableRef())->select('database')->where('id', $dbConn->getPdo()->lastInsertId())->get();
        if (!$testDbResult->count()) {
            throw new Exception("Unable to obtain a test database.");
        }
        return $testDbResult[0]->database;
    }

    protected function trackingTableRef()
    {
        return $this->trackingDb . '.' . $this->trackingTable;
    }

    public function initTestingDatabases($token = false): void
    {
        $this->setupToken($token);
        $this->debug('Starting Test Tenant Database Initialization.');
        $this->createTrackingDb();
        $this->executeWithinTempLandlordDb([$this, 'createTenantDatabases']);
        $this->debug('Done with setting up testing tenants.');
    }

    protected function debug($output)
    {
        if ($this->output) {
            $this->output->writeln($output, OutputInterface::VERBOSITY_DEBUG);
        }
    }

    /**
     * @param Builder $schema
     * @return void
     */
    protected function createTrackingDb(): void
    {
        $dbConn = DB::connection($this->landlordDatabaseConnectionName());
        $schema = $dbConn->getSchemaBuilder();
        $schema->dropDatabaseIfExists($this->trackingDb);
        $schema->createDatabase($this->trackingDb);
        $schema->create($this->trackingTableRef(), function (Blueprint $table) {
            $table->id();
            $table->string('database')->unique()->nullable();
            $table->boolean('in_use')->default(false);
        });
    }

    protected function executeWithinTempLandlordDb(callable $callback): void
    {
        $landlordDb = config('database.connections.' . $this->landlordDatabaseConnectionName() . '.database');
        $dbConn = DB::connection($this->landlordDatabaseConnectionName());

        $landlordDb = $this->tempLandlordDb;
        if ($token = ParallelTesting::token()) {
            $landlordDb = $token.'_'.$this->tempLandlordDb;
        }

        // Create temp landlord database
        $schema = $dbConn->getSchemaBuilder();
        $schema->dropDatabaseIfExists($landlordDb);
        $schema->createDatabase($landlordDb);
        self::changeDatabase($dbConn, $landlordDb);

        // Execute code within the temp landlord db.
        $callback($dbConn);

        // Restore connection and drop temp landlord database.
        self::changeDatabase($dbConn, $landlordDb);
        $schema1 = $dbConn->getSchemaBuilder();
        $schema1->dropDatabaseIfExists($landlordDb);
    }

    public static function changeDatabase(Connection $dbConn, ?string $databaseName): bool
    {
        // If transaction level is 0, and there is no database, we need to purge the connection instead of use.
        if (!$databaseName && $dbConn->transactionLevel() == 0) {
            DB::purge($dbConn->getName());
            return true;
        }
        if (!$databaseName) {
            $databaseName = "information_schema";
        }
        return $dbConn->unprepared('USE ' . $dbConn->getQueryGrammar()->wrap($databaseName));
    }

    protected function createTenantDatabases(Connection $dbConn): void
    {
        $tenantsTableName = $this->getTenantModel()->getTable();
        $this->createTenantTable($dbConn, $tenantsTableName);
        for ($i = 1; $i <= $this->testingDatabaseCount; $i++) {
            $this->createTestTenantDatabase($dbConn, $tenantsTableName);
        }
    }

    /**
     * @param Builder $schema
     * @param string $tenantTableName
     * @return void
     */
    protected function createTenantTable(ConnectionInterface $dbConn, string $tenantTableName): void
    {
        $schema = $dbConn->getSchemaBuilder();
        $schema->create($tenantTableName, function (Blueprint $table) {
            $table->id();
            $table->string('database')->unique()->nullable();
        });
    }

    /**
     * @param int $id
     * @param Builder $schema
     * @param ConnectionInterface $dbConn
     * @param string $tenantTableName
     * @return void
     */
    protected function createTestTenantDatabase(ConnectionInterface $dbConn, string $tenantTableName): void
    {
        $id = $dbConn->table($tenantTableName)->insertGetId(['database' => null]);
        $schema = $dbConn->getSchemaBuilder();
        $databaseName = sprintf('%s_%s', $this->tenantDbPrefix, $id);
        // Create databases.
        $schema->dropDatabaseIfExists($databaseName);
        $schema->createDatabase($databaseName);
        $this->grantPermissionsToDatabase($databaseName, $dbConn);
        $dbConn->table($tenantTableName)->where(['id' => $id])->update(['database' => $databaseName]);
        Artisan::call('tenants:artisan', ['--tenant' => $id, 'artisanCommand' => "migrate --database=tenant --path=database/migrations/tenant"], $this->output);
        $dbConn->table($this->trackingTableRef())->insert(['database' => $databaseName]);
        unset($databaseName);
    }

    /**
     * @param ConnectionInterface $dbConn
     * @param string $databaseName
     * @return void
     */
    public function grantPermissionsToDatabase(string $databaseName, ?ConnectionInterface $dbConn): void
    {
        // If we don't have a grantUser, then are not granting privileges.
        if (is_null($this->grantUser))
            return;
        // If dbConnection is not passed in, we should get the landlord connection.
        if (is_null($dbConn)) {
            $dbConn = DB::connection($this->landlordDatabaseConnectionName());
        }
        $dbConn->unprepared("GRANT ALL PRIVILEGES ON `" . $databaseName . "`.* TO '" . $this->grantUser . "'@'" . $this->grantHost . "'");
    }

}