Eloquent ORM: How to Use Multiple Database Connections

Introduction

When developing applications with Laravel, using its native ORM, Eloquent, is an elegant approach to interacting with databases. However, as applications grow in complexity, the need to use multiple databases becomes ever more commonplace, whether for separating data concerns, enhancing performance, or working with legacy systems. In this tutorial, we’ll learn how to use Eloquent ORM to handle multiple database connections smoothly.

Understanding Eloquent Configuration

Before diving into multiple connections, it’s important to understand how Eloquent is configured within Laravel. The typical configuration file for database settings is located at config/database.php. In this file, you will find a connections array that refers to the connections which our application can use. Each key in this array is a connection name, and the values are details such as driver, host, database name, username, and password.

'connections' => [
  'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    '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,
  ],
  // Additional connections...
],

To establish another database connection, you just need to add a new array with your desired configuration underneath the mysql connection.

Creating Multiple Connections

Laravel makes managing multiple connections a breeze. Here’s how you configure additional connections in config/database.php:

'connections' => [
  'mysql' => [
    // Primary database connection details...
  ],
  'secondary' => [
    'driver' => 'mysql',
    'host' => env('SECONDARY_DB_HOST', '127.0.0.2'),
    'database' => env('SECONDARY_DB_DATABASE', 'secondary'),
    'username' => env('SECONDARY_DB_USERNAME', 'root'),
    'password' => env('SECONDARY_DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
  ],
  // Other connections...
],

Once we have defined our additional connections, we can explicitly tell Eloquent models to use them.

Specifying Connections in Models

You can assign a specific connection to a model by defining the $connection property. For instance:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SecondaryModel extends Model
{
  protected $connection = 'secondary';

  // Model's methods and attributes...
}

By doing so, any Eloquent query on SecondaryModel will use the secondary connection instead of the default one.

On-the-Fly Connection Switching

At times, you may want to switch connections dynamically, perhaps depending on the user’s data or requests parameters. This is possible by using the setConnection() method. For example:

$user = new User;
$user->setConnection('secondary');
$data = $user->find(1);

Your model will temporarily switch to the ‘secondary’ connection just for this transaction. The switch does not affect global model behavior, keeping the operations tight and context-specific.

Raw Queries and Multiple Connections

Raw database queries can also be executed on a different connection. You may use the DB facade’s connection() method:

use Illuminate\Support\Facades\DB;

$results = DB::connection('secondary')->select('SELECT * FROM table_name');

This effectively executes the query on your secondary database. When dealing with raw queries, always sanitize inputs to prevent SQL injection attacks.

Using Multiple Databases with Migrations

Laravel’s migration system supports multiple databases as well. You can specify the connection when running migrations by using the --database flag. Additionally, you can define a connection directly in your migration file like so:

public function up()
{
  Schema::connection('secondary')->create('some_table', function ($table) {
    $table->bigIncrements('id');
    // Other columns...
  });
}

Similarly, to revert migrations from a specific connection, you can also utilize the connection method Schema::connection('secondary') in the down method.

Tips on Handling Multiple Databases

• Keep migration files for each connection in separate directories to maintain clarity.
• Regularly backup data during the development process, due to working with multiple databases.
• Be extremely careful with database transactions spanning multiple databases, as it could lead to data integrity issues.

Conclusion

Working with multiple databases in Laravel with Eloquent is both feasible and uncomplicated. Whether statically specifying database connections in models or dynamically changing them on the fly, the process remains developer-friendly and straightforward, maintaining Laravel’s motto of writing expressive, elegant syntax.