Laravel 9 Multiple Database Connections

Websolutionstuff | Mar-04-2022 | Categories : Laravel MySQL

In this tutorial, we will see laravel 9 multiple database connections. we will implement how to use laravel 9 multiple database connections.

Many times we have requirements in our project to use multiple database connections like MySQL, MongoDB, PostgreSQL etc. When you work with a very large amount of projects then you will need to manage multiple database connections. So, in this tutorial, we will see one or more database connections in a single laravel application.

So, let's see multi database connection laravel 9, laravel 9 multiple database connections, multiple database connections in laravel 9 dynamically, how to connect database in laravel 9.

Set .env Variable For Laravel 9 Multiple Database Connections

Set both database configurations in a .env file like the below code.

// Database 1

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=

// Database 2

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=database_2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=

 

 

Use .env Variable : 

Now, as we created a variable in the .env file we need to use that variable on the config file. So let's open the database.php file and add a new connections key as 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', 'root'),
            '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' => env('DB_CONNECTION_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', 'root'),
            'password' => env('DB_PASSWORD_SECOND', ''),
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
        ],

.....   

 

Read & Write Connections

Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements.

'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',
            '196.168.1.2',
        ],
    ],
    'write' => [
        'host' => [
            '196.168.1.3',
        ],
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

 

 

Use Multiple Database Connections :

Here, I will give you a simple example of how to use multiple connections with migration.

Default Database :

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email');
        $table->timestamps();
    });
}

Second Database : 

public function up()
{
    Schema::connection('mysql2')->create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email');
        $table->timestamps();
    });
}

 

Use Multiple Database Connection with Model

In this step, we will see the multi-database with a model.

Default :

<?php

namespace App;
  
use Illuminate\Database\Eloquent\Model;
   
class Users extends Model
{
  ....
}

Second :

<?php

namespace App;
  
use Illuminate\Database\Eloquent\Model;
   
class Users extends Model
{
    protected $connection = 'mysql2';
    ...
}

 

 

Use Multiple Database Connection in Controller

Default :

class UsersController extends BaseController
{
    public function getRecord()
    {
        $users = new Users;
        $find = $users->find(1);
        return $find;
    }
}

Second :

class UsersController extends BaseController
{
    public function getRecord()
    {
        $users = new Users;
        $users->setConnection('mysql2');
        $find = $users->find(1);
        return $find;
    }
}

 

Use Multiple Database Connection with Query Builder

Default :

$users = DB::table("users")->get();
print_r($users);

Second :

$users = DB::connection('mysql2')->table("users")->get();
print_r($users);

 


You might also like :

Recommended Post
Featured Post
Laravel 11 jQuery UI Ajax Autocomplete Search
Laravel 11 jQuery UI Ajax Auto...

Hello, laravel web developers! In this article, we'll see how to create an Ajax autocomplete search in laravel 11. h...

Read More

Jun-19-2024

How To Install Python In Windows
How To Install Python In Windo...

In this article, how to install python on windows. Python is a high-level, interpreted, general-purpose programming...

Read More

May-05-2022

How to Generate QR Code in Node.js
How to Generate QR Code in Nod...

In this example we will see how to generate QR Code in Node.js application. In this example we will use qrcode npm...

Read More

Sep-20-2021

Laravel 8 Eloquent orWhereHas Condition
Laravel 8 Eloquent orWhereHas...

In this example we will see laravel 8 eloquent orWhereHas() condition. In previous example we will learn about ...

Read More

Oct-15-2021