aloha_mate aloha_mate - 1 month ago 6
Node.js Question

Postgres promise multiple queries - nodejs

After reading http://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

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:

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


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

Comments