Laravel PHPUnit - Testing multiple connection system with SQLite in-memory DB.

Who is this article for?

Anyone looking for how to test multi-connection Laravel using PHPUnit in-memory database.

In this article, I’m going to walk through, how to create a migration, models, setup database connections and then finally set up PHPUnit in-memory database for multi-database Laravel App.

Before You Begin

In this article, our project uses two databases.

  • Main Database - This is the primary database
  • Audit Database - This is the audit database for storing audit/logs record.

Audit data will be used to show logs/audits of resource and also can be used to restore data.

Step 1 - Update the database.php

In the above code, you can see we have declared additional key => value pair, audit_connection.

'audit_connection' => env('AUDIT_DB_CONNECTION', 'audit'),

We are going to reference this connection name in our migrations and models rather than referencing "connections.audit" for our audit database connection.

Step 2 - Create a migration for the audit database

Here you can see in our construct we are setting the schema to use the connection set in database.audit_connections. Having the migration like this will help us override the values in our phpunit.xml.

Step 3 - Create a model for Audit

As you can see in the model rather than directly referencing the $connection as protected $connection = 'audit'; we are using __construct to set the connection. This will again help in our PHPUnit testing.

Now all our migrations and model is ready, we can now use in-memory databases for testing.

Step 4 - Update phpunit.xml

In this sample phpunit.xml, you can see that we have set the default connection to use database.connections.testing and audit connection to use database.connections.testing_audit.

In our migration and model, we have not hard coded the connections, using this technique will now let us use an in-memory database for testing.

Conclusion

In this article, you might be wondering why do we need to declare two SQLite in-memory databases when we can only use one and use it for both main testing connection as well as audit testing connection. Yes, that is correct we can always use one in-memory database, but in my experience, there are sometimes conflicts when using one in-memory database for a multi-database app. One situation I have encountered is when you have same table names in two or more database. So to be on the safe side, I tend to use an equal number of in-memory databases as our main app

Posted in LARAVEL, MYSQL, TESTING, BLOG
Akki K

Akki

Experienced Backend Developer.