Eddie's Code Shop

Multiple Databases + TDD on Laravel

|

Multiple Databases + TDD on Laravel

So this is my first foray in to TDD (Test Driven Development) at work. The way I was going about it was just running the tests for the specific feature I was building, mostly because I wanted to rerun the tests quickly to keep the momentum going. When I finally decided to run the full test-suite, I noticed that most of the tests I had written were suddenly failing!

Now there are a few good reasons for why the tests, which I made sure were showing green before pushing up, were now failing.

The first problem is that out of the box any migrations placed inside the migrations folder will run under the default database which is set in database.php.

Building this project on Laravel 6.0 (PHP 7.3.8), I didn’t fully grasp how to set up my .env file and the database.php file to support multiple databases. Out of the box, the default config in the .env.example file looks like this:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Naturally I used a developer’s greatest tool, copy-paste, and I duplicated the above; I just added ‘_2’ at the end of each environment variable. In the database.php file, I also created a duplicate of the stock database config:

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

I also changed this:

'default' => env('DB_CONNECTION', 'mysql'),

to point to mysql_2.

The problems arose when we started using Laravel Passport. With Passport, a few tables are necessary in order to have OAuth functionality, and these tables were being created in the new database (mysql_2 connection) since that was the default connection I had set. And in isolation this worked great, all my tests were green, awesome!

All tests were green, however, all my tables were being created in one database (database_2) including the migrations table.

However when these config changes I made got released to production it caused a few parts of the site to stop functioning. The culprit was of course the default database change I made since this part of the site relied on legacy data. Especially the Passport functionality we were using.

Figuring out an adequate solution for this issue took some tinkering (pun intended), a bit of mental processing, and some not insignificant use of the most powerful tool in every developer’s tool chest (Google of course). First off, I noticed that in order to have multiple databases, you had to think of DB_CONNECTION and DB_DATABASE analogously (if you want to keep your actual connection details out of your codebase by using the env() function instead). This is because in order to work with multiple databases, you must specify a new/different connection in your database.php file. My connection details changed to this:

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret
DB_CONNECTION_2=mysql_2
DB_DATABASE_2=another_database

database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],
'mysql_2' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE_2', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

With this squared away, another issue quickly became apparent. When running `php artisan migrate` I noticed that the tables were correctly being created, but the migrations were all being tracked in only one database. I wanted each database to have its own migrations table with migration records for their corresponding database only. This post on Stack Overflow put me on the right track by showing me that I could use the standard `php artisan migrate` along with the ` — database` option to specify the connection I wanted the migrations to run under. Separating the migrations into different folders would ensure that I wouldn’t accidentally run migrations for a different database by forcing the use of the ` — path` option to point to the migrations I wanted to run.

Any migrations files under the migrations folder would continue to run for the default database. All migration files under mysql_2 would only run with the ` — path` option set.
Result after running `php artisan migrate` & `php artisan migrate — database=mysql_2 — path=database/migrations/mysql_2`

Progress! The only remaining hurdle I faced was using this modified artisan command in my tests to refresh my database when I needed it. A simple solution would’ve been to have just modify my setUp() function to look like this:

class ExampleTestCase extends TestCase {
    protected function setUp(): void
    {
 	parent::setUp();
	$this->artisan(‘migrate:refresh’, [
 		‘ — database’ => env(‘DB_CONNECTION’),
		‘ — path’ => ‘path/to/migrations’
 	]);
}
 }

This would surely get the job done, but I wanted to make absolutely sure that some other developer on the team wouldn’t incorrectly or accidentally use the wrong migration command for their tests. Additionally, specifically for this project, we needed certain tables to only be truncated and not dropped (as the migrate:refresh option would do) since we did not have the migration to recreate them.

I consulted with my resident Code Ninja coworker, bouncing ideas off them to see what would work best. Eventually they suggested making use of the base TestCase.php class (…/tests/TestCase.php), overriding/overloading the setup function to run all the migrations I needed for every test. It was a great start and set me down the path to eventually finding an adequate solution.

