node.js async/await using with MySQL
Solution 1
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();
}
})()
Solution 2
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]);
}
Solution 3
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.
Solution 4
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.
Solution 5
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
}
Admin
Updated on August 19, 2021Comments
-
Admin over 2 years
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; });