How to delete both element and the associated elements in join table with express.js and react.js-CodePudding

I have a project where I have two main tables: Contacts and Workers

I also have a join table called WorkerContacts

In my project, I give the user the option of deleting contacts, something that would also require deleting elements of the join table. My concern is that with my current setup (seen below), if I run into an error where I successfully delete a contact, but then fail to delete the associated join tables (resulting from an error), that would throw off everything. So my question is, is there a way to refactor this so that it ensures that both have been completed before doing the actual deletions then sending the promise to the front end?

Here's my current situation:

Frontend:

export const destroyContact = (contact_id) => dispatch => {
  
  axios.post(`http://localhost:3001/contacts/destroy`, {id: contact_id})
  .then(() => {
    dispatch({type: 'CONTACT_DESTROYED', payload: contact_id});
    axios.post(`http://localhost:3001/workerContacts/destroy`, {id: contact_id}) //I'm scared that the first thing will run but the second one won't, causing a lot of problems. We can deal with this by just throwing a big error message for the user hopefully
    .then(() => {
      dispatch({type: 'JOIN_TABLE_ROWS_DESTROYED', payload: contact_id});
    })
    .catch(err => dispatch({type: 'ERROR_CAUGHT', payload: {err_message: err.response.data.message, err_code: err.response.request.status, err_value: err.response.request.statusText}}))
  
    })
  .catch(err => dispatch({type: 'ERROR_CAUGHT', payload: {err_message: err.response.data.message, err_code: err.response.request.status, err_value: err.response.request.statusText}}))
}

I'm using redux as well so that's why I have all of the dispatch and whatnot, but essentially I've split the deletions into two axios calls: one where I delete the contact and one where I delete the join tables.

Backend:

For the contact I have this:

export const destroy = (req, res) => {
    // Here is when we want to remove an existing contact
    Contact.deleteMe(req.body.id)
    .then(() => res.json("Contact deleted"))
    .catch((err) => res.status(500).json({message: "Something went wrong when trying to save delete this. Try and reload the page and try again "}))
}

And the associated deleteMe function:

static deleteMe(customer_id){
        //Uses SQL to delete an individual customer element
        return db.execute('DELETE FROM contacts WHERE id = ?', [customer_id]);
    }

For the jointable, I have this:

export const destroy = (req, res) => {
    // Here is when we want to remove an existing contact
    JoinTable.deleteMe(req.body.id)
    .then(() => res.json("Join tables deleted"))
    .catch(err => res.status(500).json({message: "Something went wrong on our end. Try to reload the page and start again"}))
}

And the associated deleteMe function:

static deleteMe(customer_id){
        //Uses SQL to delete an individual customer element
        return db.execute('DELETE FROM workercontacts WHERE workerContacts.contact_id = ?', [customer_id]);
    }

I'm using a MySQL database if that helps.

Hopefully this is enough information, but if you require more, I can definitely provide you with it.

CodePudding user response：

Just use a single call and execute the DELETE commands in a transaction:

export const destroyContact = (contact_id) => (dispatch) => {
  axios
    .post(`http://localhost:3001/contacts/destroy`, { id: contact_id })
    .then(() => {
      dispatch({ type: 'CONTACT_DESTROYED', payload: contact_id });
      dispatch({ type: 'JOIN_TABLE_ROWS_DESTROYED', payload: contact_id });
    })
    .catch((err) =>
      dispatch({
        type: 'ERROR_CAUGHT',
        payload: {
          err_message: err.response.data.message,
          err_code: err.response.request.status,
          err_value: err.response.request.statusText,
        },
      })
    );
};

One the backend:

static async function deleteMe(customer_id) {
    await db.execute('START TRANSACTION');
    try {
        await db.execute('DELETE FROM contacts WHERE id = ?', [customer_id]);
        await db.execute('DELETE FROM workercontacts WHERE workerContacts.contact_id = ?', [customer_id]);
        await db.execute('COMMIT');
    } catch (err) {
        await db.execute('ROLLBACK');
    }
}

...

export const destroy = (req, res) => {
    // Here is when we want to remove an existing contact
    Contact.deleteMe(req.body.id)
        .then(() => res.json("Contact deleted"))
        .catch((err) => res.status(500).json({message: "Something went wrong when trying to save delete this. Try and reload the page and try again "}))
}