How to use multiple databases in Laravel

Compatible versions (Last update - 2022/09/02)

1. 4.x
2. 5.x (Tested on v5.5)
3. 6
4. 7
5. 8 (Tested)
6. 9.X (Tested on v9.2)

---

Using .env >= 5.0 (or higher)

In .env

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

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=database2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=secret

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

'mysql2' => [
    'driver'    => env('DB_CONNECTION_SECOND'),
    'host'      => env('DB_HOST_SECOND'),
    'port'      => env('DB_PORT_SECOND'),
    'database'  => env('DB_DATABASE_SECOND'),
    'username'  => env('DB_USERNAME_SECOND'),
    'password'  => env('DB_PASSWORD_SECOND'),
],

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

Without .env <=v4.0 (or lower)

Define Connections

app/config/database.php

return array(

    'default' => 'mysql',

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

---

Schema

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

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

Or in top define connection

protected $connection = 'mysql2';

---

Query Builder

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

---

Eloquent (In Laravel <=v4)

Set the $connection variable in your model

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

Model (In Laravel >=v5)

Set the $connection variable in your model

class ModelName extends Model { // extend changed

    protected $connection = 'mysql2';

}

---

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('mysql2'); // non-static method

        $something = $someModel->find(1);

        $something = SomeModel::on('mysql2')->find(1); // static method

        return $something;
    }

}

Note Be careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats and depends on what database and/or database settings you have.

---

From Laravel Docs

Using Multiple Database Connections

When using multiple connections, you may access each connection via the connection method on the DB facade. 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(...);

You may also access the raw, underlying PDO instance using the getPdo method on a connection instance:

$pdo = DB::connection()->getPdo();

---

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

In Laravel 5.1, you specify the connection:

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

Default, Laravel uses the default connection. It is simple, isn't it?

Read more here: http://laravel.com/docs/5.1/database#accessing-connections