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

Question

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.

PunyFlash · Accepted Answer · 2022-07-12 21:07:43Z

1

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();

edited Jul 12, 2022 at 21:07

answered Jul 12, 2022 at 18:38

PunyFlash

1,0861 gold badge6 silver badges12 bronze badges

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

5 Comments

SScotti Over a year ago

I'll give that a try. That is pretty much when I am looking for. Do I need to destroy or close the connection when done or will that just happen automatically ? I basically want to create it, do what I need, and then throw it away.

SScotti Over a year ago

BTW, I see you work with Laravel and Vue. Is there a way to get in touch with you outside of SO ?

PunyFlash Over a year ago

Yea, as long as your connection is not persistent, would be better to disconnect from the database when you are done. Sorry, but I'm not really into personal consulting and would prefer to help people when I'm in the mood to help.

SScotti Over a year ago

Perfect. Just what I needed. See you around then. I actually spent about 4 months in Ukraine back in 2017-18.

SScotti Over a year ago

One last question regarding that. I would prefer that that connection be 'read-only'. It might be moot because I am just executing SELECT statements using that connection, but I think I might be encountering some DB locks, serialization errors with retries sometimes when I access that DB. It looks like there might be a way to do that in Laravel such that a write simply won't execute, but seems to work fine so far.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related