Working with Multiple Database Connection in Laravel 8

Share this Article
Reading Time: 6 minutes
5,886 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 working with multiple database connection in Laravel 8.

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

Let’s get started.


Installation of Laravel Application

Laravel Installation can be done in two ways.

  • Laravel Installer
  • By using composer

Laravel Installer

To install Laravel via Laravel installer, we need to install it’s installer first. We need to make use of composer for that.

$ composer global require laravel/installer

This command will install laravel installer at system. This installation is at global scope, so you type command from any directory at terminal. To verify type the given command –

$ laravel

This command will open a command palette of Laravel Installer.

To create ad install laravel project in system,

$ laravel new blog

With the name of blog a laravel project will be created at your specified path.

By using composer

Alternatively, we can also install Laravel by Composer command create-project.

If your system doesn’t has composer Installed, Learn Composer Installation Steps.

Here is the complete command to create a laravel project-

$ composer create-project --prefer-dist laravel/laravel blog

After following these steps we can install a Laravel application into system.

To start the development server of Laravel –

$ php artisan serve

This command outputs –

Starting Laravel development server: http://127.0.0.1:8000

Assuming laravel already installed at system.


Step #1 Update Env File

Create secondary database environment variables. Open .env file and add details of other database as well.

# Primary Database

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

# Secondary Database

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

Step #2 Update Database File

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

    ],

    //...
];

Successfully, we have configured laravel 8 application with multiple database connections. Let’s see it’s usage.


Database Connections in Migration

When we create a migration file from artisan command, it stores at /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();

    });

}

//...

Database Connections in Models

When we create models using artisan command, it will be stored at /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';

}

Database Connections at 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;
    }
}

Database Connection with 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 Working with Multiple Database Connection in Laravel 8 in a very detailed way.

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.

Find More on Laravel 8 Articles here