Database Unit Testing in Laravel with Multiple Connections

Recently while a member of our development team was working on an API for a report, they ran into issues when it came to writing the unit/feature tests, as the existing logic explictly used 2 separate connections for the writer and reader, and because we were using the DatabaseTransactions trait, we were getting failing unit tests but we weren’t sure why.

Background

Before I explain the issue in more detail, and how it was solved, a little background information might be useful to you. I will simplify example code so its easier to follow.

The Report

The report was an existing one, which could only be viewed through the website. We use 2 separate connections for the writer and reader, to spread the load (correctly) for how the data should be queried. i.e all reporting data is queried via the read connection.

    'read' => [
        'host' => env('DB_READ_HOST', '127.0.0.1'),
        'sticky' => false,
        'driver' => 'mysql',
        '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' => false,
        'engine' => null,
    ],

    'writer' => [
        'host' => env('DB_HOST', '127.0.0.1'),
        'sticky' => true,
        'driver' => 'mysql',
        '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' => false,
        'engine' => null,
    ],

We weren’t using the automatic connection shown below as we had issues with it:

'mysql' => [
    'read' => [
        'host' => env('DB_READ_HOST', '127.0.0.1')
    ],
    'write' => [
        'host' => env('DB_HOST', '127.0.0.1'),
    ],
    'sticky' => true,
    'driver' => 'mysql',
    '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' => false,
    'engine' => null,
],

Whenever we test against the database, to make our unit tests as accurate as possible, we always use the DatabaseTransactions trait so we know:

1. The data we are working with is 100% correct. i.e It only contains data generated (via factories) for the unit tests.
2. The testing database doesn’t getting filled with random data.

Unit Test Configuration

We have a phpunit.xml to use a separate database for testing with, along with some other sensible defaults. For this our default connection is the writer.

Report Repository

We use the Repository design pattern as we following SOLID principles, and so large queries are bogging down the model class.

The repository method looked something like this:

public function search(array $searchCriteria): LazyCollection
{
      return DB::connection('read')
          ->table('player_reports')
          ->select($reportSelect)
          ->where('player_reports.report_date', '>=', $searchCriteria['start_date'])
          ->where('player_reports.report_date', '<=', $searchCriteria['end_date'])
          ->cursor()
      ;
}

Reporting API Controller

Our API controller that calls the above repository method looks something like this:

public function indexAction(PlayerReportApiRequest $request): JsonResponse
{
    $results = $this->getPlayerReportingRepository()->search(
        $request->validated()
    );

    return response()->json([
        'params' => $input,
        'results' => $results->getResults()
    ]);
}

The Problem

At the start of of tests we factory the various bits of data we need in the database, i.e:

factory(Report::class, 10)->create();

This creates 10 reports as part of a database transaction, on our default connection writer.

One of the tests looked something like this:

public function testCorrectNumberOfResultsAreReturnedWhenUsingRequiredParams()
{
    factory(Report::class, 10)->create();

    $requestData = [
        'start_date' => '2023-10-01',
        'end_date' => '2023-10-30',
    ];

    $this
        ->get('/network/api/report?' . http_build_query($requestData))
        ->assertOk()
        ->assertJsonCount(3, 'results')
    ;
}

It’s quite simple:

1. Make a request to a controller
2. Controller hits a repository
3. Repository returns some data
4. Controller returns said data

Quite a simple unit test, but it was failing unexpectedly.

Now this is because database transactions are connection specific. So in the above the reports are generated into the database using the writer connection, and because the report repository uses the read connection, it can’t see that data, so my assertions were failing.

Now it took a while to track the cause, as a I forgot how database transactions were connection specific, so even though I was hitting the same database locally, it was still a different connection.

The only way the read connection could have access to the data is if the transaction was committed. Which defeats the object of using transactions in unit tests.

The Solution

In the end the solution was a simple, maybe partly dirty change, I haven’t decided yet. Code can work different depending on the environment as well as config settings via the .env files. So we followed that approach.

You remember our repository call from before:

public function search(array $searchCriteria): LazyCollection
{
      return DB::connection('read')
          ->table('player_reports')
          ->select($reportSelect)
          ->where('player_reports.report_date', '>=', $searchCriteria['start_date'])
          ->where('player_reports.report_date', '<=', $searchCriteria['end_date'])
          ->cursor()
      ;
}

We added a method to return the connection it should use, which changes behaviour if we are under the testing environment:

public function search(array $searchCriteria): LazyCollection
{
      return DB::connection($this->getReportDatabaseConnection())
          ->table('player_reports')
          ->select($reportSelect)
          ->where('player_reports.report_date', '>=', $searchCriteria['start_date'])
          ->where('player_reports.report_date', '<=', $searchCriteria['end_date'])
          ->cursor()
      ;
}

public function getReportDatabaseConnection(): string
{
      // If we are running unit tests use whatever connection
      // the testing environment is using
      if ('testing' === app()->environment()) {
          return config('database.default');
      }

      // Return default connection
      return 'read';
}

The default connection is read so it falls back nicely, so we’re only changing the connection under unit test scenarios.

Now our unit tests pass correctly. Hopefully this will help people in a similar situation.