Qfyilyi

I am running an Express server and connecting to MySQL Database. I am have two tables which are referenced via the user_id. When the user provides the email address I need to query users to find the user_id then run another query to join the tracking tables. What is the best way to achieve this? Also any best practice recommendations for any part of the code is appreciated!

Thanks

MySQL

CREATE TABLE users (

  user_id INT NOT NULL AUTO_INCREMENT UNIQUE,

  first_name VARCHAR(100) NOT NULL,

  last_name VARCHAR(100) DEFAULT NULL,

  email VARCHAR(320) NOT NULL UNIQUE,

  date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (user_id)

);



CREATE TABLE tracking (

  tracking_id INT NOT NULL AUTO_INCREMENT,

  user_id INT NOT NULL,

  metric VARCHAR(100) NOT NULL,

  unit VARCHAR(100) NOT NULL,

  amount DOUBLE DEFAULT NULL,

  date_tracked DATE,

  date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (tracking_id),

  INDEX user_ind (user_id),

  FOREIGN KEY (user_id)

   REFERENCES users(user_id)

   ON DELETE CASCADE

);

Express (Nodejs)

const express = require("express");

const app = express();

const port = 3000;

var mysql = require("mysql");

var connection = mysql.createConnection({

  host: "",

  user: "",

  password: "",

  database: "tracker"

});



app.get("/data/:email/metric/:metric", (req, res) => {

  console.log(req.params);

  let user_id = "";



  connection.connect();



  connection.query(

    `SELECT user_id FROM users WHERE email = '${req.params.email}';`,

    function(error, results, fields) {

      if (error) throw error;

      user_id = results[0].user_id;

    }

  );



  connection.query(

    `SELECT users.first_name, users.last_name, users.email, tracking.metric, tracking.unit, tracking.amount, tracking.date_tracked FROM users JOIN tracking ON users.user_id = tracking.user_id WHERE users.user_id = '${user_id}' AND metric = '${

      req.params.metric

    }';`,

    function(error, results, fields) {

      if (error) throw error;

      res.send(results);

    }

  );



  connection.end();

});



app.listen(port, () => console.log(`Example app listening on port ${port}!`));

You can accomplish this with a single query, just JOIN the tables on user_id

SELECT 

  u.first_name, 

  u.last_name, 

  u.email, 

  t.metric, 

  t.unit, 

  t.amount, 

  t.date_tracked 

FROM users u

INNER JOIN tracking AS t ON t.user_id = u.user_id

WHERE u.email = '<email>' AND t.metric = '<metric>'

Also, FWIW you should use parameterised queries to protect yourself from SQL Injection

const values = [

  req.params.email, 

  req.params.metric

];

connection.query(

  'SELECT ... WHERE u.email = ? AND t.metric = ?', 

  values, 

  (err, results, fields) => {

    if (err) return next(e); // propagate the error to Express, rather than throwing in the callback



    res.send(results);

  }

);

I need to query users to find the user_id then run another query to join the tracking tables. What is the best way to achieve this?

You can achieve it with only 1 query:

    SELECT users.first_name, users.last_name, users.email, 

            tracking.metric, tracking.unit, tracking.amount,

            tracking.date_tracked 

    FROM users 

    JOIN tracking 

    ON users.user_id = tracking.user_id 

    WHERE users.email = '${req.params.email}' AND 

          metric = '${req.params.metric}

Also any best practice recommendations for any part of the code is appreciated!

First of all, you should connect to mySql after the server as started .

Connecting&Disconnecting on each request will impact the performance(It's expensive task opening a socket and connecting outside)

so try something like :

    CONNECTION.connect().then(() => {

        // mysql is ready , let's start express server 

        // accepting connections

        app.listen(port, () => console.log(`ready on ${port}!`));

    }).catch(console.error)

Another good practice you should know is running queries in parallel
with Promise.all(). when you need to run multiple , unrelated queries ,
you can Execute them all one after the other , making the total wait time for all results much smaller .