How can I add a new patient mysql?

Question

I want to insert a new patient, first i have to insert in users but i don't know how insert in patient with a foreign key ('id_user') the id of users that i insert before.

This is my code:

app.post("/api/patient", async function(req, res){
let sql = "INSERT INTO users (email, password, name, surnames, sex, birth_date) VALUES ('"+req.body.email+"','"+req.body.password+"','"+req.body.name+"','"+req.body.surnames+"','"+req.body.sex+"','"+req.body.birth_date+"')";
const user = await query(sql);
var id = user[0].id;
console.log(id);

var sql2 = "INSERT INTO patients (sip, native_language, dependent, id_user) VALUES ('"+req.body.sip+"','"+req.body.native_language+"','"+req.body.dependent+"','"+id+"')";
const patient = await query(sql2);
  response.body = {
    code: 200,
    message: "Solicitud exitosa",
    data: patient
  } 
 res.status(response.body.code);
 res.send(response);
});

And I have the following error:

var id = user[0].id;
TypeError: Cannot read properties of undefined (reading 'id')

I am beginner programmer and I know my code is vulnerable to sql injections.

Answer 1

When executing INSERT or UPDATE statements mysql returns an object containing inserted row id, so: Change var id = user[0].id; to var id = user.insertId;