How to change mySql connection dynamically without adding DB connection info in config by manual

Question

Background

I plan to create a web application(serviceA) to create some records and submit them to multiple other web applications.

So I wanna switch DB connection from serviceA to others first.

Premise

I want to get table infos from other apps
After get table data, save them in DB of serviceA
Then submit data to other apps DB after switching DB
SercviceA and other apps are in same Docker networks so once execute "docker-compose up -d", containers for serviceA, other apps, DB for serviceA or other apps, nginx are build
This question is similar but not same as my question. How to use multiple databases in Laravel

Since the answer of the question is to add information to connect another DB in config/database.php or .env by manual.

But it's not my point. I wonder if we can connect another DB by using connection information without adding them in config/database.php or .env.

Because I want to save each DB connection info in ServiceA's DB and use it when trying to get table data or submit data from ServiceA.

This is almost same question but hasn't be solved: Laravel, how to append new connection in config/database.php file?

How to implement

I tried to implement as below:

Save info in DB of serviceA, to connect DB in other applications(e.g. user name or host)
Add them in "connections" in config/database.php. This is processed in save() in DatabaseController.php.
Then try to get a table structure from the other DB firstly

But it failed since it seemed I couldn't add new database connection info in config dynamically.

Code

//DatabaseController.php
public function save(Request $request)
    {
        $dbInfo = $request->all();
        $saveDatabase = $this->databaseService->save($dbInfo); //save db connection info in DB
        if ($saveDatabase) {
            $addDbInfoToConfig = $this->_addDbInfoToConfig($saveDatabase);
        }
    }

private function _addDbInfoToConfig($savedDb)
    {
        $dbName = $savedDb->database;
        $dbInfoInConfig = config("database.connections.$dbName");
        if ($dbInfoInConfig) {
            $dbInfo = [
                'driver' => $savedDb->driver,
                'host' => $savedDb->host,
                'database' => $savedDb->database,
                'username' => $savedDb->user_name,
                'password' => base64_decode($savedDb->password),
                'charset' => $savedDb->charset,
                'collation' => $savedDb->collation,
                'prefix' => $savedDb->prefix,
                'strict' => $savedDb->strict == 1 ? true : false,
            ];
            $dbInfoInConfig = Config::set("database.connections.$dbName", $dbInfo);
        }
        return $dbInfoInConfig;
    }

public function tableSetting(int $databaseId)
    {
        $tables = $this->databaseService->getTables($databaseId);
        return $tables;
    }

And this is the function to change connection and get tables.

public function getTables(int $databaseId)
    {
        $targetDb = Database::find($databaseId);
        $dbInfo = [
            'driver' => $targetDb->driver,
            'host' => $targetDb->host,
            'database' => $targetDb->database,
            'username' => $targetDb->user_name,
            'password' => $targetDb->password,
            'charset' => $targetDb->charset,
            'collation' => $targetDb->collation,
            'prefix' => $targetDb->prefix,
            'strict' => $targetDb->strict == 1 ? true : false,
        ];
        DB::connection($dbInfo);
        $tables = Schema::getAllTables();
        dd($tables);
    }

Question

I guess it's difficult to add arrays in config file dynamically. So I want to know how to switch DB connection and get table info or submit data in future.

DB::connection(...) takes a connection name, not an array of config values — lagbox
– lagbox, Commented Jan 24, 2024 at 5:37

Bill Karwin · Accepted Answer · 2024-01-24 05:28:45Z

2

You could try to use the Capsule Manager.

Refer to https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/README.md

Example from that github page:

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;

$capsule->addConnection([
    'driver' => 'mysql',
    'host' => 'localhost',
    'database' => 'database',
    'username' => 'root',
    'password' => 'password',
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
]);

Then you can use the $capsule to create instances of schemas, tables, queries, etc.

answered Jan 24, 2024 at 5:28

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Hayato Kuroda Over a year ago

Thank you sir Bill, and so sorry for replying too late. I could solve the issue with your suggestion, thank you so much🙇

Collectives™ on Stack Overflow

How to change mySql connection dynamically without adding DB connection info in config by manual

Background

Premise

How to implement

Code

Question

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Background

Premise

How to implement

Code

Question

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related