Using multiple database connections in laravel

Using one database connection is more common and widely known for web applications, but in advanced application architecture we might need to use multiple database connections in laravel application.

Archiving this in a laravel application is very simple. Let’s see how to use multiple database connections in laravel.

Database Configuration

We can define multiple database connections in config/database.php file as shown below

return array(
'default' => 'mysql', // This is the default connection that laravel will use in case a connection is not specified.
	'connections' => array(
    	# Primary/Default database connection
    	'mysql' => array(
        	'driver'	=> 'mysql',
        	'host'  	=> '127.0.0.1',
        	'database'  => 'database1',
        	'username'  => 'root',
        	'password'  => 'secret'
        	'charset'   => 'utf8',
        	'collation' => 'utf8_unicode_ci',
        	'prefix'	=> '',
    	),
    	# Secondary database connection
    	'mysql2' => array(
        	'driver'	=> 'mysql',
        	'host'  	=> '127.0.0.1',
        	'database'  => 'database2',
        	'username'  => 'root',
        	'password'  => 'secret'
        	'charset'   => 'utf8',
        	'collation' => 'utf8_unicode_ci',
        	'prefix'	=> '',
    	),
	),
);

Here, we have a default connection still set to MySQL. This means that, unless we specify, the application will use the MySQL connection.

Using Specific Database Connection

After defining the connection, we can specify from which database we want to fetch data during the query or we can set the connection in our eloquent model.

Using specific connection in migration

/**
* Run the migrations.
*
* @return void
*/
public function up()
{
    Schema::connection('mysql2')->create('types', function (Blueprint $table) {
      $table->increments('id');
      $table->string('code')->index();
      $table->string('name');
      $table->timestamps();
    });
}

/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
    Schema::connection('mysql2')->drop('types');
}

In the above case, when we run the migration command, ‘types’ table will be migrated to ‘mysql2’ connection, that is our connection to ‘database2’. If we’re not defining a specified connection name, then laravel will use default connection.

Using specific connection in query builder

Similar to migration schema, we can define a connection on the Query Builder as below:

$users = DB::connection('mysql2')->select(...);

Using specific connection in query Eloquent

In Eloquent, We can define specific connection using $connection property in our model

class SomeModel extends Eloquent {
	protected $connection = 'mysql2';
}

If you want to specify the connection for modal at the time of using it, that can be done via the ‘setConnection’ method or the ‘on’ static method

class SomeController extends BaseController {
  public function someMethod() {
    // Using non static method
    $someModel = new SomeModel;
    $someModel->setConnection('mysql2');
    $something = $someModel->find(1);
    
    // Using static method
    $something = SomeModel::on('mysql2')->find(1);
    return $something;
  }
}

Relationships with two tables from different database using eloquent

After creating modals, it’s very easy to use relationships in eloquence. In this example we will have two models, One for users & other is for url, Please keep this in mind that this is just an example and real word use can be different and will not be like this

User Model

<?php
  
namespace App;
use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $primaryKey = 'email';
    // $incrementing should be set to false if your primary key is not set to auto incrementing
    public $incrementing = false;
  
    public function getUser($username)
    {
        return $this->where('email', $username)->first();
    }

    public function getUserWithUrl() {
        return $this->with(['url'])->get();
    }

    public function url() {
        return $this->hasMany('App\Url', 'email', 'email');
    }
}

URL Modal

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Url extends Model
{
    protected $connection = "mysql2";
    protected $table = 'url';
    protected $primaryKey = 'id';
}

As you can see here, Our user modal did not specify any connection and so it will use mysql, Our default connection but our second modal URL specified connection mysql2 and so it’s connecting our second database.

In User model we have specided relationship with url public function, it has one to many relationship, we have also specified our local key & foregin key using which mapping of data will be done.

Now, With this our relationship is ready and we can use the function of eloquent to get user data with it’s associated urls.

In returned object we can access url like this:

class SomeController extends BaseController {
  public function someMethod() {
    $index = 0;
    
    // Using non static method
    $userModel = new User;
    $user = $userModel->getUserWithUrl();
    
    $urlsOfUser = $user[$index]->url; // Here, url is one to many relationship and so it will be in array
    return $urlsOfUser;
  }
}

Word of advice! Using multiple database connections in a single project is generally needed and is generally safe to use if used properly, but using a relationship between two tables from separate databases might have significant performance impact so use it wisely if needed.