Transactions support

[mogadanez]

Readme says that adapter supports transactions. but not found any other mentions even in sources.
Is it any guides how to use it?

[mogadanez]

Some notes:

1. patch method

https://github.com/feathersjs-ecosystem/feathers-sequelize/blob/master/lib/index.js#L180
https://github.com/feathersjs-ecosystem/feathers-sequelize/blob/master/lib/index.js#L189

getModel method accept params but update method accept constructed options which use params.sequelize

So for right use transaction I need pass params like
{ sequelize: {transaction: t}, transaction: t}

2.events
events not have any knowledge about transactions, so emit message to client even if transaction is reverted.

[jumika]

It also worth mentioning, that update method doesn't even support transactions, or any sequelize parameters.

https://github.com/feathersjs-ecosystem/feathers-sequelize/blob/master/lib/index.js#L256
return instance.update(copy, {raw: false}).then(() => this._get(id, {sequelize: options}));
The only parameter passed to the update sequelize call is { raw: false }

[jumika]

For the events i created these hooks:

import { SKIP } from '@feathersjs/feathers';

function stackTransactionEvents(context) {
  const { params, service } = context;
  if(!params.sequelize || !params.sequelize.transaction) {
    return context;
  }

  if(!params.stackedEvents) params.stackedEvents = [];
  let event;
  switch(context.method) {
    case 'create':
    case 'update':
    case 'remove':
      event = context.method + 'd';
      break;
    case 'patch':
      event = 'patched';
      break;
  }
  if(event) {
    params.stackedEvents.push({
      service,
      event,
      data: context.dispatch ? context.dispatch : context.result,
      context
    });
  }
  return SKIP;
}

function unstackTransactionEvents(context) {
  const { params } = context;
  if(!params.sequelize || !params.sequelize.transaction || !params.stackedEvents) {
    return context;
  }
  params.stackedEvents.forEach(function(stackedEvent) {
    const {service, event, context} = stackedEvent;
    let { data } = stackedEvent;
    if(!Array.isArray(data)) {
      data = [data];
    }
    console.log('EMITTING ' + event);
    service.emit(event, data, context);
  });
  return context;
}

Use something like this as a patch before hook:
(Where content and route are associated models with respective services)

function patchTransaction(context) {
  const { data , app, service, params } = context;
  if(params.sequelize && params.sequelize.transaction) {
    return context;
  }
  // If the associated content ad route are sent with the data, then a transaction is created
  if(data.content && data.route) {
    const seq = app.get('sequelizeClient');
    return seq.transaction(function(t) {
      const paramsWithTransaction = {sequelize: {transaction: t}};
      return app.service('content').patch(data.contentId, data.content, paramsWithTransaction)
      .then(function() {
        return app.service('route').patch(data.routeId, data.route, paramsWithTransaction)
        .then(function() {
          return service.patch(data.id, data, paramsWithTransaction)
        });
      });
    }).then(async function() {
      context.result = await service.get(data.id);
      return SKIP;
    });
  }
  return context;
}

You have to use the stackTransactionEvents as the last after hook on your services which are called in your transaction. With returning SKIP the automatic event dispatching is disabled.

You have to use the unstackTransactionEvents as the last after hook on your service which creating the transaction.

Please keep inn mind, that I'm not a feathers expert. Maybe the code has some flaws, or design misconceptions.

It would be nice, if someone from the core team could take a look at it, and confirm this is a right approach.

@daffl What do you think?

[TimNZ]

Good catch on not wanting events to be emitted if the transaction is aborted.

I don't think the team will say this is a good approach as it's a hack.
Do you really want a SKIP in there as you have to remember your hook ordering?

I think it best if a hook.skipEvent prop is supported that the eventHook function, as a 'finally' hook called after 'after' hooks, checks whether to emit an event.

https://github.com/feathersjs/feathers/blob/master/lib/events.js#L6

