Multiple databases connection in Laravel

Hello guys

You already know how to connect MySQL database with Laravel application. But sometimes, you might need to use multiple databases with single Laravel application. Or you might needed to synchronize data from another server database.

In this article, we will learn about how to connect Laravel application with second database. We will also learn how to connect specific Model and Migration with second connection instead of default database.

If you see your .env file, you already have default database connection details.

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

Now copy these lines and create new credential details for second database. If the second database is in remove server, then use remote server IP.

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=110.72.248.93
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=awesome_db2
DB_USERNAME_SECOND=someuser
DB_PASSWORD_SECOND=somepass

Sameway, also copy and add second database array to connections array in config/database.php file.

'connections' => [

    'mysql' => [
        'driver'    => env('DB_CONNECTION'),
        'host'      => env('DB_HOST'),
        'port'      => env('DB_PORT'),
        'database'  => env('DB_DATABASE'),
        'username'  => env('DB_USERNAME'),
        'password'  => env('DB_PASSWORD'),
    ],

    'mysql_second' => [
        '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'),
    ],
],

Now you have created and connected second database with Laravel application. When you want to query in second database from controller class, add connection() method to query.

$transactions = DB::connection('mysql_second')
    ->table('transactions')
    ->where('status', 'success')
    ->get();

If you want to use Eloquent query on second database, use on() method before query.

$transactions = Transaction::on('mysql_second')
    ->where('status', 'success')
    ->get();

And if specific table is mostly uses second database, you can bind model with specific connection. This way, you don't need to pass on() method for every query.

<?php

class Transaction extends Model
{
    protected $connection = 'mysql_second';
    // ...
}

This way you can use multiple databases to single Laravel application. I hope this will help you on your work.