How to use multiple databases in Laravel

Question

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?

Elizabetelizabeth

Answer 1

From Laravel Docs: You may access each connection via the connection method on the DB facade when using multiple connections. 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(...);

Define Connections

Using .env >= 5.0 (or higher)

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mysql_database
DB_USERNAME=root
DB_PASSWORD=secret

DB_CONNECTION_PGSQL=pgsql
DB_HOST_PGSQL=127.0.0.1
DB_PORT_PGSQL=5432
DB_DATABASE_PGSQL=pgsql_database
DB_USERNAME_PGSQL=root
DB_PASSWORD_PGSQL=secret

Using 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'),
],

'pgsql' => [
    'driver'    => env('DB_CONNECTION_PGSQL'),
    'host'      => env('DB_HOST_PGSQL'),
    'port'      => env('DB_PORT_PGSQL'),
    'database'  => env('DB_DATABASE_PGSQL'),
    'username'  => env('DB_USERNAME_PGSQL'),
    'password'  => env('DB_PASSWORD_PGSQL'),
],

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

Without .env <= 4.0 (or lower)

app/config/database.php

return array(
    'default' => 'mysql',
    'connections' => array(
        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'mysql_database',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Secondary database connection
       'pgsql' => [
            'driver' => 'pgsql',
            'host' => 'localhost',
            'port' => '5432',
            'database' => 'pgsql_database',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
        ]
    ),
);

Schema / Migration

Run the connection() method to specify which connection to use.

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

Or, at the top, define a connection.

protected $connection = 'pgsql';

Query Builder

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

Model

(In Laravel >= 5.0 (or higher))

Set the $connection variable in your model

class ModelName extends Model { // extend changed

    protected $connection = 'pgsql';

}

Eloquent

(In Laravel <= 4.0 (or lower))

Set the $connection variable in your model

class SomeModel extends Eloquent {
    protected $connection = 'pgsql';
}

Transaction Mode

DB::transaction(function () {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
});

or

DB::connection('mysql')->beginTransaction();
try {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->beginTransaction();
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
    DB::connection('pgsql')->commit();
    DB::connection('mysql')->commit();
} catch (\Exception $e) {
    DB::connection('mysql')->rollBack();
    DB::connection('pgsql')->rollBack();
    throw $e;
}

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('pgsql'); // non-static method
        $something = $someModel->find(1);
        $something = SomeModel::on('pgsql')->find(1); // static method
        return $something;
    }
}

Note: Be careful about building relationships with tables across databases! It is possible to do, but it can come with caveats depending on your database and settings.

---

Tested versions (Updated)

Version	Tested (Yes/No)
4.2	No
5	Yes (5.5)
6	No
7	No
8	Yes (8.4)
9	Yes (9.2)

Useful Links

1. Laravel 5 multiple database connections FROM laracasts.com
2. Connect multiple databases in Laravel FROM tutsnare.com
3. Multiple DB Connections in Laravel FROM fideloper.com

Alleviation

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? :( – Tenantry

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 – Single

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? – Cauline

@ChanakaDeSilva You just create a config folder and a database.php file in it for Lumen. Apparently Lumen is constantly checking to see if that file exists, and will use it if it does. – Bilabiate

@AbdullaNilam some1 came to me to make multidb app first thought was i dont know if its possible now i know its simple ;p – Drawshave

what if we want a primary db and then dynamic number of dbs, for e.g. separate db for each fiscal year of accounting – Erythrocytometer

Wow .super .Thank you Sir. In my case "$users = DB::connection('mysql2')->select(...); " this is working fine Sir – Sincerity

This answer is like an encyclopedia for multiple connection topic. – Schlessinger

Is there a way to relate the to databases via foreign keys ? Right now, if i define the users in another database, the migrations will not run , as it can't find the table users and hence no relation can be defined . – Renege

Answer 2

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

Deflexed

in Laravel 5.1, what about Eloquent? – Earflap

@simo, see the answer of Abdulla. – Deflexed

Answer 3

Laravel has inbuilt support for multiple database systems, you need to provide connection details in config/database.php file

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
'mysqlOne' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_ONE', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE_ONE', 'forge'),
            'username' => env('DB_USERNAME_ONE', 'forge'),
            'password' => env('DB_PASSWORD_ONE', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
];

