Laravel 7/6 Multiple Database Connections With Single Project
Laravel 16-Oct-2020

Laravel 7/6 Multiple Database Connections With Single Project

Laravel provides multiple database connections access functionality with it’s very easy and simple.

In this tutorial, we would love to share with you how to connect single or multiple databases using a laravel application. Sometimes, we need to connect multiple databases to the Laravel application.

Let’s see, how to implement multiple database connections in the laravel app.

Table of Contents

  • Prerequisites
  • Without .env Database Connection
  • With .env Database Connection
  • Custom Connection In Migration
  • Custom Connection In Model
  • Custom Connection In Controller
  • Custom Connection with Query Builder
  • Conclusion

Prerequisites

We need to download the fresh Laravel setup where we will implement our example.

Without .env Database Connection

In Laravel, a database configuration file is located on “config / database.php”. Here we can set up more than one database connection. If your app uses data from two different MySql databases, you can easily define them.

<?php
return => [
    'connections' => [
        // Default database connection
        'mysql' => [
            'driver' => 'mysql',
            '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' => '',
            'strict' => true,
            'engine' => null,
        ],
        // Custom new database connection
        'mysql2' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'port' => '3306',
            'database' => 'lara_multiple',
            'username' => 'root',
            'password' => '',
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
    ]
]

With .env Database Connection

Now first of all setup the second database credential in your project .env file. Update your .env file using the below code.

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

Next, you can set the database credential in your project .env file and then update your “config/database.php” with the following code given below.

<?php
return => [
    'connections' => [
        // Custom database connection
        'mysql2' => [
            'driver'    => env('DB_CONNECTION_SECOND'),
            'host'      => env('DB_HOST_SECOND'),
            'port'      => env('DB_PORT_SECOND'),
            'database'  => env('DB_DATABASE_SECOND'),
            'username'  => env('DB_USERNAME_SECOND'),
            'password'  => env('DB_PASSWORD_SECOND'),
            ...
        ],
    ]
]

Custom Connection in Migration

You can use the following example for the custom connection in your migration.

<?php
...
public function up()
{
    Schema::connection('mysql2')->create('multipost', function (Blueprint $table) {
        $table->increments('id');
        $table->string('post_title');
        $table->string('post_content');
        $table->timestamps();
    });
}
...

Open your terminal and go to your project root directory, After that run the below command for executing our migrations for the specific database connection.

php artisan migrate --database=mysql2
If you found any error when we execute the migrate command. So that time you can clear your config cache, we will provide the command below:

This command fixed your issue;

php artisan config:cache
Custom Connection In Model
You can set the “$connection” variable in your model. So use the below code for that:

<?php
class SomeModel extends Eloquent {
    protected $connection = 'mysql2';
}
Custom Connection In Controller
You can also define the connection in your controller using the “setConnection” method.

<?php
class xyzController extends BaseController
{
    public function someMethod()
    {
        $someModel = new SomeModel;
        $someModel->setConnection('mysql2');
        $something = $someModel->find(1);
        return $something;
    }
}
Custom Connection with Query Builder
You can also define a connection on the query builder.

DB::connection('mysql2')->select(...)->(...);
Conclusion
In this article, we have successfully created multiple database connections with the .env file and without using the .env file.