Set SQLite as database for unit testing in Laravel 5.1

Question

I'm trying to set-up unit testing in Laravel 5.1. Following the documentation I see this:

Laravel is built with testing in mind. In fact, support for testing with PHPUnit is included out of the box

and

When running tests, Laravel will automatically set the configuration environment to testing. Laravel automatically configures the session and cache to the array driver while testing, meaning no session or cache data will be persisted while testing.

which is awesome. But... how do I tell Laravel to use SQLite database connection when tests are ran?

Only thing there is in config/database.php is this commented code:

/* We might use this connection for unit tests
'sqlite' => [
    'driver'   => 'sqlite',
    'database' => storage_path().'/database.sqlite',
    'prefix'   => '',
],
*/

We might use this :)

Is there a way to set this globally for all test? Do I need to set the connecton in every test case?

Any help appreciated.

Answer 1

Actually it's pretty simple.

Create a testing database on your storage/ folder, with the name database.sqlite or if you want another name or another location you have to change the configs on the config/database.php file, these are the default configs:

'sqlite' => [
    'driver'   => 'sqlite',
    'database' => storage_path('database.sqlite'),
    'prefix'   => '',
],

You have two options, you either edit your .env or you just specify the name of the database where you want to run your migrations:

php artisan migrate --database=sqlite

If you prefer editing your .env file, then we have to add a new variable:

DB_CONNECTION=sqlite

That's because Laravel defaults to MySQL when this variable is absent from .env file:

//config/database.php file
'default' => env('DB_CONNECTION', 'mysql'),

Now that our app is pointing into our sqlite database we can now run our migrations and seeding. After that if you just want to keep running MySQL remove DB_CONNECTION=sqlite from the .env file.

Now that you have your migrations on your testing database the last step is just to specify, your SQLite as your default database for tests.

On your root folder you have a phpunit.xml file, open it and a new variable under <php> node:

<env name="DB_CONNECTION" value="sqlite"/>

Now your application is using MySQL and phpunit is using SQLite.

Remember that if you change the .env to change it back to your default database;

PS

Be careful when running your migrations, if you specified a connection on the migration itself, it will try to run it on that connection.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AdminUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::connection('manage')->create('admin_users', function (Blueprint $t) {
            $t->increments('id');
            $t->string('name');
            $t->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::connection('manage')->dropIfExists('admin_users');
    }
}

This migration will always run on the connection manage no matter what you specify on the .env file or in the migration command

Answer 2

I have a bit of a different solution. I am using an in memory sqlite database.

first I change the default and then add a new connection to the database.php in config

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

...

'testing' => [
    'driver' => 'sqlite',
    'database' => ':memory:',
    'prefix' => '',
],

then I add the following line to the phpunit.xml

<env name="DB_CONNECTION" value="testing"/>

I leave the .env file untouched. That way the database connection will default to 'mysql'

---

Here is the full phpunit.xml for more clarity

<?xml version="1.0" encoding="UTF-8"?>
<phpunit backupGlobals="false"
     backupStaticAttributes="false"
     bootstrap="bootstrap/autoload.php"
     colors="true"
     convertErrorsToExceptions="true"
     convertNoticesToExceptions="true"
     convertWarningsToExceptions="true"
     processIsolation="false"
     stopOnFailure="false"
     syntaxCheck="false">
<testsuites>
    <testsuite name="Application Test Suite">
        <directory>./tests/</directory>
    </testsuite>
</testsuites>
<filter>
    <whitelist>
        <directory suffix=".php">app/</directory>
    </whitelist>
</filter>
<logging>
    <log type="coverage-html" target="./report/" charset="UTF-8"
         yui="true" highlight="false" 
         lowUpperBound="50" highLowerBound="80"/>
</logging>    
<php>
    <env name="APP_ENV" value="testing"/>
    <env name="CACHE_DRIVER" value="array"/>
    <env name="SESSION_DRIVER" value="array"/>
    <env name="QUEUE_DRIVER" value="sync"/>
    <env name="DB_CONNECTION" value="testing"/>
</php>
</phpunit>