Laravel 8: How to handle multiple database connections dynamically based on user role
I've tried everything I can think of but I've been working on this all day and I'm working on a project and hit a roadblock... I am currently working on a Laravel 8 application that requires connecting to different databases based on the user role. I have set up multiple database connections in `config/database.php` as follows: ```php 'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), 'port' => '3306', 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ], 'admin_db' => [ 'driver' => 'mysql', 'host' => env('ADMIN_DB_HOST', '127.0.0.1'), 'port' => '3306', 'database' => env('ADMIN_DB_DATABASE', 'admin_db'), 'username' => env('ADMIN_DB_USERNAME', 'admin'), 'password' => env('ADMIN_DB_PASSWORD', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ], ``` The logic I'm trying to implement is to switch the connection dynamically when a user logs in. For instance, if the user has an admin role, I want to use the `admin_db` connection; otherwise, I want to use the default `mysql` connection. I am using the `Auth` facade to manage user sessions. In my `LoginController`, I have tried the following code: ```php protected function authenticated(Request $request, $user) { if ($user->role === 'admin') { config(['database.default' => 'admin_db']); } else { config(['database.default' => 'mysql']); } } ``` However, whenever I attempt to perform a query after login, I am encountering the error: `SQLSTATE[HY000] [1045] Access denied for user 'forge'@'localhost' (using password: YES)`. It seems like the connection is not switching properly, and it defaults back to the `mysql` settings. I've also tried using `DB::purge('admin_db')` before switching connections to clear the previous connection, but this did not resolve the issue. Can someone guide me on how to properly handle dynamic database connections based on user roles in Laravel? Are there any best practices I should consider? My development environment is Ubuntu 20.04. Any advice would be much appreciated. Is there a better approach? What would be the recommended way to handle this?