Laravel multiple databases PHPUnit

Question

I am developing a application with multiple database access and I want to have PHPUnit tests with this. My current approache is to have in the config\databases.php multiple connections (mysql, mysql2, mysql3) so I can have in the env file a different access for all of them. Because of this, the models have the $connection variable defined. In my first feature test I want to access a page and just see the data that I am providing in my factory, so just to get things started. In my phpunit.xml file I have specified the DB_CONNECTION to be sqlite and for each of the MySql setting to have the value=":memory:".

LATER EDIT

<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_CONNECTION" value="sqlite"/>
    <env name="DB_DATABASE_1" value=":memory:"/>
    <env name="DB_DATABASE_2" value=":memory:"/>
    <env name="DB_DATABASE_3" value=":memory:"/>
</php>

So above you can find the relevant code from PHPUnit.

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db1
DB_USERNAME=xxx
DB_PASSWORD=xxx

DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=db2
DB_USERNAME_2=xxx
DB_PASSWORD_2=xxx

DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_3=db3
DB_USERNAME_3=xxx
DB_PASSWORD_3=xxx

The problem that I have is the fact that when I run the tests, i have this error -> PDOException: SQLSTATE[HY000] [1049] Unknown database ':memory:'.

So somehow Laravel is not parsing the memory value. Any suggestion will be mush appreciated. Thank you

What do you mean you have each of the mysql setting to value=:memory:? — Devon Bessemer, Jun 06 '17 at 14:01
Could you please post some relevant code (tests and configs)? — ka_lin, Jun 06 '17 at 14:04
You'll need to post your database config file because I'm assuming you made some major modifications considering the use of `DB_DATABASE_1`, `DB_DATABASE_2` and `DB_DATABASE_3` instead of just `DB_DATABASE`. — Devon Bessemer, Jun 06 '17 at 14:13
@Devon, just to clarify follow this url -> https://laracasts.com/discuss/channels/laravel/access-another-db-in-the-service-provider — Morpheus_ro, Jun 06 '17 at 14:43
Yeah, the .env doesn't help since that really just sets ENV, doesn't use it. Your problem is that you override the connection in your model, so your connection is not going to be using sqlite when you use your model in tests. — Devon Bessemer, Jun 06 '17 at 14:59
I understand that, then how to solve this issue? basically I need to set up tests to use somehow the 3 db 'fake' connections or even some real ones, but for tests. This is the goal after all. — Morpheus_ro, Jun 06 '17 at 15:26

Curtis Blackwell · Accepted Answer · 2017-08-04T19:37:35.540

I was having the same issue, but I got things working with some help from Adam Wathan on Twitter.

Here's what I did:

phpunit.xml:

<env name="DB_CONNECTION" value="sqlite"/>
<env name="DB_DATABASE" value=":memory:"/>
<env name="DB_CONNECTION_ACTIVITY_LOG" value="sqlite"/>
<env name="DB_DATABASE_ACTIVITY_LOG" value=":memory:"/>

config/database.php:

'sqlite' => [
    'driver' => 'sqlite',
    'database' => env('DB_DATABASE', database_path('database.sqlite')),
    'prefix' => '',
],

