Laravel : How to set database connection pool?

Question

I need to connect many databases dynamically in laravel app.
How to set database connection pool?

for example,there are many second-class domain name,like this:

chicago.example.com
newyork.example.com
losangeles.example.com
...

They have separate database:

chicago
newyork
losangeles
...

I connect these databases dynamically like this:

public function store(Request $request)
{
    //post request from http://chicago.example.com/articles
    $server_name_arr=explode('.',$_SERVER['SERVER_NAME']); //the result is ['chicago','example','com']
    $db=array_slice($server_name_arr,-3,1)[0]; //the result is 'chicago'

    Config::set('database.connections.mysql.database', $db);
    DB::reconnect('mysql');

    //...
}

For performance,I want to set database connection pool,how to do it in laravel?

Any reason why you don't just have three different instances of your code for each location and just connect to the preferred database for each location? Changing the default database connection on the fly just seems problematic to me. — Devon Bessemer, May 07 '18 at 14:27
redis https://redis.io/topics/introduction / https://laravel.com/docs/5.6/redis#configuration . Check this as well https://laracasts.com/discuss/channels/eloquent/laravel-5-multiple-database-connection.As far as I know multiple db/failover db are n/a: https://github.com/laravel/framework/issues/2808 — Indra, May 07 '18 at 14:36
For performance you use cache, in order to be 99.999% up you use db-pool / failover. — Kyslik, May 07 '18 at 14:48
You need to see this : [https://stackoverflow.com/questions/39753/connection-pooling-in-php](https://stackoverflow.com/questions/39753/connection-pooling-in-php) — rrCKrr, Jan 06 '19 at 15:16

score 1 · Answer 1 · answered Dec 08 '22 at 07:15

You can define multiple Mysql connections like this in database.php

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ],
        'mysql2' => [
            'driver'    => env('DB_CONNECTION_SECOND'),
            'host'      => env('DB_HOST_SECOND'),
            'port'      => env('DB_PORT_SECOND'),
            'database'  => env('DB_DATABASE_SECOND'),
            'username'  => env('DB_USERNAME_SECOND'),
            'password'  => env('DB_PASSWORD_SECOND'),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ],

Define variables for same in .env file. Now create model with properties as below.

protected $connection = 'mysql2';
protected $table = 'tablename';

Model will refer to that particular table of remote database.

gpanagiotis · Answer 2 · 2022-11-24T17:55:37.340

If you have quite many different instances of your code and is difficult to manage the configuration (env file), you can modify your code, adding a condition to modify env file and cache the configuration if needed. This if you just need an admin functionality, it will not work if you have one instance, please look the code below.

If you have one instance and one DB server, then perhaps you can use table prefix to distinguish your tables, I mean to copy all your tables with a different prefix depending your subdomain.

But if you really need database coonection pool, there are some solutions for Laravel at Github like this , but I have never tried one.

    public function store(Request $request)
{
    //post request from http://chicago.example.com/articles
    $server_name_arr=explode('.',$_SERVER['SERVER_NAME']); //the result is ['chicago','example','com']
    $db=array_slice($server_name_arr,-3,1)[0]; //the result is 'chicago'

    $current_db = DB::connection()->getDatabaseName();
    if($db != $current_db){
       $path = base_path('.env');         
       file_put_contents($path, str_replace('DB_DATABASE=' . current_db,         
           'DB_DATABASE=' . $db, file_get_contents($path)));           
       Artisan::call('config:cache');
    }

    //...
}

Laravel : How to set database connection pool?

2 Answers2