Connecting TypeORM / node-pg to a postgres database on Heroku

I found myself deploying a side project on heroku last night and it took a lot longer than expected to simply connect to their provisioned database. I will thus list a number of issues I faced so that other people encountering the same problems will be able to hopefully save some time.

Playing Dungeons&Dragons online?

Check out my side project, Dwarven Academy!

Early-access to a new digital character sheet!

Define TypeORM environment variables

DATABASE_URL is the name of the default environment variable (or “config var” as heroku likes to call it) that contains the full database connection string. If you are running a node process you are probably already using it. However, if you run command-line scripts that require to configure TypeORM, remember that you need to define TYPEORM_URL as well! For example, in my case I needed to run migrations:

1

$ export TYPEORM_URL=$DATABASE_URL && npx ts-node ./node_modules/typeorm/cli.js migration:run

If you are wondering why the direct invocation to cli.js, it’s for window compatibility reasons. Anyhow, also remember you may need to define more environment variables to make your command-line tool behave correctly. For example:

1
2
3
4
5
6
7

TYPEORM_CONNECTION="postgres",
TYPEORM_ENTITIES="src/entities/*.ts",
TYPEORM_SUBSCRIBERS="src/subscribers/*.ts",
TYPEORM_MIGRATIONS="src/migrations/*.ts",
TYPEORM_ENTITIES_DIR="src/entities",
TYPEORM_MIGRATIONS_DIR="src/migrations",
TYPEORM_SUBSCRIBERS_DIR="src/subscribers"

Enable SSL

Error Connecting to database FATAL : no pg_hba.conf entry for host “x.x.x.x”, user “x”, database “x”, SSL off

Then, if you see the error above, it means you did not enable SSL connections in your database configuration. While developing, it’s fine not to have that enabled, however heroku has a policy that enforces all communication to its provisioned database instances to be protected by SSL encryption. To enable it:

1
2
3
4
5
6

createConnection({
    type: 'postgres',
    url: 'your connection string',
    // ...
    extra: { ssl: true },
});

1
2
3
4
5
6

createConnection({
    type: 'postgres',
    url: 'your connection string',
    // ...
    ssl: true,
});

Notice that the above is also valid if you are hard-coding your configuration in ormconfig.json. Instead, if you are using environment variables:

1

TYPEORM_DRIVER_EXTRA='{ "ssl": true }'

And if you are using node-postgres, see here.

Allow self-signed certificates

Error: self signed certificate
at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
at TLSSocket.emit (events.js:210:5)
at TLSSocket._finishInit (_tls_wrap.js:794:8)
at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
code: ‘DEPTH_ZERO_SELF_SIGNED_CERT’
}

Heroku generates self-signed certificates for its provisioned databases. As reported here, implicit disabling of unauthorized certificates has been deprecated. You either need to configure a custom certificate provided by yourself that is signed by an official certification authority, or connections will be refused. This behaviour may be disabled by changing rejectUnauthorized: false in the ssl configuration.

1
2
3
4
5
6

createConnection({
    type: 'postgres',
    url: 'your connection string',
    // ...
    extra: { ssl: true, rejectUnauthorized: false },
});

1

TYPEORM_DRIVER_EXTRA='{ "ssl": true, "rejectUnauthorized": false }'

Prevent overrides from the node TLS core module

Now, this is a painful point… the node stack itself forbids SSL connections encrypted with self-signed certificates and it overrides the behaviour of any library since it comes from the core modules. It is possible to allow them by setting NODE_TLS_REJECT_UNAUTHORIZED="0", but I would rather not. If you have time, please make sure you are using a certificate signed by a trusted CA.