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
– Devon Bessemer, Commented May 7, 2018 at 14:27
redis redis.io/topics/introduction / laravel.com/docs/5.6/redis#configuration . Check this as well laracasts.com/discuss/channels/eloquent/… far as I know multiple db/failover db are n/a: github.com/laravel/framework/issues/2808 — Indra
– Indra, Commented May 7, 2018 at 14:36
For performance you use cache, in order to be 99.999% up you use db-pool / failover. — Kyslik
– Kyslik, Commented May 7, 2018 at 14:48
You need to see this : https://stackoverflow.com/questions/39753/connection-pooling-in-php — rrCKrr
– rrCKrr, Commented Jan 6, 2019 at 15:16

Nayan Thakkar · Accepted Answer · 2022-12-08 07:15:47Z

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.

İlker Ergün · Accepted Answer · 2024-06-04 08:05:22Z

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 the 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 at 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 on your subdomain.

But if you really need database connection 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');
    }

    //...
}

Collectives™ on Stack Overflow

How to set database connection pool?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related