Laravel: connect to databases dynamically

Question

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!

Undernourished

@PhiterFernandes administrators of the system can fill in credentials, which are being encrypted and stored in a database for later use. – Undernourished

Answer 1

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.

Korea

@Undernourished you're welcome. this remains true for all config loaded from the /config directory. it's loaded during bootstrap, and can be changed anytime. – Korea

Is it also possible with this to add a connection instead of only changing a loaded one? – Undernourished

@Undernourished I believe so, yes. Though the way I do it is to have a predefined connection in the config file, with empty values. Then I set the values at runtime. – Korea

I'll try it out setting databases at runtime. I cannot be sure how much connections i have to open each pageload, so predefining is kind-of impossible. Thanks for your answer again! – Undernourished

@Undernourished you'll be using multiple connections... at the same time? I connect to over 200 databases myself, but only one at a time. So I just change the same connection config array. – Korea

Let us continue this discussion in chat. – Undernourished

Sorry to reopen old threads, how does migration work with this? – Cachou

@DanielCasserly should work just fine, so long as you are setting up the database config in a service provider so that it is ready when Laravel runs your migrations. I'm using migrations with custom database settings this way, no problem. – Korea

@Korea Fairly new to the laravel ecosystem. Since you mentioned that you connect to 200 different databases wanted to understand the performance impact of doing this. I am assuming the impact should be more if laravel is caching database connections. – Centenarian

@Centenarian I don't connect to 200 at the same time. It's always a single DB connection per PHP request. Also, all 200 databases are running on the same AWS RDS database server. So the hostname is the same, as are the credentials themselves. Only the database name changes. We haven't experienced any performance issues at all with this approach. – Korea

@Korea Understood. So you are changing the Database name using the code snippet you posted in this answer. – Centenarian

@Korea Are you connecting to two or more databases for any purpose during a single request? If you are, can post example code on how that is being done? Does it require any config changes in .env file? – Centenarian

Answer 2

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');

Inlier

I was missing DB::purge( ... ); – Scalf

Answer 3

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,
        ]);

Thermolysis

Answer 4

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);

Groomsman

I was trying to change PostgreSQL schemas dynamically and your solution helped me lot! Thank you so much! – Flash

Answer 5

//appServiceProvider.php

 Connection::macro('useDatabase', function (string $databaseName) {
            $this->getPdo()->exec("USE `$databaseName`;");
            $this->setDatabaseName($databaseName);
    });

//usage.

  \DB::connection()->useDatabase($dbName);

Rubetta

Answer 6

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";
}

Platelet

Answer 7

// 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();

Lallans

Answer 8

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

Cask

This is not valid PHP syntax. – Undernourished