How to INSERT two tables at the same time and INSERT values received as an array

Question

I have 2 tables a and b.

Two tables must be created at the same time, and table b has the forking key of table a.

Also, table b receives values as an array and multiple can be added at the same time.

1. Is there a more accurate method than SELECT/ORDER BY DESC/LIMIT 1 when inserting the created id of table a into the foreign key of table b?

2. I want to know how to receive the values of table b as an array and process them at once. The format received as req.body is as follows.(JSON)

"typeId": [1, 2, 3]
"figure": [90, 100, 50]

And this should be applied to the query like below.

INSERT INTO b(a_id, type_id, count)
VALUES
  (0, 1, 90),
  (0, 2, 100),
  (0, 3, 50);

Below is my current query. DAO.js

const createData = async(user_id, a_data, a_id, type_id, count) => {
   await myDataSource.query(
     `INSERT INTO a (user_id, a_data)
   VALUES (?,?)`,
     [user_id, a_data]
   );

   const datas = await myDataSource.query(
     `INSERT INTO d(a_id, type_id, count)
   VALUES(?,?,?)`,
     [a_id, type_id, count]
   );
   return data;
};

I need help.

Answer 1

Point 1

The way you are trying to get last id of inserted is wrong. That won't work especially when you will have many users doing transaction same time.

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

Check this solution to get the last inserted record id

Above solution will give you inserted id record which you can use as foreign key for table b.

Point 2

To insert multiple data once in a table, below is the way

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Let me know if you need any further info.