Laravel model not using assigned connection (second connection) in a multi tenant app

I am trying to build a multi tenant app on laravel. It is to get the subdomain from url, get the tenant id from subdomain, get credentials for that tenants database and connect to that by using another connect.

Below is my database config in the database.php in config folder.

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

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'erptenants'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
        'tenant' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', ''),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
    ],

As you can see there are two connections. The default and another called tenant. Tenant has new credentials assigned through a global middleware.

namespace App\Http\Middleware;

use Closure;

class TenantIdentification
{    
    public function handle($request, Closure $next)
    {

        $tk = "HYD"; //hardcoded for time being

        $tenant = \App\Models\Tenant::where('tenantKey', $tk)->first();

        \Config::set('database.connections.tenant.host', env('DB_HOST', $tenant ->host));
        \Config::set('database.connections.tenant.username', env('DB_USERNAME', $tenant ->username));
        \Config::set('database.connections.tenant.password', env('DB_PASSWORD', $tenant ->password));
        \Config::set('database.connections.tenant.database', env('DB_DATABASE', $tenant ->database));


        return $next($request);
    }
}

I assume that this assignment of DB credentials is working since I get no errors from laravel. Probably a bad practice too.

Now I have a model which has the connection defined.

<?php

namespace App\Models;

use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    protected $connection = 'tenant';
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];


}

Below is my controller and the function that is trying to retrieve data.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Models\User;

class UserController extends Controller
{
    public function verify(Request $request)
        {

            $username = $request->username;
            $password = $request->password;

            $user = \App\Models\User::where('username',$username)->where('password',$password)->first();

            var_dump($user);
        }
}

I get an error thrown to me saying

Base table or view not found: 1146 Table 'erptenants.users' doesn't exist

The retrieval must be done from erpdata instead of erptenants. This is to be assigned in the middleware. save up and load it's db credentials and for some reason it is not using the second connection "tenant" in the model to retrieve the datafrom. Laravel is trying to retrieve the information from the wrong database. What can I do about this? Because the second connection is loaded dynamically hardcoding is not practical. The only way I can think is to overwrite the default db instead of having a second db connection. But I prefer to have a seperate db connection. Any help is greatly appreciated.

On a side note...the default connection is used only validate the tenant ID and get the mysql db credentials from a table. Perhaps this should be done on the second connection. But then again the default DB credentials are taken from the .env file. Not dynamically. So I'm going to end up with this problem once again.

via Ela Buwa