javascript - node.js asyncawait using with MySQL - Stack Overflow

admin管理员组

文章数量:1134246

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

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

• javascript
• mysql
• node.js
• asynchronous

Share Improve this question Follow edited Dec 1, 2019 at 1:18 Goran Stoyanov 2,31111 gold badge2222 silver badges3232 bronze badges asked May 16, 2017 at 14:34 Burc HaserginBurc Hasergin 51911 gold badge44 silver badges66 bronze badges 2

• Just checking, you are using a version of node that supports async/await, correct? – adam-beck Commented May 16, 2017 at 14:37
• yes the latest version. – Burc Hasergin Commented May 16, 2017 at 14:40

Add a comment  | 

12 Answers 12

Sorted by: Reset to default 100

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

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.

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

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

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
}

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

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

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

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

本文标签： javascriptnodejs asyncawait using with MySQLStack Overflow