I started digging around Laravel’s codebase (something I thoroughly enjoy doing since I often learn a lot due to their exemplary code). Below is the line of progression I took through the codebase:

(backend/vendor/laravel/framework/src/Illuminate/Foundation => …)

…/Testing/TestCase.php::setUp() =>

…/Testing/TestCase.php::setUpTraits() =>

…/Testing/RefreshDatabase.php::refreshDatabase() =>

…/Testing/RefreshDatabase.php::refreshTestDatabase()

I realized that I needed to make my own migrate command. One which would understand the migrations folder structure, that each subfolder meant a different database to run the migrations for. This command would then be used inside a trait which could be used in any test, and together with a new setUp() function inside the base TestCase.php class, would work just like the original RefreshDatabase.php trait provided by Laravel out of the box. I’ve laid out the subsequent work below:

Note: I took out some company specific code from this command, otherwise it should largely be implementation agnostic.

CustomMigrateCommand.php

<?php
namespace App\Console\Commands;
use DB;
use Illuminate\Console\Command;
use Illuminate\Database\DatabaseManager;
class CustomMigrateCommand extends Command
{
const TRUNCATE = 'truncate';
const SCRATCH = 'scratch';
const REFRESH = 'refresh';
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'migrate – admin
{type? : The type of migration to run. Acceptable values are refresh, scratch, and truncate}';
/**
* The console command description.
*
* @var string
*/
protected
$description = 'Run migrations in a special manner . ';
/**
* The path to the migrations for this repository.
*
* @var string $migrationsPath
*/
protected $migrationsPath;
/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
parent::__construct();
$this->migrationsPath = 'database / migrations';
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$this->migrateDatabases($this->getConnectionNamesFromMigrationsFolder());
}
public function getConnectionNamesFromMigrationsFolder(): array
{
$dirContents =
array_diff(scandir($this->migrationsPath), [' ..', ' . ']);
$connections = array_values(array_filter($dirContents, function ($partialPath) {
return is_dir("{$this->migrationsPath}/{$partialPath}");
}));
return $connections;
}
protected function migrateDatabase(
string $connection,
string $path,
bool $scratch = false,
bool $refresh = false,
bool $truncate = false,
string $environment = '',
array $dontTruncateTables = []
): void
{
$args = [
' — database' => $connection,
];
if (!empty($path)) $args[' — path'] = $path;
if (!empty($environment)) $args[' — env'] = $environment;
if ($scratch) {
$this->wipeDbCommand($connection);
} else if ($truncate) {
$this->truncateCommand($connection, $dontTruncateTables);
return;
}
$this->migrateCommand($refresh, $args);
}
protected function migrateDatabases(array $connections): void
{
$scratch = false;
$refresh = false;
$truncate = false;
$environment = null;
switch ($this->argument('type')) {
case self::TRUNCATE:
$truncate = true;
break;
case self::REFRESH:
$refresh = true;
break;
case self::SCRATCH:
$scratch = true;
break;
default:
if ($this->option('refresh')) $refresh = true;
}
foreach ($connections as $connection) {
$path = "{$this->migrationsPath}/$connection";
$environment = $this->option('env') ?? '';
$this->migrateDatabase($connection, $path, $scratch, $refresh, $truncate, $environment);
}
}
protected function truncateCommand(
string $connection,
array $ignoreTables = []): void
{
/* @var DatabaseManager $dbConnection */
$dbConnection = DB::connection($connection);
$noForeignCheck = "SET foreign_key_checks = 0;";
$setForeignCheck = "SET foreign_key_checks = 1;";
$tables = $dbConnection->select("SHOW TABLES;");
$databaseName = config("database . connections .{$connection} . database");
$pointer = "Tables_in_{
$databaseName}";
$statements = [$noForeignCheck];
foreach ($tables as $table) {
if (in_array($table->$pointer, $ignoreTables)) continue;
$statements[] = "TRUNCATE TABLE `{$table->$pointer}`;";
}
$statements[] = $setForeignCheck;
$this->info("Will be executing the following SQL commands:");
$imploded = implode("\n", $statements);
$this->line($imploded);
$dbConnection->transaction(function () use ($dbConnection, $statements) {
foreach ($statements as $statement) {
$dbConnection->select($dbConnection->raw($statement));
}
});
}
protected function wipeDbCommand(string $connection): void
{
$args = [
' — database' => $connection,
' — drop – views' => true,
' — force' => true,
];
$this->outputArtisanCommand('db:wipe', $args);
$this->call('db:wipe', $args);
}
protected function migrateCommand(
bool $refresh = false,
array $arguments = []): void
{
$command = 'migrate';
if ($refresh) $command = 'migrate:refresh';
$this->outputArtisanCommand($command, $arguments);
$this->call($command, $arguments);
}
private function outputArtisanCommand($command, $arguments): void
{
$line = "php artisan $command ";
foreach ($arguments as $key => $value) {
$line .= is_string($key) ? "$key = '$value' " : "$value ";
}
$this->line($line);
}
}
view raw CustomMigrateCommand.php hosted with ❤ by GitHub

