Laravel Switching Database with respect to User Data from Main Database

Current Scenario,
I have a main database where I have the user database related information stored . ie. client table .
Now when the user tries to login I check in my main database where the user exist or not and if exist get the database related information . and I made a middleware to switch the database connection . Now the problem is here.

public function handle($request, Closure $next)
{
    $clientData = Client::where('email', $request['email'])
        ->where('workspace', $request['workspace'])
        ->first();
    if ($clientData) {
        $db_name = $clientData->db_name;
        $db_config_name = $clientData->db_config_name;
        $db_username = $clientData->db_username;
        $db_password = $clientData->db_password;
        DB::disconnect('mysql');
        Config::set("database.connections.mysql.database",  $db_name);
        Config::set("database.connections.mysql.username", $db_username);
        Config::set("database.connections.mysql.password", $db_password);

        return $next($request);
    } else {
        return response()->json(['error_message', 'No data'], 500);
    }
}

when I do this it works .. but what i wanted was to use the data from database.config . if I do

$clientData = Client::where('email', $request['email'])
        ->where('workspace', $request['workspace'])
        ->first();
    if ($clientData) {
        $db_name = $clientData->db_name;
        $db_config_name = $clientData->db_config_name;
        $db_username = $clientData->db_username;
        $db_password = $clientData->db_password;
        DB::disconnect('mysql');
        Config::set("database.connections" . $db_config_name . "database",  $db_name);
        Config::set("database.connections" . $db_config_name . "database", $db_username);
        Config::set("database.connections" . $db_config_name . "database", $db_password);

        return $next($request);
    } else {
        return response()->json(['error_message', 'No data'], 500);
    }

and I receive “test1” in $db_config_name this is what I have in database.config

 'test1' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => 'db_test',
        'username' => 'root',
        'password' => '',
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => false,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],

this second approach doesnot work ie. it returns the data from the default Database mysql . Can anyone explain me why this is happening and if I do the first approach I wont need to store data in database.config right ? Would love to hear suggestion.. Thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I think it’s a good idea to use the service provider rather than a middleware to connect to the database.

First of all purge the database connection cache

DB::purge('test1');

Set the database connection details

Config::set("database.connections.test1.host", "");
Config::set("database.connections.test1.database", "");
Config::set("database.connections.test1.username", "");
Config::set("database.connections.test1.password", "");

Now change the database default database connection to test1

DB::setDefaultConnection("test1");

Note: Do not store the database password in the plain format in the
database.

class DBSwitchProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot(){
        // First purge the configuration to remove config details from cache 
        DB::purge('test1');

        // set the database config details 
        Config::set("database.connections.test1.host", "host_value");
        Config::set("database.connections.test1.database", "database_name");
        Config::set("database.connections.test1.username", "database_user");
        Config::set("database.connections.test1.password", "db_password");
        
        // if current db connection is test1 
        // reconnect the database 
        if(DB::connection()->getDatabaseName() == "test1"){
            DB::reconnect("test1");
        }else{
            // change the default databse to the 
            DB::setDefaultConnection("test1");
        } 
        
        
    }
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0