const eventHook = exports.eventHook = function eventHook () {
  return function (hook) {
    if (hook.skipEvent) return; // <---- ADD THIS
    const { app, service } = hook;
    const eventName = app.eventMappings[hook.method];
    const isHookEvent = service._hookEvents && service._hookEvents.indexOf(eventName) !== -1;

    // If this event is not being sent yet and we are not in an error hook
    if (eventName && isHookEvent && hook.type !== 'error') {
      const results = Array.isArray(hook.result) ? hook.result : [ hook.result ];

      results.forEach(element => service.emit(eventName, element, hook));
    }
  };
};

I did a quick and dirty PR:
feathersjs/feathers#862

@daffl

[bernardobelchior]

Are there any guides on how to use transactions?
I'm trying but haven't been successful yet.

[TimNZ]

Feathers specific: Pass 'sequelize' as a param to your service calls, with a nested 'transaction'.

Read up on transactions in Sequelize for everything else to do with transaction lifetime.

app.get('sequelizeClient').transaction(transOptions, transaction => {
    return app.service('serviceName').create({data},{ sequelize: { transaction}  })
})

[bernardobelchior]

Thank you! Would you be interested in some documentation regarding transactions?

[TimNZ]

Just noticed the comments about update() not passing the transaction.

params.sequelize is not consistently passed to all sequelize calls, I'll do a PR.

[tunaung22]

My intention is to make multiple service call on before create hook.
When I tried inside a service's before create hook,
i got "TypeError: Converting circular structure to JSON" error.
What cause the problem ? Is there something wrong ?

