joplin-notes/joplin notion extension/Web Development/nodejs/mysql connect.md

mysql connect

Install it first using npm i mysql2 as mysql v1 may have some issues

const mysql = require("mysql");

const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "hospitals",
  multipleStatements: true,
});

app.post("/register", (req, res) => {
  const { name, address, email, phone_no, password } = req.body;
  const { patient_photo } = req.files;
  const patientPhotoPathname = patient_photo.path;

	
	  // Store the registration details
  con.query(
    `use hospitals;
    insert into patients(name,address,email,phone_no,password,patient_photo_path)
    values ('${name}','${address}','${email}','${phone_no}','${password}','${patientPhotoPathname}')
        `,
    (err, result) => {
      if (err) {
        console.log(err);

        res
          .status(400)
          .json("Error while registering. Email address must be unique");
      } else {
        console.log(result);
        res.json("Registered");
      }
    }
  );
}
	
	app.get("/fetch", (req, res) => {
  const { hospital_id } = req.body;
	
	 // console.log("body:", req.body);
  const sqlquery = `select hospital_id,count(psychiatrists.id) as  psychiatrist_count,
  sum(patients_count) as total_patients_count,hospital.name
  from psychiatrists,hospital
  where hospital.id=psychiatrists.hospital_id and hospital_id=${hospital_id};
  select id,name,patients_count from psychiatrists
  where hospital_id=${hospital_id};
  `;

  con.query(sqlquery, (err, result) => {
    if (err) {
      res.status(500).json("Some error while fetching");
      console.log(err);
      return;
    }

    console.log(result);
}

• handling disconnection due to idle timeout

    function handleDisconnect() {
  con = mysql.createConnection(dbConfig); // Recreate the connection, since the old one cannot be reused.
  con.connect(function onConnect(err) {
    // The server is either down
    if (err) {
      // or restarting (takes a while sometimes).
      console.log("error when connecting to db:", err);
      setTimeout(handleDisconnect, 10000); // We introduce a delay before attempting to reconnect,
    } // to avoid a hot loop, and to allow our node script to
  }); // process asynchronous requests in the meantime.
  // If you're also serving http, display a 503 error.
  con.on("error", function onError(err) {
    console.log("db error", err);
    // if (err.code == "PROTOCOL_CONNECTION_LOST") {
    //   // Connection to the MySQL server is usually
    //   handleDisconnect(); // lost due to either server restart, or a
    // } else {
    //   // connnection idle timeout (the wait_timeout
    //   throw err; // server variable configures this)
    // }
    handleDisconnect();
  });
    }
    handleDisconnect();
  
    con.on("connection", () => {
      console.log("db connected");
    });