How to create an in-memory database for PHPUnit testing?

Question

I'm a newcomer to PHPUnit (and unit testing in general). I want to work on a test suite that developers can run locally, but can also be run in our integration system (Codeship). I understand that it is possible to use an in-memory database, but it seems like that relies on the migrations, which we are not using (doesn't seem to handle views, stored procedures, functions, triggers, etc very well?).

What's the best way (place in Laravel) to 1) create a database in memory and seed the database with default data (to be used for ALL test)?

Answer 1

You can use SQLite.

From the docs:

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory.

Add this to the config/database.php file:

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

On the phpunit.xml file, under <php> node:

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

Read more here.

Other solution

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'   => '',
],

Use this command to run your migrations:

php artisan migrate --database=sqlite

Or add this line to the .env file:

DB_CONNECTION=sqlite

Your application is using sqlite for phpunit.

Now you can run your migrations and test. After that, just change the DB_CONNECTION to the database you are using for your project.

Answer 2

This isn't an answer you may be looking for but more of an alternative solution to consider.

From my experiences, I've found the path of least resistance is to actually mock your models (or the query builder if you are using that) and have them return the results you need them to.

When developing tests, you should always be thinking about what dependencies can be removed entirely from the test so you can keep your focus on only what you are trying to test at that time.

There are also likely going to be nuances (most likely very minor) between running an in-memory database vs how your production database will be working which will ultimately hurt the integrity of your tests and may end up even giving you false-positives. This will be especially true if you are using the query builder and you may end up needing to actually develop different queries (different syntaxes between MySQL and sqlite for example) depending on what environment you the query itself to even work.