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 tips: DB Models and Eloquent
Laravel tips: DB Models and El...

In the realm of web development, an efficient and robust data handling mechanism is paramount. Laravel, a PHP web applic...

Read More

Oct-11-2023

The Mix Manifest Does Not Exist Laravel
The Mix Manifest Does Not Exis...

In this article, we will see the mix manifest does not exist in laravel 8 and laravel 9. We will solve the mix...

Read More

Oct-28-2022

Laravel 8 Authentication using Jetstream Example
Laravel 8 Authentication using...

In this article, we will discuss laravel 8 authentication with jetstream. This post will give you a simpl...

Read More

Nov-12-2020

Laravel 10 Form Validation Example
Laravel 10 Form Validation Exa...

In this article, we will see the laravel 10 form validation example. Here, we will learn about basic form input with lar...

Read More

Mar-22-2023