Simple React JS and MySQL Integration -- CRUD App (Backend)

Hey guys, today I am demonstrating a very simple React app integrated with MySql db through a simple CRUD app. I know the app seems very trivial but believe me there is a lot to learn and can be beneficial to you some day.

Please note that in this article we will take care of the backend part and in the end I will link another article that corresponds to the front end of the application.

So, to list down the steps we need to follow in order to achieve our goal are:
Create the database to store our records
Create the server connection to the db
Define the endpoints for CRUD app
Create react app and define the frontend
Integrate the front end and backend

Above is a high level description of what we are trying to achieve, we may jump from one step to another but eventually we will reach the goal.

So, I have got XAMMP server installed on system for MySQL db to host upon. I will go to my browser and write localhost/phpmyadmin to open up the database console and create a new database (since I am creating a blog posts webpage where you can create, list, delete and like a blog post so I will name the db-->> blog_posts). I will add the following six columns in the 'posts' table in my db:

Next, we will create two folders in our main app folder, one is server(having the backend structure) and one is client (containing the front end files). So if you've named your parent folder blog then the folder structure should look like this:

Now we'll create a config folder in server folder and a db.js file inside to define our db configurations:
server ->> config ->> db.js:

const mysql = require('mysql')
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database:"blog_posts" 
})

module.exports = db;

Now in the server folder we will create an index.js file where we will define all our backend logic i-e the endpoints/routes to get, update and delete queries:

const express = require('express');
const db = require('./config/db')
const cors = require('cors')

const app = express();
const  PORT = 3002;
app.use(cors());
app.use(express.json())

// Route to get all posts
app.get("/api/get", (req,res)=>{
db.query("SELECT * FROM posts", (err,result)=>{
    if(err) {
    console.log(err)
    } 
res.send(result)
});   });

// Route to get one post
app.get("/api/getFromId/:id", (req,res)=>{

const id = req.params.id;
 db.query("SELECT * FROM posts WHERE id = ?", id, 
 (err,result)=>{
    if(err) {
    console.log(err)
    } 
    res.send(result)
    });   });

// Route for creating the post
app.post('/api/create', (req,res)=> {

const username = req.body.userName;
const title = req.body.title;
const text = req.body.text;

db.query("INSERT INTO posts (title, post_text, user_name) VALUES (?,?,?)",[title,text,username], (err,result)=>{
   if(err) {
   console.log(err)
   } 
   console.log(result)
});   })

// Route to like a post
app.post('/api/like/:id',(req,res)=>{

const id = req.params.id;
db.query("UPDATE posts SET likes = likes + 1 WHERE id = ?",id, (err,result)=>{
    if(err) {
   console.log(err)   } 
   console.log(result)
    });    
});

// Route to delete a post

app.delete('/api/delete/:id',(req,res)=>{
const id = req.params.id;

db.query("DELETE FROM posts WHERE id= ?", id, (err,result)=>{
if(err) {
console.log(err)
        } }) })

app.listen(PORT, ()=>{
    console.log(`Server is running on ${PORT}`)
})

Github repo for the backend part is:https://github.com/NasreenKhalid/Blog-React-CRUD-MYSQL/tree/master/server

Uptil now, all the backend has been taken care of and now we will create a react app where all the frontend fun takes place, the link for the frontend part of the article is:
https://dev.to/nasreenkhalid/simple-react-js-and-mysql-integration-crud-app-frontend-3i0l

I hope you'll find this article helpful.

Happy coding guys and have a great day!

Comments

[Byter-Bit]

I get a "net::ERR_CONNECTION_REFUSED" when trying to display the posts.

I've validated the database connectivity from Node, and thrown in more error reporting, but I'm still stuck.

Let me first thank you for all your good work, and say I'm sorry to post here with this problem. I'm a beginner in React and have learned a great deal from your good work.
So, first of all: I'm sure I can hit the local MYSQL database from within Node, using this test.js file called from the Node command line:

const express = require('express');
const mysql = require('mysql');
const db = mysql.createConnection({
host: "localhost",
user: "xxxx",
password: "xxxx",
database:"blog_posts"
})
db.connect((err) => {
if(err) {
console.log(err);
throw err;
}
console.log('it worked to MYSQL');
console.log(err);
});

These are of course the credentials I use in your db.js file.

I've found where the error is thrown in the MainPage.js file and I've put in more error trapping:

useEffect(()=>{
Axios.get("http://localhost:3002/api/get").then((data)=>{
console.log(data)
setPostList(data.data)
})
.catch(function (error) {
// handle error
console.log("problem here", error);
})
;
},[])

The result in the console is:

problem here Error: Network Error
at createError (createError.js:16)
at XMLHttpRequest.handleError (xhr.js:99)

I can't get beyond this. I don't think it's a CORS issue, as everything is on this machine.

[DavidMenaOlivella]

Thanks! It's been so useful.