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.