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?

Answer 1

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.

1. Promise
2. 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.