Nested transactions with pg-promise

Question

I am using NodeJS, PostgreSQL and the amazing pg-promise library. In my case, I want to execute three main queries:

1. Insert one tweet in the table 'tweets'.
2. In case there is hashtags in the tweet, insert them into another table 'hashtags'
3. Them link both tweet and hashtag in a third table 'hashtagmap' (many to many relational table)

Here is a sample of the request's body (JSON):

{
"id":"12344444",
"created_at":"1999-01-08 04:05:06 -8:00",
"userid":"@postman",
"tweet":"This is the first test from postman!",
"coordinates":"",
"favorite_count":"0",
"retweet_count":"2",
"hashtags":{
    "0":{
        "name":"test",
        "relevancetraffic":"f",
        "relevancedisaster":"f"
    },
    "1":{
        "name":"postman",
        "relevancetraffic":"f",
        "relevancedisaster":"f"
    },
    "2":{
        "name":"bestApp",
        "relevancetraffic":"f",
        "relevancedisaster":"f"
    }
}

All the fields above should be included in the table "tweets" besides hashtags, that in turn should be included in the table "hashtags".

Here is the code I am using based on Nested transactions from pg-promise docs inside a NodeJS module. I guess I need nested transactions because I need to know both tweet_id and hashtag_id in order to link them in the hashtagmap table.

// Columns
var tweetCols = ['id','created_at','userid','tweet','coordinates','favorite_count','retweet_count'];

var hashtagCols = ['name','relevancetraffic','relevancedisaster'];

//pgp Column Sets
var cs_tweets = new pgp.helpers.ColumnSet(tweetCols, {table: 'tweets'});

var cs_hashtags = new pgp.helpers.ColumnSet(hashtagCols, {table:'hashtags'});
return{
// Transactions
add: body =>
    rep.tx(t => {
        return t.one(pgp.helpers.insert(body,cs_tweets)+" ON CONFLICT(id) DO UPDATE SET coordinates = "+body.coordinates+" RETURNING id")
            .then(tweet => {
                var queries = [];
                for(var i = 0; i < body.hashtags.length; i++){
                    queries.push(
                        t.tx(t1 => {
                            return t1.one(pgp.helpers.insert(body.hashtags[i],cs_hashtags) + "ON CONFLICT(name) DO UPDATE SET fool ='f' RETURNING id")
                                .then(hash =>{
                                    t1.tx(t2 =>{
                                        return t2.none("INSERT INTO hashtagmap(tweetid,hashtagid) VALUES("+tweet.id+","+hash.id+") ON CONFLICT DO NOTHING");
                                    });
                                });
                        }));
                }
                return t.batch(queries);
            });
    })
}

The problem is with this code I am being able to successfully insert the tweet but nothing happens then. I cannot insert the hashtags nor link the hashtag to the tweets.

Sorry but I am new to coding so I guess I didn't understood how to properly return from the transaction and how to perform this simple task. Hope you can help me.

Thank you in advance.

Jean

Answer 1

Improving on Jean Phelippe's own answer:

// Columns
var tweetCols = ['id', 'created_at', 'userid', 'tweet', 'coordinates', 'favorite_count', 'retweet_count'];

var hashtagCols = ['name', 'relevancetraffic', 'relevancedisaster'];

//pgp Column Sets
var cs_tweets = new pgp.helpers.ColumnSet(tweetCols, {table: 'tweets'});

var cs_hashtags = new pgp.helpers.ColumnSet(hashtagCols, {table: 'hashtags'});

return {
    /* Tweets */
    // Add a new tweet and update the corresponding hash tags
    add: body =>
        db.tx(t => {
            return t.one(pgp.helpers.insert(body, cs_tweets) + ' ON CONFLICT(id) DO UPDATE SET coordinates = ' + body.coordinates + ' RETURNING id')
                .then(tweet => {
                    var queries = Object.keys(body.hashtags).map((_, idx) => {
                        return t.one(pgp.helpers.insert(body.hashtags[i], cs_hashtags) + 'ON CONFLICT(name) DO UPDATE SET fool = $1 RETURNING id', 'f')
                            .then(hash => {
                                return t.none('INSERT INTO hashtagmap(tweetid, hashtagid) VALUES($1, $2) ON CONFLICT DO NOTHING', [+tweet.id, +hash.id]);
                            });
                    });
                    return t.batch(queries);
                });
        })
            .then(data => {
                // transaction was committed;
                // data = [null, null,...] as per t.none('INSERT INTO hashtagmap...
            })
            .catch(error => {
                // transaction rolled back
            })
},

NOTES:

• As per my notes earlier, you must chain all queries, or else you will end up with loose promises
• Stay away from nested transactions, unless you understand exactly how they work in PostgreSQL (read this, and specifically the Limitations section).
• Avoid manual query formatting, it is not safe, always rely on the library's query formatting.
• Unless you are passing the result of transaction somewhere else, you should at least provide the .catch handler.

P.S. For the syntax like +tweet.id, it is the same as parseInt(tweet.id), just shorter, in case those are strings ;)