how to respond with MYSQL select result using node js using JSON format

Question

I am trying to build a restful API using nodejs with MySQL and Express. I started with simplest code that retrieve all records from a table but I am facing an error when sending query result to client using res.send() or res.json() but I am getting this error message:

TypeError: Converting circular structure to JSON --> starting at object with constructor 'Query' | property '_timer' -> object with constructor 'Timer' --- property '_object' closes the circle at JSON.stringify () at stringify (C:\Users\Developers\Desktop\ex\node_modules\express\lib\response.js:1123:12) at ServerResponse.json (C:\Users\Developers\Desktop\ex\node_modules\express\lib\response.js:260:14) at ServerResponse.send (C:\Users\Developers\Desktop\ex\node_modules\express\lib\response.js:158:21) at C:\Users\Developers\Desktop\ex\index.js:12:9 at Layer.handle [as handle_request] (C:\Users\Developers\Desktop\ex\node_modules\express\lib\router\layer.js:95:5) at next (C:\Users\Developers\Desktop\ex\node_modules\express\lib\router\route.js:137:13) at Route.dispatch (C:\Users\Developers\Desktop\ex\node_modules\express\lib\router\route.js:112:3) at Layer.handle [as handle_request] (C:\Users\Developers\Desktop\ex\node_modules\express\lib\router\layer.js:95:5) at C:\Users\Developers\Desktop\ex\node_modules\express\lib\router\index.js:281:22

this is my index.js code

const express=require('express');
const bodyParser=require('body-parser');
const dbObject=require('./models/db')
const app=express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended:true}));


app.get("/",(req,res)=>{

    ;
    res.send(dbObject.getAll());

})

app.listen(5200,()=>{
console.log("server started...")

});

and this is db.js where i execute the SQL command

module.exports={

    getAll:()=>{

        var getData=()=>connection.query("select * from currencies",(err,result)=>{

            if(err)
            throw err;
            console.log(result);
            return result;

        })
        return getData();
    }

}

this is what I got when i print the result in console

server started...
connected to db successful
[
  RowDataPacket { cur_id: 1, cur_name: 'Dollar', cur_letters: 'USD' },
  RowDataPacket {
    cur_id: 2,
    cur_name: 'Saudi Ryal',
    cur_letters: 'SAR'
  }
]

Abir Taheer · Accepted Answer · 2020-09-06 07:47:05Z

The problem is in your code for getAll. You're returning a value inside of a callback which doesn't actually return the value for your getAll function call. The actual value that gets returned by getAll is a mysql Query object.

The accessing of the database also happens asynchronously and so the only way to return the data in your express handler is to make the handler asynchronous as well so you can await the data or to call res.send inside of the callback for your query.

I'd suggest returning a promise in your getAll function

// db.js
module.exports = {
    getAll(){
        return new Promise((resolve, reject) => {
            connection.query("select * from currencies", (err, result) => {
                if(err){
                    // The equivalent of throwing the error
                    reject(err);
                } else {
                    // The equivalent of returning a value for getAll
                    resolve(result);
                }
            })
        });
    }

}

and then you can set up your route handler and use the await keyword to wait for the query to resolve before sending something to the client.

// index.js

app.get("/", async (req,res)=>{
    const data = await dbObject.getAll();
    
    res.json(data);
});

Collectives™ on Stack Overflow

how to respond with MYSQL select result using node js using JSON format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related