How to Synchronize MySQL Queries with Async/Await in Node.js?

Synchronizing MySQL Queries with Async/Await in Node.js

In Node.js, the async/await keywords allow for asynchronous code to be written in a synchronous manner. For database operations, such as MySQL queries, this can be particularly useful for ensuring that all results are obtained before continuing.

Challenge: Asynchronous Queries

Consider the following code snippet, which attempts to retrieve multiple database results and append them to a string:

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

In this code, each query is executed asynchronously, leading to undefined behavior for the appended_text variable. To solve this issue, Node.js 8 introduces a powerful feature.

Solution: Promisification with Async/Await

To synchronize the queries using async/await, you can leverage the native util.promisify() function in conjunction with the node mysql module. Here's an example:

const mysql = require('mysql');
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 {
    // Execute your queries sequentially using await
    const rows1 = await query('select count(*) as count from file_managed');
    const rows2 = await query('select count(*) as count from file_managed');
    const rows3 = await query('select count(*) as count from file_managed');
    const rows4 = await query('select count(*) as count from file_managed');

    // Append the results to the appended_text variable
    var appended_text = rows1.count + rows2.count + rows3.count + rows4.count;

    console.log(appended_text);
  } finally {
    conn.end();
  }
})();

In this code:

1. You first promisify the connection's query method to return a Promise.
2. Within the async function, you execute your queries sequentially and await their results.
3. Finally, you can append the results to the appended_text variable in a synchronized manner.

Using async/await with node mysql simplifies the synchronization of database queries, allowing for more readable and maintainable code.

The above is the detailed content of How to Synchronize MySQL Queries with Async/Await in Node.js?. For more information, please follow other related articles on the PHP Chinese website!