Easy way to connect multiple databases in Laravel 6,7,8 and 9 controller, model and migration

We can easily connect multiple databases in Laravel, following the steps given below.

Step 1 – Create .env variables

The most convenient and best way to make a secondary database is to add the credentials in your .env file as shown below.

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

DB_DATABASE_TWO=secondary_database
DB_USERNAME_TWO=root
DB_PASSWORD_TWO=root

Step 2 – Update the Database Configuration file

Next, you should configure your secondary connection in the database.php file.

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', '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', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE_TWO', 'forge'),
            'username' => env('DB_USERNAME_TWO', 'forge'),
            'password' => env('DB_PASSWORD_TWO', ''),
            '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'),
            ]) : [],
        ],

        // ....

    ],

    // ....

];

Step 3 – Secondary database connection on the fly

Use Database connection in a Controller, as shown below.

<?php
  
class GenderController extends Controller
{
    public function method1()
    {
        $gender = new Gender;
        $gender->setConnection('mysql2');
        return $gender->get();
    }
    public function method2()
    {
       return DB::connection('mysql2')->table("genders")->get();
    }
}

Use Database connection in a Model, as shown below.

<?php
namespace App\Models;
class Gender extends Model
{
    protected $connection = 'mysql2'; // This line will get records from mysql2 database data whenever we use this model
    protected $fillable = [
        ...
    ];
}

Use Database connection in Migrations, as shown below.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateGendersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::connection('mysql2')->create('genders', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('genders');
    }
}

Hope you learnt important concepts from this quick tutorial, do ask us in the comments section if you have any doubts.

Cheers!

Leave a Comment