Making The Mysql Database Play Nice With Your Laravel Unit Tests Enter a brief summary for this post

Unit testing with the MySql database

In an earlier post I blogged about the SqlLite database for unit testing. Nothing wrong with this setup however I bet you'd agree using the MySql database would be an advantage.

One plus point obviously are transactions and greater reliance therefore. And as PHP Laravel developers we are more familiar and happier with a true relational database.

Set up MySql for PHP Unit testing

Surprisingly there isn't all that much involved to get setup. The first thing to do is create an .env.testing file alongside the .env file. Once you have done this then copy over the following environment variables to the .env.testing file.

APP_NAME=Testing
APP_ENV=testing
APP_KEY=base64:Cfqj6QqA9nMTB8xvx+Bz3jd4CbI3qOQsasH9YxWzuMs=
APP_DEBUG=true
APP_URL=http://example.com

LOG_CHANNEL=stack

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=example_testing
DB_USERNAME=root
DB_PASSWORD=

As you can see I have renamed the environment variable APP_ENV which is important. If you forget to change this to "testing" then your setup won't work. Also be sure to setup your database environment variables with a brand new database for testing purposes.

Now look at your configuration file for the database, ./config/database.php for an addition. Below I've only highlighted those variables that need to be changed ignoring the defaults.

'mysql_testing' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'example_testing'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', '<password>'), // ... etc ...

You are basically copying the default mysql configuration and pasting it under the index of mysql_testing and adjusting accordingly.

Set up PHPUnit for testing

Last thing is to modify your phpunit.xml configuration. It's the same as with the SQLite database there are two variables. Those are:

<server name="DB_CONNECTION" value="mysql_testing"/>
<server name="DB_DATABASE" value="example_testing"/>

Done? Not so fast.

There is a third variable and if that isn't present then the setup won't work. The issue arises with Laravel's caching. When the cache is created it will likely be the .env environment that'll get cached. So the tests will run on that environment automatically even despite you stating clearing you want to use the alternative .env.testing environment.

So php artisan test --env=testing won't work and php artisan cache:clear prior will clear the cache for testing but the application will use the cached .env.testing environment unintentionally.

You must include the following variable in your PHPUnit's XML configuration:

<server name="APP_CONFIG_CACHE" value="cache/testing/cache.php" force="true"/>

This will force Laravel to create a separate cache for the .env.testing environment leaving your application untouched and there is no need for php artisan cache: clear or php artisan optimize etc. Prior to running your tests, you must migrate the database using php artisan migrate --env=testing, remembering to use the correct environment.

That's it. No need to delete the cache directory or use a different schema, one for development and another for testing. I'm pleased with the simplicity of this setup and being able to use MySql for both development and testing is the real win.