Eloquent: How to specify a different database connection

Introduction

Laravel’s Eloquent ORM provides an active record implementation that allows you to work with your database objects in an elegant and convenient manner. By default, Eloquent uses the default database connection specified in your config/database.php configuration file. However, in some scenarios, such as working with multiple databases or a legacy database, you might need to specify a different connection for certain Eloquent models. This tutorial explores how to achieve this with Eloquent.

Understanding Configuration

Before customizing the database connection for an Eloquent model, ensure that the different connections are properly defined in the config/database.php file. Through Laravel’s configuration, you can set up multiple connections under the connections key. Here is an example configuration with two MySQL connections:

'connections' => [
    'mysql' => [
        /* Default database connection settings */
    ],

    'custom' => [
        'driver' => 'mysql',
        'host' => 'custom_host',
        'database' => 'custom_database',
        'username' => 'custom_username',
        'password' => 'custom_password',
        /* Additional custom settings */
    ],
],

Explicity Setting a Connection on a Model

Once you have defined additional connections, the next step is to tell your Eloquent model to use one of them. This is done by setting the $connection property on your model. Here’s a basic example of how to specify a different connection on a model:

use Illuminate\Database\Eloquent\Model;

class CustomPost extends Model
{
    protected $connection = 'custom';
}

Now, any database query using the CustomPost model will be executed on the database defined by the ‘custom’ connection in your configuration.

Switching Connections at Runtime

You also have the flexibility to switch database connections at runtime without touching the model’s code. You can accomplish this by using the setConnection method on an instance of the model:

$post = new CustomPost();
$post->setConnection('another_connection');

The above approach changes the connection for only that particular instance of the model.

Using Multiple Database Connections in the Same Query

Now let’s explore an advanced use case where you may want to perform a query that involves joining tables from distinct databases. Eloquent doesn’t natively support cross-database joins; but, you can achieve this by using raw queries. The following demonstrates connecting to another database for a raw join operation:

$results = DB::connection('custom')->select('SELECT * FROM custom_database.custom_table AS ct JOIN another_database.another_table AS at ON ct.id = at.custom_table_id');

Note that while this works, it bypasses Eloquent’s ORM features and goes straight to using the query builder.

Connection Resolvers

For even more advanced use cases where you have complex logic to determine which database you should connect to, you can define a custom connection resolver. A connection resolver is a Closure or class method that determines which connection to use when an Eloquent model is instantiated:

use Illuminate\Database\ConnectionResolverInterface as Resolver;

class CustomConnectionResolver implements Resolver
{
   public function connection($name = null)
   {
       // Your logic to determine the connection
   }
}

// In a service provider:
$this->app['db']->setResolver(new CustomConnectionResolver());

Your connection method must return an instance of \Illuminate\Database\ConnectionInterface.

Handling Transactions Across Multiple Databases

If you are dealing with multiple database connections, you might need to manage transactions that affect all of them. In this case, you need to manually manage transactions:

DB::connection('first_connection')->beginTransaction();
DB::connection('second_connection')->beginTransaction();

// Your transactional database operations...

DB::connection('first_connection')->commit();
DB::connection('second_connection')->commit();

Don’t forget that you also need to handle rollbacks appropriately in case one of the queries fails.

Using Connection for Read/Write Operations

In some cases, you might have dedicated databases for read and write operations. Laravel Eloquent allows you to define read and write connections for your model, like so:

'connections' => [
    'mysql' => [
        /* Default settings */
    ],

    'mysql_read' => [
        'read' => [
            'host' => 'readonly-host',
        ],
        'write' => [
            'host' => 'write-host',
        ],
        /* Inherit other settings from the 'mysql' connection */
    ],
],

class CustomReadWriteModel extends Model
{
    protected $connection = 'mysql_read';
}

With this setup, Eloquent will handle the decision whether to use the read or write connection based on the type of database operation you are performing.

Conclusion

This tutorial walked you through the various methods of specifying and switching database connections for Eloquent models within a Laravel application. Knowing how to do this can be particularly useful when working with complex applications that require interaction with multiple databases or when a separate read/write setup is necessary.