myService.hooks.js:

  function (context) {
    ...
    app.get('sequelizeClient').transaction(transOptions, transaction => {
      return app.service('message').create({ text: "a new record" }, { sequelize: { transaction}  })
         .then(res => {
           // console.log('Result', res);
         })
         .catch(err => {
           // console.log('Error', err);
     });
  })

[bmbariah]

Does anyone know how to do transactions for an entire service? Something like what feathers-knex has. LINK

[RickVazquez]

any update on this?

[TimNZ]

Copy the feathers-knex hooks.js and change it to use unmanaged Sequelize transaction mechanism.

https://sequelize.org/master/manual/transactions.html

[TimNZ]

@tunaung2048 Sequelize transaction object has references back to parent => circular reference when console.log is traversing it to output.

My intention is to make multiple service call on before create hook.
When I tried inside a service's before create hook,
i got "TypeError: Converting circular structure to JSON" error.
What cause the problem ? Is there something wrong ?

myService.hooks.js:

  function (context) {
    ...
    app.get('sequelizeClient').transaction(transOptions, transaction => {
      return app.service('message').create({ text: "a new record" }, { sequelize: { transaction}  })
         .then(res => {
           // console.log('Result', res);
         })
         .catch(err => {
           // console.log('Error', err);
     });
  })

[bmbariah]

This is how I manage system-wide transaction support for ALL sequelize requests. Add the following in app.hooks.js

// Application hooks that run for every service
module.exports = {
  before: {
    all: [function (context) {
      const sequelize = context.app.get('sequelizeClient');
      context.params.transaction = sequelize.transaction();
      //console.log(context);
    }],
    find: [],
    get: [],
    create: [],
    update: [],
    patch: [],
    remove: []
  },

  after: {
    all: [function (context) {
      //context.params.transaction.commit();
      return context.params.transaction.then(t => {
        return t.commit();
      });
    }],
    find: [],
    get: [],
    create: [],
    update: [],
    patch: [],
    remove: []
  },

  error: {
    all: [function (context) {
      //context.params.transaction.rollback();
      return context.params.transaction.then(t => {
        return t.rollback();
      });
    }],
    find: [],
    get: [],
    create: [],
    update: [],
    patch: [],
    remove: []
  }
};
`

[edwardsmarkf]

this is a fantastic cookbook recipe. Thank you, Mark Edwards

…

On Tue, Jan 21, 2020 at 9:53 AM Boniface Mbaria ***@***.***> wrote: This is how I manage system-wide transaction support for ALL sequelize requests. Add the following in app.hooks.js // Application hooks that run for every servicemodule.exports = { before: { all: [function (context) { const sequelize = context.app.get('sequelizeClient'); context.params.transaction = sequelize.transaction(); //console.log(context); }], find: [], get: [], create: [], update: [], patch: [], remove: [] }, after: { all: [function (context) { //context.params.transaction.commit(); return context.params.transaction.then(t => { return t.commit(); }); }], find: [], get: [], create: [], update: [], patch: [], remove: [] }, error: { all: [function (context) { //context.params.transaction.rollback(); return context.params.transaction.then(t => { return t.rollback(); }); }], find: [], get: [], create: [], update: [], patch: [], remove: [] } };` — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#188?email_source=notifications&email_token=AAWJ3YQQ7QWXOKQ6AHHQEQLQ64R7TA5CNFSM4EMX2TDKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJQOE4Y#issuecomment-576774771>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAWJ3YWKA23KOIBVV5OEK3TQ64R7TANCNFSM4EMX2TDA> .

[Earthii]

How would you approach this problem if you need the reference to the same transaction in the service and in an after hook? If there is an error in the service or in an after hook (to create a related entity), could you do the roll back in the error:all hook?

[corepay]

@Earthii

Don't know if this helps but all my tables have a last_request_id (UUID) column. In my app.hooks.js I add this:

const { v1: uuidv1 } = require('uuid');

before: {
    all: [
      async context => {
        const { params } = context;
        if (!params.request_id) {
          params.request_id = uuidv1();
        }
     ],
     create: [
        async context => {
          const { data } = context;
          data.last_request_id = params.request_id;
      ],
     update: [
        async context => {
          const { data } = context;
          data.last_request_id = params.request_id;
      ],
     patch: [
       async context => {
          const { data } = context;
          data.last_request_id = params.request_id;
      ]

This adds the same uuid to last_request_id on any table that is part of the current transaction before it is created/updated/patched and allows me to find affected tables and rows in subsequent hooks.

To identify the paths/services in current request associated with the `last_request_id I also add the following to app.hooks.js

before: {
    all: [
      async context => {
        const { params, path } = context;
        if (!params.request_id) {
          params.request_id = uuidv1();
        }

        if (!params.rollback) {
           context.params.rollback = [];
        }
        context.params.rollback.push(path);
     ],

Now you will have access to the services that were processed in the current request where you need them

console.log(params.rollback);
//   [ 'users', 'emails', 'phones' ]

[quentin-roche]

Thanks everyone! This is a mix of multiple solutions.

It seems to work for me when I call a service from another hook using:

const file = await context.app.service('files').create({
                        parentId: context.data.parentId,
                        type: 'PDF',
                        name: 'my_neme.pdf'
                    }, context.params);

sequelize-transaction.ts

/*
 * Use this hook to manipulate incoming or outgoing data.
 * For more information on hooks see: http://docs.feathersjs.com/api/hooks.html
 */
import {HookContext, Service} from '@feathersjs/feathers';
import {Transaction} from 'sequelize';
import {v4 as uuid} from 'uuid';

import Debug from 'debug';
// eslint-disable-next-line new-cap
const debug = Debug('sequelize-transaction');

interface StackedEvent {
    service: Service<any>;
    event: string;
    data: any;
    context: HookContext;
}

async function before (context: HookContext): Promise<void> {
    debug('transaction create? ======================================================================================');
    const uuidContext = uuid();
    if (context.params.uuidStack === undefined) {
        context.params.uuidStack = [] as string[];
    }
    if (context.params.stackedEvents === undefined) {
        context.params.stackedEvents = [] as StackedEvent[];
    }
    context.params.uuidStack.push(uuidContext);
    debug(`context uuid ${uuidContext}`);
    debug(`context uuidStack [${context.params.uuidStack}]`);
    debug(`context transactionOwner ${context.params.transactionOwner}`);
    debug(`context path ${context.path}`);
    debug(`context method ${context.method}`);
    const sequelize = context.app.get('sequelizeClient');

    // If no transaction created
    if (
        context.params.sequelize === null || context.params.sequelize === undefined ||
        context.params.sequelize.transaction === null || context.params.sequelize.transaction === undefined
    ) {
        // Create the transaction
        context.params.sequelize = context.params.sequelize || {};
        context.params.sequelize.transaction = await sequelize.transaction(
            Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED);
        context.params.transactionOwner = uuidContext;
        debug(`new transaction created with owner ${uuidContext}`);
    } else {
        debug(`no new transaction created. There is already a transaction from owner ${uuidContext}`);
    }
    debug('==========================================================================================================');
}


async function after (context: HookContext): Promise<void> {
    const uuidContext = context.params.uuidStack.pop();
    debug(`event ${context.params.event}`);
    debug(context.params.event);
    debug('transaction commit? ======================================================================================');
    debug(`context uuid ${uuidContext}`);
    debug(`context uuidStack [${context.params.uuidStack}]`);
    debug(`context transactionOwner ${context.params.transactionOwner}`);
    debug(`context path ${context.path}`);
    debug(`context method ${context.method}`);

    if (context.params.transactionOwner !== undefined) {
        // Prevent from emitting event
        context.event = null;
        // But add it to the stack of events
        let event;
        switch (context.method) {
            case 'create':
            case 'update':
            case 'remove':
                event = `${context.method}d`;
                break;
            case 'patch':
                event = 'patched';
                break;
            default:
                break;
        }


        if (event) {
            context.params.stackedEvents.push({
                service: context.service,
                event,
                data: context.dispatch ? context.dispatch : context.result,
                context
            } as StackedEvent);
            debug('added an event to the stack');
        } else {
            debug('WARNING: no event added');
        }

        if (uuidContext === context.params.transactionOwner) {
            debug('this is the last context in the stack.');
            debug('commit changes');
            await context.params.sequelize.transaction.commit();

            // Emit all events
            debug('emit all events');
            context.params.stackedEvents.forEach((stackedEvent: StackedEvent) => {
                debug('not the last event in the stack. Will not emit event');
                let data = stackedEvent.data;
                if (!Array.isArray(data)) {
                    data = [data];
                }
                debug(`    - ${stackedEvent.event} ${stackedEvent.context.path}`);
                stackedEvent.service.emit(
                    stackedEvent.event,
                    data,
                    stackedEvent.context
                );
            });
        } else {
            debug('not the last event in the stack. Will not commit/emit event');
        }
    }
    debug('==========================================================================================================');
}

async function error (context: HookContext): Promise<void> {
    const contextUuid = context.params.uuidStack.pop();
    debug(context.params.event);
    debug('transaction rollback? ====================================================================================');
    debug(`path ${context.path}`);
    debug(`method ${context.method}`);
    debug(`uuid ${contextUuid}`);
    debug(`uuidStack [${context.params.uuidStack}]`);
    debug(`transactionOwner ${context.params.transactionOwner}`);

    if (contextUuid === context.params.transactionOwner) {
        debug('this is the last context in the stack.');
        debug('rollback changes');
        await context.params.sequelize.transaction.rollback();
    } else {
        debug('not the last event in the stack. Will not rollback');
    }
    debug('==========================================================================================================');
}

export const SequelizeTransaction = {
    before,
    after,
    error
};

[denisgmarques]

Tks @edwardsmarkf edwardsmarkf and @yohane55 yohane55

In my case I wanted to use the same transaction across multiple services. When I send a POST to /notification service I wanted to save all tags, using my /tag service and save all on the same request/transaction.

So I did some changes on this hook and the behaviour was changed from transaction by service to transaction by request:

You can see more details here: https://github.com/denisgmarques/feathersjs-sequelize-transaction-across-multiple-services