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 8 Multiple Database Connections
Laravel 8 Multiple Database Co...

Hello Freinds, In this tutorial we will see laravel 8 multiple database connections. Today I will give you step by st...

Read More

Apr-19-2021

Laravel 11 Drag and Drop File Upload with Dropzone JS
Laravel 11 Drag and Drop File...

Hello web developers! In this article, we'll see laravel 11 drag and drop file upload with dropzone js. He...

Read More

May-06-2024

Multi Step Form Wizard jQuery Validation
Multi Step Form Wizard jQuery...

In this article, we will see multi step form wizard jquery validation. Here, we will learn jquery validation for mu...

Read More

Jan-30-2023

How To Get Current Date And Time In Vue Js
How To Get Current Date And Ti...

In this article, we will see how to get the current date and time in vue js. In vue js very simple to get the current da...

Read More

Jan-14-2022