Level 75
May have to put some logic in a if else type construct, or have a db table holding the conn info. Double check https://laravel.com/docs/5.3/database#using-multiple-database-connections
Dec 4, 2016
13
Level 1
Laravel Dynamic Database Connections
Recently I have been trying to use a dynamic database connection with Laravel, but I have been running into issues.
I have been doing research for the past week, but the answer to this problem seems to be non-existent.
So, what I want to do is have a database connection that is SET and SAVED* after a user logs in. A user is tied to a specific company and each company will have a separate database.
This is my database.php
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
],
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
'club' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => '',
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
And this is my authenticated method override to Laravel's default one
protected function authenticated(Request $request, $user)
{
Config::set('database.connections.club.database', $user->club->db_name);
DB::purge('club');
DB::reconnect('club');
}
However, after setting the config in the authenticated when I try to access the brand new connection, it reverts to the old database.php connection.
Is there a way to get this connection to STICK (SAVE) for the rest of a user's session. IE store the new connection someone. (I know I can't use the session to store the new database connection)
Please help, Nick
EDIT*
If I put this inside the database.php
return [ // There is more code here that is irreverent to the problem.
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
],
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
'club' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => Auth::user()->club->db_name,
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
'pgsql' => [
'driver' => 'pgsql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'schema' => 'public',
'sslmode' => 'prefer',
],
],
];
I get the error Auth not found and a bunch of defined errors.
If I use config(['connection.database.club.database', Auth::user()->club->db_name]); it only stores it for 1 SINGLE request and doesn't carry over to other requests.
Level 1
Also there is no way to put logic inside of the database.php as it just gives the error...
'club' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
// THE DATABASE IS STORED INSIDE OF THE CLUB TABLE
'database' => Auth::user()->club->db_name,
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
( ! ) Fatal error: Class 'Auth' not found in F:\trapstats_v5\config\database.php on line 73
Everyone says "have a table that holds the connection info." I do. The Club table, which is referenced by the user who logged in. Each user has a club_id and that club_id references the club table that has an entry called db_name. After the user logs in the dynamic table will be set in stone and that table should be referenced later on in the application.
I have also tried config(['database.connections.club.database' => Auth::user()->club->club_id']);, but this does not work either because it is only saved for the single request. It isn't saved for future requests as the old config is loaded after the request.
Level 2
Setting configurations at run-time will only last for the current request. It would not store anything in the files (which would not be a solution anyway as you then would need one connection configuration per club).
Couldn't you use a middleware to set this configuration before each request?
Level 1
...How would I go about using a middleware? I just need something that sets the database for every request. Would a middleware work? If so, how?
Level 1
Did you find a good solution? I am also stuck with it. I have found an ugly way to do it. But I haven't tested it fully all over my application. I need a way to be sure that everything will work fine with the new connection. I also need to be able to use Eloquent with the new connection.
Level 67
@gofish Yes, use middleware.
php artisan make:middleware DynamicDatabaseConnection
Add this line in app/Http/Kernel.php at the end of the array...
protected $routeMiddleware = [
...,
'dynamic' => \App\Http\Middleware\DynamicDatabaseConnection::class,
];
In app/Http/Middleware/DynamicDatabaseConnection.php
public function handle($request, Closure $next)
{
$user = Auth::user();
Config::set('database.connections.club.database', $user->club->db_name);
DB::purge('club');
DB::reconnect('club');
return $next($request);
}
You'll need to wrap your routes in dynamic middleware.
Route::middleware('dynamic')->group(function () {
Route::get('/', 'HomeController@welcome');
});
Middleware executes in order it is defined, so you should have access to Auth::user() there, I think. Make sure to put use Illuminate\Support\Facades\Auth; at the top of your DynamicDatabaseConnection middleware file.
8 likes
Level 2
i keep getting the error Attempt to read property "db_name" on null but in my user database i added the db_name
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('db_name')->nullable();
$table->string('name');
Level 1
Thanks, problem solved. Thanks especially for the code. This is what I needed.
Level 70
If someone's answer solves your issue, please make it as the best answer.
Level 67
@noorcs Please mark best answer.
Level 1
Level 1
@fylzero Hi, i am sorry maybe it's too late now, i was following your answer and getting this error
Call to a member function prepare() on null
this is handle method in middleware
Config::set("database.connections.mysql.host", Auth::user()->db_server);
Config::set("database.connections.mysql.port", Auth::user()->db_port);
DB::purge('mysql');
DB::reconnect('mysql');
return $next($request);
Level 1
@zaidysf Hi! Please tell me how you were able to solve this problem? I just ran into her. As I found out, this problem occurs because of the session.
I'm using this:
DB::purge('tr_archive'); DB::reconnect('tr_archive');
But if you comment out this line DB::purge('tr_archive'); the error disappears, but the session is not written to the database
Please or to participate in this conversation.