Passing results of queries in promises

Question

I am using node-postgres for a database connection. For that I have APIs like that:

app.post('/api/insert', urlEncodedParser, function(req, res) {
    // do stuff
})

Now I need synchron steps:

1. Select specific id from table user
2. Check if id from table user already exists in table list 3a) if id in table list does not exist, then insert data in table list 3b) if id in table list exists, then update data in table list

So I have following code:

var userid = 0;
var listEntry = 0;

client
.query('SELECT "ID" FROM "User" WHERE "PW" = $1 AND "Name" = $2', [req.body.pw, req.body.name])
.then(res => userid = res.rows[0])
.catch(e => console.error(e.stack))
.query('SELECT " FROM "List" WHERE "UserID" = $1', [userid])
.then(res => listEntry = res.rows[0])
.catch(e => console.error(e.stack))
.query('INSERT INTO "List" ("UserID", "LastDate")', [userid, req.body.date])
.then(res => userid = res.rows[0])
.catch(e => console.error(e.stack))
.query('UPDATE "List" SET "LastDate" = $1', [req.body.date])
.then(res => userid = res.rows[0])
.catch(e => console.error(e.stack))
.finally(() { client.end()
});

What do I need to change that I can have this synchron steps as listed above? Sorry, I am new to javascript and node-postgres.

Answer 1

Whatever you return in one then ends up in the following then. For example:

client.query("SELECT...")
    .then((result) => {
        // do something with the result
        return 42;
    })
    .then((result) => console.log(result))
    .then((result) => console.log(result))

The first log prints 42 because the previous then returned 42 and the second prints undefined because the previous then returned nothing = undefined. So you actually want something like this:

client.query('SELECT "ID" FROM "User" WHERE "PW" = $1 AND "Name" = $2', [req.body.pw, req.body.name])
    .then((res) => {
        const userid = res.rows[0];
        return client.query('SELECT " FROM "List" WHERE "UserID" = $1', [userid]).then(() => {
            return client.query('INSERT INTO "List" ("UserID", "LastDate")', [userid, req.body.date]);
        });
    })
    .then(() => {
        return client.query('UPDATE "List" SET "LastDate" = $1', [req.body.date])
    })
    .catch(error => console.error(error))
    .finally(() => client.end());

This was you don't even need the semi-global variables userid and listEntry. This is generally a good thing.

The code can be more simplified when using async/await.

If you don't want to execute the following queries because there are no rows in the first response you can do something like this:

client.query('SELECT "ID" FROM "User" WHERE "PW" = $1 AND "Name" = $2', [req.body.pw, req.body.name])
    .then((res) => {
        if (res.rows.length > 0) {
            const userid = res.rows[0];
            return client.query('SELECT " FROM "List" WHERE "UserID" = $1', [userid]).then(() => {
                return client.query('INSERT INTO "List" ("UserID", "LastDate")', [userid, req.body.date]);
            }).then(() => {
                return client.query('UPDATE "List" SET "LastDate" = $1', [req.body.date])
            })
        }
    })
    .catch(error => console.error(error))
    .finally(() => client.end());