node.js async/await using with MySQL

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

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]);
}

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.

Or use mysql-async-simple

https://www.npmjs.com/package/mysql-async-simple

const { makeDb } = require('mysql-async-simple');
const mysql = require("mysql");
 
const connection = mysql.createConnection({
    host: process.env.HOST,
    user: process.env.USER,
    password: process.env.PASSWORD,
    database: process.env.DB
});
const db = makeDb();
await db.connect(connection);
 
try {
    const users = await db.query(connection, 'SELECT * FROM users');
} catch (e) {
    // handle exception
} finally {
    await db.close(connection);
}

You can use the promise-mysql package like so:

const mysql = require('promise-mysql')

const getDbConnection = async () => {
  return await mysql.createConnection({
    host: process.env.HOST,
    user: process.env.USER,
    password: process.env.PASSWORD,
    database: process.env.DB
  })
}

const getUsers = async () => {
  const db = await getDbConnection()
  const users = await db.query("SELECT * FROM users")
  await db.end()
  return users
}

As stated by LeOn - Han Li, i include small modifications, since I had to work with the result.

var mysql = require('mysql');
const util = require('util');

const conn = mysql.createConnection({
  host     : '127.0.0.1',
  user     : 'user',
  password : 'password',
  database : 'database'
});

const query = util.promisify(conn.query).bind(conn);

let result = async function() {
    var userCourse = [];
    try {
        const rows = await query('select * as count from file_managed');
    } finally {
        conn.end();
        return userCourse;
    }
};

result()
.then(value => {
    console.log(value)
});

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.

Instead of using util or promise/mysql we can implement promise inside mysql.connect

var con = require('mysql');

var mysql = con.createConnection({
    host: "localhost",
    user: "root",
    password: "pass",
    database: "test"
});

async function asyncAwait(req, res) {
    var promise1;
    mysql.connect((err) => {
        promise1 = new Promise((resolve, reject) => {
            console.log('Mysql: Connected');
            resolve(response.write(uc.upperCase('Connected\n')));
        });
        promise1
            .then(() => {
             //Implement the logic here
            })
            .catch(error => {
                console.log(error)
            });
    })
}
await asyncAwait();