Change the Database Connection Dynamically in Laravel [duplicate]

Question

I have the master database with login table and corresponding database settings for each user. On login I should dynamically change the db settings fetching from the table. I can change the db connection but this is not persisting.

Config::set("database.connections.mysql", [
'driver' => 'mysql',
"host" => $usr_host,
"database" => $usr_database,
"username" => $usr_username,
"password" => $usr_password,
...
]);

edit: New database is created for each user when he/she registers with the app and thus i dont have the database connection for each user defined in the config/database.php

Housemaster

Does this answer your question? Laravel: connect to databases dynamically – Enwomb

Answer 1

Well you can use the default database for user login and have a new field for the database name. Then whenever you need to query a different database, you can just change your db connection.

Something like this

$someModel = new SomeModel;
$databaseName = "mysql2"; // Dynamically get this value from db
$someModel->setConnection($databaseName);
$something = $someModel->find(1);

You can read more about it here. http://fideloper.com/laravel-multiple-database-connections

Wysocki

what if im not working with model, and im using just ::table – Orsola

Well this isnt elegant and wont recommend it, but an easier way of doing this would be to somehow change the returned array in app/config/database.php. So you add some condition if the condition is met then you return secondary db details else you always return primary db details. – Wysocki

Answer 2

This way you can set new parameter when it comes to database:

 \Config::set('database.connections.mysql.database', $schemaName);

Remember about PURGE to persist this settings

 DB::purge('mysql');

Cheers!

Trephine

purge function of DB class is very useful. – Feckless

Your answer is really helped me, thank you very much. there is a case were we need to change only the connection database name as we might have the same connection but with multiple dbs – Loverly

Answer 3

you need to get the config first.. then alter the specific field then set it back..

$config = Config::get('database.connections.company');
$config['database'] = "company_tenant_$id";
$config['password'] = "test2123";
config()->set('database.connections.company', $config);

Dealt

this settings changes to default in next request – Housemaster

where/which file will I have to do this? I am just lost. Please let me know the exact file path where I can do this. I just want to be able to do it once and then I should no longer have to do it every time in every class and container. – Preciosity

For the next who made it here, you configure the connection like @Dealt is doing. Then, you call the configured connection with the desired query, like this for example: $users = \DB::connection('company')->table('users')->get(); – Ardellardella

Answer 4

I think a good place to change the database connection place is in bootstrap/app.php file, use the code below:

$app->afterBootstrapping(\Illuminate\Foundation\Bootstrap\LoadConfiguration::class, function ($ap) {
    // your database connection change may happens here
});

It is before ServiceProvider register and boot, so even if you use DB or Eloquent staff in ServiceProvider, it works very well.

Happily

But I need to check if the user is logged in then I want the use the dynamic db connection, else I want to use the default login database ( by which the user can only view the login page and nothing more ) – Preciosity

I just tried your method. I really like to to do it your way. But inside that code block when I want to check if the user is logged in, it says that Auth class is not defined. How can I access the Auth class inside that code block? If I am just able to check if the user is logged in, then I will be all set for this. – Preciosity

Answer 5

In laravel what you can do is create the different connections in the connections array of the file conf/database, then these connections you can use them when you are going to carry out operations in your application.

If you use query builder or raw expresions you must use the connection ('name') method to perform queries, for example:

$users = DB::connection('mysql')
         ->table('users')
         ->select(...)
         ->get();

If you use eloquent you can specify the name of the connection in the model file in the connection attribute, for example:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'mysql';
}

A solution to your problem could be that you have created the different types of connections according to the users in the file conf/database, and save the name of the connection that the user uses as a column in the user table, and when you go to make the queries you get the name of the connection of the user, for example:

$user = User::find(Auth::id());
$connection = $user->connection;

$users = DB::connection($connection)
                      ->table('users')
                      ->select(...)
                      ->get ();

more info:

https://laravel.com/docs/5.5/eloquent#eloquent-model-conventions https://laravel.com/docs/5.5/database#read-and-write-connections

Unit

Answer 6

After an extensive search I found it this way:

Go to this file vendor\laravel\framework\src\Illuminate\Auth\Middleware\Authenticate.php

Go to the method: protected function authenticate($request, array $guards) and right after the method has started, paste the following code:

    if(auth()->check() && !empty( auth()->user()->db_name )){

        $dynamic_db_name = auth()->user()->db_name;
        $config = \Config::get('database.connections.mysql');
        $config['database'] = $dynamic_db_name;
        $config['password'] = "Your DB Password";
        config()->set('database.connections.mysql', $config);
        \DB::purge('mysql');
    }

first we are checking if the user is logged in with

auth()->check()

Then as you may have added db_name name or the like column table to your users table according to each user. So in the next condition, I am making sure that the db_name is available:

&& !empty( auth()->user()->db_name )

Then after execution enters the if condition I get the db_name from the user record and set the configuration according to the user database, save the config and use purge method of DB class to persist this setting. I was really stuck with it. I did not wanted to change my db connection in every class. So this is how I got it. Now I can use both Eloquent and DB without any tension anywhere. In my application I have one centeral database for login of all users and then for each organization there is a different database. So after the user has logged in, I do not need the centeral database (login database), I juse need the user/organization specific database. So this is how I got it to work.

Preciosity