How to use multiple databases in Laravel How to use multiple databases in Laravel laravel laravel

How to use multiple databases in Laravel


Using .env >= 5.0 (tested on 5.5)

In .env

DB_CONNECTION=mysqlDB_HOST=127.0.0.1DB_PORT=3306DB_DATABASE=database1DB_USERNAME=rootDB_PASSWORD=secretDB_CONNECTION_SECOND=mysqlDB_HOST_SECOND=127.0.0.1DB_PORT_SECOND=3306DB_DATABASE_SECOND=database2DB_USERNAME_SECOND=rootDB_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 <5.0

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

To specify which connection to use, simply run the connection() method

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

Query Builder

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

Eloquent

Set the $connection variable in your model

class SomeModel extends Eloquent {    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 connection 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


Actually, DB::connection('name')->select(..) doesnt work for me, because 'name' has to be in double quotes: "name"

Still, the select query is executed on my default connection. Still trying to figure out, how to convince Laravel to work the way it is intended: change the connection.

Edit: I figured it out. After debugging Laravels DatabaseManager it turned out my database.php (config file) (inside $this->app) was wrong. In the section "connections" I had stuff like "database" with values of the one i copied it from. In clear terms, instead of

env('DB_DATABASE', 'name')

I needed to place something like

'myNewName'

since all connections were listed with the same values for the database, username, password, etc. which of course makes little sense if I want to access at least another database name

Therefore, every time I wanted to select something from another database I always ended up in my default database