Multiple Database Drivers Testing In Laravel

Explanation

The reason I did this is I wanted the speed of SQLite but the ability to test MySQL specific syntax code. To better explain let me share an example

Example here's some sample routes, one without MySQL specific syntax and one with

Route::get('/sqlite', function () {
    return User::first();
});

Route::get('/mysql', function () {
    return User::first([DB::raw("(SELECT CONVERT_TZ(created_at,'UTC','America/Toronto')) as created_at_local")]);
});

The above CONVERT_TZ doesn't work in SQLite which means I can't test it (with the default testing database connection set to SQLite).

The error would look similar to:

SQLSTATE[HY000]: General error: 1 no such function: convert_tz

Setting Up a MySQL Database Class

To solve the above problem I've broken out the MySQL database connection into it's own class.

To do this we have to make a few changes to the database config

<?php

return [
    //...
    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

        'mysql_testing' => [
            'driver' => 'mysql',
            'url' => env('DB_TESTING_URL'),
            'host' => env('DB_TESTING_HOST', '127.0.0.1'),
            'port' => env('DB_TESTING_PORT', '3306'),
            'database' => env('DB_TESTING_DATABASE', 'forge'),
            'username' => env('DB_TESTING_USERNAME', 'forge'),
            'password' => env('DB_TESTING_PASSWORD', ''),
            'unix_socket' => env('DB_TESTING_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'),
            ]) : [],
        ],
    ]
    //...
];

Here's the additions made to phpunit.xml to the php tag

<php>
    //...
    <server name="DB_CONNECTION" value="sqlite"/>
    <server name="DB_DATABASE" value=":memory:"/>
</php>

MySQL Test Class

This class requires you have a database created in MySQL just for testing (i.e. testing_{my_app})

<?php

namespace Tests;

class MySQLDatabaseTestCase extends TestCase
{
    public function setUp()
    {
        $_ENV['DB_CONNECTION'] = 'mysql_testing';
        putenv('DB_CONNECTION=mysql_testing');

        parent::setUp();
    }
}

Using the Database Classes

<?php

namespace Tests\Feature;

use Tests\TestCase;
use Illuminate\Foundation\Testing\RefreshDatabase;

class ExampleSQLiteTest extends TestCase
{
    use RefreshDatabase;

    public function testSQLite()
    {
        $response = $this->get('/sqlite');

        $response->assertStatus(200);
    }
}

If I need to test MySQL syntax code I just extend MySQLDatabaseTestCase

<?php

namespace Tests\Feature;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\MySQLDatabaseTestCase;

class ExampleMySQLTest extends MySQLDatabaseTestCase
{
    use RefreshDatabase;

    public function testMySQL()
    {
        $response = $this->get('/mysql');

        $response->assertStatus(200);
    }
}

Sample

I've created a sample app when some basic instructions so you can see it in action

https://github.com/patoui/multiple-database-driver-example