Connecting Postgres Heroku with Knex not working

Question

We're having difficulty setting up a Heroku/Postgres database with Knex running our queries. We've set up our heroku/postgres db and created our tables, but we're having difficulty connecting to it with Knex. To make matters worse, there's almost no documentation on connecting Heroku/Postgres with Knex or any ORM so trying to figure this stuff out has been a real pain.

These are the connection patterns that I've tried.

var knex = require('knex')({
  client: 'pg',
  connection: {
    user: username,
    password: password,
    host: host,
    port: port,
    database: database,
    ssl: true
  }
  }
});

And... Note the ssl true was toggle and removed all together to no avail.

  var knex = require('knex')({
    client: 'pg',
    connection: HEROKU_POSTGRESQL_COLOR_URL,
    ssl: true
    }
  });

We've also tried this pattern as well:

var pg = require('knex')({
  client: 'pg',
  connection: HEROKU_POSTGRESQL_COLOR_URL
 });

We haven't yet pulled down a copy of our localdb, so every test we run is basically a git commit. We're basically testing an insert query on a GET request to our root (index.html) page. So on any get request to the main page, it should insert something into our waterrates table. If I switch it from insert to select, it returns an object but you can't actually see any of the data in the object.

The inserts we're attempting to use are:

knex.select('*').from('waterrates').then(function(rows){
  return rows;
});

knex('waterrates').insert({name: 'pleeeaseee work'}, {rate: 100}).then(function(rows){
   console.log(rows);
})

knex.select().

We're actually uncertain where the error could be as attempting to connect doesn't yield any errors. It's probably something silly, but we have no idea where/how to troubleshoot this. Any help would be greatly appreciated!

Thanks, B

Answer 1

I had an old version of PG installed which was causing the issue. We changed our package.json file to use the latest PG. Heroku updated it and it worked!

As a side note, if anyone looks at this in the future, heroku requires an SSL connection. Keep that in mind when you're working. The connection string that I provided above should work for you all.

B

Answer 2

For anyone who is dealing with this problem.

Please set "SSL" to false if you don't use paid dynos.

Heroku only assigns "SSL" to users who use paid dynos.

const db = knex({
  client: 'pg',
  connection: {
    connectionString : process.env.DATABASE_URL,
    ssl: false
  }
});