How to set database dynamically based on client request in Laravel

Question

I am using a Laravel Application as a backend and wish to set the database connection dynamically (and keep it until the page is refreshed) through an axios request which will contain the database to use and the credentials.

For that purpose, I am storing the received DB configuration in a session and set the env variables with it in the constructor of whichever controller I am trying to use.

Here are the default database settings in .env :

DB_DATABASE=database1
DB_USERNAME=username1
DB_PASSWORD=password1

However, the issue seems to be that the session is not being kept alive, as each sent request contains a different session ID and therefore returns an Access denied error whenever I try to interact with the Database because the session variables are undefined.

Here is how the request is sent from the client :

axios
.post("https://data-test.io/api/Setconnection", {
  database: "database2",
  username: "username2",
  password: "password2",
})
.then((result) => {
  // console.log(result);
  // do stuff here
});

This is how I store the DB connection in the session :

    class RootController extends Controller
{
    public function setConnection(Request $request){
        session(['database' => $request->database]);
        session(['username' => $request->username]);
        session(['password' => $request->password]);    
        return $request->session()->all(); // this returns the correct values
    }
}

And I set the route in api.php like so :

    Route::post('/Setconnection',[RootController::class, 'setConnection']);

Then, on all subsequent requests, I set the connection in the constructor this way :

 public function __construct() {
            Artisan::call('config:cache');
            Config::set('database', session('database'));
            Config::set('username', session('username'));
            Config::set('password', session('password'));    
}

public function getConfig(){
           return [session('database'),session('username'),session('password')]; 
           // this returns an array of undefined elements.
}

Am I making a mistake here or is this not how I am supposed to set database connections dynamically? If not then what is the best way do so ?

Answer 1

As Tim Lewis said, APIs are supposed to be "Stateless" which no previously stored data that may be utilized in every transaction/request. If you don't want to use a database to store the dynamic database credentials, you need to save dynamic database credentials on the client side and send them each request.

But if you really want to create "Stateful" which can use session in the API you can follow these instructions.

Then to change database config you should use Config::set('database.connections.dbdrivername.configname','configvalue') and there is no need Artisan::call('config:cache')

an example:

Config::set('database.connections.mysql.database', session('database'));
Config::set('database.connections.mysql.username', session('username'));
Config::set('database.connections.mysql.password', session('password'));

You should really know what you are doing because it may have a big security hole.

Answer 2

I have needed this in the past for a multi-tenant application, and my solution was this:

DB::purge('mysql');
Config::set('database.connections.mysql.database', $request->database);
Config::set('database.connections.mysql.username', $request->username);
Config::set('database.connections.mysql.password', $request->password);
DB::reconnect('mysql');

Without the DB::purge and the DB::reconnect, the changes would not be applied to the connection.

With that out of the way, I too think this is a super risky practice. You are giving the world your database name and credentials.