CustomRefreshDatabase.php (Trait)

<?php
trait CustomRefreshDatabase
{
protected function refreshTestDatabase()
{
if (!RefreshDatabaseState::$migrated) {
$this->artisan('custom-migrate', [
' — refresh' => true,
]);
$this->app[Kernel::class]->setArtisan(null);
RefreshDatabaseState::$migrated = true;
}
$this->beginDatabaseTransaction();
}
/**
* Begin a database transaction on the testing database.
*
* @return void
*/
public function beginDatabaseTransaction()
{
/* @var DatabaseManager $database */
$database = $this->app->make('db');
foreach ($this->connectionsToTransact() as $name) {
$connection = $database->connection($name);
$dispatcher = $connection->getEventDispatcher();
$connection->unsetEventDispatcher();
$connection->beginTransaction();
$connection->setEventDispatcher($dispatcher);
}
$this->beforeApplicationDestroyed(function () use ($database) {
foreach ($this->connectionsToTransact() as $name) {
$connection = $database->connection($name);
$dispatcher = $connection->getEventDispatcher();
$connection->unsetEventDispatcher();
$connection->rollback();
$connection->setEventDispatcher($dispatcher);
$connection->disconnect();
}
});
}
/**
* The database connections that should have transactions.
*
* @return array
*/
protected function connectionsToTransact()
{
return property_exists($this, 'connectionsToTransact')
? $this->connectionsToTransact : [null];
}
}
view raw CustomRefreshDatabase.php hosted with ❤ by GitHub

tests/TestCase.php

Note: it was necessary to completely override the setUpTraits function, since the parent function would call the out-of-the-box RefreshDatabase::refreshDatabase() function. To reiterate, I didn’t want the possibility that another developer on the team would accidentally call that functionality in one of their tests.

<?php
abstract class TestCase extends BaseTestCase
{
use CreatesApplication;
protected function setUpTraits()
{
$uses = array_flip(class_uses_recursive(static::class));
if (isset($uses[CustomRefreshDatabase::class])) {
$this->refreshTestDatabase();
}
if (isset($uses[DatabaseTransactions::class])) {
$this->beginDatabaseTransaction();
}
if (isset($uses[WithoutMiddleware::class])) {
$this->disableMiddlewareForAllTests();
}
if (isset($uses[WithoutEvents::class])) {
$this->disableEventsForAllTests();
}
if (isset($uses[WithFaker::class])) {
$this->setUpFaker();
}
return $uses;
}
}
view raw TestCase.php hosted with ❤ by GitHub

That’s it! I hope this helps anyone looking to work with multiple databases or legacy systems. Please reach out to me with any comments or questions!

Leave a Reply

Like what you've read? Then please make sure you've left a like or a comment. If you'd be willing to receive email notifications of new ramblings when they arrive, I'd appreciate it! If not, I'd be happy to have you back soon.

Designed with WordPress

Discover more from Eddie's Code Shop

Subscribe now to keep reading and get access to the full archive.

Continue reading