How To Connect Multiple Database In Laravel 9

In this article, we will see how to connect multiple databases in laravel 9. we will learn how to use multiple database connections in laravel 9. Also, we will see laravel 9 multiple database connections with creating migration, model, and query examples.

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

So, let's see laravel 9 multiple database connections and how to connect databases 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 Connections 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 Connections in the 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 Connections 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 POSTS

FEATURE POSTS