Sequelize Insert transaction (two tables, multiple rows)

Question

Two tables: post and post_resources. The logic I want to implement: Whenever I add data to post table, I want to add resources as well to the post_resources table, with the same foreign key of PostId

How I'm doing it:

const post = {
    name: req.body.title,
    description: req.body.description,
}

try {
  const result = await sq.transaction(async (t) => {
     await Post.create(post, { transaction: t })
          .then(async data => {
               await PostResources.create({
                   PostId: data.id,
                   name: req.body.title,
                   url: req.body.url,
                }, { transaction: t })
           })
     });

     res.status(200).json(result)

} catch (error) {
       console.log(error || 'Error occured in transaction');
}

Ps: the url is an array of urls to insert into post_ressources along with the same PostId !important

Please feel free to ask for any further details, thank you

this error occurs before completing that transaction on the second table.. const error = new Error(`${options.transaction.finished} has been called on this transaction(${options.transaction.id}), you can no longer use it. (The rejected query is attached as the 'sql' property of this error)`); — Mr Smiley, Apr 07 '22 at 23:12

score 0 · Accepted Answer · answered Apr 07 '22 at 23:18

You used await and then in the same time, which is a mistake because they are similar.

Your error says that you already commited the transaction so a solution would be like this:

const post = {
    name: req.body.title,
    description: req.body.description,
}
const t = await sequelize.transaction();
try {
     const resultPost = await Post.create(post, { transaction: t })
     const resultPostResources = await PostResources.create({
        PostId: resultPost.id,
        name: req.body.title,
        url: req.body.url,
        }, {transaction: t})
     await t.commit();
     res.status(200).json(result)
}catch (error) {
     console.log(error || 'Error occured in transaction');
}

Now you commit the transaction only after both Creation queries and also you get the values from the first one to use in second one.

Note that create returns the created object, so you can make use of it

Played around with it, and it worked.. Understanding the errors I was making helped.. Thank you for enlightening the way. — Mr Smiley, Apr 08 '22 at 11:25

Sequelize Insert transaction (two tables, multiple rows)

1 Answers1