php - How to use multiple databases in Laravel

ID : 10279

viewed : 91

Tags : phpmysqldatabaselaravelphp

Top 5 Answer for php - How to use multiple databases in Laravel

vote vote


Using .env >= 5.0 (Tested on 5.5) (Works on 8)

In .env


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


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


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(...); 


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
  2. Connect multiple databases in laravel FROM
  3. Multiple DB Connections in Laravel FROM
vote vote


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:

vote vote


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


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

vote vote


Laravel has inbuilt support for multiple database systems, you need to provide connection details in config/database.php file

return [     'default' => env('DB_CONNECTION', 'mysql'),      'connections' => [         'mysql' => [             'driver' => 'mysql',             'host' => env('DB_HOST', ''),             'port' => env('DB_PORT', '3306'),             'database' => env('DB_DATABASE', 'forge'),             'username' => env('DB_USERNAME', 'forge'),             'password' => env('DB_PASSWORD', ''),             'charset' => 'utf8',             'collation' => 'utf8_unicode_ci',             'prefix' => '',             'strict' => false,             'engine' => null,         ], 'mysqlOne' => [             'driver' => 'mysql',             'host' => env('DB_HOST_ONE', ''),             'port' => env('DB_PORT', '3306'),             'database' => env('DB_DATABASE_ONE', 'forge'),             'username' => env('DB_USERNAME_ONE', 'forge'),             'password' => env('DB_PASSWORD_ONE', ''),             'charset' => 'utf8',             'collation' => 'utf8_unicode_ci',             'prefix' => '',             'strict' => false,             'engine' => null,         ], ]; 

Once you have this you can create two base model class for each connection and define the connection name in those models

//BaseModel.php protected $connection = 'mysql';  //BaseModelOne.php protected $connection = 'mysqlOne'; 

You can extend these models to create more models for tables in each DB.

vote vote


Also you can use postgres fdw system

You will be able to connect different db in postgres. After that, in one query, you can access tables that are in different databases.

Top 3 video Explaining php - How to use multiple databases in Laravel