'mysql' => [
    'driver' => env('DB_CONNECTION', 'mysql'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

'mysql-activity-log' => [
    'driver' => env('DB_CONNECTION_ACTIVITY_LOG', 'mysql'),
    'host' => env('DB_HOST_ACTIVITY_LOG', '127.0.0.1'),
    'port' => env('DB_PORT_ACTIVITY_LOG', '3306'),
    'database' => env('DB_DATABASE_ACTIVITY_LOG', 'forge'),
    'username' => env('DB_USERNAME_ACTIVITY_LOG', 'forge'),
    'password' => env('DB_PASSWORD_ACTIVITY_LOG', ''),
    'unix_socket' => env('DB_SOCKET_ACTIVITY_LOG', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

.env:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=my-app
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION_ACTIVITY_LOG=mysql-activity-log
DB_HOST_ACTIVITY_LOG=127.0.0.1
DB_PORT_ACTIVITY_LOG=3306
DB_DATABASE_ACTIVITY_LOG=my-app
DB_USERNAME_ACTIVITY_LOG=root
DB_PASSWORD_ACTIVITY_LOG=

Also, for anyone not up to the point of the PDOException, make sure to set the connections in your migrations/models, too.

database/migrations/my_migration.php:

Schema::connection(env('DB_CONNECTION_ACTIVITY_LOG', 'mysql'))->create(...);

app/MyModel.php:

class MyModel extends Model
{
    public function __construct($attributes = [])
    {
        parent::__construct($attributes);
        $this->connection = config('app.env') === 'testing' ? 'sqlite' : 'mysql-activity-log';
    }
    ...
}

This solution may help for me. But have you solved the relationship issues like you mention in twitter conversation with Adam Wathan? Cause I also suffering from that. — Didi Halim, Jan 19 '21 at 08:36
I don't think I did. I'm pretty sure I ended up just testing with a single database instead. — Curtis Blackwell, Jan 20 '21 at 23:03

score -1 · Answer 2 · answered Jun 06 '17 at 14:03

Hard to decode where you actually put the :memory: value.

In the phpunit.xml <php> section, this should be sufficient (assuming you haven't modified the sqlite connection):

<php>
    <env name="DB_CONNECTION" value="sqlite"/>
    <env name="DB_DATABASE" value=":memory:"/>
</php>

score -1 · Answer 3 · answered Jun 21 '17 at 18:03

To resolve a similar problem, I used a trait on the Model classes.

In my phpunit.xml I have this code

<env name="DB_CONNECTION" value="sqlite_testing"/>
<env name="DB_DATABASE" value=":memory:"/>```

In my config/database.php file I have connections set up for each of the databases, and a sqlite_testing connection set up for testing

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

'mysql_connection_a' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

'mysql_connection_b' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE_B', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

'mysql_connection_c' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE_C', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

I then create a trait for each of my connections to set the connection and include them in the relevant models. e.g. if the User model needed to use mysql_connection_a I would use ConnectionATrait in the model

use App\Traits\ConnectionATrait;

class User extends Authenticatable
{
     use Notifiable, ConnectionATrait;

The trait would then look like this

trait ConnectionATrait
{
    /**
    * The database table used by the model.
    *
    * @var string
    */

    public function __construct(array $attributes = [])
   {
        parent::__construct($attributes);
        if (env('APP_ENV') != 'testing') {
            $this->connection = 'mysql_connection_a';
        }else{
            $this->connection = 'sqlite_testing';
        }
    }
}

If you use migrations in your tests I also had to do a similar approach in the migration files and use a trait for each connection.

For the mysql_connection_a I create a trait that looks likes below that overrides the getConnection method:

trait ConnectionAConnectionTrait
{
    /**
    * Get the migration connection name.
    *
    * @return string
    */
    public function getConnection()
    {
        if (env('APP_ENV') != 'testing') {
            return 'mysql_connection_a';
        }
        return 'sqlite_testing';
    }
}

Then in the migration it would look like this

use Database\migrations\traits\ConnectionAConnectionTrait;

class CreateUsersTable extends Migration {

     use ConnectionAConnectionTrait;

     /**
     * Run the migrations.
     *
     * @return void
     */
     public function up()
     {
         Schema::connection($this->getConnection())
            ->create('users', function(Blueprint $table)
            {

It's better not to use the `env()` helper, b/c it busts the config cache [[reference](https://laravel.com/docs/5.4/configuration#configuration-caching)]. Instead, you can use `config('app.env')`. — Curtis Blackwell, Aug 04 '17 at 17:01

Laravel multiple databases PHPUnit

3 Answers3

Linked