Issue workaround for multi-tenancy, multi-database testing with stancl/tenancy

0_notes.md

Notes for the log locations

The "Creating MySQL database" is logged from the \Stancl\Tenancy\TenantDatabaseManagers\MySQLDatabaseManager::createDatabase() method:

public function createDatabase(TenantWithDatabase $tenant): bool
{
    $database = $tenant->database()->getName();
    $charset = $this->database()->getConfig('charset');
    $collation = $this->database()->getConfig('collation');

    Log::debug('Creating MySQL database');

    return $this->database()->statement("CREATE DATABASE `{$database}` CHARACTER SET `$charset` COLLATE `$collation`");
}

The "New PDO Connection" is logged from the \Illuminate\Database\Connectors\Connector::createPdoConnection() method:

protected function createPdoConnection($dsn, $username, $password, $options)
{
    Log::debug('New PDO Connection', [
        'dsn' => $dsn,
        'username' => $username,
        'password' => $password,
        'options' => $options
    ]);
    try {
        return new PDO($dsn, $username, $password, $options);
    } catch (\Throwable $e) {
        Log::debug('Connection failed');
        throw $e;
    }
}

The "JOB: DeleteDatabase, DATABASE: testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6" is from the \Stancl\Tenancy\Jobs\DeleteDatabase::handle() method:

public function handle()
{
    event(new DeletingDatabase($this->tenant));

    Log::debug("JOB: DeleteDatabase, DATABASE: {$this->tenant->tenancy_db_name}");
    $this->tenant->database()->manager()->deleteDatabase($this->tenant);

    event(new DatabaseDeleted($this->tenant));
}

This is a workaround for an issue with creating MySQL users/password per tenant database in tests, ideally it shouldn't have to be done this way but I have not been able to find a way to get tests to RELIABLY pass using separate users. Not being able to mimick important production pieces like this in tests is a problem, but unsure how to resolve.

See my relevant StackOverflow question here: https://stackoverflow.com/posts/77744498/edit

CreatesTenants.php

<?php // tests/CreatesTenants.php

namespace Tests;

use App\Models\Central\Tenant;

trait CreatesTenants
{
    protected function createTenant(array $data = []): Tenant
    {
        return Tenant::factory($data)->create();
    }
}

laravel.log.md

[2024-01-03 19:22:16] testing.DEBUG: New PDO Connection {
    "dsn": "mysql:host=mariadb;port=3306;dbname=testing",
    "username": "sail",
    "password": "password",
    "options": {
        "8": 0,
        "3": 2,
        "11": 0,
        "17": false,
        "20": false
    }
}
[2024-01-03 19:22:17] testing.DEBUG: Creating MySQL database
[2024-01-03 19:22:17] testing.DEBUG: New PDO Connection {
    "dsn": "mysql:host=mariadb;port=3306;dbname=testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6",
    "username": "sail",
    "password": "password",
    "options": {
        "8": 0,
        "3": 2,
        "11": 0,
        "17": false,
        "20": false
    }
}
[2024-01-03 19:22:17] testing.DEBUG: Connection credentials [{
    "driver": "mysql",
    "host": "mariadb",
    "port": "3306",
    "database": "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6",
    "username": "sail",
    "password": "password",
    "unix_socket": "",
    "charset": "utf8mb4",
    "collation": "utf8mb4_unicode_ci",
    "prefix": "",
    "prefix_indexes": true,
    "strict": true,
    "engine": null,
    "options": [],
    "name": "tenant"
}]
[2024-01-03 19:22:17] testing.DEBUG: Using tenant: {
    "id": "3fc6ae2d-296d-4d08-8098-262a1a6cefc6",
    "tenancy_db_username": "sail",
    "tenancy_db_password": "password",
    "tenancy_db_name": "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6"
}
[2024-01-03 19:22:17] testing.DEBUG: New PDO Connection {
    "dsn": "mysql:host=mariadb;port=3306;dbname=testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6",
    "username": "sail",
    "password": "password",
    "options": {
        "8": 0,
        "3": 2,
        "11": 0,
        "17": false,
        "20": false
    }
}
[2024-01-03 19:22:17] testing.DEBUG: Database tables: [
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.migrations",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.model_has_permissions",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.model_has_roles",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.permissions",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.roles",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.role_has_permissions",
    "testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6.users"
]
[2024-01-03 19:22:17] testing.DEBUG: TENANT ["3fc6ae2d-296d-4d08-8098-262a1a6cefc6"]
[2024-01-03 19:22:17] testing.DEBUG: JOB: DeleteDatabase, DATABASE: testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6
[2024-01-03 19:22:17] testing.DEBUG: Issuing DROP SQL statement: DROP DATABASE `testing_t_3fc6ae2d-296d-4d08-8098-262a1a6cefc6`

