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

As of March, 2021, the default environment variable provided by Heroku for it’s built-in Postgres service has a mismatch on SSL settings which leads to crash of deployed app when Knex tries to connect to PostgreSQL.

Reproduction of the Issue

  1. Create an app on Heroku
  2. Attach a Heroku Postgres service to the app
  3. Deploy the app and run PostgreSQL connection from Knex (here I ran an migration)
  4. App crashed with following error messages:
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
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

I have tried multiple ways in resolving the issue and the shortest path turns out to be adding a SSL setting to Knex connection setting by parsing the environment variable.

// 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,
});
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

During my trial and error in resolving the issue, I ran into the following situations as well. You might find some useful information here if you are facing a different error from the above one.

Scenario 1

I followed the following Heroku documentation to deactivate SSL verification via the following Heroku command in Heroku CLI

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

Scenario 2

I saw a stackoverflow suggestion to force enable SSL verification via the following Heroku command in Heroku CLI and I tried it.

heroku config:set PGSSLMODE=require -a YOUR_APP_NAME
Running migrations
Error starting app! Error: self signed certificate

Aspiring full-stack software engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store