Viewed   356 times

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?



Using .env >= 5.0 (tested on 5.5)

In .env



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.

Without .env <5.0

Define Connections


return array(

    'default' => 'mysql',

    'connections' => array(

        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '',
            'database'  => 'database1',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',

        # Secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => '',
            'database'  => 'database2',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',


To specify which connection to use, simply run the connection() method

Schema::connection('mysql2')->create('some_table', function($table)

Query Builder

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


Set the $connection variable in your model

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';


You can also define the connection at runtime via the setConnection method or the on static method:

class SomeController extends BaseController {

    public function someMethod()
        $someModel = new SomeModel;

        $someModel->setConnection('mysql2'); // non-static method

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

        $something = SomeModel::on('mysql2')->find(1); // static method

        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();

Useful Links

  1. Laravel 5 multiple database connection FROM
  2. Connect multiple databases in laravel FROM
  3. Multiple DB Connections in Laravel FROM
Saturday, November 5, 2022
Using Multiple Database Connections If your application defines multiple connections in your config/database.php configuration file, you may access each connection via the connection method provided by the DB facade. The connection name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file or configured at runtime using the config helper: use IlluminateSupportFacadesDB; $users = DB::connection('sqlite')->select(...); this sql lite is the name of db connection .
Saturday, December 17, 2022

MySQL has a different timeout than PHP. You could increase it in php.ini on the line mysql.connect_timeout = 14400. Also increase the default_socket_timeout = 14400

Note that if your PHP setting allow you to do an ini_set, you can also do as follows:

ini_set('mysql.connect_timeout', 14400);
ini_set('default_socket_timeout', 14400);
Thursday, December 22, 2022

If both databases they are on the same server, and the tables have the same schema, then you should use this query instead:

mysql_query("INSERT INTO database2.backup_emp SELECT * FROM database1.emp");

This way you don't need to worry about multiple database connections.

Wednesday, August 31, 2022

This gentleman (Olivier) had the same problem! (A year ago) He wrote a small adaptation for the Controllers! It's pretty small and it turns out, it works in 1.3 and 2.x.

Anyhow, this is my final solution, that I put in the app/Model/AppModel.php:

class AppModel extends Model
   * Connects to specified database
   * @param String name of different database to connect with.
   * @param String name of existing datasource
   * @return boolean true on success, false on failure
   * @access public
    public function setDatabase($database, $datasource = 'default')
      $nds = $datasource . '_' . $database;      
      $db  = &ConnectionManager::getDataSource($datasource);

        'name'       => $nds,
        'database'   => $database,
        'persistent' => false

      if ( $ds = ConnectionManager::create($nds, $db->config) ) {
        $this->useDbConfig  = $nds;
        $this->cacheQueries = false;
        return true;

      return false;

And here is how I used it in my app/Controller/CarsController.php:

class CarsController extends AppController
  public function index()

    $cars = $this->Car->find('all');

    $this->set('cars', $cars);


I'm betting, I'm not the first or last one with this problem. So I really hope this information will find people & the CakePHP community.

Monday, November 21, 2022

The details are implementation dependent but generally speaking, results are buffered. Executing a query against a database will return some result set. If it's sufficiently small all the results may be returned with the initial call or some might be and more results are returned as you iterate over the result object.

Think of the sequence this way:

  1. You open a connection to the database;
  2. There is possibly a second call to select a database or it might be done as part of (1);
  3. That authentication and connection step is (at least) one round trip to the server (ignoring persistent connections);
  4. You execute a query on the client;
  5. That query is sent to the server;
  6. The server has to determine how to execute the query;
  7. If the server has previously executed the query the execution plan may still be in the query cache. If not a new plan must be created;
  8. The server executes the query as given and returns a result to the client;
  9. That result will contain some buffer of rows that is implementation dependent. It might be 100 rows or more or less. All columns are returned for each row;
  10. As you fetch more rows eventually the client will ask the server for more rows. This may be when the client runs out or it may be done preemptively. Again this is implementation dependent.

The idea of all this is to minimize roundtrips to the server without sending back too much unnecessary data, which is why if you ask for a million rows you won't get them all back at once.

LIMIT clauses--or any clause in fact--will modify the result set.

Lastly, (7) is important because SELECT * FROM table WHERE a = 'foo' and SELECT * FROM table WHERE a = 'bar' are two different queries as far as the database optimizer is concerned so an execution plan must be determined for each separately. But a parameterized query (SELECT * FROM table WHERE a = :param) with different parameters is one query and only needs to be planned once (at least until it falls out of the query cache).

Thursday, October 20, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :