Configuring Free Heroku Node/PostgreSQL hosting with Knex
Code first explanation after.
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.
Turning off SSL Validation
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
sslconfiguration object if you specify thePGSSLMODEconfig 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.
Passing Heroku Connection URI
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.
Conclusion
Hope this helps!
Versions
pg: 8.5.1, knex: 0.21.17
