CODEWITHSUNDEEP
javascriptnode.jspostgresql
Krøllebølle
Krøllebølle

Reputation: 3018

Return query result state from PostgreSQL query in node.js

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?

Upvotes: 1

Views: 3305

Answers (1)

snkashis
snkashis

Reputation: 2991

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

Upvotes: 1

Related Questions