Knex migration in postgres Heroku - Error: Unable to acquire connection

Question

I am trying to run my first migration which creates a single table in a Heroku postgres database.

When I try to run knex migrate:latest --env development I receive the error

Error: Unable to acquire a connection

Things I've tried:

• adding ?ssl=true to the end of my connection string stored in process.env.LISTINGS_DB_URL as I'm aware this is sometimes a requirement to connect with heroku
• setting the env variable PGSSLMODE=require

I also stumbled across this article where someone has commented that knex will not accept keys based on environment. However, I'm attempting to follow along with this tutorial which indicates that it does. I've also seen numerous other references which re-enforce that.

I'll also add that I've been able to connect to the database from my application and from external clients. I'm only encountering this error when trying to run the knex migration.

Furthermore, I've tried identifying how I can check what is being sent as the connection string. While looking at the knex documentation:

How do I debug FAQ

If you pass {debug: true} as one of the options in your initialize settings, you can see all of the query calls being made.

Can someone help guide me in how I actually do this? Or have I already successfully done that in my knexfile.js?

Relevant files:

// knex.js:

var environment = process.env.NODE_ENV || 'development';
var config = require('../knexfile.js')[environment];

module.exports = require('knex')(config);



// knexfile.js:

module.exports = {

    development: {
        client: 'pg',
        connection: process.env.LISTINGS_DB_URL,
        migrations: {
            directory: __dirname + '/db/migrations'
        },
        seeds: {
            directory: __dirname + '/db/seeds'
        },
        debug: true
    },

    staging: {
        client: 'postgresql',
        connection: {
            database: 'my_db',
            user: 'username',
            password: 'password'
        },
        pool: {
            min: 2,
            max: 10
        },
        migrations: {
            tableName: 'knex_migrations'
        }
    },

    production: {
        client: 'postgresql',
        connection: {
            database: 'my_db',
            user: 'username',
            password: 'password'
        },
        pool: {
            min: 2,
            max: 10
        },
        migrations: {
            tableName: 'knex_migrations'
        }
    }

};

Answer 1

As noted by @hhoburg in comments below, the error Error: Unable to acquire a connectionis a generic message indicating something is incorrect with Knex client configuration. See here.

In my case, Knex wasn't referencing process.env.LISTINGS_DB_URL in knexfile.js because:

• that variable was set in my .env file
• the dotenv module wasn't be referenced/called by Knex

The correct way of setting this up is detailed in the knex issue tracker here.