node.js async/await using with MySQL

Question

I need to get all results synchronized and append to a string with async/await keywords like c#

I am new to node.js and i can not adapt this new syntax to my code.

var string1 = '';
var string2 = '';
var string3 = '';
var string4 = '';

DatabasePool.getConnection(function(err, connection) {

        connection.query(query,function (err, result) {
            if (err){};
            string1 = result;
        });

        connection.query(query,function (err, result) {
            if (err){};
            string2 = result;
        });     

        connection.query(query,function (err, result) {
            if (err){};
            string2 = result;   
        });

        connection.query(query,function (err, result) {
            if (err){};
            string2 = result;
        }); 

       // i need to append all these strings to appended_text but  
       // all variables remain blank because below code runs first.
       var appended_text = string1 + string2 + string3 + string4;


});

Answer 1

if you happen to be in Node 8+, you can leverage the native util.promisify() with the node mysql.

Do not forget to call it with bind() so the this will not mess up:

const mysql = require('mysql'); // or use import if you use TS
const util = require('util');
const conn = mysql.createConnection({yourHOST/USER/PW/DB});

// node native promisify
const query = util.promisify(conn.query).bind(conn);

(async () => {
  try {
    const rows = await query('select count(*) as count from file_managed');
    console.log(rows);
  } finally {
    conn.end();
  }
})()

Answer 2

Assuming that your ORM that you are using it promise-based you can do something like this

async function buildString() {
  try {
    const connection = await DatabasePool.getConnection();
    const string1 = await connection.query(query);
    const string2 = await connection.query(query);
    const string3 = await connection.query(query);
    const string4 = await connection.query(query);

    return string1 + string2 + string3 + string4;
  } catch (err) {
    // do something
  }
}

Any promise can be used with async/await by putting await in front of the call. However, notice that this function must be used within an async function "wrapper". You need to handle the errors in try/catch blocks.

I also want to point out that these 4 queries are not run simulatneously. You'll still need to use Promise.all for that.

Answer 3

It seems you use mysqljs which isn't a promised based library. So you can't achieve what you want using this library. So what you can do is use a promised based library like Sequelize or else as a comment suggests:

use a tool like Bluebird's promisifyAll to wrap the library.

I don't know much about wrapping thing, so what I did was to switch to the sequelize.

Answer 4

Use mysql2 packet. It has promise wrapper so you can do that:

async function example1 () {
  const mysql = require('mysql2/promise');
  const conn = await mysql.createConnection({ database: test });
  let [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]);
}

Answer 5

If you want to use mysql (also called mysqljs) you have to do a little bit of work if you don't want to use a wrapper. But it's easy enough. Here is how the connect function would look like:

const mysql = require('mysql')

var my_connection = mysql.createConnection({ ... })

async function connect()
{
    try
    {
        await new Promise((resolve, reject) => {
            my_connection.connect(err => {
                return err ? reject(err) : resolve()
            })
        })
    }
    catch(err)
    {
        ...handle errors...
    }
}

connect()

As you can see the await will know how to handle a promise. You create such and use the resolve/reject functions in the callback implementation. That's all there is to it, really, so using a wrapper may be a bit much unless you access your database a lot.

Answer 6

You would have to make sure that the mysql library you are using either supports Promises, which are required by async/await, or use a tool like Bluebird's promisifyAll to wrap the library.

async function appendedText() {
  const connection = await DatabasePool.getConnectionAsync();
  const [string1, string2, string3, string4] = await [
    connection.query(query1),
    connection.query(query2),
    connection.query(query3),
    connection.query(query4),
  ];
  return string1 + string2 + string3 + string4;
}

Note that calling appendedText() will actually return a Promise and not a value.

appendedText().then(appended_text => {});