Specifying DB Connections on Laravel

In the middle of a project, I was struck during a discussion with my client. Except that I will be using MSSQL instead of the usual MySQL for the databases, I was uninformed to use three databases for the three separate sql dump he sent to me.

Oh and by the way, my client’s database uses an account’s username as a primary key. How cool is that?

Enough of my eerie story.

There are different ways to make the Schema Builder, Query Builder (Fluent), Eloquent use a different connection compared to the default set in the database configurations (which is in root/app/config/database.php).

Here’s an overview

1. Configurations

2. Eloquent

• Setting the default connection to be used by the model
• Using the different methods provided by Eloquent (‘on’, ‘resolveConnection’, ‘setConnection’)

3. Schema Builder

4. Query Builder

Let’s start by creating our own connections.

This is our default database.php (found in app/config)

We can simply add our own connection by simply adding new lines of code inside the connections array. I prefer to put custom connections below the default connections provided by Laravel.

Here is an example

<?php
return array(
    'connections' => array(

        // ... Laravel's default connection

        // My custom connections
        'sqlite' => array(
            'driver'   => 'sqlite',
            'database' => __DIR__.'/../database/production.sqlite',
            'prefix'   => '',
        ),

        'my-db' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'my-db',
            'username'  => 'root',
            'password'  => 'root',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        'another-db' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'another-db',
            'username'  => 'root',
            'password'  => 'root',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

    ),
)

Let’s start with the Schema Builder.

To migrate a table to a particular database different from the default, you simply use the connection()method.

$connection = ‘sample’;

Schema::connection($connection)->create(function(Blueprint $table) {

//

});

Easy? Yeah, it hell was.

This time, why don’t we try same thing, but this time, with our Eloquent models?

It’s really easy, too.

We can change the default connection used by the entire Model itself. This means that all queries using the model will start using the connection we set. We may be able to achieve this by changing the connection property which is set to the default connection by default in Eloquent.

Simply add a property to overwrite the default.

class Pogi extends Eloquent {

protected $connection = ‘my-db’;

}

This makes the model Pogi use the existing connection my-db. If this connection does not exist, an exception will be thrown. No worries in breaking your application!

Another way would be by using the ‘on’ method which is, too, available by default in Eloquent. Let’s use a model named User in our example, and the connection named my-db.

class UserCtrl extends BaseController {

    public function show()
    {
        $user = User::on(‘my-db’)->find(1);
    }
}

The connection named my-db will be used only upon this execution. This means, if we try do:

$user = User::on(‘another-db’)->find(1);

$users = User::all();

$user will start using the connection named ‘another-db’, while $users will use the default connection set in the configurations.

With the Query Builder, it’s a piece of cake, too.

By simply using the same method: connection.

// connection name

$connection = ‘sample’;

$db = DB::connection($connection)

// do something

Laravel makes this very clean and easy.

Easy and clean, wasn’t it?

Another Issue:

And now that you mention it uses Builder's method, find() seems useless. Though when I checked what method it does use, an instance seems to use the model's static find(). I tested it by simply dying (die()) inside Model's find() method

// does not work, uses the Model's static find
try {
    MyModel::on('another_connection');
    $myModel = MyModel::find($id);
    echo "test 1 good<br>\n";
} catch(\Exception $e) {
    echo "test 1 fail<br>\n";
}

// works but uses Builder's find()
try {
    $myModel = MyModel::on('another_connection')->find($id);
    echo "test 2 good<br>\n";
} catch(\Exception $e) {
    echo "test 2 fail<br>\n";
}

// does not work, uses the Model's static find
try {
    $myModel = MyModel::find($id);
    echo "test 3 good<br>\n";
} catch(\Exception $e) {
    echo "test 3 fail<br>\n";
}

// does not work, uses Model's static find
try {
    $myModel = new MyModel;
    $myModel->setConnection('another_connection');
    $myModel = $myModel->find($id);
    echo "test 4 good<br>\n";
} catch(\Exception $e) {
    echo "test 4 fail<br>\n";
}

// does not work, uses Model's static find
try {
    $myModel = new MyModel;
    $myModel = $myModel->setConnection('another_connection')->find($id);
    echo "test 5 good<br>\n";
} catch(\Exception $e) {
    echo "test 5 fail<br>\n";
}

// works but uses Builder's find()
try {
    $myModel = new MyModel;
    $myModel = $myModel->setConnection('another_connection')->newQuery()->find($id);
    echo "test 6 good";
} catch(\Exception $e) {
    echo "test 6 fail";
}
As how I understood the method, setting the instance's connection to another_connection then using the instance's find() should use the instance's connection.

Static methods called in the model should use Builder's methods instead
see: https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Eloquent/Model.php#L3125

and instance methods should call the instance's methods
see: https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Eloquent/Model.php#L3111

or just proxy everything to Builder instead of having a find() method so the responsibility of querying stuff all falls on Builder