Possible to Dynamically Connect to Postgres DB from Laravel without using Config ? See Example-CodePudding

I have a situation where I'd like to dynamically choose the DB host for postgres DB connections in a Laravel application running in Docker. I don't particularly want to use ENV values or the config/database.php file because it is a one-off query to the external DB that I would prefer to embed in Helper Class.

These actually could go in the .env file:

PSQL_DB_PORT=5432
PSQL_DB_DATABASE=postgres
PSQL_DB_USERNAME=postgres
PSQL_DB_PASSWORD=postgres

and in my helper I can get the hostname for the remote (actually in Docker) postgres server, which will basically be something like:

postgres_index-db1, postgres_index-db2, postgres_index-db3, etc., but I need to fetch that dynamically and store it in a variable $host.

I tried playing around with a thing like this:

    DB::disconnect('pgsql');
    Config::set("database.connections.pgsql", [
    
                'driver' => 'pgsql',
                'host' => $host ,
                'port' => env('PSQL_DB_PORT', '5432'),
                'database' => env('PSQL_DB_DATABASE'),
                'username' => env('PSQL_DB_USERNAME'),
                'password' => env('PSQL_DB_PASSWORD'),
                'charset' => 'utf8',
                'prefix' => '',
                'prefix_indexes' => true,
                'schema' => 'public',
                'sslmode' => 'prefer',
    ]);
    dd(DB::connection('pgsql'));
    $query = "select DISTINCT value from maindicomtags where taggroup = 8 and tagelement = 128";
    $names = DB::connection('pgsql')->select($query,[]);

and the dd(DB::connection('psql')) (a Laravel dump) statement actually shows the correct config, but when the query tries to execute it must be looking for the pgsql config in my config/database.php file because it throws an error:

Database connection [pgsql] not configured

because it must be looking for the config in the config/database.php file, and I removed the definition from there. I want it to use the connection that is defined above.

Is there a a way to do what I am trying to do. I could even just use the connection string for Postgres if that is possible.

Might actually be working because I might have had a typo earlier (e.g. psql vsl pgsql).

Does this seem like an adequate way to make that type of dynamic connection ? It is really just the host that will be different.

CodePudding user response：

Instead of setting your config, just create a custom database connection using factory. The only downside - you will not be able to use Eloquent as it resolves connection by its name defined in configs. Also, avoid using env variables anywhere else than config files - create other config and use it instead. There is quite a chance something could break somewhere else if you change config on the go:

$factory = app(\Illuminate\Database\Connectors\ConnectionFactory::class);

$db = $factory->make([
    'driver' => 'pgsql',
    'host' => $host ,
    'port' => $port ?? 5432,
    'database' => $database,
    'username' => $username,
    'password' => $password,
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'schema' => 'public',
    'sslmode' => 'prefer',
]);

$names = $db->table('maindicomtags')
    ->where('taggroup', 8)
    ->where('tagelement', 128)
    ->distinct()
    ->pluck('value');

$db->disconnect();