Transaction on rest api

Question

I am working on a project where I need to implement transaction to rollback PostgreSQL database durin REST operation using Nodejs. I have implemented transaction separately for GET, PUT and POST methods. Do I need to use transaction once or am i on a right track? Thanks in advance for your help.

I want to ensure my database rollback data if it is needed. I am using pg-promise library to get the result.

db.tx(t => {
    return t.batch([
        t.query('UPDATE users SET active = $1 WHERE id = $2', [true, 123]),
        t.query('INSERT INTO audit(event, id) VALUES($1, $2)', ['activate', 123])
    ]);
})
.then(data => {
    // success;
})
.catch(error => {
    // error;
});

Or, should I implement below method?

    module.exports = { 
  // if you can run all migration queries simultaneously  
  up: ({ sequelize: db }) => db.transaction(transaction => Promise.all([
    db.query('...', { transaction }),
    db.query('...', { transaction }),
  ])),

  // If you have to wait for every query before executing next one
  down: ({ sequelize: db }) => db.transaction(async (transaction) => {
    await db.query('...', { transaction });
    await db.query('...', { transaction });
  }),
};

Thank you in advance.

Answer 1

I recommend you to use Bookshelf, it's a very mature ORM running over the Knex query builder. Bookshelf provide a transaction API which will make a rollback if any "rejection" in the code is called. Eg:

const knex = require('knex')({
  client: 'pg',
  connection: {
    host: '127.0.0.1',
    user: 'user',
    password: 'password',
    database: 'db',
    charset: 'utf8'
  }
});

const bookshelf = require('bookshelf')(knex);

const User = bookshelf.Model.extend({
  tableName: 'users'
});

const deleteUsers = ids =>
  bookshelf.transaction(transacting =>
    Promise.map(ids, id => User.forge({ id })
      .fetch({ transacting })
      .then((user) => {
        if (!user) return Promise.reject(new Error('User not found (this is a rollback)'));

        return user.destroy({ transacting }); // If something wrong happens here, a rollback is called
      })))
    .then(() => Promise.resolve('Transaction Complete! (this is a commit)'));

Here I'm just making a deleteUsers() function which receives an array of ids, then start a transaction to start looking for the users and then delete them. If any user is not found or one of the DELETE operations fails there will be a rollback.

Answer 2

Sequelizejs' docs shows that your first way is OK. I would go for second, for better control. Rest, nodejs - that means microservices. Even if not - I feel that single transaction per request is the right way.

If you need better control on what goes as "unit of work", then, perhaps I would put them as separate transactions. That puts additional load to the DB, so I would not go that way, if not needed.