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

Question

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.

---below is my env file

APP_ENV=local
APP_DEBUG=true
APP_KEY=base64:somekeyhere=
APP_URL=http://localhost

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=erptenants
DB_USERNAME=root
DB_PASSWORD=

CACHE_DRIVER=file
SESSION_DRIVER=file
QUEUE_DRIVER=sync

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_DRIVER=smtp
MAIL_HOST=mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

Are DB_HOST, DB_USERNAME etc. defined in your .env? Please post your .env file — Paras
looks like it has some defined. env file added to question body — Ela Buwa

Paras Paras · Accepted Answer · 2017-03-13T11:28:23

Looks like your config set statements have errors:

\Config::set('database.connections.tenant.host', env('DB_HOST', $tenant ->host));

This: env('DB_HOST', $tenant ->host) returns the DB_HOST if defined in your .env and would only return $tenant->host if DB_HOST is not defined in your .env

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

1 Answers