I'm creating an application in Laravel 5(.1) where it is needed to connect to different databases. The only problem is that it's not known which databases it has to connect to, so making use of the database.php in config is not possible. A controller is in charge of making a connection with dynamically given connection details.
How can I make a new connection to a database, including making use of the DB class? (Or is this possible)
Thanks in advance!
The simplest solution is to set your database config at runtime. Laravel might expect these settings to be loaded from the config/database.php file, but that doesn't mean you can't set or change them later on.
The config loaded from config/database.php is stored as database in Laravel config. Meaning, the connections array inside config/database.php is stored at database.connections.
So you can easily override/change these connections like this:
Config::set("database.connections.mysql", [
"host" => "...",
"database" => "...",
"username" => "...",
"password" => "..."
]);
From there on out, any Eloquent models that use this mysql connection will be using this new database connection config.
I'd recommend doing this in a Service Provider if possible.
/config directory. it's loaded during bootstrap, and can be changed anytime.
I've stumbled upon the same problem.
You can actually change database settings in runtime and use them.
Use the config() function to set extra or overwrite existing connection settings.
config(['database.connections.mynewconnection' => {settings here}]);
Keep in mind that these settings are cached. So when you need to use the new settings, purge the DB cache for the connection you're gonna use.
DB::purge('mynewconnection');
You can also manipulate the default connection that is used. This can come in handy if you wish to use migrations over different connections and keep track of them with a migration table within the used connection. Or other cool stuff ofcourse...
DB::setDefaultConnection('mynewconnection');
You might need to use these:
use Illuminate\Support\Facades\Config;
use DB;
Set database configurations:
Config::set("database.connections.mysql_external", [
'driver' => 'mysql',
"host" => "localhost",
"database" => "db_name",
"username" => "root",
"password" => "root",
"port" => '8889',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
]);
Connect to database and do stuff:
$users = DB::connection('mysql_external')->select('Select id from users');
Disconnect database and reset config variables
DB::disconnect('mysql_external');
Config::set("database.connections.mysql_external", [
'driver' => 'mysql',
"host" => "localhost",
"database" => "",
"username" => "",
"password" => "",
"port" => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
]);
I ran into this problem too with a script that imports multiple MS Access DB files into MySQL and I was not satisfied with any of the solutions which suggested editing configuration at runtime. It was ugly and messy to dynamically create a new config for every single Access DB file that I wanted to import. After some playing, I manged to persuade Laravel to switch DBs on the existing connection like this:
$mysqlConn = DB::connection();
$mysqlConn->getPdo()->exec("USE $schemaName;");
$mysqlConn->setDatabaseName($schemaName);
//appServiceProvider.php
Connection::macro('useDatabase', function (string $databaseName) {
$this->getPdo()->exec("USE `$databaseName`;");
$this->setDatabaseName($databaseName);
});
//usage.
\DB::connection()->useDatabase($dbName);
Create a new database connection in your databse.php
'connections' => [
'new_db_connection' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_NEW', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
'old_db_connection' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_OLD', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
]
Create helper method
static function getDatabaseName()
{
// Apply your condition and return databse
if(url('/') === 'http://localhost:8001'){
return 'new_db_connection';
} else {
return 'old_db_connection';
}
}
Using query builder
$databaseName = Helper::getDatabaseName();
DB::connection($databaseName)
->table('your table name')
->select('*')
->get();
Using model
<?php
namespace App\Models;
use App\Helper;
use Illuminate\Database\Eloquent\Model;
class Test extends Model {
public function __construct()
{
// You can apply the below variable dynamically and model
// will use that new connection
$this->connection = Helper::getDatabaseName();
}
protected $table = "users";
}
// Without Using any Facades
config(['database.connections.mynewconnection' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_OLD', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
]]);
// With Facades
// Use Illuminate\Support\Facades\Config;
Config::set('database.connections.mynewconnection', [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_OLD', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
]);
Access the database using Query Builder
DB::connection('mynewconnection')->table(<table_name>)->get();
This is a pretty "eloquent" way of handling this without needing to juggle secondary database connections (something that can get messy real quick if you already need separate configs for your local and cloud environments).
Keep in mind this is for secondary "micro" databases that run next to your primary on the same instance
Say you have BaseModel.php that all your models extend:
/**
* The micro database used by the model
*
* @var string
*/
protected $microDatabaseKey = '';
/**
* Get the table associated with the model.
*
* @return string
*/
public function getTable() {
if (!empty($this->microDatabaseKey)) {
return $this->getMicroDatabaseName().'.'.$this->table;
} else {
return parent::getTable();
}
}
/**
* Get the micro database name
*
* @return string
*/
public function getMicroDatabaseName(string $microDatabaseKey) {
return config('database.micro_databases.'.$this->microDatabaseKey);
}
Add the following to your database.php config:
'micro_databases' => [
'micro_secondary_database' => env('DB_DATABASE_MICRO_SECONDARY', 'micro_secondary_database'),
]
Then in your SecondaryDatabaseModel.php:
/**
* The micro database used by the model
*
* @var string
*/
protected $microDatabaseKey = 'micro_secondary_database';
That's it. Nice and clean and extensible.
getMicroDatabaseName() method and even if you were, it is just returning a different table name? How does this change the database connection that Laravel uses?
we can do it another way
class SomeModel extends Eloquent {
if(app::environment('local'))
{
protected $connection = 'mysql2';
}
}
now extend use SomeModel class instead of Model everywhere.
Reference : https://fideloper.com/laravel-multiple-database-connections