Pest.php

<?php // tests/Pest.php

use Tests\TenantTestCase;

uses(TenantTestCase::class)
    ->group('tenant-feature')
    ->in('Feature/Tenant');

function accessProtected($obj, $prop): mixed
{
    $reflection = new ReflectionClass($obj);
    $property = $reflection->getProperty($prop);
    $property->setAccessible(true);

    return $property->getValue($obj);
}

TenantApiAuthorizationTest.php

<?php // tests/Feature/Tenant/TenantApiAuthorizationTest.php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

beforeEach(function (): void {
    Log::debug('Connection credentials', [accessProtected(DB::connection(), 'config')]);
    Log::debug('Using tenant:', [
        'id' => $this->tenant->id,
        'tenancy_db_username' => $this->tenant->tenancy_db_username,
        'tenancy_db_password' => $this->tenant->tenancy_db_password,
        'tenancy_db_name' => $this->tenant->tenancy_db_name,
    ]);
    $databaseName = DB::connection()->getDatabaseName();
    $tableNames = DB::connection()->getDoctrineSchemaManager()->listTableNames();
    $prefixedTableNames = array_map(function ($tableName) use ($databaseName) {
        return $databaseName.'.'.$tableName;
    }, $tableNames);

    Log::debug('Database tables:', $prefixedTableNames);
});

test('requests without a valid token are unauthorized', function (): void {
    Log::debug('TENANT', [tenant()->id]);
    $response = $this->get('/api/v1');

    $response->assertStatus(401);
});

TenantTestCast.php

<?php // tests/TenantTestCase.php

namespace Tests;

use App\Models\Central\Tenant;
use Database\Seeders\Central\Local\LocalStripeSeeder;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\TestCase as BaseTestCase;
use Illuminate\Support\Facades\DB;
use Stancl\Tenancy\Middleware\InitializeTenancyBySubdomain;
use Stancl\Tenancy\Middleware\PreventAccessFromCentralDomains;
use Stancl\Tenancy\TenantDatabaseManagers\MySQLDatabaseManager;

abstract class TenantTestCase extends BaseTestCase
{
    use CreatesApplication;
    use CreatesTenants;
    use RefreshDatabase;

    protected ?Tenant $tenant = null;

    protected function setUp(): void
    {
        parent::setUp();

        $this->seed(LocalStripeSeeder::class);

        config([
            'tenancy.database.prefix' => 'testing_t_',
            'tenancy.database.managers.mysql' => MySQLDatabaseManager::class,
        ]);

        $this->initializeTenancy();
    }

    protected function tearDown(): void
    {
        $this->cleanupTenancy();

        parent::tearDown();
    }

    protected function cleanupTenancy(): void
    {
        $this->tenant->delete();
        DB::purge('tenant');
    }

    public function initializeTenancy(): void
    {
        $this->tenant = $this->createTenant([
            'tenancy_db_username' => config('database.connections.mysql.username'),
            'tenancy_db_password' => config('database.connections.mysql.password'),
        ]);
        tenancy()->initialize($this->tenant);

        $this->withoutMiddleware([
            InitializeTenancyBySubdomain::class,
            PreventAccessFromCentralDomains::class,
        ]);

        config(['app.url' => 'http://tenant.localhost']);
        $urlGenerator = url();
        $urlGenerator->forceRootUrl('http://tenant.localhost');

        $this->withServerVariables([
            'SERVER_NAME' => 'tenant.localhost',
            'HTTP_HOST' => 'tenant.localhost',
        ]);

        auth()->loginUsingId(1);
    }
}