Setting dynamic database in config during Login - Laravel

Question

I'm trying to change the database connection on login based on the user's company.

Here my user has a company and its DB is companyA.

Below is my LoginController where I changed the connection:

public function authenticated(Request $request,User $user)
{
    \Config::set('database.connections.dynamicdb', array(
        'driver'    => 'mysql', 
        'host'      => '127.0.0.1',
        'database'  =>  $user->company,
        'username'  =>  'root',
        'password'  =>  '',
        'charset'   => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'strict'    => false,
        'options'   => [                                
            \PDO::ATTR_EMULATE_PREPARES => true
        ]
    ));

    return redirect()->intended($this->redirectPath());
}

So based on user->company which is already defined in users table, the database name is changed.

But somehow it doesn't work. The error shown is

No database selected.

I tried below code to check if the values are set during login.

return \Config::get('database.connections.dynamicdb');

It showed all values set to my requirements. But when I check after login and reaching /home, the value of database in config is null.

So what all changes should I do. Is my technique right? Or is there any other solution for this.

In my Stock Model i have added the below lines:

protected $table = 'stocks';  
protected $connection = 'dynamicdb';

And the query I'm running is just a get all query:

 Stock::orderBy('tag_no','asc')->get()

Can anyone please tell me why this happens? What should i do?

Answer 1

All requests are stateless so current request doesn't know that you set new config value in previous request.

You should call Config::set(...) every time when you want to use dynamic database and set database name getting this value from User instance.

Setting above should be done using middleware and service provider.

Create new middleware and register it for web middleware group (You may do this using the $middlewareGroups property of your HTTP kernel):

protected $middlewareGroups = [
    'web' => [
        //...
        \App\Http\Middleware\YourMiddleware::class,
    ],
    //...
];

Then:

<?php namespace App\Http\Middleware;

class YourMiddleware
{
    public function handle($request, Closure $next)
    {
        if (Auth::check()) {
            $database_name = Auth::user()->company;

            // Set your config here using $user->company
            // ...
        }

        return $next($request);
    }
}

If you must to set this value once (during authentication), you should combine above code and sessions to store this information between requests:

session(['db_name' => $dbname]); // Set db_name and store it

$db_name = session('db_name'); // Get db_name from session

Read more about HTTP Sessions: https://laravel.com/docs/5.7/session#retrieving-data