Connect multiple databases in laravel

Rakesh Sharma      9 Comments  

How to Connect multiple databases in laravel 5

Working with databases and running queries is simple in laravel. Also you can easily work with connect multiple databases in laravel. In laravel database configuration for your laravel application is located at config/database.php. This file contain database connections. You may define all of your database connections in this file. You may specify which connection should be used by default in return array like.

// Define default database for your application.
'default' => 'mysql',

This file included examples for all of the supported database systems. Now let’s start how to connect multiple databases in laravel 5. For this first go to your config/database.php, open it in your favorite editor and add your multiple databases under connections array.

'connections' => [
 // our primary default database.
	'mysql' => [
		'driver'    => 'mysql',
		'host'      => env('DB_HOST', 'localhost'),
		'database'  => env('DB_DATABASE', 'laravelu'),
		'username'  => env('DB_USERNAME', 'root'),
		'password'  => env('DB_PASSWORD', ''),
		'charset'   => 'utf8',
		'collation' => 'utf8_unicode_ci',
		'prefix'    => '',
		'strict'    => false,
	],
// our second database.
	'mysql2' => [
		'driver'    => 'mysql',
		'host'      => env('DB_HOST', 'localhost'),
		'database'  => env('DB_DATABASE', 'laravel'),
		'username'  => env('DB_USERNAME', 'root'),
		'password'  => env('DB_PASSWORD', ''),
		'charset'   => 'utf8',
		'collation' => 'utf8_unicode_ci',
		'prefix'    => '',
		'strict'    => false,
	],

],

Now we have added to databases in this file with named “laravelu” and “laravel”. So initially we have defined our databases. Now we need to play with our multiple databases.

Accessing connection and running query with Query Builder :- Now let’s see how to run query with both databases. When we are using multiple connections, we can 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. To run query you need to make first DB object then you can access data with this object easily like

// Running query with default connection.
$userArray = DB::table('users')->get();
print_r($userArray);

// Makeing an object of second DB.             
$users2 = DB::connection('mysql2');
// Getting data with second DB object.
$u = $users2->table('sc_items')->get();
print_r($u);

Now you can access default DB as normal query run. Then for second you can use $users object. For more follow Accessing Connections.

Note :- If you have an error with connections, or second db didn’t connect and showing error with “firstdb.table” not found.

Connect multiple databases in laravel

Go to “root/.env” file and remove initial database configuration (remove code looks like below) For more about Environment Configuration. Save .env file and run you queries.

DB_HOST=localhost
DB_DATABASE=laravelu
DB_USERNAME=root
DB_PASSWORD=''

There are more methods to Connect multiple databases in laravel and access connections. The default and easy i have shared to connect multiple databases in laravel and access connections and run your queries.

  • Test Me

    how do I join tables using that two database?

    I have table sample here:

    Management Database
    User table
    Department table

    Application Database
    Application table
    mail person

    so I want to join the User table and the Application table. Application table has a foreign key user_id to point it on the User table.

    Do you have any example for that?

    Please..

    • Nitin Sridhar

      Same problem… Please tel me if u have resolved

      • Mable Lee

        Hi Nitin, Did you got a solution, I am also searching for a solution to join two tables from two db which are in two different server.

        • Nitin Sridhar

          I resolved by writing raw mysql query

  • I got this error here!! ERROR : SQLSTATE[HY000] [2002] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

  • Han Son

    I have the same problem about second db didn’t connect and showing error with “firstdb.table” not found.And I already remove the config about database in .env, but still the same error.

    • nelson tovo

      I am also experiencing the same problem…did you manage to find a solution to your problem

      • Han Son

        actually, I do. You can try `composer require hanson/framework` instead of `laravel/framework`. This problem happened in laravel 5.3, see https://github.com/laravel/framework/pull/17748 . However , it haven’t merge to laravel/framework officially.

        • nelson tovo

          I was able to solve my problem:
          1.In your .env,add another database connection params e.g

          DB_CONNECTION=mysql
          DB_HOST=localhost
          DB_DATABASE=database1
          DB_USERNAME=username1
          DB_PASSWORD=password1
          DB_HOST=host1

          DB_EXT_CONNECTION=mysql
          DB_EXT_DATABASE=database2
          DB_EXT_USERNAME=username2
          DB_EXT_PASSWORD=password2
          DB_EXT_HOST=host2

          2.In your config/database.php under connection,add the fowllowing

          ‘mysql2′ => [
          ‘driver’ => ‘mysql’,
          ‘host’ => env(‘DB_EXT_HOST’, ‘host2′),
          ‘database’ => env(‘DB_EXT_DATABASE’, ‘database2′),
          ‘username’ => env(‘DB_EXT_USERNAME’, ‘username2′),
          ‘password’ => env(‘DB_EXT_PASSWORD’, ‘password2′),
          ‘charset’ => ‘utf8′,
          ‘collation’ => ‘utf8_unicode_ci’,
          ‘prefix’ => ”,
          ‘strict’ => false,
          ],
          //Now we have established the connection,

          3.In your controller,

          $someModel = new Models();

          $someModel->setConnection(‘mysql2′);
          $something = $someModel->get();

          foreach($something as $mod){
          $model=new Models();
          $model->id=$mod->id;
          $model->make_id=$mod->make_id;
          $model->make=$mod->make;
          $model->model=$mod->model;
          $model->save();

          }
          This is used whenever you want to connect to the external database. Intailize the model ,and give it the connection you want to use and then the rest remains the same.