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?

CodePudding user response：

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.

CodePudding user response：

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