How to Use Multiple Databases in Laravel ?

Laravel support multiple databases within a single application. Whether you need to connect to different databases for various services or manage data across multiple environments, Laravel makes it easy to configure and query multiple connections seamlessly.

Configuring Multiple Databases

To configure multiple databases in Laravel, you need to update the config/database.php file. Laravel uses the connections array to define different database connections.

Step 1: Define Multiple Connections

Edit the config/database.php file and add your additional database connections under the connections array:

'second_db' => [
    'driver' => 'mysql',
    'host' => env('SECOND_DB_HOST', 'your_remote_host'),
    'database' => env('SECOND_DB_DATABASE', 'your_database'),
    'username' => env('SECOND_DB_USERNAME', 'your_username'),
    'password' => env('SECOND_DB_PASSWORD', 'your_password'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

You can define as many connections as needed for your application.

Step 2: Set Up Environment Variables

To keep sensitive information secure, define the new database credentials in the .env file:

SECOND_DB_HOST=your_second_host
SECOND_DB_DATABASE=your_database
SECOND_DB_USERNAME=your_username
SECOND_DB_PASSWORD=your_password

Using Multiple Database Connections

Once the connections are configured, you can start using them in your queries.

1. Querying Different Databases

Use the connection method to specify which database connection to use:

$users = DB::connection('second_db')->table('users')->get();

2. Using Multiple Connections in Models

If you want a specific model to use a different database connection, set the $connection property in the model:

class RemoteUser extends Model
{
    protected $connection = 'second_db';
    protected $table = 'users';
}

Now, you can use this model to interact with the SECOND_db database:

$users = RemoteUser::all();

3. Running Migrations on a Specific Database

If you need to run migrations for a specific connection, use:

php artisan migrate --database=SECOND_db

This ensures migrations are applied to the designated database connection.

Handling Transactions Across Databases

Laravel does not support cross-database transactions natively. However, you can handle multiple transactions using DB::transaction() individually:

DB::connection('mysql')->transaction(function () {
    DB::connection('second_db')->transaction(function () {
        // Perform database operations
    });
});