PostgreSQL with NodeJS - Getting the row back or at least the ID

node.js, postgresql, sql

Solution

The simplest answer I have isn't specific to node or the pg module. You could just append your `INSERT INTO` sql statement to use `RETURNING *` which should return the row(s) you created

You haven't posted your specific example, so I'll make one up:

pg.connect(config, function (error, client, done) {
    if (error) // Handle error
    var query = "INSERT INTO people (name) VALUES ($1) RETURNING *",
        params = ["Tom"]
    client.query(query, params, function (error, result) {
        result.rows // Will contain your inserted rows
        done();
    });
});

Problem

I have switched to PostgreSQL from mongoDB (finally!) in our new mobile app API and so far everything is going well. The one problem I have is that when I am inserting rows into database, I would need the inserted data back. I am using the "pg" node module, and on top of that I wrote simple Model structure for validation and etc. Now when I save this model/row to the database, I basically have the whole structure, yet still missing some important values that are generated by database, namely ID and date created. So I would need pg to either return the whole inserted object, or at least its id so I can fill it in the model. I googled some answers, but nothing satisfactory, and nothing for nodejs specifically. Thank you!

Original source