Nested mysql transactions between two tables using secelize.js file

I have 2 sequelize models (Event and Inventory associated with 2 tables). I created an Event._create method to use it to create an event on a db event with multiple products recorded in the db inventory at the same time. Each inventory is associated with the event_id of the newly created event.

Because all of these things have to succeed or fail altogether, I use the seelize transaction to achieve this.

At first I thought about doing something like this.

sequelize.transactionPromise = Promise.promisify(sequelize.transaction, sequelize);

return sequelize.transactionPromise({autocommit: 0})
.then(function(t) {
    return Event.create(ev, {transaction: t})
    .then(function(event){
        var event_id = event.id;  // ------ (*)
        return Promise.resolve([1, ..., event_number])
        .then(function(){
            Inventory.create({product_id: some_product_id, event_id: event_id}, 
            {transaction: t});
        });
    .then(function(){ 
        return Promise.cast(t.commit())
        .then(function() { // successfully committed  
            return res.json(d);
        }).catch(function(err){ // cannot commit somehow
            return res.json(500, err.toString());
        });
    }).catch(function(err){  // error rollback
        return Promise.cast(t.rollback())
        .then(function() {
            return Promise.reject('rollback: ' + err.toString());
        });
    });
});

      

        
        
        
      

    

But that doesn't work because before the transaction the transaction (*) is irrelevant and gives me an event_id of NULL.

Instead, I do something like below:

var Event = sequelize.model('Event'); 
var Inventory = sequelize.model('Inventory');

var _create = function(t, ev){
    var ev_id_secret = {secret: 'some random secret'};
    return Promise.cast(Event.create(ev_id_secret))
    .then(function(d){
        ev_id_secret.id = d.id;
        return true;
    }).then(function(){
        return Promise.resolve(_.range(ev.number_of_products))
        .map(function(){
            var inventory = {
                event_id: ev_id_secret.id,
                product_id: ev.product_id
            };
            return Promise.cast(Inventory._create(t, inventory));
        });
    }).then(function(){  // thennable a transaction
            return Promise.cast(Event.update(ev, ev_id_secret, {transaction: t}));
    });
};

      

        
        
        
      

    

So, I can do something like this.

sequelize.transactionPromise=Promise.promisify(sequelize.transaction, sequelize); 
return sequelize.transactionPromise({autocommit: 0})
.then(function(t) {
    return Event._create(t, ev)
    .then(function(){
        return Promise.cast(t.commit())
        .then(function() {
            return res.json(d);
        }).catch(function(err){
            return res.json(500, err.toString());
        });
    }).catch(function(err){
        return Promise.cast(t.rollback())
        .then(function() {
            return Promise.reject('rollback: ' + err.toString());
        });
    });
}).catch(function(err){
    console.log(err.stack);
    res.json(500, {error: err.toString()});
});

      

        
        
        
      

    

What I do with _create is that I just insert an empty event (with a randomly generated secret) in the Event db and some empty products in the Inventory db and then get the event_id using that secret to query, updating the event and inventory accordingly ...

The point is that when a promise is rejected, a transactional rollback () is called and empty records of events and products are left in the db. So I have to deal with empty entries later, which is really worrying.

So my question is how to make a transaction between two tables correctly? Am I on the right track?

ps: as a side question, you can see my code is filled with Promise.xxx statements, this provides control flow, but the promise gets really messy. Is there something I can do to improve my code? Thank.