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 be different in the future, i.e. administrators can generate reports like this, which is the source of using heterogeneous database systems.

So my question is Does Laravel provide any Facade to handle this situation? Or does any other framework have features that better suit the problem?

Answer 1

In Laravel 5.1, you specify the connection:

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

By default, Laravel uses the default connection. Pretty simple, isn't it?

Read more here: http://laravel.com/docs/5.1/database#Visit the connection

Answer 2

From the Laravel Documentation: You can access each connection when using multiple connections, through the DB connection method on the appearance. The name passed to the connection method should correspond to one of the connections listed in the config/database.php configuration file:

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

Define connection

Use .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

Useconfig/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'),
],

No .env

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',
        ]
    ),
);

Architecture/Migration

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

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

Alternatively, define a connection at the top.

protected $connection = 'pgsql';

Query Builder

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

model

(In Laravel >= 5.0 (or higher))

Set $connectionVariables

in the model

class ModelName extends Model { // extend changed

    protected $connection = 'pgsql';

}

eloquent

(In Laravel

Set $connectionVariables

in the model

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

Trading 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;
    }
}

---

Test version (Updated)

Version	Tested (Y/N)
4.2	no
5	is (5.5)
6	no
7	no
8	is (8.4)
9	is (9.2)