Getting Query object instead of results with Async/await function and npm-mysql

Question

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 categories WHERE parent_id=?';
      var results = await connection.query(sql, id);
      // console.log(results);
      return(results);
    } catch(err) {
      console.log(err);
      return false;
    }
  }

But instead of results inside the results variable i just get the query object.

Query {                                                                                                                               
  _events:                                                                                                                            
   [Object: null prototype] {                                                                                                         
     error: [Function],                                                                                                               
     packet: [Function],                                                                                                              
     timeout: [Function],                                                                                                             
     end: [Function] },                                                                                                               
  _eventsCount: 4,                                                                                                                    
  _maxListeners: undefined,                                                                                                           
  _callback: undefined,                                                                                                               
  _callSite:                                                                                                                          
   Error                                                                                                                              
       at Protocol._enqueue (C:\Users\fedesc\Sites\borsalino\node_modules\mysql\lib\protocol\Protocol.js:144:48)                      
       at Connection.query (C:\Users\fedesc\Sites\borsalino\node_modules\mysql\lib\Connection.js:198:25)                              
       at sortCategory (C:\Users\fedesc\Sites\borsalino\server\routes\categories.js:35:38)                                            
       at router.post (C:\Users\fedesc\Sites\borsalino\server\routes\categories.js:48:31)                                             
       at process._tickCallback (internal/process/next_tick.js:68:7),                                                                 
  _ended: false,                                                                                                                      
  _timeout: undefined,                                                                                                                
  _timer: Timer { _object: [Circular], _timeout: null },                                                                              
  sql: 'SELECT * FROM categories WHERE parent_id=\'0\'',                                                                              
  values: '0',                                                                                                                        
  .... }

The query as seen in object is

    sql: 'SELECT * FROM categories WHERE parent_id=\'0\'',                                                                              
    values: '0',

EDIT#1

an async/await for INSERT query does works. it's only when i need to retrieve data back that i don't get it.

but i can't manage to get the results back even though i do have some in table that should return. i feel like there is something i still not quite understand about mysql and async calls.

thanks guys.

Hello World · Accepted Answer · 2020-06-25 09:46:31Z

2

I use async/await of mysql query like this:

var getCategories = function (id) {
    return new Promise(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 = await getCategories();
} catch (error) {
    console.log(error);
}

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

edited Jun 25, 2020 at 9:46

answered Jun 25, 2020 at 9:40

Hello World

2,9277 gold badges33 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fedesc Over a year ago

But this is just changing async/await to another version of Promise syntax (a dirtier one). i could leave my async function as is, and just change the connection action to return a callback. But i want to maintain in my code a unified aproach for promises. The try/catch is more readable to me. also a async/awaits for INSERT query works. it's only when i need to recieve data back that i don't get it.

Hello World Over a year ago

connection.query doesn't have that kind of syntax or function you have used in your code. var results = await connection.query(sql, id); <--- this is not a valid MySQL fn

Jim Thompson · Accepted Answer · 2020-10-15 16:23:42Z

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 support


const ac = awaitableConnection( config );
demoAwait();

async function demoAwait() {
    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();
    }
}

function awaitableConnection( config ) { // wrapped in a promise
    const 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

xMayank · Accepted Answer · 2020-06-25 09:35:41Z

1

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){
      return error;  
  }
  return results;
});

answered Jun 25, 2020 at 9:35

xMayank

1,9852 gold badges8 silver badges20 bronze badges

1 Comment

fedesc Over a year ago

Thanks an async/await for INSERT query does works. it's only when i need to retrieve data back that i don't get it.

fedesc · Accepted Answer · 2020-06-25 15:45:59Z

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();

  return this._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

//mysql
const mysql = require('mysql');
function Database() {
  this.connection = mysql.createConnection({
    host     : 'localhost',
    user     : '*************',
    password : '*************',
    database : '*************',
    multipleStatements: true
  });
  
  this.query = (sql, args) => {
    return new Promise((resolve, reject) => {
      this.connection.query(sql, args, (err, rows) => {
        if (err)
          return reject(err);
        resolve(rows);
      });
    });
  };

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

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);
      return false;
    }
  }

Hope this helps anyone.

Collectives™ on Stack Overflow

Getting Query object instead of results with Async/await function and npm-mysql

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related