Return query result state from PostgreSQL query in node.js

Question

I have a node.js server connected to a PostgreSQL database using the pg module. At one point I will insert data into two different database tables for a single HTTP POST. If the first query fails, the second should not be executed, but I have some trouble achieving this.

My generalized query function looks like this:

// General insertion function. If endResponse is true, the response will be ended,
// if it is false it will just write to the response and not end it (unless errors occurs).
function performInsertQuery(request, response, query, endResponse) {
    var pg = require('pg');

    var client = new pg.Client(request.app.get('postgreConnection'));
    client.connect(function(error) {
        if (error)
        {
            message = 'Could not connect to PostgreSQL database: ' + error;
            response.end(message);
            console.error(message);
            client.end();
        }
        else
        {            
            client.query(query, function (error, result)
            {
                if (error)
                {
                    var message = 'Error running query ' + query + ': ' + error;
                    response.writeHead(500, {'content-type':'application/json'});
                    response.end(message);
                    console.error(message);
                    client.end();
                }
                else
                {
                    var message = 'Query performed: ' + query;
                    if (endResponse)
                    {
                        response.end(message);
                    }
                    else
                    {
                        response.write(message + '\n');
                    }
                    console.log(message);
                    client.end();
                }

            });
        }
    });
}

Later, I have something like the following:

// query = some query
performInsertQuery(request, response, query, false);

// Stop running here if there was any errors running the query.

// Currently, this gets executed even though the first query fails.
// anotherQuery = another query
performInsertQuery(request, response, anotherQuery, true);

I have tried returning true and false from the function performInsertQuery, but since these are inner functions the result is not returned properly from the outer functions. Also, some if it is run asynchronously, which makes things a bit harder as well. I was not able to make it work with try/catch around the call to performInsertQuery either. I guess I could do another query to see if data was inserted, but this seems unnecessary and not very robust.

What would be the best way to return a success or failure state from performInsertQuery?

There are simply too many issues with your code to list here, starting with the wrong approach to PG library instantiation, the wrong connection usage, the wrong invocation of client.end(), etc. If you want to avoid that kind of unholy mess you're in, give a try to pg-promise, which should hide all the complexities related to connection and query sequencing. — vitaly-t
– vitaly-t, Commented May 14, 2015 at 21:38

snkashis · Accepted Answer · 2015-05-08 15:13:33Z

1

I know this doesn't exactly handle your question as you intended(dealing with this entirely in node.js), but this sounds like an excellent usecase for a Postgres transaction....Do not commit results unless all insertions/updates are successful. SQL transactions are built for scenarios like yours.

Here are the docs and example code for it with your node module. https://github.com/brianc/node-postgres/wiki/Transactions

http://www.postgresql.org/docs/9.2/static/tutorial-transactions.html

answered May 8, 2015 at 15:13

snkashis

3,0111 gold badge20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Krøllebølle Over a year ago

Cool, I didn't know about transactions. I think this is a more robust way to solve my problems. I only add a BEGIN to the beginning of the total query and an COMMIT at the end and keep the insertion function as in the question. Works perfectly.

vitaly-t Over a year ago

If the author formulated his question correctly, then this answer is incorrect. Use of a transaction may result in a situation when the first query succeeds, and the second one fails, which will ROLLBACK the result of the first query also, which isn't what the author asked for.

Krøllebølle Over a year ago

Well, it is what I wanted to ask, I just didn't know how to because I am a JavaScript noob and did not know about transactions or how they worked. Anyway, you are correct that using pg-promise or other promise libraries is a way better option than the approach I have in the question. I didn't know about promises and related stuff either, but now I do. Thanks.

Collectives™ on Stack Overflow

Return query result state from PostgreSQL query in node.js

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related