Trying to connect my node.js to Heroku PostgreSQL database. Following Heroku Postgres tutorial

Question

I'm following these two Heroku tutorials:

https://devcenter.heroku.com/articles/getting-started-with-nodejs

and

https://devcenter.heroku.com/articles/heroku-postgresql

I have the 'hello world' app working. But I am getting an error when I add the node.js code to connect to postgreSQL.

My package.json

{
  "name": "node-example",
  "version": "0.0.1",
  "dependencies": {
    "pg": "2.x",
    "express": "3.1.x"
  },
  "engines": {
    "node": "0.10.x",
    "npm": "1.2.x"
  }
}

My web.js

var express = require("express");
var app = express();
app.use(express.logger());

app.get('/', function(request, response) {
  response.send('Hello World!');
});

var port = process.env.PORT || 5000;
app.listen(port, function() {
  console.log("Listening on " + port);
});

var pg = require('pg');

pg.connect(process.env.DATABASE_URL, function(err, client, done) {
  client.query('SELECT * FROM your_table', function(err, result) {
    done();
    if(err) return console.error(err);
    console.log(result.rows);
  });
});

My Heroku postgres database is working well and I can connect to it directly with

heroku pg:psql

Here are my logs:

2013-09-29T13:13:34.777156+00:00 heroku[web.1]: State changed from starting to up
2013-09-29T13:13:34.784018+00:00 app[web.1]: 
2013-09-29T13:13:34.787193+00:00 app[web.1]: events.js:72
2013-09-29T13:13:34.787469+00:00 app[web.1]:         throw er; // Unhandled 'error' event
2013-09-29T13:13:34.787642+00:00 app[web.1]:               ^
2013-09-29T13:13:34.790791+00:00 app[web.1]: error: relation "junk" does not exist
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at Connection.parseE (/app/node_modules/pg/lib/connection.js:546:11)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:375:17)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at null.<anonymous> (/app/node_modules/pg/lib/connection.js:92:20)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at Socket.EventEmitter.emit (events.js:95:17)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at Socket.<anonymous> (_stream_readable.js:746:14)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at Socket.EventEmitter.emit (events.js:92:17)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at emitReadable_ (_stream_readable.js:408:10)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at emitReadable (_stream_readable.js:404:5)
2013-09-29T13:13:34.790791+00:00 app[web.1]:     at readableAddChunk (_stream_readable.js:165:9)
2013-09-29T13:13:34.790968+00:00 app[web.1]:     at Socket.Readable.push (_stream_readable.js:127:10)
2013-09-29T13:13:36.511975+00:00 heroku[web.1]: Process exited with status 8
2013-09-29T13:13:36.527681+00:00 heroku[web.1]: State changed from up to crashed
2013-09-29T13:21:22+00:00 heroku[slug-compiler]: Slug compilation started
2013-09-29T13:21:38+00:00 heroku[slug-compiler]: Slug compilation finished
2013-09-29T13:21:39.239935+00:00 heroku[web.1]: State changed from crashed to starting
2013-09-29T13:21:40.589773+00:00 heroku[web.1]: Starting process with command `node web.js`
2013-09-29T13:21:41.345806+00:00 app[web.1]: Listening on 20977
2013-09-29T13:21:41.368323+00:00 app[web.1]: { [error: relation "your_table" does not exist]
2013-09-29T13:21:41.368323+00:00 app[web.1]:   length: 101,
2013-09-29T13:21:41.368323+00:00 app[web.1]:   detail: undefined,
2013-09-29T13:21:41.368323+00:00 app[web.1]:   severity: 'ERROR',
2013-09-29T13:21:41.368323+00:00 app[web.1]:   hint: undefined,
2013-09-29T13:21:41.368323+00:00 app[web.1]:   position: '15',
2013-09-29T13:21:41.368323+00:00 app[web.1]:   code: '42P01',
2013-09-29T13:21:41.368323+00:00 app[web.1]:   name: 'error',
2013-09-29T13:21:41.368323+00:00 app[web.1]:   internalPosition: undefined,
2013-09-29T13:21:41.368512+00:00 app[web.1]:   where: undefined,
2013-09-29T13:21:41.368512+00:00 app[web.1]:   file: 'parse_relation.c',
2013-09-29T13:21:41.368512+00:00 app[web.1]:   line: '864',
2013-09-29T13:21:41.368323+00:00 app[web.1]:   internalQuery: undefined,
2013-09-29T13:21:41.368512+00:00 app[web.1]:   routine: 'parserOpenTable' }
2013-09-29T13:21:41.938926+00:00 heroku[web.1]: State changed from starting to up
2013-09-29T13:21:38.600520+00:00 heroku[api]: Deploy 95a0a35 by *********@gmail.com
2013-09-29T13:21:38.625733+00:00 heroku[api]: Release v17 created by *******@gmail.com
2013-09-29T13:22:08.383050+00:00 heroku[router]: at=info method=GET path=/ host=pure-lake-7106.herokuapp.com fwd="58.7.243.156" dyno=web.1 connect=3ms service=6ms status=200 bytes=12
2013-09-29T13:22:08.383327+00:00 app[web.1]: - - - [Sun, 29 Sep 2013 13:22:08 GMT] "GET / HTTP/1.1" 200 12 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/28.0.1500.71 Chrome/28.0.1500.71 Safari/537.36"
2013-09-29T13:22:10.046808+00:00 app[web.1]: - - - [Sun, 29 Sep 2013 13:22:10 GMT] "GET /favicon.ico HTTP/1.1" 404 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/28.0.1500.71 Chrome/28.0.1500.71 Safari/537.36"
2013-09-29T13:22:10.049179+00:00 heroku[router]: at=info method=GET path=/favicon.ico host=pure-lake-7106.herokuapp.com fwd="58.7.243.156" dyno=web.1 connect=1ms service=3ms status=404 bytes=34
2013-09-29T13:29:40+00:00 heroku[slug-compiler]: Slug compilation started
2013-09-29T13:30:07.484077+00:00 heroku[api]: Deploy a2cc795 by [email protected]
2013-09-29T13:30:07.515481+00:00 heroku[api]: Release v18 created by [email protected]
2013-09-29T13:30:07+00:00 heroku[slug-compiler]: Slug compilation finished
2013-09-29T13:30:08.016355+00:00 heroku[web.1]: State changed from up to starting
2013-09-29T13:30:10.017792+00:00 heroku[web.1]: Starting process with command `node web.js`
2013-09-29T13:30:10.099473+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2013-09-29T13:30:11.008770+00:00 app[web.1]: Listening on 47344
2013-09-29T13:30:11.065531+00:00 app[web.1]:   name: 'error',
2013-09-29T13:30:11.065531+00:00 app[web.1]:   length: 101,
2013-09-29T13:30:11.065531+00:00 app[web.1]:   severity: 'ERROR',
2013-09-29T13:30:11.065531+00:00 app[web.1]:   code: '42P01',
2013-09-29T13:30:11.065531+00:00 app[web.1]:   detail: undefined,
2013-09-29T13:30:11.065531+00:00 app[web.1]:   position: '15',
2013-09-29T13:30:11.065531+00:00 app[web.1]: { [error: relation "your_table" does not exist]
2013-09-29T13:30:11.065531+00:00 app[web.1]:   internalPosition: undefined,
2013-09-29T13:30:11.065531+00:00 app[web.1]:   internalQuery: undefined,
2013-09-29T13:30:11.065840+00:00 app[web.1]:   where: undefined,
2013-09-29T13:30:11.065840+00:00 app[web.1]:   file: 'parse_relation.c',
2013-09-29T13:30:11.065840+00:00 app[web.1]:   line: '864',
2013-09-29T13:30:11.065840+00:00 app[web.1]:   routine: 'parserOpenTable' }
2013-09-29T13:30:11.065531+00:00 app[web.1]:   hint: undefined,
2013-09-29T13:30:11.482704+00:00 heroku[web.1]: State changed from starting to up
2013-09-29T13:30:11.651117+00:00 heroku[web.1]: Process exited with status 143
2013-09-29T13:30:17.729604+00:00 app[web.1]: - - - [Sun, 29 Sep 2013 13:30:17 GMT] "GET / HTTP/1.1" 200 12 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/28.0.1500.71 Chrome/28.0.1500.71 Safari/537.36"
2013-09-29T13:30:19.361615+00:00 heroku[router]: at=info method=GET path=/favicon.ico host=pure-lake-7106.herokuapp.com fwd="58.7.243.156" dyno=web.1 connect=1ms service=3ms status=404 bytes=34
2013-09-29T13:30:19.364457+00:00 app[web.1]: - - - [Sun, 29 Sep 2013 13:30:19 GMT] "GET /favicon.ico HTTP/1.1" 404 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/28.0.1500.71 Chrome/28.0.1500.71 Safari/537.36"
2013-09-29T13:30:17.728815+00:00 heroku[router]: at=info method=GET path=/ host=pure-lake-7106.herokuapp.com fwd="58.7.243.156" dyno=web.1 connect=1ms service=19ms status=200 bytes=12
2013-09-29T13:32:28+00:00 heroku[slug-compiler]: Slug compilation started
2013-09-29T13:32:43.338858+00:00 heroku[api]: Deploy d4cf2ba by [email protected]
2013-09-29T13:32:43.359317+00:00 heroku[api]: Release v19 created by [email protected]
2013-09-29T13:32:43+00:00 heroku[slug-compiler]: Slug compilation finished
2013-09-29T13:32:43.746015+00:00 heroku[web.1]: State changed from up to starting
2013-09-29T13:32:45.354842+00:00 heroku[web.1]: Starting process with command `node web.js`
2013-09-29T13:32:46.098651+00:00 app[web.1]: Listening on 37156
2013-09-29T13:32:47.127328+00:00 app[web.1]: { [error: relation "your_table" does not exist]
2013-09-29T13:32:47.127328+00:00 app[web.1]:   code: '42P01',
2013-09-29T13:32:47.127328+00:00 app[web.1]:   name: 'error',
2013-09-29T13:32:47.127328+00:00 app[web.1]:   length: 101,
2013-09-29T13:32:47.127328+00:00 app[web.1]:   hint: undefined,
2013-09-29T13:32:47.127328+00:00 app[web.1]:   position: '15',
2013-09-29T13:32:47.127328+00:00 app[web.1]:   severity: 'ERROR',
2013-09-29T13:32:47.127328+00:00 app[web.1]:   detail: undefined,
2013-09-29T13:32:47.127561+00:00 app[web.1]:   where: undefined,
2013-09-29T13:32:47.127561+00:00 app[web.1]:   routine: 'parserOpenTable' }
2013-09-29T13:32:47.127328+00:00 app[web.1]:   internalPosition: undefined,
2013-09-29T13:32:47.127328+00:00 app[web.1]:   internalQuery: undefined,
2013-09-29T13:32:47.127561+00:00 app[web.1]:   file: 'parse_relation.c',
2013-09-29T13:32:47.127561+00:00 app[web.1]:   line: '864',
2013-09-29T13:32:47.197293+00:00 heroku[web.1]: State changed from starting to up
2013-09-29T13:32:50.505267+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2013-09-29T13:32:53.246120+00:00 heroku[web.1]: Process exited with status 143
2013-09-29T14:39:50.833246+00:00 heroku[web.1]: Idling
2013-09-29T14:39:52.828292+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2013-09-29T14:39:54.545662+00:00 heroku[web.1]: Process exited with status 143
2013-09-29T14:39:54.559151+00:00 heroku[web.1]: State changed from up to down

