Skip to content
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 · 11 comments
Closed

Heroku PostgreSQL connection problem #239

tybenz opened this issue Apr 10, 2014 · 11 comments
Labels

Comments

@tybenz
Copy link

@tybenz 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
Copy link
Member

@tgriesser 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
Copy link
Author

@tybenz 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
Copy link
Author

@tybenz 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?

@tgriesser
Copy link
Member

@tgriesser 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
Copy link

@jede 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
Copy link

@kevbook 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
Copy link
Member

@tgriesser 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
Copy link

@kevbook kevbook commented Sep 29, 2014

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

@liamhession
Copy link

@liamhession 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
Copy link

@liamhession liamhession commented Mar 22, 2018

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

@olso
Copy link

@olso olso commented Jun 9, 2020

import knex, { Config } from "knex";
// @ts-ignore
import knexStringcase from "knex-stringcase";

import { db, isDev } from "../config";

const config = knexStringcase({
  debug: isDev,
  client: "pg",
  connection: {
    connectionString: db,
    ssl: {
      rejectUnauthorized: false,
    },
  },
  searchPath: ["public"],
  // pool: isDev ? { max: 4 } : { min: 4 },
  asyncStackTraces: true,
} as Config) as Config;

export default knex(config);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
6 participants