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){};
            string3 = result;   
        });

        connection.query(query,function (err, result) {
            if (err){};
            string4 = 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;
});

Just checking, you are using a version of node that supports async/await, correct? — adam-beck, May 16 '17 at 14:37

LeOn - Han Li · Answer 1 · 2018-08-21T19:32:12.443

97

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

edited Aug 21 '18 at 19:32

answered Aug 04 '18 at 23:22

LeOn - Han Li

9,388
1
65
59

4

Thanks for the note on adding bind. I've spent way too long try to work out why I've been having issues with promisify. – Chris Owens Jan 18 '19 at 05:15
Thanks a lot for the hint on the correct way of doing the bind – fmquaglia Dec 21 '19 at 15:51
1

If i have to provide 2 arguments to query function, typescript giving error "expected 1 argument". I am using two arguments because query string contains a `?`. Is there a way to avoid appending second argument in single query string? – Amit Kumar May 12 '20 at 14:59
For typescript: const query: (arg1: string, arg2?: string[])=>Promise = promisify(db.query).bind(db); – Amit Kumar May 12 '20 at 15:11
The mysql-async-simple package does that for you. – Hash Oct 10 '20 at 15:23
@AmitKumar, for TS, instead of passing queryString, you can also use the `QueryOptions` as the 1st argument, which contains `sql`,`values` and other options. – LeOn - Han Li Nov 15 '21 at 18:37

score 71 · Answer 2 · answered Nov 13 '17 at 10:43

71

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

answered Nov 13 '17 at 10:43

mr_squall

2,215
22
19

12

This is the superior answer... Holy cow Mysql2 is *MUCH FASTER* than mysql, and offers tons of stuff mysql does not, like promises and compression and prepared statements. – Nick Steele Apr 25 '19 at 01:52
4

dont forget to end connection after executing query with await conn.end(); or better use pool instead of connections like createPool and ending pool. – Fuad All Apr 17 '20 at 12:32
1

This should be the accepted answer as mysql is now deprecated, mysql2 is still maintained to this day and supports async – Epic Speedy Apr 16 '21 at 16:12
I am a little confused here, do I need to create a connection to MySQL with each query or make conn as global and use in all routes? – Ali Raza Nov 25 '21 at 06:45
1

`https://npmcompare.com/compare/mysql,mysql2` --> `mysql2` is a better package, also it has more meaning full error messages which this comparison won't tell – garg10may Jan 07 '22 at 11:57

adam-beck · Answer 3 · 2017-05-16T15:00:37.270

13

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.

edited May 16 '17 at 15:00

answered May 16 '17 at 14:47

adam-beck

5,659
5
20
34

1

Be aware that await can only be used within async methods. – Wilco Bakker May 16 '17 at 14:48
1

Thanks @WilcoBakker. I was actually just editing my answer :) – adam-beck May 16 '17 at 14:49
I use mysql package.I think this is not promise-based?Which mysql package should i use ? – Burc Hasergin May 16 '17 at 14:53
There are quite a few so I think you may want to take some time to explore the tradeoffs. Some that you may come across are: objection.js, bookshelf, sequelize – adam-beck May 16 '17 at 14:56
how can i get the returned row values from the queries instead of getting strings? – Burc Hasergin May 16 '17 at 18:30

score 6 · Answer 4 · answered Dec 14 '18 at 20:56

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.

score 3 · Answer 5 · answered Oct 09 '20 at 03:04

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

score 3 · Answer 6 · answered Dec 11 '20 at 14:29

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

score 2 · Answer 7 · answered Nov 25 '20 at 11:18

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
}

score 1 · Answer 8 · answered May 16 '17 at 14:49

1

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 => {});

answered May 16 '17 at 14:49

Explosion Pills

188,624
52
326
405

2

Can you really use await like that to mimic Promise.all? Any idea where I can find more information on that because my quick googling has returned very complex methods. – adam-beck May 16 '17 at 14:54
@adam-beck, it was working for a while... it requires a `Promise.all()` now, though. See here: https://stackoverflow.com/questions/34382710/es7-getting-result-from-an-array-of-promises-using-await-generator#answer-34384344 – Alexis Wilke Aug 20 '19 at 16:20

score 1 · Answer 9 · answered Apr 20 '22 at 10:43

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

score 0 · Answer 10 · edited Dec 14 '18 at 19:20

0

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.

edited Dec 14 '18 at 19:20

Alexis Wilke

19,179
10
84
156

answered Jul 18 '18 at 09:42

margherita pizza

6,623
23
84
152

score 0 · Answer 11 · answered Jun 17 '21 at 07:51

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

score 0 · Answer 12 · answered May 22 '23 at 00:00

In my case i had to create a helper function which is a promise and resolves as follows

const mysqlQuery = async (connection, queryConfig) => {
    const config = { ...queryConfig, timeout: 4000 }

   return new Promise((resolve, reject) => {
     return connection.query(config, function (error, results, fields) {
        if (error) {
            reject(error);
        }
        resolve(results);
      });
   })
}

Now i can easily use the above like

    const response = await mysqlQuery(connection, {
       sql: 'SELECT * FROM `user`',
    });

     console.log(response)

where connection is you mysql connection as like below

    var connection = mysql.createConnection({
        host,
        user, //
        password, //
        database,
        port
    })
   
    connection.connect(async (err) => {
          if(err){
             //connection error
             return;
           }

         //successifull connection
     })

node.js async/await using with MySQL

12 Answers12

Linked

Related