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. 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
- 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:
- Query builder via the connection method provided by the DB facade.
use Illuminate\Support\Facades\DB;
$users = DB::connection('mysql')->select(...);
- 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.