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 the
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.
Hope this helps!
pg: 8.5.1, knex: 0.21.17
Heroku Postgres — Connecting in Node JS
Heroku Postgres is a managed SQL database service provided directly by Heroku. You can access a Heroku Postgres…
Announcements — pg defaulting to SSL enabled
firstname.lastname@example.org is being released which contains a handful of breaking changes. I will outline each breaking change here and try…
Heroku SSL — provides free automated SSL to all paid dynos
Heroku provides free Automated Certificate Management (ACM) for all applications running on paid dynos in the Common…
Node.js v15.9.0 Documentation — rejectUnauthorized property
The TLS/SSL is a public/private key infrastructure (PKI). For most common cases, each client and server must have a…