SQLite In-Memory Database for Unit Tests in Laravel

Introduction

When testing your code (and this is particularly true for functional tests), even if you mock most of the database iteractions, at some point you need to hit the persistent layer of your application in order to ensure that everything works correctly. In these cases you obviously are not willing to make definitive changes to your development database each time you run a test.

Luckily for us Laravel 5 provides two useful methods to solve this problem:

• Database Transactions

Each time you run a test, Laravel will cleverly wrap the database calls in a transaction, so when the test finishes the transaction rolls back, leaving the database in its previous state.

• Switch to a test database

You can set up an alternative database to use when Laravel switches to a test environment. However you still have to make sure that this database remains in the same state between each test iteration, otherwise it could affect the results. Remember: tests must be performed in isolation.

However there’s a handy solution that elegantly solves this problem, and at the same time it speeds up the execution of the tests: setup a SQLite database in memory, so when the tests are over, it will simply be destroyed.

How to

The first step is making sure that you have correctly defined your migrations. I’m assuming that you are already familiar with this task so I will skip over.

Next we have to add a new parameter to our .env file to tell Laravel which is the default database for the local environment, so in this case we are going to use mysql by adding the following line

DB_DEFAULT=mysql

We also have to edit the config/database.php configuration file in order to use our new environment property. We can use the env function which takes a property name as first argument and optionally provides a default value as second argument.

<?php

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

?>

In the same file we have to add a new SQLite Database to our list. Let’s call it sqlite_testing

<?php

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

?>

Note that we have defined the parameter :memory: as database value, to use a memory version of SQLite. The database instance ceases to exist as soon as the connection is closed, therefore is ideal for performing each test in isolation.

We are nearly there! The following passage is to tell Laravel to use this database each time it runs the test suite.

For PHPUnit you can simply set the value of each environment variable that you want to use in the phpunit.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit>
	...
	<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_DEFAULT" value="sqlite_testing" />
	</php>
</phpunit>

Finally for each TestCase that needs to interact with the database you have to use this line to tell Laravel to run the migrations before the each testing method.

<?php

class ControllerTest extends TestCase
{
	use \Illuminate\Foundation\Testing\DatabaseMigrations;

	\** @test *\
	public function this_test_needs_to_iteract_with_the_database() {
		...
	}
}

?>

Further reads

In most of the cases this approach is recommended for speeding up your tests, however if you need to heavily seed the database each time before your tests, you might want to check out this great article from Chris Duell.