using multiple databases but getting error when connecting to secondary database

I'm making a command to connect to a secondary database and have this code in a function:

public function updateDatabase()
  { 
$datos = \DB::connection('pgsql_vitrina')
    ->select('SELECT
               .... redacted ....
            ');

in the database.php file in the connections array there is the following:

'pgsql_vitrina' => [
    'driver' => 'pgsql',
    'url' => env('DATABASE_URL_VITRINA', ''),
    'host' => "localhost",
    'port' => env('DB_PORT_VITRINA', ''),
    'database' => env('DB_DATABASE_VITRINA', ''),
    'username' => env('DB_USERNAME_VITRINA', ''),
    'password' => env('DB_PASSWORD_VITRINA', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'schema' => 'public',
    'sslmode' => 'prefer',
],

In the .env file there is also this:

DATABASE_URL_VITRINA=localhost
DB_HOST_VITRINA=localhost
DB_PORT_VITRINA=5432
DB_DATABASE_VITRINA=vitrina
DB_USERNAME_VITRINA=postgres
DB_PASSWORD_VITRINA=1234

And the error I'm getting is this:

  SQLSTATE[08006] [7] FATAL: the database ocalhost doesn't exists
 Database\Connectors\Connector.php:78
   PDO::__construct("pgsql:host=localhost;dbname='ocalhost';port=5432;sslmode=prefer", "postgres", "1234", [])

I have already run the commands php artisan optimize, artisan cache:clear, artisan config:clear and still it doesn't work. any ideas?

LaryAI

3 years ago

Level 58

It looks like there is a typo in the database URL in the error message. Instead of "localhost", it says "ocalhost". Double-check the values in the .env file to make sure they are correct.

Also, try specifying the database name directly in the connection configuration instead of using the environment variable. Change this line:

'database' => env('DB_DATABASE_VITRINA', ''),

to:

'database' => 'vitrina',

If that doesn't work, try connecting to the database using the PDO driver directly to see if there are any other errors. Here's an example:

$pdo = new PDO('pgsql:host=localhost;dbname=vitrina;port=5432;sslmode=prefer', 'postgres', '1234');
$stmt = $pdo->query('SELECT * FROM some_table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($results);

If you still can't connect, make sure that the PostgreSQL server is running and that you have the correct permissions to access the database.

Pippo

3 years ago

Level 2

@dru you say that you are using multiple connections: chek the .env for unwanted duplicates entries for DB_DATABASE_VITRINA

1 like

dru

3 years ago

Level 3

Hi @Pippo, I just checked and there aren't duplicates. or any other with the suffix _VITRINA

Pippo

3 years ago

Best Answer

Level 2

@dru ok, that was just a try, I've had similar problems in the past. Try to comment DATABASE_URL_VITRINA in .env:

#DATABASE_URL_VITRINA=localhost

and remove default value in 'pgsql_vitrina' configuration:

'pgsql_vitrina' => [
    'driver' => 'pgsql',
    'url' => env('DATABASE_URL_VITRINA'),
   /* ... */
],

1 like

dru

3 years ago

Level 3

Hi @Pippo , thanks! Only needed to comment the database_url_vitrina from the .env at it started working.... by why did it work now?

Pippo

3 years ago

Level 2

@dru 'url' key in configuration array ('pgsql_vitrina' in your case) must contains a complete PDO connection string and is used for particualar situation in alternative to single components (host, port, database...) - i think it ovverrides them

1 like

Please or to participate in this conversation.