Search code examples
postgresqlexpressherokuknex.js

Heroku can't connect with Postgres DB/Knex/Express

I have an Express API deployed to Heroku, but when I attempt to run the migrations, it throws the following error:

heroku run knex migrate:latest Running knex migrate:latest on ⬢ bookmarks-node-api... up, run.9925 (Free) Using environment: production Error: connect ECONNREFUSED 127.0.0.1:5432 at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1117:14)

In my knexfile.js, I have:

production: {
    client: 'postgresql',
    connection: {
      database: process.env.DATABASE_URL
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      directory: './database/migrations'
    }
  }

I also tried assigning the migrations directory to tableName: 'knex_migrations' which throws the error:

heroku run knex migrate:latest Running knex migrate:latest on ⬢ bookmarks-node-api... up, run.7739 (Free) Using environment: production Error: ENOENT: no such file or directory, scandir '/app/migrations'

Here is the config as set in Heroku:

-node-api git:(master) heroku pg:info
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            10.7
Created:               2019-02-21 12:58 UTC
Data Size:             7.6 MB
Tables:                0
Rows:                  0/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported

I think the issue is that for some reason, it is looking at localhost for the database, as if the environment is being read as development though the trace shows Using environment: production.

Solution

  • When you provide an object as your connection you're providing individual parts of the connection information. Here, you're saying that the name your database is everything contained in process.env.DATABASE_URL:

    connection: {
  database: process.env.DATABASE_URL
},

    Any keys you don't provide values for fall back to defaults. An example is the host key, which defaults to the local machine.

    But the DATABASE_URL environment variable contains all of the information that you need to connect (host, port, user, password, and database name) in a single string. That whole value should be your connection setting:

    connection: process.env.DATABASE_URL,