Sequelize, reading the id of inserted row right after inserting it without commiting the transaction

Question

The perfect solution for me would be to insert a parent Model with some child associations and create on cascade the children associations. However, as I am starting with Sequelize and I did manage to get that working, I need to insert the data and set up the associations manually so far to get it working.

Basically, after inserting the parent row, I need to be able to get the autoincremented id (sequence managed by mysql, not sequelize) in order to assign that id as a foreign key to the child entity. Ideally I would like to execute everything atomically inside a transaction so if something goes wrong, we rollback everything. That being said, unless I commit the transaction manually after the insertion, I am unable to get the id of the parent entity. Is there a way to read the value prior the transaction has been commited, some dirty read?

this is the code so far:

const ipiInterestedParty = {
            ip_base_nr: '118',
        };
    const transaction = await session.transaction();
    const instance = await IpiInterestedParty.create(ipiInterestedParty, {transaction});
    await transaction.commit();
        const createdWithId = await IpiInterestedParty.findOne({
            where: {ip_base_nr: '118'}
        });

This is the only way I was able to get the parent id, however, as the transaction has been already commited, in case that after that there is any error after that, the transaction has been commited and cannot be undone, leaving the system inconsistent.

Any help please?

Answer 1

Usually you get instance with a primary key value after executing create method of a model, if you indicated in a model's PK autoIncrement: true. So that way you already have an id of a parent record to create child records. Don't forget to pass a transaction object to all methods that make CRUD inside a transaction.