CODEWITHSUNDEEP
javascriptpostgresqlexpressnode-postgres
Michael P.
Michael P.

Reputation: 1413

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?

Upvotes: 13

Views: 17488

Answers (3)

Rahul Dudhane
Rahul Dudhane

Reputation: 554

Adding RETURNING fieldName1, fieldName or * in the query is the solution for the above problem.

Working Perfectly!

Upvotes: 0

Nadeem Qasmi
Nadeem Qasmi

Reputation: 2427

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]

Upvotes: 14

Michael P.
Michael P.

Reputation: 1413

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!

Upvotes: 18

Related Questions