How to resolve connection error to PostgreSQL from Knex.js on Heroku

This is my first technical write-up to keep a record on an issue I ran into when trying to deploy a full-stack app to Heroku during a student project at Code Chrysalis.

TLDR

Easiest way to resolve the issue is to add the SSL setting on your Knex connection by parsing the given environment variable as follows.

The solution also appears in a Heroku support document:

Reproduction of the Issue

  1. Attach a Heroku Postgres service to the app
  2. Deploy the app and run PostgreSQL connection from Knex (here I ran an migration)
  3. App crashed with following error messages:

On the app log

heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=XXXXXX.herokuapp.com request_id=bb768ca2-b16c-462a-8424-b8408ffb1cd2 fwd="XXX.XXX.XXX.XXX" dyno= connect= service= status=503 bytes= protocol=https

On Heroku console when I tried npm run start manually

Running migrations
Error starting app! error: no pg_hba.conf entry for host “XXX.XXX.XXX.XXX”, user “XXXXXXXX”, database “XXXXXXXX”, SSL off

Resolving the issue

// in your knexfile.js// Should come with install of pg 
const parse = require("pg-connection-string").parse;
// Parse the environment variable into an object
const pgconfig = parse(process.env.DATABASE_URL);
// Add SSL setting to default environment variable
pgconfig.ssl = { rejectUnauthorized: false };
const db = knex({
client: "pg",
connection: pgconfig,
});

Logically using the broken down credentials as follows should also work but I failed to made this functioning. Just for your reference.

const db = knex({  
client: "pg",
connection: {
host : 'your_database_host',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test',
ssl: { rejectUnauthorized: false },
});

Relevant Scenarios

Scenario 1

heroku config:set PGSSLMODE=no-verify -a YOUR_APP_NAME

Result: Does not fix the issue on my first attempt, but working when I tried on a different project. It can be a simpler solution yet there’s a chance of unpredictable result.

Scenario 2

heroku config:set PGSSLMODE=require -a YOUR_APP_NAME

Result:

If forcing SSL verification without adding the SSL setting. The following error will appear.

Running migrations
Error starting app! Error: self signed certificate

Adding pgconfig.ssl = { rejectUnauthorized: false }; will solve the issue. Hence setting PGSSLMODE=require is not required but will also not hurt here.

Aspiring full-stack software engineer