Quick Note: Changing Testing Database in Laravel

By default, Laravel uses in-memory SQLite database to run your tests doing database operations. You can confirm this by checking phpunit.xml in the root of your Laravel project.

<!-- ... -->
<php>
    <!-- ... -->
    <server name="CACHE_DRIVER" value="array"/>
    <server name="DB_CONNECTION" value="sqlite"/>
    <server name="DB_DATABASE" value=":memory:"/>
    <!-- ... -->
</php>
<!-- ... -->

Laravel does this because it is much much faster to run tests in-memory SQLite. Considering we have two tests called TestFoo and TestBar, the lifecycle of database testing is similar to below:

  1. Run all migrations before all tests.
  2. Run each TestFoo tests.
  3. Clear database after each TestFoo tests if RefreshDatabase trait is used.
  4. Run TestBar.
  5. Clear database after each TestBar tests if RefreshDatabase trait is used.
  6. Rollback all migrations.

Migration process and clearing all tables in a database is quite costly. That's why it is a viable choice for Laravel to use in-memory SQLite databases by default. However, SQLite might not have all the features you'd like to use as MariaDB/MySQL. In my particular case, I have wanted to use stored columns. There aren't any stored columns in SQLite. Thus, it is essential to use MariaDB as testing database. However, please note that running tests on a database not running on a memory just like SQLite will run the tests significantly slower.

Creating A Testing Database

In my case, I will use MariaDB. You need to create a separate testing database instead of a database you target in your machine. It's mainly because you might have factories and seeders which affect your main database. They will be affected (probably your records will get deleted) due to being used on tests. That's why we need a separate one.

So I login my MariaDB as root:

sudo mysql -u root

And then set up my testing database:

CREATE DATABASE myapp_testing; -- replace "myapp" with whatever you'd like
CREATE USER 'tester'@'localhost' identified by '111111'; -- change your username and password with what you'd like
GRANT ALL ON myapp_testing.* TO 'tester'@'localhost'; -- grant all privileges of user "tester" on "myapp_testing" database
FLUSH PRIVILEGES; -- refresh privilege cache

You can replace these with whatever you'd like.

Configuring PHPUnit Accordingly

First thing you notice might be that I create a new user. You might ask "Why create a new user since I can use my own user on my development machine?". It's because you put your database credentials to .env file, which is in .gitignore file and will not be pushed to a git repository. On the other hand, we will add these to phpunit.xml, which is not listed in .gitignore (clearly because it's needed to be pushed in order to work in other machines) and thus will be pushd to a git repository. While you might not choose to create a new user, you definitely should do it for security reasons. We all have our usernames and passwords that we use in our local development server in our minds. We should not let people know that.

After we do this, we can change our phpunit.xml accordingly:

<server name="DB_CONNECTION" value="mysql"/>
<server name="DB_DATABASE" value="myapp_testing"/>
<!-- The lines below are new -->
<server name="DB_USERNAME" value="tester"/>
<server name="DB_PASSWORD" value="111111"/>

And next time we run php artisan test, you will realize it will use MariaDB mainly because it is significantly slower. As stated above, migration and refreshing the database take more time on a database that's on disk rather than in a memory like SQLite.