How to use Multiple database in Laravel and database connection change with User?

Question

I want to create user base database, for example in my system there are tow user A and B. I have a master Database and two database user_a (for user A) and user_b (for user B). In master Database i have the all users information. Now what i want, when user A logged in system it access master database and user_a database, and when user B logged in database connection should be master database and user_b database.

Answer 1

Here is the my answer after the suggestion by @ADyson

I did many searching on INTERNET, but did not found any perfect solution. There were some blogs on where only explained that create two or more connection database.php config file, and then access these connection in models using $connection. Yes i agree its a good solution but, what if millions of users are in my system, i don't want to create all the connection on database.php file manually.

So i did a experiment and its working for me and i want to share this solution to all the other developers.

First i give an option in master database for the database name respective to all the users(Super admin can add database name once user created by super admin as in my system)

Second i created a Middleware DatabaseSwitcher.php and registered this Middleware globally in Kernel.php and call this Middleware after auth Middleware in web.php:

(['middleware' => ['auth', 'DatabaseSwitcher']]).

Below are the code for the Middleware.

<?php 
namespace App\Http\Middleware;
use Closure;
use Illuminate\Contracts\Auth\Guard;
use Config;    //to get configuration data
class DatabaseSwitcher {
    /**
     * The Guard implementation.
     *
     * @var Guard
     */
    protected $auth;

    /**
     * Create a new filter instance.
     *
     * @param  Guard  $auth
     * @return void
     */
    public function __construct(Guard $auth)
    {
        $this->auth = $auth;
    }
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {   
        //check if user logged in
        if ( !$this->auth->guest() )
        {    
            //get authenticate user information
            $user = $this->auth->user();
            //get user's database            
            $user_db = $user->user_database;
            //first get default mysql connection array and use in new variable for new connection which will create dynamically.(default connection is defined in database.php config file)
            $dbConfig = config('database.connections.mysql');
            //now use database name which is in user record; 
            $dbConfig['database'] = $user_db;
            //now set a new database connection name is mysql_new in my case 
            Config::set("database.connections.mysql_new", $dbConfig); 
            //now set default connection which is created for the user
            Config::set("database.default", 'mysql_new'); 
            //now there are two connection one for master (mysql) and other for user(mysql_new) and default connection is (mysql_new)
            //we can access these two connection in every models by using $connection as mentioned in Larave documentation.            
        } 
        return $next($request);
    }
}

Now we can use both the database connection dynamically in model by using standard structure or laravel like:

protected $connection = 'mysql'; 
protected $connection = 'mysql_new'; 

There are all the things good, but still there might be an issue with the Laravel Validation rules, when we use unique and exist.

To overcome this problem i used connection name with the unique and exist rule. for example //connection should be name of database connection like mysql and mysql_new (in my case)

'name' => 'required|unique:connection.users,name',
'email' => 'required|exist:connection.users,email',

I hope it will help all the other developers who want to looking system look like this. Sorry for my English, as i am not expert in grammar.