Configuring and Utilizing Multiple Databases in Laravel

In Laravel, managing multiple databases is a common requirement for complex applications where data is distributed across different databases. Whether you need to interact with multiple databases for sharding, legacy systems, or simply for better organization, Laravel provides a convenient way to handle multiple database connections out of the box. n this guide, we’ll explore how to configure and utilize multiple database connections in Laravel, along with practical examples to demonstrate various scenarios.

Before diving into the usage, let’s set up multiple database connections in Laravel. Laravel’s database configuration is stored in the config/database.php file. Inside this file, you’ll find an array of database connections. To add a new connection, simply define a new array with the connection details.

'connections' => [
    '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', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'second_db' => [
        'driver' => 'mysql',
        'host' => env('SECOND_DB_HOST', '127.0.0.1'),
        'port' => env('SECOND_DB_PORT', '3306'),
        'database' => env('SECOND_DB_DATABASE', 'forge'),
        'username' => env('SECOND_DB_USERNAME', 'forge'),
        'password' => env('SECOND_DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
],

We’ve added a new database connection named second_db here. Ensure you’ve updated the environment variables in your .env file accordingly.

Once you’ve set up the database connections, you can utilize them throughout your application. Laravel’s Eloquent ORM provides a convenient way to interact with databases.

1. Model Setup

When defining Eloquent models that use a different database connection, specify the $connection property.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SecondModel extends Model
{
    protected $connection = 'second_db';
}

2. Querying Data

When querying data from a specific database connection, you can use the on method on the Eloquent query builder.

$users = App\Models\SecondModel::on('second_db')->get();

3. Raw SQL Queries

You can execute raw SQL queries on a specific connection using the DB::connection() method.

$users = DB::connection('second_db')->select('select * from table_name');

Conclusion

Managing multiple databases in Laravel provides flexibility and scalability to your applications. By following the steps outlined in this guide, you can seamlessly configure and utilize multiple database connections in your Laravel projects. Whether it’s for distributed data storage, legacy integrations, or other requirements, Laravel’s robust database management capabilities make handling multiple databases a breeze.