How to return a JSON object from a mySQL query in node.js

Question

I'm very new to coding and node.js in particular so this is probably a dumb question - but all the examples I read for mySQL database queries end with just logging the query results to the console... what I want to do (or at least what I think I want to do) for my web application is return the query result as the product of a function so that I can allow my .ejs file to make use of the JSON object.

The situation as the moment is no matter what I try my .ejs file receives "Hello World" as the product of getDriver(). I want it to receive 'rows' (ie. the JSON object that my SQL query returns with)

I can see the JSON result in the console & it looks perfect.. I just can't seem to make it 'exist' outside of the function :(

var express = require('express');
var router = express.Router();
var mysql = require('mysql');
/* GET about page. */
router.get('/', function(req, res, next) {
    res.render('SQLtest', { title: 'SQL test',
                        result: getDriver() // I want this to be my JSON object
    });
});

module.exports = router;


function getDriver() {
    var result = "Hello World"; // my .ejs see's this
        var connection = mysql.createConnection(
            {
                host: 'localhost',
                user: 'root',
                password: 'password',
                database: 'my database',
                port: 3306
            }
        );

        connection.query("SELECT * FROM my_table",
            function (err, rows) {
                if (err) {
                console.log(err);
                }

                console.log(rows); // query result looks fine as JSON object
                result = rows; // now change "Hello World" to the JSON object
            }
        );
    return result; // however this still = "Hello World"  :(
}

ishmaelMakitla · Accepted Answer · 2016-12-03 19:49:46Z

7

The database query is an asynchronous call - this means that the return result statement may execute BEFORE the query results are returned and the value of result is updated via result = rows.

What you need to do is to introduce a callback function as a parameter in your getDriver function. Your code will then look like this (I moved the database connection code out of the getDriver function):

//You should move this out of the function - you want to set it once
 var connection = mysql.createConnection(
     { host: 'localhost',
       user: 'root',
       password: 'password',
       database: 'my database',
       port: 3306
     });

function getDriver(callback) {    
        connection.query("SELECT * FROM my_table",
            function (err, rows) {
                //here we return the results of the query
                callback(err, rows); 
            }
        );    
}

This is how you now call the getDriver function:

router.get('/', function(req, res, next) {   
    //now you can call the get-driver, passing a callback function
    getDriver(function (err, driverResult){ 
       //you might want to do something is err is not null...      
       res.render('SQLtest', { 'title': 'SQL test',
                        'result': driverResult});

    });
});

Give it a try and let me know if it works. I hope this helps.

answered Dec 3, 2016 at 19:49

ishmaelMakitla

3,8223 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil_of_odd Over a year ago

Thank you so much for the reply, this is working perfectly now! I'll do some more practice with callback functions as I would never have guessed this was the problem.

ishmaelMakitla Over a year ago

Great! Please accept the answer so your question can be marked as answered. You can vote the answer as well if you find it helpful.

Phil_of_odd Over a year ago

Should be accepted now :) - I have another general question actually as since I spent so long not finding the answer before posting here I always worry that I'm not working in the correct way; my embedded JS files take the JSON object & write it out into tables on the front end (that's all my web app needs to do) .. so passing the database query across in this way is correct? or is there some other method I shoudl be using?

ishmaelMakitla Over a year ago

@Phil_of_odd - please post this as a separate question so that other members of the community can also weigh in and assist. You will need to include the code that you need advise on. Happy coding.

Collectives™ on Stack Overflow

How to return a JSON object from a mySQL query in node.js

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related