Laravel 11 Multiple Database Connection Example

Hi, Dev

In this guide, I'll walk you through the process of configuring multiple database connections in a Laravel 11 application.

In Laravel 11, the capability to define and utilize multiple database connections within your application is readily available. This functionality proves invaluable when your application requires access to diverse databases or data origins.

Configuring multiple databases is a straightforward process: simply augment them to the `config/database.php` file. Each database entry mandates a distinctive name along with pertinent particulars such as its type (e.g., MySQL, MongoDB, SQLite, etc.), location, and authentication credentials.

After you've set up the databases, you can use them anywhere in your app by saying which one to use when you make a database request. You can easily switch between databases while your app is running, so you can work with different data sources without any trouble.

You can see how to use multiple databases with migration, model, retrieve data, etc.

As we know, sometimes we need to use multiple database connections like MySQL, MongoDB, etc. I can say that when you work with a large number of projects, then you will need them maybe. So let's follow the below steps.

Set ENV Variable:

Here, you need to set configuration variables in the .env file. Let's create them as below:

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mydatabase
DB_USERNAME=root
DB_PASSWORD=root
   
DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=mydatabase2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=root

Database Configuration:

Now, as we created a variable in the env file, we need to use that variable in the config file. So, let's open the database.php file and add a new connections key as shown below:

config/database.php

<?php
  
use Illuminate\Support\Str;
  
return [
   
    'default' => env('DB_CONNECTION', 'mysql'),
   
    'connections' => [

        .....
   
        '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'),
            ]) : [],
        ],
        'mysql_second' => [
            '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_SECOND', ''),
            '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'),
            ]) : [],
        ],
.....    

Getting Data from Multiple Database using DB:

I will write two routes for getting products from different database connections. You can see a simple example with a DB.

Let's add two routes to your file:

routes/web.php

<?php
  
use Illuminate\Support\Facades\Route;
  
/*------------------------------------------
--------------------------------------------
Getting Records of Mysql Database Connections
--------------------------------------------
--------------------------------------------*/
Route::get('/get-mysql-products', function () {
    $products = \DB::table("products")->get();
      
    dd($products);
});
  
/*------------------------------------------
--------------------------------------------
Getting Records of Mysql Second Database Connections
--------------------------------------------
--------------------------------------------*/
Route::get('/get-mysql-second-products', function () {
    $products = \DB::connection('mysql_second')->table("products")->get();
      
    dd($products);
});

Multiple Database Connections with Migration:

You can create separate migrations for multiple database connections.

Default:

<?php
.....
public function up(): void
{
    Schema::create('blog', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->string('body')->nullable();
        $table->timestamps();
    });
}
.....

Second Database:

<?php
.....
public function up(): void
{
    Schema::connection('mysql_second')->create('blog', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->string('body')->nullable();
        $table->timestamps();
    });
}
.....

Multiple Database Connections with Model:

Default:

<?php
  
namespace App\Models;
  
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
  
class Product extends Model
{
    use HasFactory;
   
    protected $fillable = [
        'name', 'detail'
    ];
}

Second Database:

<?php
  
namespace App\Models;
  
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
  
class Product extends Model
{
    use HasFactory;
  
    protected $connection = 'mysql_second';
  
    protected $fillable = [
        'name', 'detail'
    ];
}

Multiple Database Connections in Controller:

Default:

<?php
  
use App\Models\Product;
    
class ProductController extends BaseController
{

    /**
     * Write code on Method
     *
     * @return response()
     */
    public function getRecord()
    {
        $products = Product::get();
        return $products;
    }

}

Second Database:

<?php

use App\Models\Product;
  
class ProductController extends BaseController
{
    
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function getRecord()
    {
        $product = new Product;

        $product->setConnection('mysql_second');

        $something = $product->find(1);

        return $something;
    }

}

I hope it can help you...