How to use event-driven callbacks with mySQL queries efficiently?

Question

I had some issues with async calls, but after understanding event-driven functions in Node a bit better, I have the following code. It basically does 2 SQL queries. The first checks to see if an existing phone number is in the table. If there is, nothing happens. If there isn't, then it runs a second query to add a new value into the table.

Currently it's event-driven, but .. how do I re-design it so that the outer function query is a separate function, and can even be used as a validation check for code elsewhere? (basically turning it into a generic helper function)?

router.post('/', function(req, res){
    var mysql = req.app.get('mysql');

    //values for 1st query - initial check
    var customerCheck = "SELECT phone_number FROM customer WHERE phone_number=?";
    var phoneNumberCheck = [req.body.phone_number];

    //values for after 1st query validation passes
    var sql = "INSERT INTO customer (name, phone_number, points, is_activated) VALUES (?,?,?,?)";
    var inserts = [req.body.name, req.body.phone_number, 0, true];

    mysql.pool.query(customerCheck, phoneNumberCheck, function(error, results, fields){
        if(error){
            console.log("Failed to verify!");
            res.write(JSON.stringify(error));
            res.end();
        }else{
            console.log(results);
            if(results.length){
                console.log("phone number exists, not adding.");
                res.redirect('/view_customers');
            }else{
                mysql.pool.query(sql,inserts,function(error, results, fields){
                    if(error){
                        console.log("Failed to insert customer to DB!");
                        res.write(JSON.stringify(error));
                        res.end();
                    }else{
                        res.redirect('/view_customers');
                    }
                });

            }
        }
    });
});

Marcos Casagrande · Accepted Answer · 2018-03-17 23:39:00Z

Here's a more organized way to do it, of course it can be improved, but I leave that to you.

I added a customer class, where you have check and insert functions, now you can use them individually.

I wrapped the DB query in a promise, to avoid callback hell (Learn how to avoid it here)

Finally the router function has become really simple and easy to read.

customer.js

class Customer {

    constructor(mysql) {
        this.mysql = mysql;
    }

    async check(number) {

        const statement = 'SELECT phone_number FROM customer WHERE phone_number=?';

        const results = await this.query(statement, [number]);

        // If there is a customer with that phone number
        // we return true, false otherwise.
        return results.length;

    }

    insert(name, number) {
        const statement = 'INSERT INTO customer (name, phone_number, points, is_activated) VALUES (?,?,?,?)';

        return this.query(statement, [name, number, 0, true]);

    }

    query(query, placeholders) {
        // We wrap the query in a promise
        // So we can take advantage of async/await in the other functions
        // To make the code easier to read avoiding callback hell
        return new Promise((resolve, reject) => {

            this.mysql.pool.query(query, placeholders, (error, results) => {

                if(error)
                    return reject(error);

                resolve(results);

            });
        });
    }

}

module.exports = Customer;

router

const Customer = require('./customer');

// ... rest of your code

router.post('/', async(req, res) => {

    const customer = new Customer(req.app.get('mysql'));

    try {

        // We check whether the client exists or not
        const check = await customer.check(req.body.phone_number);

        if(!check) // We insert the customer if it doesn't exists
            await customer.insert(req.body.name, req.body.phone_number);

        // After successful insert or if the customer already exists
        // we redirect to /view_customers
        res.redirect('/view_customers');

    } catch(error) {

        // Probably a database error
        console.log('Oops');
        res.write(JSON.stringify(error));
        res.end();
    }

});

k0hamed · Accepted Answer · 2018-03-17 23:31:02Z

0

you can write a function for it that returns a promise to simplify things, something like:

function checkFn(mysql, v) {
  return new Promise((resolve, reject) => {
    var customerCheck = "SELECT phone_number FROM customer WHERE phone_number=?";
    var phoneNumberCheck = [v];
    mysql.pool.query(customerCheck, phoneNumberCheck, function(error, results, fields){
      if(error){
          console.log("Failed to verify!");
          reject();  // reject promise
      }else{
          console.log(results);
          if(results.length){
              console.log("phone number exists, not adding.");
              resolve(true);   // resolve promise with true
          }else{
              resolve(false);   // resolve promise with false
          }
      }
    })
  })
}

then you can use it with async/await

router.post('/', async function(req, res){    // notice: async
  try {
    let numberExist = await checkFn(req.app.get('mysql'), req.body.phone_number)
    if (numberExist) {
       // do something if number exists
    } else {
       // do something if number doesn't exist
    }
  }
  catch (e) {
    // do something if error
  }

read : Async function

edited Mar 17, 2018 at 23:31

answered Mar 17, 2018 at 23:08

k0hamed

5022 silver badges11 bronze badges

2 Comments

Marcos Casagrande Over a year ago

This doesn't work, checkFn doesn't return a promise.

k0hamed Over a year ago

@MarcosCasagrande umm,I just missed the lines as I was editing in the answer, fixed it.

Collectives™ on Stack Overflow

How to use event-driven callbacks with mySQL queries efficiently?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related