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.
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.
Share edited Jun 25, 2020 at 9:46 fedesc asked Jun 25, 2020 at 9:25 fedescfedesc 2,6103 gold badges25 silver badges39 bronze badges4 Answers
Reset to default 2I 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
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'; unment 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
As I can see in documentation https://www.npmjs./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;
});
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-pilers), 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.