Ron I Ron I - 2 months ago 17
Node.js Question

Can get data from node, but not command line using postgres

I created a table called alpha and a table within alpha named 'event'. I can add a row to the event table, and retrieve all rows from the event table in node. However when I try in the command line I am getting a 'blank' when I select all.

Command line (not seeing any rows):

(master U:1 ✗) happy psql alpha
psql (9.4.4)
Type "help" for help.

alpha=# psql \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+------------
public | event | table | pgOwner
(1 row)

alpha-# select * from event
alpha-#


Node code (shows rows):

let promise = require('bluebird');
let moment = require('moment');

let options = {
// Initialization Options
promiseLib: promise
};

let pgp = require('pg-promise')(options);
let connectionString = 'postgres://localhost:5432/alpha';
let db = pgp(connectionString);

// add query functions
function getAllEvents(req, res, next) {
db.any('select * from event')
.then(function (data) {
res.status(200)
.json({
status: 'success',
data: data,
message: 'Retrieved ALL events'
});
})
.catch(function (err) {
return next(err);
});
}

function addEvent(req, res, next) {
// let date = moment(new Date()).format('MMMM Do YYYY, h:mm A');
db.none('insert into event(firstName, lastName, eventDate, emotion, answer)' +
'values(${firstName}, ${lastName}, ${emotion}, ${emotion}, ${answer})',
req.body)
.then(function () {
res.status(200)
.json({
status: 'success',
message: 'Inserted one event'
});
})
.catch(function (err) {
return next(err);
});
}


module.exports = {
getAllEvents: getAllEvents,
addEvent: addEvent,
};


enter image description here

Answer

As documented in the manual

At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command.

In other words, you have to end every statement with a ;, in contrast to executing queries via a driver, which does not require ;.

Comments