MYSQL returns error when a query is made through an endpoint-CodePudding

I'm developing a simple GET endpoint using NodeJS, express and MySql, but whenever i use the mysql.query('select * from table'), through an service, the server is shutdown. The same query is successful when i declare it in the database.js file, but not when integrated with the rest of my system.

My database.js is as follows:

const mysql = require("mysql");

const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "rootpwd",
  port: 3306,
  database: "blog",
});

con.query("select * from post", (er, row) => {
  if (er) throw er;
  console.log(row);
  return row;
});

This con.query function is only declared for test purpose, and deleted when endpoint is called. When i run my server and this query is declared, it logs in my console all the content in this table. When i run node database.js all my entries in posts table are shown in console.

But when i call, in another file, database.query('select * from post', (er, rows) => { if (er) throw er return rows }) The localhost is shutdown with the following message:

-> starting at object with constructor 'Query'
    |     property '_timer' -> object with constructor 'Timer'
    --- property '_object' closes the circle
    at JSON.stringify (<anonymous>)
    at stringify (/home/guilherme/Documentos/Projects/rest_api/node_modules/express/lib/response.js:1150:12)
    at ServerResponse.json (/home/guilherme/Documentos/Projects/rest_api/node_modules/express/lib/response.js:271:14)
    at /home/guilherme/Documentos/Projects/rest_api/server/route/postsRoute.js:7:19
    at processTicksAndRejections (internal/process/task_queues.js:95:5)

I've installed body-parser in my project and i'm using express.json() as a middleware in my app:

const express = require("express");

const app = express();

app.use(express.json());
app.use("/", require("./route/postsRoute"));

app.listen(process.env.PORT || 3000, () =>
  console.log(`Server running on port ${process.env.PORT || 3000}`)
);

My route file:

const express = require("express");
const router = express.Router();
const postService = require("../service/postService");

router.get("/posts", async (req, res) => {
  const posts = await postService.getPosts();
  res.status(200).json(posts);
  res.end();
});

My Service file:

exports.getPosts = async () => {
  const test = await postsData.getPosts();
  console.log(test, "this is what is returned");
  return test;
};

And finally, my data file:

exports.getPosts = () =>
  database.query("select * from post;", (er, rows) => {
    if (er) throw er;
    return JSON.stringify(rows);
  });

this JSON.strinfy was inserted for test purpose, and the error is returned the same way.

Node version: v14.18.0 dependecies:

"dependencies": {
    "axios": "^1.2.1",
    "body-parser": "^1.20.1",
    "express": "^4.18.2",
    "jest": "^29.3.1",
    "mysql": "^2.18.1",
}

If anyone needs more information to help me debug this, please let me know.

I've tried parsing the content that is returned in my query, but it didn't returned anything useful. I've added async and awaits, but it didn't helped either.

I'm expecting to see all my tables content when i access the /posts route.

CodePudding user response：

You're converting the result to JSON twice, in the data file and the services file. You should only do the JSON conversion in one place, not both.

I recommend doing it only in the service, so in the data code use

exports.getPosts = () =>
  database.query("select * from post;", (er, rows) => {
    if (er) throw er;
    return rows;
  });

CodePudding user response：

So the main issue was that 'mysql' lib does not handle promises correctly. I've added mysql2 and and changed my Data.js file to database.promise().query("select * from post;");

And it works just fine now.