Postgres promise multiple queries - nodejs

Question

After reading https://stackoverflow.com/a/14797359/4158593 : about nodejs single thread and that it takes the first parameter of async function, processes it and then uses the callback to respond when everything is ready. What confused me is what if I had multiple queries that need to be excused all at once and tell nodeJS to block other requests by adding them in a queue.

To do that I realised that I need to wrap my queries in another callback. And promises do that pretty well.

    const psqlClient = psqlPool.connect(); 
    return psqlClient.query(`SELECT username FROM usernames WHERE username=$1`, ['me'])
    .then((data) => {
        if(!data.rows[0].username) {
           psqlClient.query(`INSERT INTO usernames (username) VALUES ('me')`);
        }
       else { ... } 
 });

This code is used during sign up, to check if username isn't taken before inserting. So it very important that nodejs puts other requests into a queue, and makes sure to select and insert at the same time. Because this code might allow people with the same username sent at the same time to select a username that has been already been taken, therefore two usernames will be inserted.

Questions

1. Does the code above executes queries all at once?

2. If 1 is correct, if I was to change the code like this

   const psqlClient = psqlPool.connect(); 
   return psqlClient.query(`SELECT username FROM usernames WHERE username=$1`, ['me'], function(err, reply) { 
   if(!reply.rows[0].username) {
   psqlClient.query(`INSERT INTO usernames (username) VALUES ('me')`);
   }
   });
   

   would that effect the behaviour?

3. If 1 is wrong, how should this be solved? I am going to need this pattern (mainly using select and insert/update one after another) for things like making sure that my XML sitemaps don't contain more than 50000 urls by storing the count for each file in my db which happens dynamically.

Answer 1

The only thing that can guarantee data integrity in your case is a single SELECT->INSERT query, which was discussed here many times.

Some examples:

• Is SELECT or INSERT in a function prone to race conditions?
• Get Id from a conditional INSERT

You should be able to find more of that here ;)

---

I also touched on this subject in a SELECT ⇒ INSERT example within pg-promise.

---

There is however an alternative, to make any repeated insert generate a conflict, in which case you can re-run your select to get the new record. But it is not always a suitable solution.

Answer 2

Here's a reference from the creator of node-postgres: https://github.com/brianc/node-postgres/issues/83#issuecomment-212657287. Basically queries are queued, but don't rely on them in production where you have many requests....

However you can use BEGIN and COMIT

var Client = require('pg').Client;

var client = new Client(/*your connection info goes here*/);
client.connect();

var rollback = function(client) {
  //terminating a client connection will
  //automatically rollback any uncommitted transactions
  //so while it's not technically mandatory to call
  //ROLLBACK it is cleaner and more correct
  client.query('ROLLBACK', function() {
    client.end();
  });
};

client.query('BEGIN', function(err, result) {
  if(err) return rollback(client);
  client.query('INSERT INTO account(money) VALUES(100) WHERE id = $1', [1], function(err, result) {
    if(err) return rollback(client);
    client.query('INSERT INTO account(money) VALUES(-100) WHERE id = $1', [2], function(err, result) {
      if(err) return rollback(client);
      //disconnect after successful commit
      client.query('COMMIT', client.end.bind(client));
    });
  });
});

Check out: https://github.com/brianc/node-postgres/wiki/Transactions

However this doesn't block the table. Here's a list of solutions: Update where race conditions Postgres (read committed)