How can I Fetch and display Mysql data into ReactJS front end with Node JS as backend?

Question

Trying to figure out on how to fetch data from mysql and display it in ReactJS. I'm using NodeJS on the backend along with express. I tried a code snippet found on the internet but it doesn't work as it is expected.

Here's what i get when i run the react app.

TypeError: http.ServerResponse is undefined

My NodeJS code

//require mysql, http and express
//const connection = createConnection({with host, user, pass, db});
const app = express();
app.get('/posts', function(request, result){
    connection.connect();
    connection.query("SELECT * FROM 'some_table';", function(err, results, fields){
         if(err) throw err;
        result.send(results);
    })
    connection.end();
})
app.listen(3000);

My React code

class Display extends React.Component{
    constructor(props){
        super(props);
        this.state={ posts : [] };

        fetch('http://localhost:3000/posts/')
            .then(response =>{
                response.json();
            })
            .then(posts => {
                this.setState({posts})
            })
            .then( (err) => {
                console.log(err);
            })
    }
    render(){
        return(
            <div>
                <ul>
                    {this.state.posts.map( post => 
                    <p>
                        <li>Some Text_1: {post.db_col_1}</li>
                        <li>Some Text_2: {post.db_col_2}</li>
                        <li>Some Text_3: {post.db_col_3}</li>
                    </p>
                    )}
                </ul>
            </div>
        )
    }
}
export default Display;

Maybe i didn't get you, but I am using fetch in the react part of my code. — IVAN PAUL, Mar 29 '20 at 12:51

score 2 · Answer 1 · answered Mar 29 '20 at 13:02

According to the React documentation, The constructor for a React component is called before it is mounted. It also states the following:

Avoid introducing any side-effects or subscriptions in the constructor. For those use cases, use componentDidMount() instead.

You should do API calls in componentDidMount. According to React documentation:

If you need to load data from a remote endpoint, componentDidMount is a good place to instantiate the network request.

Your code should look like the following:

import React from "react";

class Display extends React.Component {
  constructor(props) {
    super(props);

    this.state = { posts: [] };
  }

  componentDidMount() {
    fetch("http://localhost:3000/posts/")
      .then(response => {
        response.json();
      })
      .then(posts => {
        this.setState({ posts });
      })
      .then(err => {
        console.log(err);
      });
  }

  render() {
    return (
      <div>
        <ul>
          {this.state.posts.map(post => (
            <p>
              <li>Some Text_1: {post.db_col_1}</li>
              <li>Some Text_2: {post.db_col_2}</li>
              <li>Some Text_3: {post.db_col_3}</li>
            </p>
          ))}
        </ul>
      </div>
    );
  }
}

export default Display;

The above snippet will work provided your back-end Node.js application is returning the proper data.

What is the data that your back-end is returning? If `this.state.posts` is undefined, it means that your back-end isn't returning any `posts` array in the response. — Nirmalya Ghosh, Mar 31 '20 at 08:56
Backend is returning data, its just that the this.state.posts cannot be accessed from where it is called. — IVAN PAUL, Mar 31 '20 at 14:41
`.then(posts => { this.setState({ posts }) })` In this line, your posts are getting set in the React state. Can you check if the state is getting populated properly? If you share a CodeSandbox or Codepen link, I can help you more with this. — Nirmalya Ghosh, Mar 31 '20 at 19:21

score 2 · Accepted Answer · answered Mar 29 '20 at 13:22

Your code needs some error handling and CORS policy. So I would recommend to you do;

Make sure your backend is up and running

You need to check your ports on backend.

Make sure database up and running

You need to check your connection is there for your database. No need to connect to your database each time when you make request. So better to connect once.

Try your API result via Postman or any other tool

You need to make sure your backend is reachable via any other client app. You can also open your browser and test your API with opening the link in browser 'http://localhost:3000/posts'

Activate CORS policy for your backend.

SPA needs CORS policy to make a request to the backend. You can use cors npm library for that or you can create your own rules.

Use a fetch library

You can use fetch but it is not supported by all browsers. It would be nice to Axios or any other request tool on your client code.

const cors = require('cors')
const app = express();

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});

connection.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

app.use(cors());

app.get('/posts', (req, res) => {
  connection.query("SELECT * FROM 'some_table';", (err, results, fields) => {
    if(err) throw err;
    res.send(results);
  });
});

app.listen(3000, (error) => {
  if (err) throw err;
  console.log(`App listening on port ${port}!`)
});

Thanks @hurricane, I now know what I am actually doing, but the code still doesn't work, it is working on the server but the client is not getting the response back. All it says that the cors request failed. — IVAN PAUL, Apr 04 '20 at 07:50
Yes I'm using app.use(cors()); statement. when I run the Element Inspector for network diagnostics it show no response headers and response tab also shows "No Response". It is working fine when i pass a simple text, but not fetching the json data. — IVAN PAUL, Apr 06 '20 at 08:39

score 0 · Answer 3 · answered Sep 11 '21 at 10:59

Taking up the other answer that mentions password: "yourpassword", check that you have a password set for the user that you use to connect to the database.

I had the issue with PostgreSQl, but that could be the same thing somewhere else: the fresh install does not automatically set a password for the super user, nor is a password set automatically for any other user that you create from the super user login.

The errors shown do not hint at the issue, see for example TypeError: Cannot read property 'rows' of undefined which seems to solve an error of the "rows" in the code, but in reality is just an error of a missing password.

If there is no password set, you can try as much as you want, you will not get access to the backend. In PostgreSQL, I had to follow FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4).

How can I Fetch and display Mysql data into ReactJS front end with Node JS as backend?

3 Answers3