How can I retrieve rows from mysql database and send them as JSON via Express.js?

Question

I'm a complete beginner in Node.js and I want to send registered variable and claimed variable at the same time in the form of JSON via Express.js

This is the only thing I came up with right now and it does not even work.

app.get('/item_reg', (req, res) => {
    var registered = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url) AS 'Registered' FROM Items_found WHERE type = 0");
    var claimed = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url)  AS 'Claimed' FROM Items_found WHERE type = 1");
    //sending a response
    res.json([[registered], [claimed]]);
});

This is my DataBase but I only want some of the attributes as in the queries above not all of them.

enter image description here

Thank you in advance.

What do you mean it doesn't work? Where? What happen when you load `localhost:port/item_reg` on your browser? — FanoFN, Mar 03 '21 at 00:37
This is what happened. TypeError: Converting circular structure to JSON --> starting at object with constructor 'Socket' | property '_writableState' -> object with constructor 'WritableState' | property 'afterWriteTickInfo' -> object with constructor 'Object' --- property 'stream' closes the circle — ChesnutMaster, Mar 03 '21 at 00:44
You should [add that information into your question](https://stackoverflow.com/posts/66449048/edit). And I suggest you do a search in StackOverflow about this particular issue. I think you'll find [something similar](https://stackoverflow.com/questions/4816099/chrome-sendrequest-error-typeerror-converting-circular-structure-to-json) — FanoFN, Mar 03 '21 at 00:48

score 0 · Answer 1 · answered May 28 '21 at 07:53

Are you using MySQL? I assume mysql module has been installed in your project. Mysql.query function take function as argument (callback), so get async/await or promise and you can try this one :

// mysql module
const mysql = require('mysql');
const connection = mysql.createConnection({
  // environment mysql here
});

// util module for handle callback in mysql query
const util = require('util');

// create variable to get result from querying
let resultQuery = util.promisify(connection.query).bind(connection);

// so in your route use async/await and try catch 
app.get('/item_reg', async (req, res) => {
    try {
    // use resultQuery instead as await
    var registered = await resultQuery("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url) AS 'Registered' FROM Items_found WHERE type = 0");
    var claimed = await resultQuery("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url)  AS 'Claimed' FROM Items_found WHERE type = 1");
    //sending a response
    // the query string is select JSON_OBJECT, is that return json string as result? if yes, so :
    registered = JSON.parse(registered);
    claimed = JSON.parse(claimed);
    res.json({ registered, claimed });
    } catch (err) {
        res.json({ message: 'error message'});
    }
});

score 0 · Answer 2 · answered May 28 '21 at 11:59

Async flow would be the culprit. You can try

connection.query("SELECT JSON_OBJECT('name'........", function(err,results,fields){
    registered = results;
});
connection.query("SELECT JSON_OBJECT('name'........", function(err,results,fields){
    claimed = results;
});

How can I retrieve rows from mysql database and send them as JSON via Express.js?

2 Answers2