Multiple database connections in Laravel 5

Hi Everyone

Today we will learn how to do multiple database connections in laravel.

Sometimes we need to fetch data from multiple database. How can we do it in laravel? To achieve this we need to do following step.

In Laravel in config/database.php file, contains an array of all possible connections like MySQL, PostgreSQL, SQL Server.

 'connections' => [  
     'sqlite' => [  
       'driver'  => 'sqlite',  
       'database' => storage_path('database.sqlite'),  
       'prefix'  => '',  
     ],  
     'mysql' => [  
       'driver'  => 'mysql',  
       'host'   => env('DB_HOST', 'localhost'),  
       'database' => env('DB_DATABASE', 'forge'),  
       'username' => env('DB_USERNAME', 'forge'),  
       'password' => env('DB_PASSWORD', ''),  
       'charset'  => 'utf8',  
       'collation' => 'utf8_unicode_ci',  
       'prefix'  => '',  
       'strict'  => false,  
     ],  
     'pgsql' => [  
       'driver'  => 'pgsql',  
       'host'   => env('DB_HOST', 'localhost'),  
       'database' => env('DB_DATABASE', 'forge'),  
       'username' => env('DB_USERNAME', 'forge'),  
       'password' => env('DB_PASSWORD', ''),  
       'charset' => 'utf8',  
       'prefix'  => '',  
       'schema'  => 'public',  
     ],  
     'sqlsrv' => [  
       'driver'  => 'sqlsrv',  
       'host'   => env('DB_HOST', 'localhost'),  
       'database' => env('DB_DATABASE', 'forge'),  
       'username' => env('DB_USERNAME', 'forge'),  
       'password' => env('DB_PASSWORD', ''),  
       'charset' => 'utf8',  
       'prefix'  => '',  
     ],  
   ],  

Suppose we have to make connection for multiple database on mysql.

So we will make another array in connection like below.

 'connections' => [   
     'mysql_external' => [  
       'driver'  => 'mysql',  
       'host'   => env('DB_HOST', 'localhost'),  
       'database' => env('DB_DATABASE', 'forge'),  
       'username' => env('DB_USERNAME', 'forge'),  
       'password' => env('DB_PASSWORD', ''),  
       'charset'  => 'utf8',  
       'collation' => 'utf8_unicode_ci',  
       'prefix'  => '',  
       'strict'  => false,  
     ],    
   ],  

And we will use different env variable Like below.

  'mysql_external' => [  
       'driver'  => 'mysql',  
       'host'   => env('DB_EXT_HOST', 'localhost'),  
       'database' => env('DB_EXT_DATABASE', 'forge'),  
       'username' => env('DB_EXT_USERNAME', 'forge'),  
       'password' => env('DB_EXT_PASSWORD', ''),  
       'charset'  => 'utf8',  
       'collation' => 'utf8_unicode_ci',  
       'prefix'  => '',  
       'strict'  => false,  
     ],  

Now our env file look like below for database connections.

 DB_HOST=localhost  
 DB_DATABASE=testing  
 DB_USERNAME=homestead  
 DB_PASSWORD=secret  
 DB_EXT_HOST=localhost  
 DB_EXT_DATABASE=testing2  
 DB_EXT_USERNAME=homestead  
 DB_EXT_PASSWORD=secret  

To connect new database in our controller

 class TestController extends Controller  
 {  
   public function getTest()  
   {  
     $db_ext = \DB::connection('mysql_external');  
     $countries = $db_ext->table('countries')->get();  
     print_r($countries);  
   }  
 }  

So, by using above step, we can fetch and insert data from external database.

Hope it will help you.

Thanks