How can I retrieve results from a MySQL query in Node.js?

Question

I have just started working with Node.js and need to retrieve some data from a database. That has thrown me into the world of promises and callbacks, which I having difficulty getting to work.

I am creating a connection pool, which works fine:

const mysql = require('mysql2');
var database = mysql.createPool({
    host: "localhost",
    user: "user",
    password: "password",
    database: "db"
});

Then my research has suggested that the following code should wrap the resolved database query into a function. This seems to work:

var query = "SELECT * FROM table;";
async function getResult(){
    const res = database.query(query);
    return res[0];
};

Finally, I want to consume the result and use it in the remaining program. However, this just returns an unresolved promise:

var result = getResult(); // This should be available to the global scope.
console.log(result); // Promise { <pending> }

I don't understand how to resolve the promise and actually get the data I need.

If I try to add an await in the function, I get an error:

async function getResult(){
    const res = await database.query(query);
    return res[0]; // ERROR
}; 

If I try to add an await to the function output, I also get an error:

var result = await getResult(); // ERROR

I feel there should be a simple step that I am missing.

EDIT: The errors I get are; for the await in the function:

You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require('mysql2/promise') instead of 'mysql2' for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper /var/www/html/nodejs/node_modules/mysql2/lib/commands/query.js:43 throw new Error(err); ^

Error: You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require('mysql2/promise') instead of 'mysql2' for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper at Query.then (/var/www/html/nodejs/node_modules/mysql2/lib/commands/query.js:43:11) at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v18.17.1

and for the await outside the function:

SyntaxError: await is only valid in async functions and the top level bodies of modules at internalCompileFunction (node:internal/vm:73:18) at wrapSafe (node:internal/modules/cjs/loader:1178:20) at Module._compile (node:internal/modules/cjs/loader:1220:27) at Module._extensions..js (node:internal/modules/cjs/loader:1310:10) at Module.load (node:internal/modules/cjs/loader:1119:32) at Module._load (node:internal/modules/cjs/loader:960:12) at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:81:12) at node:internal/main/run_main_module:23:47

Node.js v18.17.1

Answer 1

When you're using a MySQL database with a connection pool, and you want to perform a query and retrieve the results.

Please see:

1. Create a Database Connection Pool:

const mysql = require('mysql2');
const database = mysql.createPool({
    host: "localhost",
    user: "user",
    password: "password",
    database: "db"
});

2. Define a Function to Get Results:

var query = "SELECT * FROM table;";

async function getResult() {
    // Use 'await' to wait for the query to complete
    const [rows, fields] = await database.promise().query(query);
    return rows;  // Return the result
}

3. Call the Function in an Async Context:

async function main() {
    // Use 'await' when calling the function to get the actual result
    var result = await getResult();
    console.log(result);  // Log the result
}

// Call the main function to start the process
main();

This is working for me. Please let me know, if it's working as you intended.

Answer 2

Because my code is running inside a WebSockets server (a detail not in my OP, but is perhaps relevant), I am unsure how to bubble up the asynchronous functions to the calling WebSockets functions.

With help of Amit Mishra's answer, I have come up with a solution that seems to work in my situation though. I am just sending the result of the database query through the socket from inside the async function containing the query.

This achieves my goal, but seems like it may be a hack that should not be the accepted answer - I'd love to know why Node.js gurus might think this is a bad idea.

const WebSocketServer = require('ws').Server
const mysql = require('mysql2');

const server = new WebSocketServer({port: 8080});

var database = mysql.createPool({
    host: "localhost",
    user: "user",
    password: "password",
    database: "db"
});

server.on('connection', function(socket){
    socket.on('message', function(rawMessage){

        !async function(){
        
            var query = "SELECT * FROM table;";
            var [rows, fields] = await database.promise().query(query);

            socket.send(rows); // Actually the database response is formatted into a JSON object before sending.
        }();

    });
});