Node.Js Async/Await Using with MySQL

Await/Async nodejs with mysql db won't giving result what is expected

You can not use con.query in such a way as it returns a function with more than just results. You also do not need to call con.connect() - it is implicit with every .query()

The following should work for you. I've included additional functions to illustrate the async nature of the approach.

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: process.env.HOST || '127.0.0.1',
  user: process.env.USER || 'local_user',
  password: process.env.PASSWORD || 'local_password',
  database: process.env.NAME || 'local_database'
});

const do_thing_one = async (payload) => {
  return new Promise((resolve, reject) => {
    payload.one = 'thing one';
    resolve(payload)
  });
};

const selectCountries = async (payload) => {
  return new Promise((resolve, reject) => {
    connection.query('SELECT countries.* FROM countries', function (error, results, fields) {
      if (error) reject(error);
      payload.countries = results
      resolve(payload);
    });
  });
};

const do_thing_three = async (payload) => {
  return new Promise((resolve, reject) => {
    payload.three = 'thing three'
    resolve(payload)
  });
};

const execute = async () => {
  let payload = {}
  await do_thing_one(payload)
  await selectCountries(payload)
  await do_thing_three(payload)
  console.log(payload);
}

execute();

Will log…

{
  one: 'thing one',
  countries: [
    RowDataPacket { id: 1, name: 'Brazil' },
    RowDataPacket { id: 2, name: 'China' },
    RowDataPacket { id: 3, name: 'Japan' }
  ],
  three: 'thing three'
}

How to do async calls to MySQL database in NodeJS

Ok so I finally managed to solve the issue. The main thing that had to be done was to switch from a callback-based code to the more modern async/away way to handle async code, this make the code much less complicated and easier to handle and read.

Also I switched from the mysql library to mysql2 library, which is more adapted to async functions. The final code looks like this:

const mysql2 = require('mysql2/promise');

// Connect to server
const pool = mysql2.createPool({
    host     : "ENDPOINT",
    user     : "USERNAME",
    password : "PASSWORD",
    port     : "3306",
    database : "DATABASENAME",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Function for checking the user in the database
async function checkUser(username, displayName, profilePicture, email) {
  const result = await pool.query('SELECT * from users WHERE username = ?', [username]);
  if (result[0].length < 1) {
    console.log('User not found, adding new user...');
    const newResult = await pool.query('INSERT INTO users (username, displayname, profilePicture, email) VALUES (?, ?, ?, ?)', [username, displayName, profilePicture, email]);
    return newResult[0].insertId;
  }
  console.log('User found in DB')
  return result[0][0].user_id;
}

// Calling the check function with the input values from the user
async function check() {
let user = await checkUser(aUser.username, aUser.displayName, aUser.profilePicture, aUser.email);
console.log(`user ID is: ${user}`);
}

// Invoking the check function      
check();

Can't figure out how to do async functions in NodeJS for MySQL

const express = require('express');
const app = express();
const mysql = require('mysql2'); // async/await works only in mysql2, also you need to use mysql2 as dependency (npm i mysql2)

app.use(express.json())

mysql.createConnection({
  host     : 'localhost',
  user     : 'uname',
  password : 'password',
  database : 'users'
}); //keeps always connected to the db

app.get('/user/:name', async (req, res) => {
   const name = req.params.name;
   const user = await mysql.query(`select fname from users where fname = ${name}`);
   console.log(user); 
});

app.listen(3000, () => console.log('Server started listening on port 3000');

My recommendations:

1. Give meaningful names to functions and variables;
2. Learn to connect and retrieve data from db with ORM (my favorite ones Objectionjs + Knexjs);

Nodejs async/await for MySQL queries

To await you need a Promise, Not Callback. In your case you are not returning a promise to await.

router.post('/assign_new_item_id', async (req, res) => {
    // .....
    try {
        let qr1 = "INSERT INTO foo1 ........;"
        await new Promise((res, rej) => {
            pool.query(qr1, (err, row) => {
                if (err) return rej(err);
                res(row);
            });
        });
        let qr2 = "UPDATE foo1 .....;"
        await new Promise((res, rej) => {
            pool.query(qr2, (err, row) => {
                if (err) return rej(err);
                res(row);
            });
        });
    } catch (err) {
        console.log(err)
    }
});

Here I am promisifing the pool.query method and returning a promise.

Nodejs async/await mysql queries

So I'm still using only the npm mysql package, but now I transformed all my queries into promises like below, so I can just wait until all the queries are completed.

const create = (idCliente, transactionId, amount, status) => {

    const sql = "INSERT INTO transactions SET ?";
    const params = {
        id_cliente: idCliente,
        transaction_id: transactionId,
        amount: amount,
        status: status
    };

    return new Promise((resolve, reject) => {
        pool.query(sql, params, (err, result) => {
            if (err) {
                return reject(err);
            }
            resolve(result);
        });
    });
};

then I use like this:

create(params)
       .then((result) => { 
           //call more queries here if needed 
       })
       .catch((err) => { });

---

Related Topics