How to change/resolve DB connections in Laravel 5 dynamically?

Question

I am working on an application which is multi tenant multi DB architecture which basically means that every tenant has his own database instead of all the tenants living in the same DB.

Now I am constantly struggling with the fact that I am not able to change the DB connections successfully and I am not sure what is right way to do it.

My database config file looks like so:

'connections' => [

    'archive' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

    'tenant' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

],

So basically the main DB is archive and it holds all the tenant (company) information and when a user wants to log in, he basically provides the tenant (company) username first, then provides his own login credentials. This way I know which database I should connect to and authenticate the user from. One the authentication is successful, I save the username of the tenant to a session username variable which is in turn used by a middleware I have written. So basically if the session has a username variable, I know to switch to the database every time a request is fired from that user. This way I am keeping track of which request is supposed to connect to which database.

Login logic:

$this->changeDb($request->input('username'));
$this->saveUsernameToSession($request);

if (\Auth::attempt(['email' => $request->input('email'),
                 'password' => $request->input('password')])) {
    // Authentication passed...
    return redirect('/');
}else{
    // If the auth atttemp is not successful
    // Set default DB as the main one again.
    $request->session()->pull('username');
}

Now the changeDB() method is the one I am interested in and am not sure if I am doing it right.

\Config::set('database.connections.tenant.database', $username);
\Config::set('database.default', 'tenant');

So this is what it does behind the scenes. It basically changes the tenant connection in the database config file to the username of the tenant, because that is same as the database of the tenant. Then it sets the default database to the tenant connection.

Is this the correct way of resolving the DB connection? Or are there other ways I am not aware of. Maybe Laravel provides some methods internally which I can use to switch/resolve database connections.

The login works fine but the reason I am concerned is that many of the other things don't work as fine for example, I ran in to this problem when trying to seed fake tenants and seeding their fake users.

Seeder runs fine the first time but does not do some tasks in the subsequent loops in Laravel 5?

I was using the same approach that I am using in the login but somehow it did not work. And although the answer provided solved my issue I am still not sure how?

Also I was trying to use a similar approach in a controller method to change DBs and it did not work the way I expected again:

public function showUsersForTenant($id)
{
    $tenant = Tenant::findOrFail($id);

    \Config::set('database.connections.tenants.database', $tenant->username);

    $users = User::on('tenant')->get();

    return response()->json($users, 200);
}

This is supposed to return all the users for a particular tenant (company) to the super admin of the system who is connected to the main database. But it does not connect to the tenant database and keeps returning users from the main DB.

There are some packages out there which support multi tenancy multi database approach but since I am a novice developer, I cannot make much sense out of their repositories and how THEY are changing/resolving the DB connection on the fly.

https://github.com/orchestral/tenanti

https://github.com/laraflock/multi-tenant

https://github.com/uxweb/laravel-multi-db

So how to change/switch/resolve connections successfully in Laravel 5?

Answer 1

As the author of the second package you mention (hyn/multi-tenant), I can tell you the secret lies in where you hook into Laravel.

Assuming you are using a vanilla Laravel, the place Models are selecting their connection is from the getConnection() method. By overruling the Eloquent Model and having all your application models use that extended Model you are able to define which connection to use 'on-the-fly'.

Now onto the second problem, which concerns seeders and migrators. This is a whole different story. I actually overruled the whole MigrateCommand Laravel ships with in order to stay as close to Laravel's working method. An easier way is to create a custom command, which sets the config for a specific connection and then run the migrate/seed command from within that command with the connection argument.