How to FEATHERS-sequelize before create hook CALL a mysql function

Question

I am trying to reproduce Java/Hibernate/TABLE Sequence functionality https://dzone.com/articles/hibernate-identity-sequence using feathersjs-sequelize and mysql database.

So, I created the following function in mysql

FUNCTION `generate_pessoa_seq`() RETURNS bigint(16)
BEGIN
DECLARE R_ID BIGINT(16);
UPDATE pessoa_seq SET next_value = next_value + 1;
SELECT next_value INTO R_ID FROM pessoa_seq;
RETURN R_ID;

After that I tested in mysql cli:

mysql> select generate_pessoa_seq();
+-----------------------+
| generate_pessoa_seq() |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0,00 sec)

Ok. Now I can use 11 as the ID value for my table called "pessoa".

So I am trying to follow the idea bellow (pessoa hook)

const pessoaBeforeCreateHook = options => { // always wrap in a function so you can pass options and for consistency.
  return hook => {
    console.log('Pessoa hook ' + hook);

    sequelize = hook.app.get('sequelize');
    var newId = -1; //forcing an error
    sequelize.query('select generate_pessoa_seq();',{ type: sequelize.QueryTypes.SELECT}).then(function(response){

       //getNewId();
       newId = response;

    }).error(function(err){
      console.log(err);
      newId=-1;
    });
    hook.data.IdiPessoa=newId;
    return Promise.resolve(hook); // A good convention is to always return a promise.
  };
};

exports.before = {
  all: [],
  find: [],
  get: [],
  create: [pessoaBeforeCreateHook()],
  update: [],
  patch: [],
  remove: []
};

Thank you for any help.

Answer 1

daffl said:

The Sequelize query returns a promise and runs asynchronously, so any logic that you want to run once it resolves needs to be in the .then handler. You also have to return that promise from the hook function so that it will wait until it is done:

So, the final code is here:

pessoaService.hooks({
  before: {
    create(hook) {
    const sequelize = app.get('sequelize');
    var newId = -1; //forcing an error 
    return sequelize.query('select generate_pessoa_seq() as nextId;',{
      nest: true,
      raw: true }).then(function(response){
        //getNewId();
        var stringId = response[0];
        newId =  parseInt(stringId.nextId, 10);
        hook.data.IdiPessoa=newId;
        return hook;
      }).error(function(err){
        console.log(err);
        return hook; 
      });
    }
  }
});