Işık Işık - 3 months ago 51
Node.js Question

Interdependent Transactions with pg-promise

I am trying to build an app involves posts and tags for posts. For these I have a

post
,
tags
and
post_tag
table.
tags
has the tags I have defined before hand and in somewhere in the app is suggested to the user on the front-end.
post_tag
table holds the post and tag ids as pairs on each row.

I use express.js and postgreql and pg-promise.

As far as I know I need a transactional query(ies) for a create post operation.

Also I need a mechanism to detect if a tag was not in
tags
table when the user created the post, so that I can insert it on the fly, and I have a
tag_id
for each tag that is neccessary to use in
insertion
of the
post_id
and
tag_id
into
post_tag
table. Otherwise, I will have a
foreign key error
since I need to
post_tag
table's columns
post_id
and
tag_id
to reference
posts
and
tags
table
id
columns, respectively.

Here is the url function I use for this I have used so far unsuccessful:

privateAPIRoutes.post('/ask', function (req, res) {
console.log('/ask req.body: ', req.body);
// write to posts
var post_id = ''
var post_url = ''
db.query(
`
INSERT INTO
posts (title, text, post_url, author_id, post_type)
VALUES
($(title), $(text), $(post_url), $(author_id), $(post_type))
RETURNING id
`,
{
title: req.body.title,
text: req.body.text,
post_url: slug(req.body.title),
author_id: req.user.id,
post_type: 'question'
} // remember req.user contains decoded jwt saved by mw above.
)
.then(post => {
console.log('/ask post: ', post);
post_id = post.id
post_url = post.post_url


// if tag deos not exist create it here
var tags = req.body.tags;
console.log('2nd block tags1', tags);
for (var i = 0; i < tags.length; i++) {
if (tags[i].id == undefined) {
console.log('req.body.tags[i].id == undefined', tags[i].id);
var q1 = db.query("insert into tags (tag) values ($(tag)) returning id", {tag: tags[i].label})
.then(data => {
console.log('2nd block tags2', tags);
tags[i].id = data[0].id


// write to the post_tag
db.tx(t => {
var queries = [];
for (var j = 0; j < tags.length; j++) {

var query = t.query(
`
INSERT INTO
post_tag (post_id, tag_id)
VALUES
($(post_id), $(tag_id))
`,
{
post_id: post_id,
tag_id: tags[j].id
}
)
queries.push(query);
}
return t.batch(queries)
})
.then(data => {
res.json({post_id: post_id, post_url: post_url})
})
.catch(error => {
console.error(error);
})
})
.catch(error => {
console.error(error);
});
}
}
})
.catch(error => {
console.error(error);
})
});

Answer

The main problem you have - you can't use the root-level db object inside a task or transaction. Trying to create a new connection while inside a transaction breaks the transaction. You would need to use t.tx in such cases. However, in your case I don't see that you need it at all.

corrected code:

privateAPIRoutes.post('/ask', function (req, res) {
    console.log('/ask req.body: ', req.body);
    db.tx(t => {
        return t.one(
            `
        INSERT INTO
        posts (title, text, post_url, author_id, post_type)
        VALUES
        ($(title), $(text), $(post_url), $(author_id), $(post_type))
        RETURNING *
        `,
            {
                title: req.body.title,
                text: req.body.text,
                post_url: slug(req.body.title),
                author_id: req.user.id,
                post_type: 'question'
            } // remember req.user contains decoded jwt saved by mw above.
        )
            .then(post => {
                console.log('/ask second query: post[0]: ', post);
                console.log('/ask second query: tags: ', req.body.tags);
                console.log('/ask second query: tags[0]: ', req.body.tags[0]);

                // the key piece to the answer:
                var tagIds = req.body.tags.map(tag=> {
                    return tag.id || t.one("insert into tags(tag) values($1) returning id", tag.label, a=>a.id);
                });

                return t.batch(tagIds)
                    .then(ids=> {
                        var queries = ids.map(id=> {
                            return t.one(
                                `
                                INSERT INTO post_tag (post_id, tag_id)
                                VALUES ($(post_id), $(tag_id))
                                RETURNING post_id, tag_id
                                `,
                                {
                                    post_id: post.id,
                                    tag_id: id
                                }
                            )
                        });
                        return t.batch(queries);
                    });
            });
    })
        .then(data => {
            // data = result from the last query;
            console.log('/api/ask', data);
            res.json(data);

        })
        .catch(error => {
            // error
        });
});

The key here is simply to iterate through the tag id-s, and for the ones that are not set - use an insert. Then you settle them all by passing the array into t.batch.


Other recommendations:

  • You should use method one when executing an insert that returns the new record columns.
  • You should use try/catch only once there, on the transaction. This is relevant to how to use promises, and not just for this library
  • You can place your queries into external SQL files, see Query Files

To understand conditional inserts better, see SELECT->INSERT