In node.js, How to return mysql results from a function?

Question

I tried to separate function to another file, as the function fetching data from mysql database.

This is db.js

const mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "sample"
});

con.connect()

module.exports = function(query) {
    con.query(query, function (err, result) {
        if (err){
            console.log(err);
        } else{
            console.log(result)
            return result
        }
    });
};

This is main.js

const express = require('express')
const db = require('./db')

const app = express()

app.get('/test', function(req, res){
    var sql = "SELECT id FROM user"
    console.log(db(sql))
    res.send(db(sql))
});

In main.js on console.log(db(sql)) got undefined.

But in db.js on console.log(result) I got the values as:

[
  RowDataPacket { id: 1 },
  RowDataPacket { id: 2 },
  RowDataPacket { id: 3 }
]

Why did I get undefined in the main.js? Is there any solution for this issue?

hellikiam · Accepted Answer · 2022-02-25 08:20:09Z

Since you are using callback function, you can't directly return the value from it. you have 2 options to do what you want to do.

Promise
Async/Await (mysql2 module needed)

Try this,

Querying

function(query) {
    return new Promise((resolve, reject) =>{
        try{
            con.query(query, function (err, result) {
                if (err){
                    return reject(err)
                }
                
                return resolve(result)
            });
        }
        catch(e){
            reject(e)
        }
    })
};

Main

app.get('/test', async function(req, res){
    var sql = "SELECT id FROM user"
    
    try{
        const userId = await db(sql)
        
        return res.send({
            success: true,
            result: {
                userId
            }
        })
    }
    catch(e){
        console.error(e)
        return res.status(500).send({
            success: false,
            message: 'internal server error'
        })
    }
})

One more thing, if you have a good reason to write query by yourself, you can use knex for making it easier (https://www.npmjs.com/package/knex), which is a query builder, meaning doing nothing to do with database connection.

You should check the way to handle async job in javascritpt. Callback is oldest way to doing so and have many good reasons to replace it from calllback to promise or async/await.

Syed Salleh · Accepted Answer · 2022-02-25 10:37:42Z

Sollution

Try async/await with mysql2

Dont go for mysql2/primse because it will cause unexpected errors when your database is in the cloud or deployed somewhere like clearDB addons provided by Heroku

Follow these steps... create config file for your database connection seperately

import mysql from 'mysql2'

let db = mysql.createPool({
host: 'your host name',
user: "your username",
password: "your password",
database: "your database name",
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
})
export { db }

execute the query the same like this i am doing

import {db} from 'where you defined the above db config'
app.get('/test', async function(req, res){
const promise= db.promise()
var sql = "SELECT id FROM user"
const [rows,field] = awiat promise.execute(sql)

res.send(rows)
 });

Collectives™ on Stack Overflow

In node.js, How to return mysql results from a function?

2 Answers 2

Querying

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Querying

1 Comment

Comments

Related