Step-By-Step Guide To multiple database connections in laravel

In this tutorial, I will give you an example of the “How to Connect Multiple Database Connection in Laravel“, So you can easily apply it with your laravel 5, laravel 6, laravel 7, and laravel 8 application.

First, what we’re doing here, This is the example :

Need of Multiple Database Connection in Laravel

We use a single database on our project mostly but sometimes while we work on bigger projects like advanced ERP or CRM and healthcare applications where data is stored in multiple databases and we need to manage or create numbers of tables in multiple databases. so we need to connect multiple databases to manage all the large amounts of data, so we need to connect multiple databases to manage all the large amounts of Data.

Here we have created 3 databases and we have some entries in these tables for testing.

8bityard
laravel_db_test_1
laravel_db_test_2

Database Configuration

At first, we need to update our database configuration to connect to these databases, and then we create models and a controller for fetching data from these different databases tables we can use query builder or ORM to performing this task, we use Laravel ORM.

.env file :

We will find here we are already connected with a single database with the name of 8bityard with is belong to 8bityard Database. So, we want to add two more databases just copy these database properties and paste them twice we just change the property name like: “_SECOND” and “_THIRD”; we change the database name, host, user, password,

you can put any names it’s not mandatory, like : DB_CONNECTION_1, DB_CONNECTION_2, DB_CONNECTION_3

APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:CHKbTU+tDJrWLxSlOwpLJYRpyQq3CnDc9Hrw3K2cTBI=
APP_DEBUG=true
APP_URL=http://localhost

LOG_CHANNEL=stack

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=8bitYard
DB_USERNAME=root
DB_PASSWORD=

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

DB_CONNECTION_THIRD=mysql
DB_HOST_THIRD=127.0.0.1
DB_PORT_THIRD=3306
DB_DATABASE_THIRD=laravel_db_test_2
DB_USERNAME_THIRD=root
DB_PASSWORD_THIRD=

BROADCAST_DRIVER=log
CACHE_DRIVER=file
QUEUE_CONNECTION=database
SESSION_DRIVER=file
SESSION_LIFETIME=120

After this step, we need to change on database.php file.

config\database.php :

In the databse.php file, you can see there are lots of connections available on this file like mysql,pgsql,sqlsrv.

We just copy the SQL server and paste it twice. we rename the second or third databases like mysql2,mysql3 and rename all the other databases’ properties,

DB_HOST_SECOND,DB_PORT_SECOND,DB_DATABASE_SECOND,DB_USERNAME_SECOND,DB_PASSWORD_SECOND

DB_HOST_THIRD,DB_PORT_THIRD,DB_DATABASE_THIRD,DB_USERNAME_THIRD,DB_PASSWORD_THIRD.

'connections' => [

        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

        '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'),
            ]) : [],
        ],

         'mysql2' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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', ''),
            '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'),
            ]) : [],
        ],

         'mysql3' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST_THIRD', '127.0.0.1'),
            'port' => env('DB_PORT_THIRD', '3306'),
            'database' => env('DB_DATABASE_THIRD', 'forge'),
            'username' => env('DB_USERNAME_THIRD', 'forge'),
            'password' => env('DB_PASSWORD_THIRD', ''),
            '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'),
            ]) : [],
        ],

        'pgsql' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
        ],

    ],

After these all steps Please restart your server using php artisan:serve command in the terminal.

Define Connection in Models :

We have already created 3 models (Category, Contact, Newsletter) and provide the database connection (mysql2,mysql3) where these tables exist, for fetching data from different databases and import all these models.

Category.php
Contact.php
Newsletter.php

app\Category.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $table = 'categories';
}

app\Model\Contact.php

<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    public $connection = "mysql2";
}

app\Model\Newsletter.php

<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;

class Newsletter extends Model
{
     public $connection = "mysql3";
}

Create Route :

routes\web.php

#Test and Connect Multiple DB  
Route::get('/get-db-data','TestMultipleDbController@list_tabledata');

Create Controller :

php artisan make:controller TestMultipleDbController

App\Http\Controllers\TestMultipleDbController

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Category;
use App\Model\Contact;
use App\Model\Newsletter;



class TestMultipleDbController extends Controller
{
    function list_tabledata()
    {
        $getfirst_db_data  = Category::all();
        $getsecond_db_data = Contact::all();
        $getthird_db_data  = Newsletter::all();

        dd($getfirst_db_data,$getsecond_db_data,$getthird_db_data);
    }
}

After following these steps successfully, you are able to fetch these tables’ data from different databases.