Reading Time: 2 minutes

Sometimes you may want to have multiple dynamic database connection in laravel and the solution is to use multiple database connections in laravel. This tutorial guide shows you a multiple database connection example in laravel for laravel 5, laravel 6, laravel 8 and laravel 9.

Here’s how to go about it:

  1. 1. Add database environment variables on the .env file
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_a
DB_USERNAME=root
DB_PASSWORD=strongpassword

DB_CONNECTION_B=mysql
DB_HOST_B=127.0.0.1
DB_PORT_B=3306
DB_DATABASE_B=database_b
DB_USERNAME_B=root
DB_PASSWORD_B=strongpassword

NOTE: You may share env variables if they’re the same like username and password instead of defining again

  1. 2. Add the database configuration 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'),
],

'mysqlb' => [
    'driver'    => env('DB_CONNECTION_B'),
    'host'      => env('DB_HOST_B'),
    'port'      => env('DB_PORT_B'),
    'database'  => env('DB_DATABASE_B'),
    'username'  => env('DB_USERNAME_B'),
    'password'  => env('DB_PASSWORD_B'),
],
  • 3. Access the database connection:

You can access each database connection via:

  1. Query builder via the connection method provided by the DB facade.
use Illuminate\Support\Facades\DB;
 
$users = DB::connection('mysql')->select(...);
  1. Eloquent – Set the connection property of the Model class; that is the $connection variable
class ExampleModel extends Eloquent {

    protected $connection = 'mysqlb';

}

In the Schema to manipulate database tables. To specify which connection to use, simply run the connection() helper method.

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

Extra
You can also define the connection at runtime via the setConnection method or the on static method:

class MyController extends BaseController {

    public function sampleUsingStaticMethod()
    {
        $model = new myModel;

        $var = model::on('mysqlb')->find(1);

        return $var;
    }
    
    public function sampleUsingNonStaticMethod()
    {
        $model = new myModel;

        $model->setConnection('mysqlb');

        $var = $model->find(1);

        return $var;
    }

}

The connection name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file or configured at runtime using the config helper…. more fro laravel docs.

Categorized in:

Laravel

Tagged in: