How to make a function to query MySQL in NodeJS?

Question

I made this:

const mysql = require('mysql2/promise')

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
})

async function query(query) {

    const result = await pool.query(query)
    return result[0]

}

console.log(query('SELECT * FROM `users`'))

and I got back

Promise { <pending> }

How do I get back my results from querying the database, just like PHP can do?
In PHP I never had to do such a thing like async/await and promises...

I also tried using mysql:

const mysql = require('mysql')

const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'nodejs'
})

function query(query) {
    db.query(query, (err, result) => {
        if (err) throw err
        return result
    })
}

console.log(query('SELECT * FROM `users`'))

but I got an undefined result

Rachit Anand · Answer 1 · 2020-04-19T01:58:36.170

3

I am not very familiar with MySQL and the libraries that you are using.

However, the Promise { <pending> } response that you are getting is because you didn't await your query execution.

Since the function is marked as async and is also performing an async action, it returns a Promise that needs to be awaited to be resolved.

The code below should work:

const mysql = require('mysql2/promise')

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
})

async function query(query) {

    const result = await pool.query(query)
    return result[0]

}

(async () => {
    const queryResult = await query('SELECT * FROM `users`');
    console.log(queryResult);
} )();

To understand how async-await works, consider the code below:

console.log('I will get printed first');
const asyncFunction = async () => {
   await setTimeout(()=> {}, 1000)
   console.log('I will get printed third');
   return 'hello'
}

(async () => {
  const result = await asyncFunction();
  console.log(`I will get printed last with result: ${result}`);
})();

console.log('I will get printed second');

The console.log statement I will get printed last with result will wait for the asyncFunction to complete execution before getting executed.

edited Apr 19 '20 at 01:58

answered Apr 17 '20 at 00:27

Rachit Anand

616
5
16

```const queryResult = await query('SELECT * FROM `users`') ^^^^^ SyntaxError: await is only valid in async function``` – zS1L3NT Apr 17 '20 at 00:52
I have updated my answer. The extra function added creates a dummy async function that helps you run the await command. – Rachit Anand Apr 17 '20 at 17:46
One last thing: If I add a ```console.log('Start')``` before calling the async function and a ```console.log('End')``` after calling the async function, the code outputs ```Start End {queryResult}``` instead of ```Start {queryResult} End```... – zS1L3NT Apr 17 '20 at 17:55
That is right. That is the non-blocking nature of NodeJS. In simple terms, say you are cooking three things, START, QUERY and END. You put START first on the burner, Next you put QUERY and at last you put end. START finishes cooking first, and you serve it. END finishes next and you serve that as well. However, QUERY cooks last and hence you serve it last. Node handles this with the help of an Event Loop. Here is an article to help you understand event loops: `https://www.tutorialspoint.com/nodejs/nodejs_event_loop.htm` – Rachit Anand Apr 17 '20 at 18:09
But if I cannot get this fixed, all that this question would have helped me to do is shorten my code by 2 lines but have much much more indentation... If it is possible, can edit the code in such a way that the END waits for QUERY to finish cooking before I cook END? – zS1L3NT Apr 18 '20 at 02:36
Inside the anonymous function, any code that you write after the `const queryResult = await query('SELECT * FROM `users`');`, will wait for the query to be executed. Thus in the code sample above, when `console.log(queryResult);` gets executed, the query has already executed and `queryResult` is populated with the results. Any code that you put outside the anonymous function will not wait for the query to be executed. – Rachit Anand Apr 18 '20 at 04:34

Atharva Jawalkar · Accepted Answer · 2020-04-20T06:47:05.180

try this:

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "yourusername",
    password: "yourpassword",
    database: "mydb"
});

// function definition
function runQuery (con, sqlQuery) {

    return new Promise((resolve, reject) => {
        console.log("START");
        if(con){
            con.connect(function (err) {
                if (err) throw err;
            });

            if (sqlQuery) {
                con.query(sqlQuery, function (error, result, fields) {
                    connection.end(); // end connection
                    if (error) {
                        throw error;
                    } else {
                        return resolve(result);
                    }
                });
            } else {
                connection.end(); // end connection
                // code:  handle the case 
            }
        } else {
            // code: handle the case
        }

    });

}

var sqlQuery = 'SELECT * FROM tableName';

// function call and pass the connection and sql query you want to execute
var p  = runQuery(con, sqlQuery); 
p.then((data)=>{ // promise and callback function
    console.log('data :', data); // result
    console.log("END");

});

One last part: If I add a console.log('Start') after the runQuery function and a console.log('End') to the end of the code, (This sandwiches the calling of the function runQuery), the code will output ```Start End data: {resultData}```. How do I put the ```{resultData}``` between the lines ```Start``` and ```End```? — zS1L3NT, Apr 17 '20 at 14:49
For your above query in comment, I edited my answer. Check that once. @zS1L3NT — Atharva Jawalkar, Apr 20 '20 at 06:49

score 0 · Answer 3 · answered Apr 17 '20 at 07:33

0

Try this:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

answered Apr 17 '20 at 07:33

Atharva Jawalkar

166
1
12

I want to make a function to query the database like query('SELECT * FROM users') and it would return all the users – zS1L3NT Apr 17 '20 at 09:06

How to make a function to query MySQL in NodeJS?

3 Answers3