Configuring Free Heroku Node/PostgreSQL hosting with Knex

Code first explanation after.

an elephant, symbolizing postgresql
In your server knexfile, pass the connection string and a flag to turn off SSL validation

This can be found on the Heroku docs, but I still lost time getting it to work with Knex. While debugging, my errors included heroku run knex migrate Error: self signed certificate, Knex: Timeout acquiring a connection, and error: no pg_hba.conf entry for host “ip_address”, user “username”, database “database”, SSL off.

There are two concerns here, turning off SSL and passing the connection URI intact.

Because node-postgres enables SSL validation by default while free heroku hosting doesn’t provide it automatically, you need to turn it off. Knex can pass down configuration to node-postgres through the ssl property which also can pass down properties to node. The node docs entry for rejectUnauthorized can be found here. Additionally you can turn off SSL through the Heroku CLI:

Alternatively, you can omit the ssl configuration object if you specify the PGSSLMODE config var: heroku config:set PGSSLMODE=no-verify.

Downside of this is that some code examples show no SSL config. Its best to explicitly declare it in code.

You can have manual SSL on free heroku hosting, but this article doesn’t cover it.

Heroku provides a config var called DATABASE_URL when the postgres add-on is installed. It updates dynamically and contains all the connection information (host, user, password, database, port). Knex allows you to add a connection string directly on the connection property, ignore this. Knex doesn’t mention it, but you can also add the connection string on the connection object through the connectionString property. This is found on the node-postgres docs; node-postgres parses it into its components and passes it to node.

Some code examples I’ve seen have shown no SSL config and the string on the Knex connection property directly, which was the source of some confusion.

Hope this helps!

pg: 8.5.1, knex: 0.21.17

fresh out of webdev bootcamp, passing it forward