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?