Multiple databases in Laravel PHP

Question

We are building a multi-tenant application. Through the admin interface, we will add new tenant as and when required. This application needs to work with 1+n database.

1 Main DB with about 5 tables. n DBs for each tenant that we create. The tenant specific database may reside on the separate db server altogether.

Question:

What is the best way to achieve this ?
Where do we store the the db connection information for each tenant ?
Sometime, we may have to fire join queries on tables in tenant and main db. How would this work?

Thanks in advance for reading and any possible solution please.

score 0 · Answer 1 · answered Jul 18 '17 at 08:48

Google is your friend, so is the documentation: https://laravel.com/docs/5.4/database#using-multiple-database-connections

config/database.php

And add a new connection for each database-connection you want to use.

To switch connections:

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

score 0 · Answer 2 · answered Jul 18 '17 at 08:53

We can set the DB connection in config/database.php in connections part:

'connections' => [
    //Our primary DB
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        '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,
    ],
    //Secondary DB
    'mysql2' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB2_DATABASE', 'secondary'),
        'username' => env('DB2_USERNAME', 'forge'),
        'password' => env('DB2_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
    ],
],

And for Model we can add

class User2 extends Model
{
   protected $connection = 'mysql2';
}

And in migration we can use connection method.

Schema::connection('mysql2')->create('table')

And if you use Eloquent ORM we can use setConnection('mysql2') on it.

$user = new User2;
$user->setConnection('mysql2');

This answer based on this question

Thanks for your valuable inputs. However, in case of our application, we will go on creating multiple tenants and for each tenant there will be a separate DB. Hence, we need a mechanism to store the DB connection information for each tenant. Any inputs on how to achieve this? — Simran Kahlon, Jul 18 '17 at 11:11

score 0 · Answer 3 · answered Jul 18 '17 at 08:55

In my case a wont use multi-tenant store child db configurations in main database set child db params in middleware use $connection = 'child' in needed models join is not possible, because may be different servers, but huge part of relations is work propertly (not whereExists, withCount.. etc)

Hope it`s help

Multiple databases in Laravel PHP

3 Answers3