Once you have this you can create two base model class for each connection and define the connection name in those models

//BaseModel.php
protected $connection = 'mysql';

//BaseModelOne.php
protected $connection = 'mysqlOne';

You can extend these models to create more models for tables in each DB.

Halfbound

Answer 4

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.

Eurus

I had a similar problem, I changed the value but not the key. It worked like this: 'database' => env('DB_NEW_DATABASE', 'myNewDatabase'). Well noted! – Overtop

Answer 5

Laravel by default provides support for multiple database connections and you can dynamically switch between them. Here's how you can handle multiple database connections.

Define Database Connections in config/database.php

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', 5432),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => env('DB_CHARSET', 'utf8'),
        'prefix' => env('DB_PREFIX', ''),
        'schema' => env('DB_SCHEMA', 'public'),
        'sslmode' => env('DB_SSL_MODE', 'prefer'),
    ],
],

NB: you can have as many connections as needed, irrespective of the driver, ie, multiple MySQL connections, etc.

Switching Between Connections In your Eloquent models or queries, you can specify the connection to use

// Use the default connection (mysql)
$users = DB::table('users')->get();

// Use a specific connection
$users = DB::connection('pgsql')->table('users')->get();

Dynamic Connection Switching

$connectionName = 'pgsql';
$users = DB::connection($connectionName)->table('users')->get();

Raw Queries You can also execute raw queries using specific connection

$users = DB::connection('pgsql')->select('select * from users');

Cross-Database Joins I've personally tested this in one of my projects, doing cross-database join. You can specify the connection for each table in the join.

$users = DB::table('mysql_users')
            ->join('pgsql.other_table', 'mysql_users.id', '=', 'other_table.user_id')
            ->select('mysql_users.*', 'other_table.column')
            ->get();

Always adjust your migrations, models, and queries when working with multiple databases.

Useful links

1. Using Multiple Database Connections Laravel

2. Multiple DB Connections in Laravel

Vilmavim

Answer 6

Using Laravel 9, and connecting to three different databases, the default one MySQL, and two other Postgres databases, I found that defining model relations works properly across models from different databases if I simply set the $connection value explicitly on all models, especially the ones that use the default database connection. This means that if you change your default connection you will have to update those models, but this happens rarely, if at all.

Eleazar

Answer 7

Also you can use postgres fdw system

https://www.postgresql.org/docs/9.5/postgres-fdw.html

You will be able to connect different db in postgres. After that, in one query, you can access tables that are in different databases.

Piled

Answer 8

This worked for me

The Middleware:

<?php 
namespace App\Http\Middleware;

use Config;
use Closure;
use DB;

class DBSelect
{

   public function handle($request, Closure $next)
   {
    //$db_name = "db1";
    $db_name = "db2";
    Config::set('database.connections.mysql.database', $db_name);

    DB::reconnect('mysql');

    return $next($request);
  }
 }

global Kernel.php

protected $middleware = [
    .....
    \App\Http\Middleware\DBSelect::class,
];

I changed some code from this answer (https://mcmap.net/q/93689/-laravel-multiple-database-connection-dynamically-after-login-duplicate) and it worked for me.

Lupe

Answer 9

MongoDB also officially supports Laravel. If you have it added it in your config, you can access it similar to relational databases For example,

$moviesCollection = DB::connection('mongodb')->collection('movies')->get();

Oliana

Answer 10

Laravel, like many modern PHP frameworks, uses an ORM (Object-Relational Mapping) called Eloquent. Eloquent provides a comfortable and unified API for interacting with different database systems, but by default, it expects one primary connection. However, Laravel is flexible enough to manage multiple connections, even to heterogeneous databases.

Here's how you can approach the situation in Laravel:

Multiple Database Connections:

In your config/database.php file, you can define multiple database connections.

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

Verdi

What makes this answer useful? What does it add to the information already provided in other answers? – Paraphernalia

Answer 11

Not a good solution if you want to clone the existing system and to run the existing code on a new database for a new customer.

We would have to edit hundreds of eloquent calls to insert the DB::connection('foo')

Kemppe