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;

Answer 1

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).

Answer 2

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}!`)
});

Answer 3

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.