How to Use Multiple Databases in Laravel

Laravel is one of the top framework which allows us to write flexible code. More over it is a flexible framework which allows us to use different database connections. Moreover we can use different DB engines as well. These connections can be easily configured using .env and database.php configuration file. 

So lets add these new database details to the .env file. Here we are using DB_ORG prefix for new database variables.

DB_ORG_HOST="DB HOST"
DB_ORG_PORT="PORT"
DB_ORG_DATABASE="DB NAME"
DB_ORG_USERNAME="DB USER"
DB_ORG_PASSWORD="DB PASSWORD"

The name here is not important, we can use any name here. And we can use as many databases as well adding more configurations.

Adding new Database connection in database.php Config

Now let's move to update database.php file to add new connection to laravel database configuration. Let's have a look at for connections key in database.php file. Then add a new mysql db connection to this array.

'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'),
            ]) : [],
        ],
        'mysql_org' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_ORG_HOST', '127.0.0.1'),
            'port' => env('DB_ORG_PORT', '3306'),
            'database' => env('DB_ORG_DATABASE', 'forge'),
            'username' => env('DB_ORG_USERNAME', 'forge'),
            'password' => env('DB_ORG_PASSWORD', ''),
            'unix_socket' => env('DB_ORG_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'),
            ]) : [],
        ],

So the updated database file will looks like the above. Please make sure to use a unique name for the new connection. Here we are using mysql_org for the better understanding.

Now lets see how we can use this in an application.

DB connection in an Eloquent Model

It's easy to specify a database connection in laravel when using eloquent for database operations. It is easy as defining a connection variable in model class as shown below.

<?php

namespace App\Models\Ad;

class Ad extends Model
{
    /**
     * Database connection.
     *
     * @var string<string>
     */
    protected $connection= 'mysql_org';
    
}

After defining this connection all the operations using model will be performed in the newly connected database mysql_org.

Database Connection In Laravel Query Builder

Here the syntax will be bit different where we are going to specify the database connection directly in the query. Let's see an example using a database table using users.

$users = DB::connection('mysql_org')->select('id','email')->get();

So the we used a connection method to specify the connection name defined in the database.php config file.

Database Connection in Schema Operation

If you want to perform a schema operation on our new database connection then we can specify that connection method here as well.

Schema::connection('mysql_org')->create('users', function (Blueprint $table) {
    $table->id();
});

That is all from us.