Getting Query Object Instead Of Results With Async/await Function And Npm-mysql

I have this function which is async and i'm trying to make a simple query from npm-mysql db. let sortCategory = async (id) => { try { var sql = 'SELECT * FROM categori

Solution 1:

I use async/await of mysql query like this:

var getCategories = function (id) {
    returnnewPromise(function (resolve, reject) {
        var sql = `SELECT * FROM categories WHERE parent_id=?`;
        connection.query(sql, [id], function (err, result) {
            if (!err) {
                resolve(result);
            } else {
                resolve({
                    status: "error",
                    message: "Error Getting Data",
                    debug: err
                });
            }
        });
    });
};

try {
    var categories = awaitgetCategories();
} catch (error) {
    console.log(error);
}

Above code is very different from yours but you can use the above method to use in further case

Solution 2:

Thank you for your helpful posts fedesc. I’d been struggling with this for days. Based on your lead, I ended up with this which is elegant relative to my earlier attempts:

'use strict';
const mysql = require('mysql');
const config = require('./config.js');
const util = require('util'); // needed for async supportconst ac = awaitableConnection( config );
demoAwait();

asyncfunctiondemoAwait() {
    try {
    const results1 = await ac.query( 'SELECT * FROM table' );
    const results2 = await ac.query( 'SELECT * FROM table WHERE whatever' );
    console.log(results1); // all above results are available// throw 'test error'; uncomment to test an error
    } catch ( err ) {
    console.log(err);
    } finally {
    await ac.close();
    }
}

functionawaitableConnection( config ) { // wrapped in a promiseconst connection = mysql.createConnection( config );
    return {
      query( sql, args ) {
        return util.promisify( connection.query )
          .call( connection, sql, args );
      },
      close() {
        return util.promisify( connection.end ).call( connection );
      }
    };
}

The technique remains readable when queries are placed in a loop. I have to acknowledge Michał Męciński for the pattern of this technique. In Sept 2019 he updated the article fedesc linked above while taking advantage of node.js 8 or later. The article also demonstrates how to use a similar technique for transactions. Node.js, MySQL and async/await

Solution 3:

As I can see in documentation https://www.npmjs.com/package/mysql

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
  // error will be an Error if one occurred during the query// results will contain the results of the query// fields will contain information about the returned results fields (if any)
});

You code should became

var sql = 'SELECT * FROM categories WHERE parent_id=?';

connection.query(sql, [id], function (error, results, fields) {
  if(error){
      returnerror;  
  }
  return results;
});

Solution 4:

1st of all thank you kind responders.

The answer of both of you was indeed the same and the correct one. So i just accepted the quickest responder.

NPM Mysql functions do operate in an old school callback style (and needs to be updated) What was really strange for me is that an INSERT statement did work out of the box - I guess this is because you don't really need a callback if you don't need data to be retrieved.

And async/await is part of node and not mysql.

So the call did indeed fired but without a callback.

Connection.prototype.query = function query(sql, values, cb) {
  var query = Connection.createQuery(sql, values, cb);
  query._connection = this;

  if (!(typeof sql === 'object' && 'typeCast'in sql)) {
    query.typeCast = this.config.typeCast;
  }

  if (query.sql) {
    query.sql = this.format(query.sql, query.values);
  }

  if (query._callback) {
    query._callback = wrapCallbackInDomain(this, query._callback);
  }

  this._implyConnect();

  returnthis._protocol._enqueue(query);
};

Therefore your answers are accurate and correct.

Allow me to elaborate on a possible solution i found for my problem with the hope that maybe it'll help readers who face this approach issue as well.

There is a workaround i've found when still searching for a solution here - How to use classes in Node.js (with no pre-compilers), and why you should and here - Node.js, MySQL and promises

The solution was "promisifying" mysql functions with a class alike function that converts all mysql functions to promises.

Which than will give the option to work with database in an async/await approach.

Also there are tools that promisify functions that uses callbacks like this one here

//mysqlconst mysql = require('mysql');
functionDatabase() {
  this.connection = mysql.createConnection({
    host     : 'localhost',
    user     : '*************',
    password : '*************',
    database : '*************',
    multipleStatements: true
  });
  
  this.query = (sql, args) => {
    returnnewPromise((resolve, reject) => {
      this.connection.query(sql, args, (err, rows) => {
        if (err)
          returnreject(err);
        resolve(rows);
      });
    });
  };

  this.close = () => {
    returnasync () => {
      try {
        this.connection.end(err => {
          if (err) throw err;
          return;
        });
      } catch(e) {
        return e;
      }
    }
  };
};
var connection = newDatabase();

Setting you db connection this way will allow you now to use async/await as in original question.

let sortCategory = async (id) => {
    try {
      var sql = 'SELECT * FROM categories WHERE parent_id=?';
      var results = await connection.query(sql, id);
      // console.log(results);return results;
    } catch(err) {
      console.log(err);
      returnfalse;
    }
  }

Hope this helps anyone.