Laravel - Handling Multiple Connections Posted on May 18, 2016

In a recent project I have been working on I needed to handle multiple connections in Laravel. I don't mean just connect to the other DB and run a few "select" or "update" statements.

I needed to have full blown eloquent models and also migrations to go to different databases.

Database configuration

The first thing we would need to do is setup our other database connections. Inside config/database.php you will see you have a default connection for "mysql" and it looks something like this

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

If we want to add a new connection we can just duplicate this entire thing and add it below. Here is an example of my second connection.

'mysql.other_db' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_OTHER_DATABASE', 'forge'),
            'username' => env('DB_OTHER_USERNAME', 'forge'),
            'password' => env('DB_OTHER_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

So far pretty straight forward. We now have two mysql connections installed. Be aware that your connection name does not need to start with "mysql" i just like to quickly see what kind of database I am on.

Migrations

Now that we have different connections we need to look at how we can handle them in migration files. I didn't separate the migration into a different directory as I would need to edit the artisan command to do this, and I just didn't want to do that.

So, all of my migrations still exists in the database/migrations folder. With that being said if we take a look at a regular migration file below

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name')
        $table->timestamps();
    });
}

You can see there isn't anything different... because well I didn't change anything. However if we want this table (users) to go to our other database all we have to do is change the first line.

Schema::connection('mysql.other_db')->create('users', function (Blueprint $table) {

Now when we run "php artisan migrate", Laravel will see that we want to use our other connection and place that table on to our database.

Eloquent Model

Our final step would be to generate an eloquent model, there are a TON of different ways to do this:

What ever way you choose that is up to you and your workflow. But in the end you should end up with a model like this (condensed down)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $table = 'users';
    protected $fillable = [
        'name',
    ];

    protected $hidden = [];

    protected $casts = [];

    protected $dates = [];

}

So we have all seen this before, however we still aren't telling Laravel that we want to use our other connection. All we need to do is add a new property called connection.

I usually add it at the top of the class, before the table variable.

protected $connection = 'mysql.other_db';

And now when we call this model itself, it will not use our default connection, but rather it will use the mysql.other_db connection to select, insert, update, delete, etc ..

Thank you for reading, and remember to check out my other project: https://laracademy.co/

Share this post