New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Heroku PostgreSQL connection problem #239

Closed
tybenz opened this Issue Apr 10, 2014 · 10 comments

Comments

Projects
None yet
5 participants
@tybenz
Copy link

tybenz commented Apr 10, 2014

Trying to do something similar to #47 (connect to postgres database on heroku). I tried to set up my connection config with the host, user, password, and database name from the $DATABASE_URL environment variable.

I've tried both the EC2 url and the IP address as the host.

When I set up the model node and call save, the record should be saved to the database. When I connect to psql via heroku pg and query the users table it's empty.

The example uses Bookshelf. Let me know if I should file the issue under that repo. Although my understanding is that the connection config just gets passed to Knex.

var Bookshelf = require( 'bookshelf' );

Bookshelf.PG = Bookshelf.initialize({
  client: 'pg',
  debug: true,
  connection: {
    host     : process.env.PG_HOST || 'localhost',
    user     : process.env.PG_USER || 'postgres',
    password : process.env.PG_PASSWORD || 'postgres',
    database : process.env.PG_DB || 'db',
    charset  : 'utf8'
  }
});

var User = Bookshelf.PG.Model.extend({
  tableName: 'users',
  idAttribute: 'id'
});

var u = User.forge({email: 'test@fake.com', password: 'test'});
u.save();
@tgriesser

This comment has been minimized.

Copy link
Owner

tgriesser commented Apr 10, 2014

What does

u.save().then(function(resp) {
  console.log(resp);
}).catch(function(e) {
  console.log(e.stack);
});

yield as the error for you?

Also if you replace the connection object with just the $DATABASE_URL, does that work?

@tybenz

This comment has been minimized.

Copy link
Author

tybenz commented Apr 10, 2014

It actually doesn't add anything to the logs. No resp. No e.stack.

Also, I wasn't aware you could set the $DATABASE_URL string as the connection setting. Tried it. But it also did not work.

I must be doing something painfully obvious that's messing me up here.

@tybenz

This comment has been minimized.

Copy link
Author

tybenz commented Apr 10, 2014

Found it! I forgot the process.exit(); that I had included at the end of my script when I copy/pasted into this issue.

I wasn't comprehending that the db insertion was async. Took that out, let it run longer and it did in fact work... So it's ok to use the DATABASE_URL string as the connection setting?

@tybenz tybenz closed this Apr 10, 2014

@tgriesser tgriesser added the question label Apr 10, 2014

@tgriesser

This comment has been minimized.

Copy link
Owner

tgriesser commented Apr 10, 2014

Yep, should work fine, the connection object just gets passed along to the underlying db driver by knex, in your case the pg module, and that accepts a connection string.

@jede

This comment has been minimized.

Copy link

jede commented May 6, 2014

I ended up here struggling with wierd connection errors on Heroku. My issue was that the connection string given by Heroku doesn't have "?ssl=true" in the end even though Heroku requires it. This can be solved by "heroku config:set PGSSLMODE=require" which will tell the pg adapter to use SSL if the config doesn't say anything about it. It could be the case that pg.Client and pg.connect doesn't behave completely the same.

@kevbook

This comment has been minimized.

Copy link

kevbook commented Sep 29, 2014

@jede I tried running a script locally and connect to heroku pg, nothing happens. What am i doing wrong?

 var pg = require('pg'),
  knex = require('knex');

var url = 'postgres://user:pass@host:5432/db';
  var knex = require('knex')({
    client: 'pg',
    debug: true,
    connection: url + '?ssl=true'
  });

  knex.schema.hasTable('users', function(done) {
    console.log(done)
  });
@tgriesser

This comment has been minimized.

Copy link
Owner

tgriesser commented Sep 29, 2014

@kevbook that script doesn't actually do anything because there is nothing coercing the query into actually running (no exec, then, etc.)...

Try it with

  knex.schema.hasTable('users', function(done) {
    console.log(done)
  }).then();

and see if anything happens.

@kevbook

This comment has been minimized.

Copy link

kevbook commented Sep 29, 2014

Ah, okay. I am so stupid. Thank you.

@sahat sahat referenced this issue Jul 7, 2016

Closed

Postgresql Heroku #151

@liamhession

This comment has been minimized.

Copy link

liamhession commented Mar 22, 2018

If anyone like me ends up here, if you're using connection and a full postgres://user:pass@host... to specify where to connect to, and are throwing sslmode=require and/or ssl=true onto the end and it's not working: you have to split the connection string into its components, so that your connection configuration object has host: 'host' etc. and then the sslmode: 'require' option will work

@liamhession

This comment has been minimized.

Copy link

liamhession commented Mar 22, 2018

And also it was necessary to set PGSSLMODE=require in the environment where the knex connection was started

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment