Laravel Multiple Database Connection Example

Laravel multiple database connection; In this tutorial, we will learn how to connect single or multiple databases using a laravel 7/8/9 apps. Sometimes, we need to connect multiple databases to the Laravel application.

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

Laravel Multiple Database Connection Example

Use the following steps to connect multiple database in laravel 7/8/9 apps; is as follows:

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 set up 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.