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

Yes, getResult() returns a promise, and you must wait for the result. There's no way around this. — Bergi
– Bergi, Commented Jan 18, 2024 at 17:11
It is not clear to me though how I should wait for the result. Can you point me in the right direction? — randr
– randr, Commented Jan 18, 2024 at 17:13
See stackoverflow.com/q/29516390 for the general answer. Both the awaits you have placed seem appropriate. But unless you tell us what error they "caused" (exposed), we can't tell you what the problem is or how to fix it. — Bergi
– Bergi, Commented Jan 18, 2024 at 17:18
Thanks for the update. For the first error, the message is quite clear on what you need to change. For the second, you must place your await getResult() call inside an async function, where were you trying to use it? — Bergi
– Bergi, Commented Jan 18, 2024 at 17:23

Oliver Nybo · Accepted Answer · 2024-01-23 15:27:39Z

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.

randr · Accepted Answer · 2024-01-19 08:16:04Z

0

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.
        }();

    });
});

answered Jan 19, 2024 at 8:16

randr

3443 silver badges11 bronze badges

2 Comments

Bergi Over a year ago

You might not need that IIFE, you can just mark the function that you are passing to socket.on('message', …) as async

randr Over a year ago

Yup. You are right, that is more elegant. socket.on('message', async function(rawMessage){... and just remove the IIFE. It works great.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related