I get this erroe when I do a foreman start

p$ foreman start
12:39:41 web.1  | started with pid 13983
12:39:41 web.1  | Listening on 5000
12:39:41 web.1  | /home/roland/github/heroku_app/web.js:18
12:39:41 web.1  |   client.query('SELECT * FROM your_table', function(err, result) {
12:39:41 web.1  |          ^
12:39:41 web.1  | TypeError: Cannot call method 'query' of null
12:39:41 web.1  |     at /home/roland/github/heroku_app/web.js:18:10
12:39:41 web.1  |     at /home/roland/github/heroku_app/node_modules/pg/lib/pool.js:54:25
12:39:41 web.1  |     at /home/roland/github/heroku_app/node_modules/pg/node_modules/generic-pool/lib/generic-pool.js:271:11
12:39:41 web.1  |     at /home/roland/github/heroku_app/node_modules/pg/lib/pool.js:27:26
12:39:41 web.1  |     at null.<anonymous> (/home/roland/github/heroku_app/node_modules/pg/lib/client.js:169:9)
12:39:41 web.1  |     at EventEmitter.emit (events.js:95:17)
12:39:41 web.1  |     at null.<anonymous> (/home/roland/github/heroku_app/node_modules/pg/lib/connection.js:97:12)
12:39:41 web.1  |     at Socket.EventEmitter.emit (events.js:95:17)
12:39:41 web.1  |     at Socket.<anonymous> (_stream_readable.js:746:14)
12:39:41 web.1  |     at Socket.EventEmitter.emit (events.js:92:17)
12:39:41 web.1  | exited with code 8
12:39:41 system | sending SIGTERM to all processes
SIGTERM received

EDIT my console.logs;

var express = require("express");
var app = express();
app.use(express.logger());

app.get('/', function(request, response) {
  response.send('Hello World!');
  console.log("hello roland");
});

var port = process.env.PORT || 5000;
app.listen(port, function() {
  console.log("Listening on " + port);
});

var pg = require('pg');


pg.connect(process.env.DATABASE_URL, function(err, client, done) {
   console.log(err+"!!!!!!!!!!!!!!!");
  client.query('SELECT * FROM your_table', function(err, result) {
    done();
    if(err) return console.error(err);
    console.log(result.rows);
  });
});

In your client.connect() callback, what is the value of err? It seems like it works at least part of the time, since your_table is being queried (as is evident from the pg logs). It doesn't exist, btw. — Nitzan Shaked, Sep 30 '13 at 4:59
@NitzanShaked, I don't know, I didn't write this code but pasted it directly form the tutorial. Should I do a console.log(err): ? and see. — rolandnsharp, Sep 30 '13 at 5:16
@NitzanShaked I get: console.log(err+"!!!!!!"); ^ ReferenceError: err is not defined — rolandnsharp, Sep 30 '13 at 5:25
post your code. It should be. Probably put the console log in a wrong place. — Nitzan Shaked, Sep 30 '13 at 5:31

DJ_ · Accepted Answer · 2013-10-13 03:18:54Z

It's Heroku's problem. The "process.env.DATABASE_URL" variable they tell you to use in pg.connect is not functioning.

A simple

console.log(process.env.DATABASE_URL);

Will show that this variable is undefined.

Until Heroku offers a fix, you can hard-code the connection URL as the first argument to pg.connect().

To find your credentials, you can go to your app's PostgreSQL add-on connection settings through http://heroku.com.

The new pg.connect method will look like

var connectionString = "postgres://*USERNAME*:*PASSWORD*@*HOST*:*PORT:/*DATABASE*"

pg.connect(connectionString, function(err, client, done) {
   client.query('SELECT * FROM your_table', function(err, result) {
      done();
      if(err) return console.error(err);
      console.log(result.rows);
   });
});

why not use the heroku toolbelts command 'heroku config:set VAR=value' that way it isn't in your repo? — light24bulbs, Nov 12 '14 at 5:38
config:set sets the variable for the dyno. For local variables the .env file can be used: devcenter.heroku.com/articles/… — AlexStack, Jun 25 at 22:10

njenson · Answer 2 · 2015-08-01 18:54:24Z

up vote 3 down vote

If the above answers fall a little short for anyone (they did for me) - try appending ?ssl=true to the end of your DATABASE_URL environment variable. Credit to the author of this answer. Best of luck.

answered Aug 1 '15 at 18:54

njenson

543311

2

This totally made it work – Marcos Placona Feb 26 at 15:26

add a comment |

Rafal Spacjer · Answer 3 · 2014-01-28 20:38:57Z

You can use: heroku pg:info command to list all your databases. There you will find the exact databse url that you can use in your app - it should be something like that: HEROKU_POSTGRESQL_DBNAME_URL. This url can be used in node.js application:

pg.connect(process.env.HEROKU_POSTGRESQL_DBNAME_URL, function(err, client, done) {
   client.query('SELECT * FROM your_table', function(err, result) {
      done();
      if(err) return console.error(err);
      console.log(result.rows);
   });
});

Fox Wilson · Answer 4 · 2014-02-02 20:34:11Z

up vote 1 down vote

Try heroku pg:promote like so:

heroku pg:promote HEROKU_POSTGRESQL_WHATEVER_URL

This should set the DATABASE_URL variable on Heroku's side.

answered Feb 2 '14 at 20:34

Fox Wilson

99121130

add a comment |

ocolot · Answer 5 · 2016-09-13 11:08:53Z

I would not recommend hardcoding the URL, because you may be willing to create staging app with the same code.

You may use nconf to make sure Heroku's environment variables (stored in a .env file, read by foreman when launching your app with Procfile).

You may create a config.js file gathering your variables:

const nconf = require('nconf')

module.exports = nconf.argv().env()

Then use it to retrieve your variables:

import config from './config'

config.get('HEROKU_POSTGRESQL_DBNAME_URL')

Note that you could use the exact same setup as Heroku on your local machine. All you need is to create a Procfile to describe how to start your server and a .env file with you local variables (e.g. your local postgresql database url). Check https://github.com/strongloop/node-foreman

asked	3 years ago
viewed	7738 times
active	26 days ago

current community

your communities

more stack exchange communities

Trying to connect my node.js to Heroku PostgreSQL database. Following Heroku Postgres tutorial

5 Answers 5

Your Answer

Not the answer you're looking for? Browse other questions tagged node.js postgresql heroku or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

Trying to connect my node.js to Heroku PostgreSQL database. Following Heroku Postgres tutorial

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged node.js postgresql heroku or ask your own question.

Linked

Related

Hot Network Questions