Changing the database name dynamically over the same connection does not work

Question

In the login section of the application, the user selects the database to be connected from the list of companies and the main page of the program opens according to the selected database. The database name of the connection named 'second_db' needs to be changed according to the company chosen before the homepage opens. (footnote: not new connection!, just changing existing database name of 2.connection.) In other words, the database will be able to change dynamically according to the data selected from the list.

also the route group prefix name should change according to the new database name selected. example: localhost:8000/newdbname/homepage like..

I created a middleware class for this database change and I find the new database name according to the company information selected on the listview. Then I set the 'second_db' connection as the new database name with the config::set function.and I'm running this middleware just before the controller that will redirect to the homepage.

So far everything is working fine. 'second_db' connection is using the new db name in middleware and later running controller. but when I run a query about other models in other controller, including route group prefix name, it uses the old database name... why this change only works in middleware and accompanying controller? here are my codes:

web.php:

$databaseName = config('database.connections.second_db.database'); //still using old dbname after //middleware

Route::group(['prefix' => $databaseName ], function () { 
    Route::get('/anasayfa', function() {
        return view('anasayfa');
    });
    Route::get("/carikartliste", [App\Http\Controllers\ListeController::class,'getCarikartModelTablo'])->name("liste.carikartlar");
    Route::get("/stokkartliste", [App\Http\Controllers\ListeController::class,'getStokkartModelTablo'])->name("liste.stokkartlar");
});

Route::post('/goto-homepage', [App\Http\Controllers\Controller::class, 'goToHomePage'])->name('gotohomepage')->middleware('switch_db'); // this working after selected company on listview.

switchDatabase.php: //Middleware

public function handle(Request $request, Closure $next) // this working good 
{   
    $kisaUnvan = $request->input('kisaUnvan');
    $company= DB::table('sys_firmalar')->where('KisaUnvan', $kisaUnvan)->first();
 
    if (!$firma) {
        return response()->json(['success' => false, 'message' => 'Firma not found.']);
    }

    $newDatabaseName= $company->dbAdi;
    
    config::set('database.connections.second_db.database', $newDatabaseName);

    DB::setDefaultConnection('second_db');

    DB::purge();
    Artisan::call('config:clear');
    Artisan::call('optimize:clear');
    DB::disconnect();
    DB::reconnect();

    return $next($request);
}

Controller: // working after middleware

public function goToHomePage(Request $request)  // this working with newdbname okey.
{   
    $newDbName= DB::Connection('second_db')->getDatabaseName();
       
    return response()->json(['success' => true, 'message' => '/' . $newDbName. '/homepage']);
}

and when I run a query about the new database in another controller, it gets an error because it uses the old database.

public function getCarikartModelTablo() // error using olddbname
{   
    $tableData = \App\Models\cari::all();
    $tabloId = 1;
       
    return view("carikartliste", ['tableData' => $tableData, 'tabloId' => $tabloId]);
}

Answer 1

I think it was happened because of the config second_db has not yet to be updated when you trying to set the connection as default.

Have you tried to closing the database connection and then clear the config, finally you can try to call DB::setDefaultConnection('second_db')

Hope this helps!