Reputation: 281
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 xxxxxxxxxxx@gmail.com
2013-09-29T13:30:07.515481+00:00 heroku[api]: Release v18 created by xxxxxxxxx@gmail.com
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 xxxxxxxx@gmail.com
2013-09-29T13:32:43.359317+00:00 heroku[api]: Release v19 created by xxxxxxxx@gmail.com
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);
});
});
Upvotes: 13
Views: 35104
Reputation: 798
I had the same issue while connecting to my NestJS (NodeJS app) to Heroku PostgreSQL database. I fixed it by adding the SSL: true property in the ormconfig.json file.
Use the following code snippet for a NodeJS app:
const { Client } = require('pg');
const client = new Client({
connectionString: process.env.DATABASE_URL,
ssl: true,
});
client.connect();
client.query('SELECT table_schema,table_name FROM information_schema.tables;', (err, res) => {
if (err) throw err;
for (let row of res.rows) {
console.log(JSON.stringify(row));
}
client.end();
});
And for a Java app:
@Configuration
public class MainConfig {
@Bean
public BasicDataSource dataSource() throws URISyntaxException {
URI dbUri = new URI(System.getenv("DATABASE_URL"));
String username = dbUri.getUserInfo().split(":")[0];
String password = dbUri.getUserInfo().split(":")[1];
String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setUrl(dbUrl);
basicDataSource.setUsername(username);
basicDataSource.setPassword(password);
return basicDataSource;
}}
Note: Follow this link for other technologies database connection details.
Upvotes: 0
Reputation: 450
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);
});
});
Upvotes: 22
Reputation: 728
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
Upvotes: 0
Reputation: 2794
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.
Upvotes: 14
Reputation: 2831
Try heroku pg:promote
like so:
heroku pg:promote HEROKU_POSTGRESQL_WHATEVER_URL
This should set the DATABASE_URL variable on Heroku's side.
Upvotes: 2
Reputation: 4918
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);
});
});
Upvotes: 2