'node.js async/await using with MySQL
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;
});
Solution 1:[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:[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:[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:[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:[5]
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);
}Solution 6:[6]
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
}
Solution 7:[7]
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 => {});
Solution 8:[8]
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)
});
Solution 9:[9]
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.
Solution 10:[10]
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();
Solution 11:[11]
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);
}Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow