CODEWITHSUNDEEP
node.jsherokudatabase-migrationknex.jsheroku-postgres
HendPro12
HendPro12

Reputation: 1104

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

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:

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'
        }
    }

};

Upvotes: 11

Views: 19911

Answers (7)

Trinh Hieu
Trinh Hieu

Reputation: 805

I realized that in Nodejs the env variable is not readable so you need to install dotenv and configure it. I did the following steps and it worked. Hope this is helpful to anyone who is having problems

Step 1:

npm i dotenv --save

Step 2: Create .env file and add variables

//.env
DATABASE_URL=postgresql://postgres:[email protected]:5432/db_name

Step 3: add dot env configuration

//server.js
require("dotenv").config();

Upvotes: 0

ritz
ritz

Reputation: 5706

I got this error when trying to update data to a database before running corresponding migration.

Upvotes: 0

Yunat Amos
Yunat Amos

Reputation: 99

if you are getting this error in nodejs try removing this line

myDb.destroy().then();

Upvotes: 1

undefinedindustries
undefinedindustries

Reputation: 1

I received this same error in the same situation. Turns out I forgot to provision a database before migrating, so there was nothing to connect to.

To fix this error,

Before running: 

heroku run knex migrate:latest

I ran this command: 

heroku addons:create heroku-postgresql

and that worked nicely.

Upvotes: 0

upupming
upupming

Reputation: 1447

Step 1:

First install dotenv: 

npm i dotenv --save

Create a .env file in the root of your project, add:

DATABASE_URL=postgres://...

Step 2:

In the beginning of your knexfile.js, add: 

require('dotenv').config();

Change the postgres connection to something like:

{
  client: 'postgresql',
  connection: process.env.DATABASE_URL,
  pool: {
    min: 0,
    max: 15
  },
  migrations: {
    directory: ...
  },
  seeds: {
    directory: ...
  }
}

Upvotes: 3

HendPro12
HendPro12

Reputation: 1104

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.

Upvotes: 12

hhoburg
hhoburg

Reputation: 383

I'm not sure if this will help at all, but I began running into the same issue today on my local environment. After way too much searching, I found that this is the new error message for an invalid connection configuration or a missing connection pool. After fiddling around with it for way too long, I switched my connection to use my .env file for the configuration environment; I had been using a hard-coded string ('dev') in my knex.js file, which didn't work for some reason.

Is your .env file working properly? Did you try messing with the pool settings, and are you positive your username and password are correct for the staging and production database?

I hope that link helps!

Upvotes: 2

Related Questions