Laravel 5 testing in memory

Question

I am writing tests via behat for my large Laravel 5 project.

I have a testing copy of my MySQL database in MySQL and a seeder for that database that shares some of the seeders of other environments. All of that works as expected.

However, I tried switching to using a sqlite in-memory database because it would speed up my automated tests dramatically and because I'm running "artsian migrate:refresh ---seeder=TestDatabaseSeeder at the start of every behat scenario.

The problem I'm having is that some of my seed data causes sqlite to throw a very non-descript syntax error but MySQL is completely fine with the seed data.

Ideally, I think, I'd like to have it use MySQL in-memory for performance purposes and to keep the database engine consistent. Is there an easy way with or without Laravel to use MySQL in memory when running tests? A solution that does NOT involve duplicating & editing migration files in a way that makes sqlite happy?

Answer 1

As of 2019 here is the way to make Laravel PHPunit tests to use the in-memory DB.

Setup SQLite In-memory for Laravel / Lumen tests

You have to have the following in place:

1. All database migrations for all the tables and fileds in your DB as you normally do for an actual DB, see Laravel migrations official docs.
2. Use the RefreshDatabase trait (DatabaseMigrations trait for Lumen) in your PHPUnit test class (official docs).
3. Set the following in your config/database.php file in connection setion:

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

4. Set the following line in your phpunit.xml file in <php> section:

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

Thus as soon as you run your PHPUnit tests, PHPUnit will read phpunit.xml, replace the .env's DB_CONNECTION variable with testing. It will trigger the DB configuration testing (which is in-memory sqlite DB) to be used.

The tests with in-memory DB vs disk DB is (according to my experience) ~5-30 times faster. 30 times difference is for small test suite of ~200 CRUD tests all running DB queries on half a dozen tables with some hasMany and belongsToMany pivot relationships.

Refresh Database for Each Test

To run database migrations to have a clean database before each test add the following trait the top of your test class (Laravel):

use Illuminate\Foundation\Testing\RefreshDatabase;
// ... then in the class body:
use RefreshDatabase;

For Lumen it should be

use Laravel\Lumen\Testing\DatabaseMigrations;
// ...then
use DatabaseMigrations;

Do not forget to use model factories to seed the DB with test data if needed.

And yes, the migrations will run before and after each test so your start and finish with clean database after your tests finish (this will have an implications if you seeded the on-disk DB with some data and once in a while switch the tests to run on it instead of running on the in-memory DB).

Fix SQLite Cannot add a NOT NULL column with default value NULL error

Now you succesfully have set up the in-memory DB testion for your app. As you try to use the foreign keys to cascade changes to some related tables expect the above mentioned error to appear.

If you quickly switch your tests to the on-disk DB (comment out <env name="DB_CONNECTION" value="testing"/> in your phpunit.xml file) the error disappears. After switch back to in-memory DB check your on-disk DB is not empty. If so run migrate:refresh and db:seed (here you are assumed to had earlier prepared the DB seeds you need) with artisan.

By default SQLite disables the foreign keys. Add the following snippets to your PHPUnit test class to fix this:

// At the file's top to import the DB facade
use Illuminate\Support\Facades\DB;


// In the setUp() method
parent::setUp();
if (DB::connection() instanceof \Illuminate\Database\SQLiteConnection) {
    DB::statement(DB::raw('PRAGMA foreign_keys=on'));
}

See detailed explanations and more, better, different solutions here.

Hope this clarifies the subject enough to work seamlessly.