Cannot add or update a child row SQL error

Question

I am trying to do the following transaction:

static async save(habit){
    await db.beginTransaction;
    try {
        await db.execute('SELECT @habitId:=MAX(habits.habitId)+1 FROM habits');
        await db.execute('INSERT INTO habits(habits.habitId, habits.name, habits.repeatVal, habits.notification, habits.color, habits.question, habits.id) VALUES (@habitId, ?, ?, ?, ?, ?, ?)',
            [habit.name, habit.repeatVal, habit.notification, habit.color, habit.question, habit.id]);
        await db.execute('INSERT INTO habit_status(habit_status.habitId, habit_status.date, habit_status.status) VALUES (@habitId, "2021-04-01", 0), (@habitId, "2021-04-02", 0), (@habitId, "2021-04-03", 0), (@habitId, "2021-04-04", 0), (@habitId, "2021-04-05", 0), (@habitId, "2021-04-06", 0), (@habitId, "2021-04-07", 0), (@habitId, "2021-04-08", 0)');
        await db.commit();

    } catch (err) {
        console.error(`Error occurred while creating habit: ${err.message}`, err);
        db.rollback(function(){});
        console.info('Rollback successful');
        return 'error creating habit';
    }
}

Unfortunately I am getting the following error:

Error occurred while creating habit: Cannot add or update a child row: a foreign key constraint fails (intend.habit_status, CONSTRAINT habit FOREIGN KEY (habitId) REFERENCES habits (habitId) ON DELETE CASCADE) Error: Cannot add or update a child row: a foreign key constraint fails (intend.habit_status, CONSTRAINT habit FOREIGN KEY (habitId) REFERENCES habits (habitId) ON DELETE CASCADE)

As well as:

TypeError: db.rollback is not a function

I think it's because @habitId is probably not passing the habitId to my third execute function. But why? And how to fix this?

Answer 1

At least you didn't call beginTransaction properly forgetting to add brackets:

await db.beginTransaction();

As of db.rollback try to check if db is really a DB connection object you expect it to be.