I want to combine multiple databases in my system. Most of the time the database is MySQL; but it may differ in future i.e. Admin can generate such a reports which is use source of heterogeneous database system.

So my question is does Laravel provide any Facade to deal with such situations? Or any other framework have more suitable capabilities for problem is?

3 Answers 11

In Laravel 5.1, you specify the connection:

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

Default, Laravel uses the default connection. It is simple, isn't it?

Read more here: http://laravel.com/docs/5.1/database#accessing-connections

upvote
  flag
in Laravel 5.1, what about Eloquent? – simo
1 upvote
  flag
@simo, see the answer of Abdulla. – schellingerht
up vote 127 down vote accepted

Define Connections

app/config/database.php

return array(

    'default' => 'mysql',

    'connections' => array(

        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'host1',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'host2',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

Schema

Within the Schema Builder, you can use the Schema facade with any connection. To specify which connection to use, simply run the connection() method:

Schema::connection('mysql2')->create('some_table', function($table)
{
    $table->increments('id'):
});

Query

Similar to Schema Builder, you can define a connection on the Query Builder:

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

Eloquent

You can also define which connection to use in your Eloquent models as well!

One way is to set the $connection variable in your model:

<?php

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

You can also define the connection at runtime via the setConnection method

<?php

class SomeController extends BaseController {

    public function someMethod()
    {
        $someModel = new SomeModel;

        $someModel->setConnection('mysql2');

        $something = $someModel->find(1);

        return $something;
    }

}

Note Be careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats and depends on what database and/or database settings you have.


From Laravel Docs

Using Multiple Database Connections

When using multiple connections, you may access each connection via the connection method on the DB facade. The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file:

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

You may also access the raw, underlying PDO instance using the getPdo method on a connection instance:

$pdo = DB::connection()->getPdo();

Using .env 5.5 ( EDIT - 2017-10-22)

In .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=homestead2
DB_USERNAME_SECOND=homestead
DB_PASSWORD_SECOND=secret

In config/database.php

'mysql' => [

    'driver' => env('DB_CONNECTION'),
    'host' => env('DB_HOST'),
    'port' => env('DB_PORT'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
],

'mysql2' => [
    'driver' => env('DB_CONNECTION_SECOND'),
    'host' => env('DB_HOST_SECOND'),
    'port' => env('DB_PORT_SECOND'),
    'database' => env('DB_DATABASE_SECOND'),
    'username' => env('DB_USERNAME_SECOND'),
    'password' => env('DB_PASSWORD_SECOND'),
],

Note: In mysql2 if DB_username and DB_password is same, then you can use env('DB_USERNAME') which is metioned in .env first few lines.

Schema, Eloquent, and Query will be same as above


Useful Links

  1. Source
  2. Laravel 5 multiple database connection FROM laracasts.com
  3. Connect multiple databases in laravel FROM tutsnare.com
1 upvote
  flag
Thanks for answer and give brief details – Chintan7027
3 upvote
  flag
Good job abdullah – devpro
upvote
  flag
Hi, I tried this solution but even though there are no error messages the table is still created on the database specified in the connection "mysql" instead of "mysql2". What can I try next? :( – Pedro Araujo Jorge
upvote
  flag
You can use class SomeModel extends Model { and also make sure you have removed get values from env file as env('DB_DATABASE', 'name') when you creating new DB configuration array in database.php file as @sba has mentioned – Sadee
2 upvote
  flag
thanks for the answer – vision
upvote
  flag
Hey, I'm using Lumen and for the database config part, i have only ".env" file. Not database.php. So how can I fix this? – Chanaka De Silva
1 upvote
  flag
exactly what I was looking for... – Akshay Khale

Actually, DB::connection('name')->select(..) doesnt work for me, because 'name' has to be in double quotes: "name"

Still, the select query is executed on my default connection. Still trying to figure out, how to convince Laravel to work the way it is intended: change the connection.

Edit: i figured it out. After debugging Laravels DatabaseManager it turned out my database.php (config file) (inside $this->app) was wrong. In the section "connections" i had stuff like "database" with values of the one i copied it from. In clear terms, instead of

env('DB_DATABASE', 'name')

i needed to place something like

'myNewName'

since all connections where listed with the same values for database, username, password, etc. which of course makes little sense if i want to access at least another database name

Therefore, every time i wanted to select something from another database i always ended up in my default database

1 upvote
  flag
Thank you! You saved my sanity. I tried to figure out why all my queries referred to the default database. Then I read your post and realized, that env always returned the .env values and the second parameter was just a fallback that Laravel didn't use. – Moha

Not the answer you're looking for? Browse other questions tagged or ask your own question.