Reading Time: 7 minutes

5,277 Views

In large scope projects, we use multiple data sources to control the flow of data and to manage it. Here, inside this article we will see the concept of Laravel 9 How To Connect Multiple Database Connections.

There are very few steps which is very very easy to do and connect with multi databases and perform operations. This is going to be very interesting article to learn and see the things. After this article two things will be totally clear out.

Multiple databases
Multiple connection groups

Article contains classified information about the concept of multiple databases. We will cover the entire concept so that you can learn about the complete steps in a very precised way.

Learn More –

Let’s get started.

Laravel Installation

Open terminal and run this command to create a laravel project.

composer create-project laravel/laravel myblog

It will create a project folder with name myblog inside your local system.

To start the development server of laravel –

php artisan serve

URL: http://127.0.0.1:8000

Assuming laravel already installed inside your system.

Update .env File

The very first step to create secondary databases. To create secondary database environment variables we will use .env.

Open .env file and add details of other database as well.

//...

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=root
  
//...
  
DB_CONNECTION_2=mysql
DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=database_2
DB_USERNAME_2=root
DB_PASSWORD_2=root  
  
//...

Here, database_1 and database_2 are two databases we have connected inside .env file.

Update Database File

Open database.php file from /config folder. This file contains the information of connection groups. Now, we need to use .env variables (secondary database) into it.

<?php

use Illuminate\Support\Str;

return [

    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [

        //...
        
        // Primary database
        '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'),
            ]) : [],
        ],

        // Secondary database
        'mysql_2' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST_2', '127.0.0.1'),
            'port' => env('DB_PORT_2', '3306'),
            'database' => env('DB_DATABASE_2', 'forge'),
            'username' => env('DB_USERNAME_2', 'forge'),
            'password' => env('DB_PASSWORD_2', ''),
            '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'),
            ]) : [],
        ],
      
        //...

    ],

    //...
];

Database Connections in Migration

When we create a migration file from artisan command, it stores inside /database/migrations folder and connected with primary database..

To connect with multiple database to migrations, look here these examples.

Primary Database(Default)

<?php

//...

public function up()

{
    Schema::create('products', function (Blueprint $table) {

        $table->increments('id');
        $table->string('title');
        $table->string('description')->nullable();
        $table->timestamps();

    });

}

//...

Secondary Database

To connect migration with second database, use only Schema::connection(‘mysql_2’). Use connection method and connection name in both up() and down() methods.

<?php

//...

public function up()

{
    Schema::connection('mysql_2')->create('products', function (Blueprint $table) {

        $table->increments('id');
        $table->string('title');
        $table->string('description')->nullable();
        $table->timestamps();

    });

}

//...

This is all about connecting migrations with multiple databases groups.

Database Connections in Model

When we create models using artisan command, it will be stored inside /app/Models and by default it will be connected to primary database.

To connect with multiple databases, look these examples.

Primary Database(Default)

<?php
  
namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model {
  
  //...
  
}

Secondary Database

To use secondary database in model, use $connection protected variable in Model class.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model{

    protected $connection = 'mysql_2';

}

This is all about connecting models with multiple databases groups.

Database Connections in Controller

If suppose, sometimes we want to connect database at runtime then we can connect direct from controller.

Primary Database(Default)

<?php

//...
class ProductController extends BaseController
{
    public function getProducts()
    {
        $productModel = new Product;

        $find = $productModel->find(1);

        return $find;
    }
}

Secondary Database

To connect with secondary database we use setConnection() method by model object and set connect group into it.

<?php

//...

class ProductController extends BaseController
{
    public function getProducts()
    {
        $productModel = new Product;

        $productModel->setConnection('mysql_2');

        $find = $productModel->find(1);

        return $find;
    }
}

This is all about connecting controllers with multiple databases groups.

Database Connections in Query Builder

We will see connection of query builder with primary and secondary database.

Primary Database(Default)

//...

$products = DB::table("products")->get();

print_r($products);

//...

Secondary Database

//...

$products = DB::connection('mysql_2')->table("products")->get();

print_r($products);

//...

We hope this article helped you to learn about Laravel 9 How To Connect Multiple Database Connections Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.