How to make SQL queries to effect react app:
I’m making this tutorial because this concept was one of the things that brought forth many challenges for me ,and I’m hoping that I can make this process of getting started a bit easier for someone else. And even though it was hard to get passed those barriers after that is a smooth sale, so let’s begin.
Create React app
So let’s start with creating a react app since that is what the user will be interacting with.
To Install react is through terminal. You should have new versions of Node and npm installed on your machine
And all you have to do is to run these codes:
npx create-react-app my-app
cd my-app
npm start
Now your react app should be running on localhost port 3000. To change the layout of your app go to your application folder which should be named “my-app” according to the code you ran.
Go to the src folder, you can enter App.js and change the code there to make the page look the way you want it to.
What we’re going to build now are two inputs and a submit button.
You can add custom styling by creating your own .css file and importing it in the index.js file like so(don’t forget react uses className=”” for elements instead of class=””):
import ‘./style.css’;
after you’re satisfied with the looks lets get to the fun part…SERVER.
Server and sql
Create a folder next to the “my-app” folder that we creates at the very beginning , inside of it create a index.js. and there are some dependencies you would want to install:
npm install body-parser ,cors ,express ,mysql ,nodemon
after your done you’ll need to import all of them to your index.js like so:
const express= require(‘express’)
const app= express()
const bodyParser= require(‘body-parser’)
const mysql= require(‘mysql’)
const cors= require(‘cors’)
as you can see we are using express . express is basically a minimal and flexible node.js web app framework and then we will use our imports on it:
app.use(cors())
app.use(express.json())
app.use(bodyParser.urlencoded({extended: true}));
1.cors is for the limitations that we have using api’s without this you wouldn’t be able to get the data and would get a CORS error.
2. express.json is a built-in middleware function in Express. It parses incoming requests with JSON payloads and is based on body-parser.
3. body-parser is used to parse incoming request bodies in a middleware before your handlers, available under the req.body property which we’ll use in a sec.
I will be using port 3001 since we are already using port 3000 for our front-end react app.
Now to check if the app is running use this code:
app.listen(3001,()=>{
console.log(‘running’)
})
So before we get into it ,if you want to make a local host you will need to first install mysql on your systemand then either install a visual database design tool (I use mysql workbench) or just do it through the terminal. So now create a database(in example: my_database) and a table(in example: new_table) with rows of id, name and description.
from now on everything was a bit more familiar for me, it’s the same process of creating a connection and making CRUD(create ,read ,update ,delete) calls.
To create a connection you can use the createPool function like this:
const db=mysql.createPool({
host:”localhost”,
user:”root”,
password:””,
database:”my_database”
})
Now to test lets try running a sql query :
app.post(‘/’,(req,res)=>{
const sql=”INSERT INTO new_table (name,description) VALUES (“test name”,”test description”)”
db.query(sql ,(err,result)=>{
console.log(result);
})
})
In this post method the first parameter I’m passing is the url in which this method will work. In this example it’s the same as before localhost:3001 but if I were to write this:
app.post(‘/post’,(req,res)=>{
const sql=”INSERT INTO new_table (name,description) VALUES (“test name”,”test description”)”
db.query(sql ,(err,result)=>{
console.log(err);
})})
Then this method would happen at ‘localhost:3001/insert’.
The rest is self-explanatory the “sql” variable is the mysql query that we want to do. (err) can be used to “console.log(err)” the errors that might occur. And the result is obviously the data that server is passing us which will come in handy when we will use a get method.
Now check your database to see if the data was inserted. If not go to the url we set (localhost:3001/insert) and check the console log for errors and fix them.
Ok now that we have got the jest of how our back-end works lets let our react app interact with our server.
Now to use the api we are going to need to use axios in react which is pretty well-know. The same as before to install it inside the terminal in the “my-app ” folder enter:
npm install axios
and to import it put this code in app.js:
import Axios from ‘axios’;
we are also going to need useState and use Effect so…
import { useState ,useEffect } from ‘react’;
ok good now we will define the variables name and description:
const [name,setName]=useState(‘’)
const [description,setDescription]=useState(‘’)
now we will get those variables from the inputs we previously made.
<input type=”text” name=”name” onChange={(e)=>{ setName(e.target.value)}}/>
<textarea type=”text” name=”description” onChange={(e)=>{ setDescription(e.target.value)}}/>
Now that we have the values at the click of the submit button we will send this information to our backend using axios and insert it into our table from there .
<button onClick={submit}>submit</button>
So after the user submits we will first check if the input has anything in it and then we will do a post method to the url we created for posting to the database in the back end(localhost:3001/post).
And we will pass the parameters name and description. And give them a name that we will receive in the back end(example:’theName’,’theDescription’)
const submit=()=>{
if(name!==”” ){
Axios.post(‘http://localhost:3001/post’,
{ theName:name ,
theDescription:description
}).then(()=>{
console.log(‘success’)
}).catch(e => console.error(e));
}else{
alert(“fill the name input”)
}}
Great! Now in the back end we just need to tweak our code a bit to get these information dynamically .
So the req.body that we talked about earlier now comes to play. We can get the information we sent from react through the request’s body.
And we have to put them in a variable I like to give the variable the same name; it’s just less names to keep track of.
app.post(‘/post’,(req,res)=>{
const theName= req.body.theName;
const theDescription= req.body.theDescription;
const sql=”INSERT INTO new_table (name,description) VALUES (?,?)”
db.query(sql , [theName,theDescription],(err,result)=>{
console.log(result);
})
})
As you can guess the ‘?’s at the end of our query let us add the values more dynamically. In the next line we pass the variables in an array respectively.
Now your application must be working . you can insert information into your table through your app.
Now that we concurred the Create part of CRUD the rest will be super easy.
READ:
In the server folder index.js create another url for get
app.get(‘/get’,(req,res)=>{
const sql=”SELECT * FROM new_table”
db.query(sql ,(err,result)=>{
res.send(result);
})
})
After we get the results we can send them out and show them on the (localhost:3001/get) page with res.send(result) or you can show anything with the res.send() function.
And that’s it for the back end.
In react we are going to need a variable to put the array of the informationfrom our table in:
const [dataList,setDataList]=useState(‘’)
Now we are finally gonna use the useEffect that we imported. Like this:
useEffect(()=>{
Axios.get(‘http://localhost:3001/get').then((result)=>{
setDataList(result.data);
})
},[])
This function will run the get method once when we load the page and get the information we need
Then I will map through the array and show them the way I want:
{Object.keys(dataList).map((val)=>{
return (
<div className=”saved-notes” key={val}>
<p className=”title”>title:{listData[val].name} </p>
<p className=”text”>{listData[val].description}</p>
</div>
)
})}
Update:
It’s the same concept as post but this time we want to replace a new description for an existing one:
<input id=”updateInput” type=”text” name=”description” onChange={(e)=>{setNewDescript(e.target.value)}}/>
<button onClick={()=>{updateReview(listData[val].name,listData[val].description)}}>update</button>
Ill pass the new input value to another axios:
const updateReview=(name,description)=>{
Axios.put(`http://localhost:3001/update`,
{ theName:name ,
description:description,
newDescript:newDescript
})
.then(()=>{
console.log(‘success’)
}).catch(e => console.error(e));
// setData([…listData,{theName:name , description:description}]);
setNewDescript(‘’);
window.location.reload();
}
And in the back end we will do the updating:
app.put(‘/update’,(req,res)=>{
const theName= req.body.theName;
const description= req.body.description;
const newDescript= req.body.newDescript;
const sql=”UPDATE new_table SET description=? WHERE name=? AND description=?”
db.query(sql , [newDescript,theName,description] ,(err,result)=>{
if(err) console.log(err);
res.send(result);
})
})
Delete:
Delete is a bit different as to how you will transfer the parameters from react to backend.
You do the same thing as update to the function but instead of passing the parameter as data ,You will send the name through the url.
const deleteReview=(name,description)=>{
Axios.delete(`http://localhost:3001/api/delete/${name}`)
.then(()=>{
}).catch(e => console.error(e));
window.location.reload();
and down here you can see how we use that parameter . we give it a name in the url and instead of using req.body we use req.pram since it’s a url parameter.
app.delete(‘/delete/:theName’,(req,res)=>{
const theName= req.params.theName;
const sql=”DELETE FROM new_table WHERE name=?”
db.query(sql , theName ,(err,result)=>{
if(err) console.log(err);
res.send(result);
})
})
So there you go now you know how to connect your react and your mysql server I hope that this was helpful
Thank you for reading my blog