Postgresql with Laravel

By | 31/10/2019

I love Postgres and it comes out the box with Rails however it took some configuring in Laravel.

Open up your project in your favourite code editor.

Head to the config folder then open the database.php file

First off tell the application to use postgresql as the database by changing

'default' => env('DB_CONNECTION', 'mysql') 
to 
'default' => env('DB_CONNECTION', 'pgsql')

Scroll down and you’ll see the psql database connection settings which you’ll no doubt change when deploying… or will I, I’ve not got that far yet!

'pgsql' => [
    'driver' => 'pgsql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'schema' => 'public',
    'sslmode' => 'prefer',
]

I changed these to match my local settings hover couldn’t get it to connect to my local psql database! It turns out I need to edit the .env file. I should’ve know as I use dotenv in rails to set environment variables – why would it be any different?

Open your .env file in the root of the application and set the following to match your psql database connection details:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=YOUR DATABASE NAME
DB_USERNAME=YOUR LOCAL DATABASE USERNAME
DB_PASSWORD=YOUR LOCAL DATABASE PASSWORD

Rails has a console… where’s the laravel console – turns out it’s called tinker! head to the terminal and run

$ php artisan tinker

enter the following at the prompt

>>>> DB::connection()->getPdo();

You’ll see the following output if all is good.

=> PDO {#2983
inTransaction: false,
attributes: {
CASE: NATURAL,
ERRMODE: EXCEPTION,
PERSISTENT: false,
DRIVER_NAME: "pgsql",
SERVER_INFO: "PID: 34943; Client Encoding: UTF8; Is Superuser: off; Session Authorization: php_storm_admin; Date Style: ISO, MDY",
ORACLE_NULLS: NATURAL,
CLIENT_VERSION: "11.5",
SERVER_VERSION: "11.5",
STATEMENT_CLASS: [
"PDOStatement",
],
EMULATE_PREPARES: false,
CONNECTION_STATUS: "Connection OK; waiting to send.",
DEFAULT_FETCH_MODE: BOTH,
},
}

One of the really useful tools in Rails was the console, you can access the equivalent in laravel by running

$ php artisan tinker

You’ll be greeted by a different console – try running

>>> DB::connection()->getPdo();

If all goes well scroll to the bottom of the output and you shoul see:

CONNECTION_STATUS: “Connection OK; waiting to send.”

That’s it – we’re ready to go.