Search code examples
mysqlnode.jsexpressnode-mysql

Proper way to execute sequence of mysql queries with javascipt mysql client

Simply put : I have a table 'a', a table 'b' and a many to many table 'a_b'. I need to insert a row in table 'b', and then if it was inserted properly, use the id of this one to insert it into 'a_b' to have it referencing id in table 'a'. Something like this :

INSERT INTO b (foo) VALUE ('bar');

Then take the resulting ID of this one to do

INSERT INTO a_b (a_id, b_id) VALUES ('some_table_a_id', 'generated_b_table_id');

I'm doing this on a NodeJS server Express API on a post request, so it would look somehting like this :

app.post("/whatever", (req, res) => {
  db.query(insert_into_b_sql, [params], (err, result, fields) => {
    if (!err) {
      db.query(insert_into_a_sql, [params], (err, result, fields) => {
        if (!err) {
          res.sendStatus(200);
        } else {
          throw err;
        }
      });
    } else {
      throw err;
    }
  });
});

I see many problems with this :

  1. I don't have the last ID of my inserted row, and I don't think I can get it. So I would need a third SQL query inside of this to get the ID. It already feels wrong to do it this way, so I think nesting a third SQL query would be even worse.
  2. I make many SQL queries, which can result in costs increase once it gets to prod.
  3. If the first query succeed, but the second does not, I need more queries to rollback because there is no commit mechanism (not that I know of).
  4. I think I could use stored procedure, but it's not a possibility here because it would need to be stored and maintained in a repo and team decided to avoid this.

So here we are : is it legit to chain SQL queries the way I described? If it is, is there any way to prevent the rollback necessity that I described? Else, what are the other options, considering that stored procedure should be avoided if possible?

Solution

  • I suppose you're using nodejs mysql library as it is marked in the title. For other libraries, you can keep the concept though.

    1. I don't have the last ID of my inserted row, and I don't think I can get it.

    The method query() should return the last inserted record. You can check the result and fields in the callback. It's also noted in the doc: https://www.npmjs.com/package/mysql#getting-the-id-of-an-inserted-row

    1. I make many SQL queries, which can result in costs increase once it gets to prod.

    Yeah, but based on the requirement, we must have 2 separate queries.

    1. If the first query succeed, but the second does not, I need more queries to rollback because there is no commit mechanism (not that I know of).

    You need to use transactions. Once a command in the transaction failed, you can roll back the whole. Check out the document here