I need to change Laravel 5 database connection in run time.
How can I achieve this?
-
This link will help fideloper.com/laravel-multiple-database-connectionsimsheth– imsheth2015-06-25 05:43:44 +00:00Commented Jun 25, 2015 at 5:43
Add a comment
|
5 Answers
You can change the connection details during run time using
Config::set('database.connections.mysql.database', 'other_database');
but don't forget to run purge before in order to delete the cache
DB::purge('mysql');
3 Comments
Renan Coelho
I wasted ten hours trying to change de default database at runtime. What I forgot was the DB::purge() method. Thank you!
Adam Kozlowski
Really thanks for help. DB::purge('mysql') is the key!
I Want Answers
If the other database uses another password, you'll need
Config::set('database.connections.mysql', Config::get('database.connections.other_database')); where "other_database" is its own key pointing to an array of credentialsUpdate: This answer is from 2015! I haven't used Laravel in years and I am not up to date with the latest best practices. This answer keeps getting upvoted so I suppose it works but please proceed with caution.
Well, my immediate answer to this is: don't. Chances are that you can accomplish your task by changing your data model and working with some more advanced relationships. It's hard to tell without knowing what you want to do but it seems to me like a bad idea in general, specially if you're planning on using eloquent models and so on
That said, in some scenario where you really need to alter data in another database or execute some raw query you can use the DB::connection() method. Something like:
$data = DB::connection('another_connection')->select(...);
You can specify that another_connection variable at your database.php file. Like this:
<?php
return array(
'default' => 'mysql',
'connections' => array(
# Your regular connection
'mysql' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'user',
'password' => 'password'
'charset' => 'utf8',
),
# Your new connection
'another_connection' => array(
'driver' => 'mysql',
'host' => 'another_host',
'database' => 'another_db',
'username' => 'user1',
'password' => 'password1'
'charset' => 'utf8',
),
),
);
You can even specify a connection for each eloquent model using protected $connection = 'another_connection'; also you can specify a connection for each model instance created/queried at run time
$user = new User;
$user->setConnection('another_connection');
$user1 = $user->find(1);
But then again, I personally don't think this is a good idea and it seems to me that everything can get messy and fall apart very quickly as your application grows in complexity.
4 Comments
Ben
Thanks for your answer, I tried changing the
connection property in the Model's method, but using the connection method on the DB facade did the trick. To elaborate a valid use case: I'm creating small system that checks if all data is correctly mapped. This requirement introduced itself when I was experiencing free text entrances opportunaties for users and myself on the reporting end. All application data is stored in a MySQL database but I need to query the SQL server in order to validate if my - in MySQL - saved queries run into trouble on the production end.
Mina Abadir
Personally, I am against switching connections. And I am not getting the idea of validating data after storing it, data should be validated before storage.
Yevgeniy Afanasyev
Thanks, just for the record, Not only
DB but also Schema has a connection function, it works like so: Schema::connection('pgsql_admin')->create(
Alex
actually, it's very good idea to changing connections in runtime if you're using multiple databases and did unit/function test well.
You can change it like this since Laravel 5.x or 6.x
config(['database.default' => 'databasename']);
Where databasename has to be specified in config/database in the connections array.
1 Comment
shrimpwagon
This is the way to do it. The database conection needs to be defined. It seems that JUST changing the database name for the 'mysql' connection doesn't always work
You can configure another connection at runtime (tested on Laravel 9)
$runtimeConnectionConfig = array_merge(config('database.connections.mysql'), [
'host' => 'db_host',
'database' => 'db_name',
'user' => 'db_user',
'password' => 'db_pass',
]);
config(['database.connections.NEW_CONNECTION_NAME' => $runtimeConnectionConfig]);
$runtimeConnection = DB::connection('NEW_CONNECTION_NAME');
$data = $runtimeConnection->table('users')->get();
Comments
We have a similar scenario, where to look for historic data, we change the database. We use the following in the model:
public function inventory($db, $id) {
return DB::table($db . '.inventory')->where('inventoryid', $id)->get();
}
and then, on the code, we send the $db as a parameter:
$inventory = Inventory::inventory('data_2016', 2);
This way, you don't need to define the connection for each of the different databases and don't tinker with the database config files.
The only downside is that you are unable to use the model directly, instead you have to use the DB helper.
