CODEWITHSUNDEEP
mysqlsequelize.js
kevLinK
kevLinK

Reputation: 97

Getting auto-generated (via trigger) field from an insert in sequelize

I have a base controller for generic insert/update operations across the whole API, using only a table dictionary so we can use the same function to insert data into many tables.

The problem is there is a table that uses a correlative number generated via trigger, and when sequelize returns the inserted value, it includes the new ID but the correlative field returns empty, and I need it to show it on the interface.

I've thought of just querying the new field again to the API, or querying it on the same save function again when it includes these certain tables names, but is there a way to tell sequelize to "wait" for this new generated value and then return the data alright? Just like getting the new ID

Or maybe this needs to be fixed on the database? I don't have much experience in that field, but we are using MySQL if that helps.

function Init(models, dictionary) {
    this.post = (req, res, next) => {
        const { obj } = req.body;
        const model = models[dictionary[obj._type]];

        //Just stripping fields starting with "_"
        const objClear = {};
        for (const attr in obj) {
            if (attr.charAt(0) !== '_') {
                objClear[attr] = obj[attr];
            }
        }

        //Saving
        model.create(objClear).then(
            (objSaved) => {
                const data = {
                    obj: objSaved.get({ plain: true }),
                    action: 'inserted',
                };

                //I guess I could query the new row here again
                res.json(data);
            },
        ).catch(next);
    };
}
module.exports = {
    Init,
};

The response looks like:

{"obj":{"TOTAL":"0","ID":14,...,"TRANSACTION_NO":""},"action":"inserted"}

Where TRANSACTION_NO is the field generated with a trigger.

Upvotes: 0

Views: 427

Answers (1)

KenOn10
KenOn10

Reputation: 1968

AFAIK, you have to query the new row unless you use Postgres (in which case you might try the Model.create option called "options.returning")

Two quick tests that did NOT solve the problem:

  • an afterCreate hook - the model still shows fields created by a trigger as null.
  • a model having a default value from a DB function - the model shows the function call, not the result of the function (which does make it to the DB field).

Hope someone else has a solution!

Upvotes: 1

Related Questions