Laravel has ability to make multiple database connection and can be used any of connection at runtime. In this article i will show you to create multiple database connection from different servers and then use these connection in our application during the run time according to use of them.
Laravel manages multiple connection in query builder, Eloquent model and even in run time query so we can fetch the result appropriately. We will get a clear understanding how we can achieve all these in migration, run time configuration, DB builder and Eloquent.
We will take simple examples of each of methods to understand this. All these examples will work in any version on laravel 5, laravel 6, laravel 7, laravel 8 and laravel 9.
Sometime in our application we want different connections for storing relational data and log data like audit to store in different database storage.
Let’s quickly see how it works, first of all we will update our .env configuration file and then update the config/database.php file as follow
Create Config for Mutiple Database Connection
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=password
DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=database2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=passwordHere we created another env constant DB_CONNECTION_SECOND and so on so we can use them in config/database.php as follow
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'mysql2' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL_SECOND'),
'host' => env('DB_HOST_SECOND', '127.0.0.1'),
'port' => env('DB_PORT_SECOND', '3306'),
'database' => env('DB_DATABASE_SECOND', 'forge'),
'username' => env('DB_USERNAME_SECOND', 'forge'),
'password' => env('DB_PASSWORD_SECOND', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],Here we created a new connection mysql2 which we can connect to another database
'mysql2' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL_SECOND'),
'host' => env('DB_HOST_SECOND', '127.0.0.1'),
'port' => env('DB_PORT_SECOND', '3306'),
'database' => env('DB_DATABASE_SECOND', 'forge'),
'username' => env('DB_USERNAME_SECOND', 'forge'),
'password' => env('DB_PASSWORD_SECOND', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],Use Multiple Database Connection
Next, we have created config and connection details in config successfully. now we will see to use these connection using multiple ways and methods in migration, model, controller, db query builder and controller itself.
Use Multiple Database Connection in Model
Sometimes we want some model dedicatedly for a specific connection so that we can query all the queries in that database connection only so to do that we need to define $connection property to model as follow
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
protected $connection = 'mysql2';
}So we defined protected $connection = 'mysql2'; to for this model article so all queries will point to mysql2 connection.
Read Also : How to make database connection in Laravel 8 ?
Use Multiple Database Connection in Migration
In this method we will use other database connection mysql2 in migration so below is the code
<?php
.....
public function up()
{
Schema::connection('mysql2')->create('articles', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->string('body')->nullable();
$table->timestamps();
});
}So we defined Schema::connection('mysql2'); to use mysql2 connection.
Use Multiple Database Connection in DB builder
Sometimes we want run query using query builder so we can use as follow
DB::connection('mysql2')->table("articles")->get();
Use Multiple Database Connection in Controller run time
There can be many conditions where we want to use multiple connection in our controller or model logic layer we want connection on demand so we can easily set connection using $Model->setConnection('mysql2'); and complete example is here
<?php
class ArticleController extends BaseController
{
public function get()
{
$Model = new Article;
$Model->setConnection('mysql2');
$find = $Model->find(1);
return $find;
}
}