Problems connecting codeigniter 4 with postgres - CodeIgniter\Database\Exceptions\DatabaseException #8

Question

I want to make a project with codeigniter 4 and postgresql I have already seen several examples, I even read the documentation but it still shows an error when connecting to the database.

My file .env is like this:

# database.tests.hostname = localhost
# database.tests.database = postgres_test
# database.tests.username = test
# database.tests.password = test123
# database.tests.DBDriver = postgre

And my file config\App.php

public $tests = [
        'DSN'      => 'pgsql:host=localhost;port=5432;dbname=database_name',
        'hostname' => 'localhost',
        'username' => 'test',
        'password' => 'test123',
        'database' => 'postgres_test',
        'DBDriver' => 'postgre',
        'DBPrefix' => 'db_',  
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'production'),
        'cacheOn'  => false,
        'cacheDir' => '',
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 5433,
    ];

I don't know what I'm doing wrong, I understand that the .env file overwrites the config\App.php configuration, and gives me the error CodeIgniter\Database\Exceptions\DatabaseException #8 and show throw new DatabaseException('Unable to connect to the database.');

Someone who knows more about the subject who can help me would greatly appreciate it.

Answer 1

Your .env file will overwrite the config/App.php settings. However you are setting the values on commented items.

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

# database.default.hostname = localhost
# database.default.database = ci4
# database.default.username = root
# database.default.password = root
# database.default.DBDriver = MySQLi

# database.tests.hostname = localhost
# database.tests.database = ci4
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi

All the values that start with # are commented. You need to remove it. However like you see there are two database groups. One for running unit test the other to run your app. The default one runs your app. That's the one you need to uncomment. Like so:

database.default.hostname = localhost
database.default.database = postgres_test
database.default.username = test
database.default.password = test123
database.default.DBDriver = Postgre

Note that I wrote the Postgre in uppercase as is that is how is referenced in the docs.

For more information on this subject you can find more here:

https://codeigniter.com/user_guide/database/configuration.html?highlight=env#configuring-with-env-file

Answer 2

I used codeigniter 4 and postgres 10.

In the php configuration, make sure you enable the pgsql extension

then you can use this .env setting

or if you want to edit directly in the database.php configuration, leave the DSN blank

Answer 3

For PHP 7.4+, did you try to connect to database without CI, to make sure you installed the database correctly?

You can try this to check the connection:

# test_postgre.php

$host = 'localhost';
$db = 'yourdbname';
$user = 'yourdbuser';
$pass = 'yourdbpass';
$port = '5432';

$db_handle = pg_connect("host={$host} port={$port} dbname={$db} user={$user} password={$pass}");

if ($db_handle) {
    echo "\nConnection attempt succeeded. \n\n";
} else {
    echo "\nConnection attempt failed. \n\n";
}

echo "Connection Information\n";
echo "======================\n\n";

echo "DATABASE NAME:" . pg_dbname($db_handle) . "\n";
echo "HOSTNAME: " . pg_host($db_handle) . "\n";
echo "PORT: " . pg_port($db_handle) . "\n\n";

Then go to your terminal, and run:

php test_postgres.php

If you can put this file in your public folder, you can run from your browser also.