How to connect to multiple databases in Laravel

Introduction

When working on complex applications in Laravel, you might encounter situations where you need to interact with multiple databases. Fortunately, Laravel provides an elegant solution to manage multiple database connections seamlessly. In this tutorial, we’ll explore how to set up and use multiple databases within a Laravel application. We will start with a basic setup and move on to more advanced concepts, providing code examples along the way.

Prerequisites

• Basic understanding of Laravel’s Eloquent and database migrations.
• Laravel application up and running.
• Access to at least two different databases.

Configuring Database Connections

To connect to multiple databases, we first need to configure the connections in the config/database.php file.

'connections' => [
    'mysql' => [
        // Primary database connection...
    ],
    'secondary' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST_SECOND', '127.0.0.1'),
        'port' => env('DB_PORT_SECOND', '3306'),
        'database' => env('DB_DATABASE_SECOND', 'forge'),
        'username' => env('DB_USERNAME_SECOND', 'forge'),
        'password' => env('DB_PASSWORD_SECOND', ''),
        'unix_socket' => env('DB_SOCKET_SECOND', ''),
        // Other configurations...
    ],
    // Other connections...
],

After configuring your connections, you can set the default connection or dynamically switch between them within your code.

Using Multiple Database Connections in Models

You can specify which connection a particular Eloquent model should use by setting the
connection property:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SecondaryModel extends Model
{
    protected $connection = 'secondary';
    // Model details...
}

Migrating Tables to Different Databases

When running migrations, you can specify the connection by using the --database option:

php artisan migrate --database=secondary

Querying Multiple Databases

You can run database queries on a specific connection using the on method:

$users = DB::connection('secondary')->select(...);

Setting Up Model Relationships Across Databases

To define relationships between models in different databases, ensure the model definitions point to the accurate connections and reference the appropriate table names.

Advanced Configuration for Cross-Database Joins

In some databases, such as SQL Server, you can join tables across databases if the user has the necessary permissions. Here’s an example:

$result = DB::table('database1.users')
            ->join('database2.posts', 'database1.users.id', '=', 'database2.posts.user_id')
            ->select('database1.users.name', 'database2.posts.title')
            ->get();

Side Notes

Reusing Database Connections

To improve the performance of an application dealing with multiple databases, you may reuse existing connections instead of creating new ones for common database hosts.

Handling Transactions Across Multiple Databases

Transactions are a bit trickier with multiple databases, especially concerning consistency and rollbacks. Each database connection will need its own transaction management — you may need to implement a strategy for distributed transactions.

Conclusion

This concludes our journey through configuring and using multiple databases in a Laravel application. We’ve seen how to set up our connections, use them within models, manage migrations, and even establish cross-database relationships. Always remember to test thoroughly when working with multiple databases to ensure the integrity and performance of your application.