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?

This link will help you: stackoverflow.com/questions/31041893/… — Vajiheh Habibi
– Vajiheh Habibi, Commented Jan 29, 2019 at 19:27
As @ZaquPL explains in his answer, whatever config you set is only going to be good for the current request, ie the request where the user logs in and authenticated() is called. As soon as your redirect() happens at the end of that method, that generates a new request, and the default config is loaded again. — Don't Panic
– Don't Panic, Commented Feb 4, 2019 at 10:52

ZaquPL · Accepted Answer · 2019-02-03 19:51:03Z

7

+50

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

edited Feb 3, 2019 at 19:51

answered Feb 3, 2019 at 19:21

ZaquPL

7992 gold badges12 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sven Hakvoort Over a year ago

Good answer, however I have 1 addition. In order to ensure that the right database is used also call DB::reconnect();

the_hasanov · Accepted Answer · 2019-02-05 08:45:51Z

First you need create new default db for connection and add to database.php like normal connection

    'dynamicdb' => [
        'driver'      => 'mysql',
        'host'        => env('DB_HOST', '127.0.0.1'),
        'port'        => env('DB_PORT', '3306'),
        'database'    => 'default',
        //others
    ],

next overriding model methods in Stock

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

 /**
 * @return string
 */
public function getTable()
{
    $table = parent::getTable();
    $database = config('database.connections.dynamicdb.database');
    return starts_with($table, $database)
        ? $table
        : $database . '.' . parent::getTable();
}

/**
 * Set the table associated with the model.
 *
 * @param  string $table
 * @return $this
 */
public function setTable($table)
{
    $database = config('database.connections.dynamicdb.database');
    $this->table = starts_with($table, $database)
        ? $table
        : $database . '.' . $table;

    return $this;
}

Usage : \Config::set('database.connections.dynamicdb.database',$user->company); or you can create helper for it Don't forget this method work only one connection and connected user have access all databases

swap · Accepted Answer · 2019-02-05 13:01:25Z

0

Add Multiple DB in .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=

ALT_DB_HOST=127.0.0.1
ALT_DB_PORT=3306
ALT_DB_DATABASE=database2
ALT_DB_USERNAME=root
ALT_DB_PASSWORD=

Edit config/database.php

'connections' => [
    'mysql' => [
        ......
    ],

    'alt_mysql' => [
        'driver' => 'mysql',
        'host' => env('ALT_DB_HOST', '127.0.0.1'),
        'port' => env('ALT_DB_PORT', '3306'),
        'database' => env('ALT_DB_DATABASE', 'vibecloud'),
        ...
    ],

If Whole model used for ALT_MYSQL then

protected $connection = 'alt_mysql';

ELSE

protected function stock_info() {
  return \DB::connection('alt_mysql')->select('*')->from('stocks')->get();
}

answered Feb 5, 2019 at 13:01

swap

2923 silver badges18 bronze badges

1 Comment

Lano Angel Over a year ago

For every call we might have to call this. So this is one solution but not the prefect one.

Collectives™ on Stack Overflow

Setting dynamic database in config during Login - Laravel

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related