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

Question

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?

Solution 1

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!

Solution 2

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]

Comments

[ReFruity]

I think your query is vulnerable to sql injection. Use pg feature of templating with client.query('INSERT INTO Users (first_name, last_name) VALUES ($1, $2) RETURNING *', [user.first_name, user.last_name]). It has build-in sanitazing.