Laravel - How to use multiple db connections dynamically

Question

In my project i have to select, on login, which database i want to use in application until next logout.

I'm thinking in way to save de name of database into a global variable but i don't know how.

In this moment i'm trying to set the database on route:

    Route::group(['middleware' => 'auth'], function () {
  Route::get('app/groups', function()
  {
    DB::disconnect();
    Config::set('database.default','db2');
    DB::reconnect();

    return view('app.main-folders');
  });
});

I do the the login with db1 and the page was returned to app/groups and it is work, change the database to db2 and show the data but when i select another link it seems that it loses the connection because give me an sql error.

Which is the best option to do that? The selection of the database has to be dynamic.

If i save the database name into a global variable i can use that name to do queries:

DB::connection($name)->select(...);

How can i solve that?

Thanks

Steve.NayLinAung · Accepted Answer · 2016-11-25 08:35:21Z

1

Config::set() will not work in this way. It works per 1 request only. That's why it loses the "new" connection (and keep connecting the original default connection) when you visit to another link.

The only way I think that works is store the new database connection name in session.

And grab that connection name from session in your controller constructor.

Example Code:

In your routes;

Route::group(['middleware' => 'auth'], function () {
  Route::get('app/groups', function()
  {
    session(['database_name' => 'db2']);
    return view('app.main-folders');
  });
});

In your controller;

class ItemController extends Controller
{
    public function __construct()
    {
        // without middleware, you cannot access 
        // session() in constructor of the controller in Laravel 5.3
        $this->middleware(function ($request, $next) {
            Config::set('database.default', session('database_name', 'default_database_connection'));
            return $next($request);
        });
    }

    public function ListCustomer()
    {
         $customers = Customer::all();
         return $customers;
    }
}

Or, if you change connection per Eloquent Model in your controller, you can also try like that;

class ItemController extends Controller
{
    public function ListCustomer()
    {
        $customer = new Customer();
        $customer->setConnection(session('database_name', 'default_database_connection'));
        $customers = $customer->get();
        return $customers;
    }
}

But you have to use this approach with extra care. Because it has some caveats when using with relationships.

answered Nov 25, 2016 at 8:35

Steve.NayLinAung

5,1453 gold badges30 silver badges52 bronze badges

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

4 Comments

user3242861 Over a year ago

It is possible to do this in route instead controller? For example: in post login i create the session and use it. @Steve.NavyLinAung

Steve.NayLinAung Over a year ago

@user3242861 In my example, I create the session in the route. But use this session values in the controller. If you want to use the session values in the route, I think it's pretty possible if you implement correctly. But to help you more, you need to provide more of your code.

user3242861 Over a year ago

With config::set on construct doesn't work too. I got save the database name in session on login post and use this session in queries to tell witch database is to use and work well. Thanks for helping me @Steve.NavyLinAung

Steve.NayLinAung Over a year ago

@user3242861 I forget to say you have to put config::set in the constructor of every controllers. Otherwise, you have options to implement ServiceProvider or Custom Middleware. Whatever, glads to hear that your code works well now.

webphax · Accepted Answer · 2016-11-24 21:57:43Z

0

You can find a great solution here: https://laracasts.com/discuss/channels/general-discussion/change-database-name-of-connection-on-the-fly

answered Nov 24, 2016 at 21:57

webphax

14 bronze badges

1 Comment

user3242861 Over a year ago

There is any way to list the connections of database.php file? @Phil

Collectives™ on Stack Overflow

Laravel - How to use multiple db connections dynamically

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related