How to use multiple databases in Laravel

Question

I want to combine multiple databases in my system. Most of the time the database is MySQL; but it may differ in future i.e. Admin can generate such a reports which is use source of heterogeneous database system.

So my question is does Laravel provide any Facade to deal with such situations? Or any other framework have more suitable capabilities for problem is?

Answer

[From Laravel Docs](https://laravel.com/docs/5.4/database#using-multiple- database-connections): You may access each connection via the connection method on the DB facade when using multiple connections. The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file:

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

Define Connections

Using .env >= 5.0 (or higher)

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mysql_database
DB_USERNAME=root
DB_PASSWORD=secret

DB_CONNECTION_PGSQL=pgsql
DB_HOST_PGSQL=127.0.0.1
DB_PORT_PGSQL=5432
DB_DATABASE_PGSQL=pgsql_database
DB_USERNAME_PGSQL=root
DB_PASSWORD_PGSQL=secret

Using config/database.php

'mysql' => [
    'driver'    => env('DB_CONNECTION'),
    'host'      => env('DB_HOST'),
    'port'      => env('DB_PORT'),
    'database'  => env('DB_DATABASE'),
    'username'  => env('DB_USERNAME'),
    'password'  => env('DB_PASSWORD'),
],

'pgsql' => [
    'driver'    => env('DB_CONNECTION_PGSQL'),
    'host'      => env('DB_HOST_PGSQL'),
    'port'      => env('DB_PORT_PGSQL'),
    'database'  => env('DB_DATABASE_PGSQL'),
    'username'  => env('DB_USERNAME_PGSQL'),
    'password'  => env('DB_PASSWORD_PGSQL'),
],

Note: In pgsql, if DB_username and DB_password are the same, then you can use env('DB_USERNAME'), which is mentioned in .env first few lines.

[Without .env <= 4.0 (or lower)](https://github.com/laravel- shift/laravel-4.2/blob/master/app/config/database.php#L47-L86)

app/config/database.php

return array(
    'default' => 'mysql',
    'connections' => array(
        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'mysql_database',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Secondary database connection
       'pgsql' => [
            'driver' => 'pgsql',
            'host' => 'localhost',
            'port' => '5432',
            'database' => 'pgsql_database',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
        ]
    ),
);

Schema / Migration

Run the connection() method to specify which connection to use.

Schema::connection('pgsql')->create('some_table', function($table)
{
    $table->increments('id'):
});

Or, at the top, define a connection.

protected $connection = 'pgsql';

Query Builder

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

Model

(In Laravel >= 5.0 (or higher))

Set the $connection variable in your model

class ModelName extends Model { // extend changed

    protected $connection = 'pgsql';

}

Eloquent

(In Laravel <= 4.0 (or lower))

Set the $connection variable in your model

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

Transaction Mode

DB::transaction(function () {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
});

or

DB::connection('mysql')->beginTransaction();
try {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->beginTransaction();
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
    DB::connection('pgsql')->commit();
    DB::connection('mysql')->commit();
} catch (\Exception $e) {
    DB::connection('mysql')->rollBack();
    DB::connection('pgsql')->rollBack();
    throw $e;
}

You can also define the connection at runtime via the setConnection method or the on static method:

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

Note: Be careful about building relationships with tables across databases! It is possible to do, but it can come with caveats depending on your database and settings.

Tested versions ( Updated )

Useful Links

1. Laravel 5 multiple database connections FROM laracasts.com
2. Connect multiple databases in Laravel FROM tutsnare.com
3. Multiple DB Connections in Laravel FROM fideloper.com