Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Node-postgres Inserting a new record into my database does not return the new entry's data

Here's the route from which the query is being executed:

userRouter.route("/new")
    .post(function (req, res) {
        var user = req.body;
        pg.connect(connectionString, function (error, client, done) {
            var queryString = "INSERT INTO Users (id, first_name, last_name) VALUES (" + "'" + [user.id, user.first_name, user.last_name].join("','") + "'" + ")";
            console.log(queryString);
            client.query(queryString, function (error, result) {
                console.log(result.rows);
                done();
            });
        });
    });

The problem is that the "result" value I'm attempting to reference from within the second console is basically blank:

{
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  _parsers: [],
  RowCtor: null,
  rowAsArray: false,
  _getTypeParser: [Function: bound ]
 }

Shouldn't result.rows contain an array containing an object representing the row I just created in the database?

like image 938
Michael P. Avatar asked Jan 23 '16 17:01

Michael P.


People also ask

What does Postgres return on insert?

In an INSERT , the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values.


2 Answers

All right, I've figured it out.

Apparently, I was attempting to use a feature that has not yet been implemented in Node-Postgres, as described here: https://github.com/brianc/node-postgres/wiki/Todo

Insert/update/select row count in query result callback

Though this would be extremely awesome off course, it is possible to obtain the behaviour by adding RETURNING id or even RETURNING * to the query. This works fine for single columns for me. I will test this for multiples and the handling of that case by this package. I am quite certain it must be possible in postgres.

So, I updated my query to return all data for the new row:

var queryString = "INSERT INTO Users (first_name, last_name) VALUES (" + "'" + [user.first_name, user.last_name].join("','") + "'" + ") RETURNING *";

And then I modified my query to account for the "row" event handler, which is trigger when a new row is entered into the database. It is within the context of this event handler that the new row data is accessible to me (as per the "RETURNING" parameter above:

var query = client.query(queryString, function (error, result) {
    done();
});

query.on("row", function (row, result) {
    console.log("Inside the row event handler.");
    res.render("users/show", { user: row });
});

Aaaand it works!

like image 145
Michael P. Avatar answered Oct 26 '22 23:10

Michael P.


update the query and add at the end of the query " RETURNING * "

  var queryString = "INSERT INTO Users (id, first_name, last_name) VALUES (" + "'" + [user.id, user.first_name, user.last_name].join("','") + "'" + ") RETURNING *";

get result.rows[0]

like image 22
Nadeem Qasmi Avatar answered Oct 26 '22 23:10

Nadeem Qasmi