7

I'm building an application which requires connecting 2 database. first one is static and another one is dynamic. config/database.php is like

'mysql' => 
array (
  'driver' => 'mysql',
  'host' => '127.0.0.1',
  'port' => '3306',
  'database' => 'blog',
  'username' => 'root',
  'password' => '',
  'unix_socket' => '',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
  'prefix' => '',
  'strict' => true,
  'engine' => NULL,
),
'business2' => 
array (
  'driver' => 'mysql',
  'host' => '127.0.0.1',
  'port' => '3306',
  'database' => 'blog2',
  'username' => 'root',
  'password' => '',
  'unix_socket' => '',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
  'prefix' => '',
  'strict' => true,
  'engine' => NULL,
),

and model code is like 

Class  TableNewData extends Model
{
    protected $connection = 'business3';
    protected $table = 'table2_data';
    public function getData()
    {
        return $this->get()->toArray();
    }
}

I am able to connect multiple databases if I give static connection details but I am unable to connect database if I give dynamic connection details like

 $connection = Session::get()->connection;

or

$connection=$_SESSION('connection');

What is the best way to connect multiple databases dynamically without effecting performance of application?

Improve this question
8
  • try setConnection method $someModel->setConnection($connection); Commented Jun 28, 2018 at 4:51
  • throwing error Class App\Models\TableNewData does not exist Commented Jun 28, 2018 at 4:54
  • don't you have app\Models\TableNewData.php class ? check class namespace Commented Jun 28, 2018 at 4:55
  • Yes I have the class and I am getting the above error only after placing setConnection method Commented Jun 28, 2018 at 4:57
  • Try running composer dumpautoload to for the class not existing exception. Commented Jun 28, 2018 at 5:05

2 Answers 2

Reset to default
9

I had the same problem as you. This blog can definitely help you out.

The Ultimate Guide for Laravel Multi Tenant with Multi Database

Here is how the config/database.php file looks like based on your situation. Since the second one is dynamic, there is no need to define the database.

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'blog'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', 'password'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => 'InnoDB',
    ],
'business' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => '',
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', 'password'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => 'InnoDB',
    ],

Basically, set up a database helper function which connects to the database at runtime and then calls it in the right middleware.I just put the helper file at database/utilities/helpers.php

function connect($hostname, $username, $password, $database)
{
    // Erase the tenant connection, thus making Laravel get the default values all over again.
    DB::purge('business');
    // Make sure to use the database name we want to establish a connection.
    Config::set('database.connections.tenant.host', $hostname);
    Config::set('database.connections.tenant.database', $database);
    Config::set('database.connections.tenant.username', $username);
    Config::set('database.connections.tenant.password', $password);
    // Rearrange the connection data
    DB::reconnect('business');
    // Ping the database. This will throw an exception in case the database does not exists.
    Schema::connection('tenant')->getConnection()->reconnect();
}

Don't forget to tell the composer that the helper function can be used globally by adding those line into the composer.json file.

"autoload": {
    "classmap": [
        "database"
    ],
    "files":[
        "database/utilities/helpers.php"
    ],
    "psr-4": {
        "App\\": "app/"
    }
},

You also want to have static and dynamic models that should be extended to define which database connections to use.

class StaticModel extends Model
{
    protected $connection = 'mysql';
}
class DynamicModel extends Model
{
    protected $connection = 'business';
}

In the middleware set up the dynamic database connection according to the database name.

connect(getenv('DB_HOST'), getenv('DB_USERNAME'), getenv('DB_PASSWORD'), getenv('DB_SYMBOL') . $databasename);

Thus, you can use the model as normal but it has the dynamic database connections

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

Comments

4

One way of changing the connection at runtime is to set the values via the config:

config(['database.connections.mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'my_database'),
    'username'  => env('DB_USERNAME', 'my_user'),
    'password'  => env('DB_PASSWORD', 'my_password'),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
]]);

This can be applied in a middleware to dynamically switch between tenant databases, for example.

You can also specify a connection via the DB facade:

DB::connection('mysql_2')->select(...);
Improve this answer

6 Comments

Where I have to keep the code? In config/database.php or model or middleware? I have 100+ databases and everytime I have to connect 1 static database and another one is one in remaining 100 databases
You're saying each database has it's own unique connection credentials? If that's the case, I would store them in a database and do a lookup to find the necessary username/password. I've seen this approach done in large SAS applications with 1000s of customers, each with their own database.
Yes. primary database will have username/password and connection name. I am not storing credential details in database but keeping them in config/database.php file
Storing connection details in database or code is not my problem. I am facing issue with calling that data dynamically. If I give connection details statically it is working fine but I am unable to give connection details dynamically
That's what I'm showing with the config(...) in my answer. Every time a request comes in, use the config helper to set the connection details. This will set the values in the container and you won't need to call setConnection or anything else. This is just an example where default is mysql in config/database.php.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.