Using Promises with Queries with Node.JS/NPM's MySQL Library

Question

I'm using the mysql NPM library with Node.JS and I'm trying to query a database and have the function return a Promise instead of requiring a callback. Here's what I've tried:

index.js:

var result = await asyncQuery.main(connection, "SELECT * FROM tbl WHERE id = ?", "1234567890")

asyncQuery.js:

var colors = require('colors') module.exports = { main: function(con, q, vars) { return new Promise((resolve, reject) => { if (!vars) { con.query(q, function (err, result) { if (err) { console.log(colors.red(err.stack)) return reject(err); } resolve(result) }) } else { con.query(q, vars, function (err, result) { if (err) { console.log(colors.red(err.stack)) return reject(err); } resolve(result) }) } }) } }

This works fine if no vars/args are passed (?), but once a ? is used, I get an error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

If I were to run a regular query, with a callback, this would work perfectly fine. There's something wrong with asyncQuery.js, but I'm not sure what the issue is.

Does anyone have a fix for this code, or an easier way to achieve my goal? I've heard of the Promise MySQL Library, but I'm not sure if it supports Promise and callback queries. If it does, let me know and I'll give it a shot.

NodeJS Version: 9.1.0

NPM Version: 5.5.1

NPM's MySQL Library Version: 2.15.0

Marcos Casagrande · Accepted Answer · 2018-06-01 00:51:02Z

That exact query works and will not trigger that error, you probably had a more complex query with multiple placeholders, such as:

SELECT * FROM tbl WHERE id = ? or ref_id = ?,

In that case instead of sending a string, you will need to send an array with as many values as ? you have.

So just send: ["1234567890", "1234567890"] instead of a string for the above query.

Furthermore, your method can be simplified into:

module.exports = { main(con, q, vars) { return new Promise((resolve, reject) => { con.query(q, vars, function(err, result) { if (err) { console.log(colors.red(err.stack)) return reject(err); } resolve(result) }) }) } }

Allowing you to drop the if/else.

As a personal note, I prefer the mysql2 package which already has a promise wrapper and it's faster than mysql, with the benefit of having almost the same API.

Woah this actually worked! What I don't understand is that my example query actually DID work, as I just found out, without the [], but a more complex query needed the [], even though it only had one ?. Nonetheless, thank you for the response
alternatively main: function(con, q, ...vars) { - or since ES2015 is a thing main(con, q, ...vars) {
@apxx To be honest always sent an object/array, it's now working for me sending a string, but not in every case, so if you provide your query, I can edit my answer a little bit.

colxi · Accepted Answer · 2018-05-31 23:55:25Z

You can use the mysql-promise package from npmjs, wich behaves as a wrapper for mysql and mysql2 : https://www.npmjs.com/package/mysql-promise

Usage example:

var db = require('mysql-promise')(); db.configure({ "host": "localhost", "user": "foo", "password": "bar", "database": "db" }); db.query('UPDATE foo SET key = ?', ['value']) .then( function(){ return db.query('SELECT * FROM foo'); }) .spread(function (rows) { console.log('Loook at all the foo', rows); });

Collectives™ on Stack Overflow

Using Promises with Queries with Node.JS/NPM's MySQL Library

2 Answers 2

3 Comments

Comments

Hot Network Questions