React Node.js MySQL CRUD Tutorial for Beginners

hello friends today we are gonna create a small application using react not just mysql in this app we will fetch all books from a database we will be able to create a new book delayed any specific book or update any of them it's a really important tutorial to understand the basics of an sql database and how to use it with a react app because in the future we are gonna create some bigger apps using these technologies by the way let me know in the comments what kind of projects you want to see in the next tutorials ok if you are ready let's get started okay firstly we are gonna download the latest version of mysql to do that we are gonna go to dev.mysql.com and you are gonna see this website which was designed in 2005 i think look at those images and here we are gonna click mysql 8 and here just choose your operating system and download mysql and after downloading you can just install mysql and during installation it's gonna ask you if you want to use any password or not it's not asking me because i've already created mysql if you want to you can leave your password empty or you can give any password doesn't matter and after that i'm gonna close this installer and one more thing we are gonna download and it's gonna be mysql workbench again choose your operating system and download it it's really easy to install i'm not gonna show it basically this workbench is a visual database that we can design our tables we can add new items inside and it's perfect for beginners that because you can see your tables you can test them quickly so we are gonna use it also and after the installation you are gonna see this screen let's come here i'm gonna click here and write my password and this is our mysql instance as you can see there is a default folder here i'm gonna delete this drop schema and i'm gonna create my own schema i will say create schema let's give a name i will just say test as you can see there is a code version of what we are doing we are just creating a schema and its name is test i will just apply and it's successful as you can see it's here and right now we can create our first table i'm going to come here create table and table name will be books right now i want to create all necessary fields to create this boop model firstly we are going to need an id i will say just id it's going to be integer it's going to be primary key that because this is going to be the unique id that we can create different books and there is an option here auto increment and it means when we create any book we don't have to specify any id it's gonna automatically increment this number if we create any book its id will be one if we create the second one it's gonna be automatically two and the second one will be title it's gonna be string and its limits will be 45 characters and it shouldn't be null so i'm gonna click here it means not null if we create any book it has to have any title and one more is gonna be description again string but this time i'm gonna increase this character limit and it's gonna be 255 again not null and let's create one more and it's gonna be cool picture of this book and it can be null i'm gonna leave here empty and it's enough for the beginning i think let's create our table i'm gonna apply as you can see this is the code version if you don't use my skull workbench you have to open your terminal and create your table like that i will apply as you can see it's successful and let's see our first table as you can see it's here i'm gonna right click and select all rows and as you can see it's empty for now and as you realize there is an sql code here this is how we are selecting all books inside this table select all from books table we are gonna write this code in our application soon but before let's see how we can create a new book i'm gonna come here id will be one title will be let's say test title description and cover dot png let's apply and this is how we create new book using sql code inside into our table name and here we specify which fields we are gonna use we use id title description and cover and here which values we are gonna put inside those fields i'm gonna apply and let's select all books again you can come here and right click and select rows and also we already have here our sql code i'm just gonna run using this button and our item is here let's do the same thing using our application to do that i'm going to open my vs code and i'm going to create two folders first one will be our backend server backhand and the second one will be our react application to do that you can say frontend or client okay we are going to create our react app but before let's take care of our backend servers i'm going to open my terminal let me make this bigger you can see better right now and i'm gonna go to backend folder and inside i'm gonna install my libraries to do that firstly i should initialize my note application i will say npm init dash y and if you look inside as you can see there is a package.json and here let's install necessary libraries firstly we are gonna need express server npm install express server and we are gonna need my squad and also i will use node mode and it allows to hot reload our application whenever we make any changes you are gonna understand better right now if you don't know nordmon i'm gonna install them and they are ready what i want to do is to create my main javascript file and it's gonna be index.js as you can see our main file will be index.js you can name it whatever you want and here i will create my express application i'm going to import express from express let's create our app and it's going to be express function and finally to run our application we have to listen any port number i will say app.listen you can give here any port number i'm usually using this number and whenever we run our app i'm gonna write here connected to backend i will save and let's start our app to do that i will come here and say not index.js and as you can see there's an error here that's because we cannot use import statement here like that if you want to import them using as6 modules you have to come here and say type and it's going to be module let's try right now node index.js and as you can see connected to backend and if i make any changes here i will save and i have to come here kill my connection and start again but in the development mode it's not a good idea we shouldn't have to come here and start our app again and again i'm gonna come here and inside scripts start not mom and index.js right now if i say mpm start as you can see it runs this nodemon line and basically it listens our application and whenever we make any changes i will save as you can see it starts again this is why we are using normal okay perfect right now we are ready to connect to mysqldb i will say const db i should import here mysql and i'm gonna run create connection method and inside i should write some configuration first one will be host since we are using our localhost i will just write here localhost we are gonna have a user it's gonna be root and i'm gonna write here a password if you didn't create any password you can just leave it empty but my password is lamadev one two three and i'm gonna write here database and it's gonna be test remember db name is test and table name is books okay i'm gonna save and let's try to select all books from our table but before i want to show you how we can reach our backhand server i will come here and i will say app get method and whenever we visit our homepage homepage of this backend server we are gonna get a request from the user and we are gonna send a response after that and here let's send a message to user response.json hello this is the backend i will say localhost and our port number if i enter as you can see this is our message this is how we are making api requests using an express server let's create here another request i'll say app again get and our endpoint will be books request response and i'm gonna return all books in our db to do that we are gonna need an sql query remember how we are getting all books from our db i will say const query and it's gonna be select all from our table name which is books let's run this query as you remember we have a db here using this db we can run any query and our query will be select all from books like that and it's gonna return as either an error or if there is no problem it's gonna return a data and i'm gonna write here a condition if there is an error return response.json you can write here an error message or basically you can just return your error and if there is no error i'm gonna return my data response json and data let's save i'm gonna come here and i will say books and as you can see it returns all books inside our db by the way if you have any authentication problem if there is a authentication problem use this code basically we are going to update our root user and we are going to write here our password this problem happens that because by default mysql creates no password and when you create any password sometimes it gives some problems to prevent this you can use this code and of course your password here okay and after that let's see how we can create any book let's come here and create another route here of course in the future when we create an application we are gonna be more organized and we are going to create our routes in different folder and we are going to make our tv operations in different folder but for now i'm just showing you how you can make crude operations so i will come here and i'm gonna say app.post i'm saying pause that because we are gonna send book details title description and cover we cannot do this using a get method and again it's gonna be books request response this time we are gonna use this request that because a user is gonna send our book details and let's write here our query i will say const query and remember into our table name books and i will write here my field names using this backtick i will say title we are gonna send description and we are gonna send a cover picture values and i'm gonna write here a question mark of course you can directly say request body title description and cover picture but i recommend you to use this question mark it provides and security let's write our values i'll say const values it's going to be an array and here we are gonna need a title description and cover of course we are gonna get this from user but for now let's test it i will say title from backhand description from backhand and power picture from backhand and let's write our query db.query i'm gonna pass my query like that and also i'm gonna pass my values and after that remember it's gonna return an error or data if there's an error we are gonna return it if there is no error we are gonna send our data so how we can test this request as i said we cannot test using this browser because it's a get method to do that we are gonna use postman software it allows you to make any api request as you can see this is our previous youtube application as you can see we use this endpoint and try to sign in this time we are gonna test this endpoint and post method let's do that i will say localhost i should clean here but anyway and remember books our endpoint and here i'm gonna choose pause method let's send as you can see our status is 200 which is successful and this is the response that mysql sends us but of course there is nothing meaningful here i will come here and change here i will say book has been created successfully and as you can see book has been created successfully perfect let's check our cat method and as you can see our all books are here let me make here bigger okay perfect but now we can send data as a user to do that we are gonna use this body section it's gonna be row and here we are gonna send a json object what we are gonna send we are gonna send a title is gonna be title from client description from client and cover picture from client let's take them right now instead of those strings i'm going to say user request and inside body remember we are sending using body and title and others description and cover picture let's try right now i'm gonna send oops it's a cat method and as you can see there's an error here that's because by default we cannot send any data to our express server to prevent this problem i'm gonna open my index file and i'm gonna write here and express server middleware and it's going to be app.use express.json and it allows us to send any json file using a client let's try again i'm gonna send and this time it's successful let's check and they are here perfect right now instead of this postman i want to use my react application i'm going to close here and i'm going to take care of this client site right now i'm going to open if tab here new terminal client side and i'm going to create my react app to do that i'm going to use mpx create react app and i'm going to install inside this folder so i will say dot and enter okay it's ready let's start our app npm start and as you can see it's ready i'm gonna open my app.js and i'm gonna clean here like that and here i will just say hello okay it's here so right now what i want to do is to create some pages for example if i visit localhost and books we are gonna see our all books if i visit localhost and add we are gonna see a form here that we can send book information to create a new book to do that we are going to be using react-graduate i will say npm install react router door let's see how we can use it as you can see we are gonna wrap our app using this browser router and after that we are gonna create our routes let's copy this i'm gonna start my app again and i'm gonna paste it here let's wrap our app browser rather let's select this and create our routes our first route will be our home page let's say path and the home page and whenever we visit this page we are gonna call a component but we don't have yet let's create here i'm gonna create a folder and let's say pages now first one will be books.yes or jsx and second one will be add.js and one more update.js like that let's create our functions quickly if you don't know those shortcuts i recommend you to watch my escort shortcuts video and i'm gonna call here the books component here pages and books and other one is gonna be add and update like that and let's see as you can see this is our homepage i'm going to go to add page and let's say update perfect it works so what i want to do is when i visit to home page i want to show here all my books i'm going to open books page and i'm going to fetch all data using my backend server to do that we are going to need one more library and it's going to be npm install axios it allows us to make any api requests using a react app start my app and let's try to do that i'm going to create a use date and we are going to store our books inside this book state so let's say books setbooks use statehook and at the beginning is going to be empty and i'll say use effect dependency will be an empty array it means it's going to run just once and let's create our fetching function i will say const fetch all books it's gonna be an async function that because we are making an api request it's really important guys if you forget this async you are going to get an error and i will say try catch block it can be any error if there is an error i will just write it here and what i'm gonna do i will say const response i'll say axios of course i should import this import axios from axios like that okay and of course as i said it's an async function so we have to await this operation and it's gonna be a get method and remember our endpoint localhost port number and endpoint and finally let's see what we have i'll say console.log response and of course i should call this function here so whenever we run this books component it's gonna run this use effect and inside this use effect we have this function and it's gonna fetch all data of course slash here and let's see i'm gonna refresh and open my console and as you can see there is a problem here that because our back-end server is not allowing an application to use our back-end api to prevent this i'm gonna come here and install a course library i will say npm install of course let's run again i'm gonna come here one more middleware here and it's going to be app.use and course function you can specify here any domain name like localhost 3000 or you can make any blacklist for other domains but of course it's not the concern of this video don't worry about that that much i'm gonna come here and say course and let's try again i'm gonna refresh and as you can see we have a response here and inside this response we have a data and all books here so i can use this data and i can update my books here i'll say set books response.data and after that we can show all books here firstly i'm going to add a title here lama bookshop let's create book steve and inside i'm gonna map through my books array and for each book inside this array i'm gonna return another div let's say book and it's gonna include its image but of course as you remember it can be null so what i'm gonna do is to come here and write a condition if there is book and color picture if there is no cover we are not going to show this image element and of course if there is it's going to be book dot co and after that let's add here h2 tag and it's going to be boot.title description it's gonna be a p tag and what else actually we can write here book price we didn't create but we can i will say span and boot.price now i will save and as you can see all those books are here perfect and there's a warning here that because if you are using a map you have to write here a unique key what's our unit key remember we have id all book ids are different so we can give it here and perfect there is no problem so i'm gonna come here and add one more field here to do that i will right click and alter table and let's create one more field here and it's going to be price it's going to be integer and not no let's update i will apply and that's all okay so let's create here a button that's gonna redirect us to add page i'm gonna come here and i'll say button add new book and i'm gonna use here a link using react rather than and when we click on this button we are gonna go to add page let's see i will come here and click as you can see it's here of course we are gonna give some style but before let's see how we can add a new book i'm going to come here and i'm going to create here form let's say class name is going to be a form let's give a title add new book i will create an input let's give a placeholder it's going to be title and others description price and cover of course it's going to be a number okay so how i'm going to take values from those inputs i will just create a use state let's say book setbook or input set inputs whatever you say use statehook and at the beginning they are going to be empty description price is going to be null color picture i like that and whenever i change any value here i'm going to set this book and remember my use state hook video it's really important video guys you should definitely watch you are gonna learn how to use use state properly so what i'm gonna do is to give here some names it's gonna be title make sure that those names are exactly the same names in this book and price and cover so what i want to do is to add here and on change method and i will say handle change function let's create this function here const handle change and whenever we change any input we are going to set our book and remember how we are doing this spread operator and event target dot name which we have given here and it's gonna equal event target dot value that's all let's see console.logbook as you can see it works properly right now we have all inputs i'm gonna create here a button and when we click on this button we are gonna send our fields to our backhand server so i will say button and add let's create here on click method handle click close handle click if you click on this button by default it's gonna refresh our page to prevent this i'm just gonna write here event prevent default and after that using axios we are gonna send our data and of course if you are making api requests it should be async function and i'm gonna say try catch block and await axios this time it's gonna be a post method and my end point here books and i'm gonna send my json object and it's gonna be my object here let me close here actually okay and if everything is okay i want to be navigated to home page to do that again we are going to use react router dom let's say const navigate use navigate as you can see from react.on and using this navigate function we are going to go to home page and if there is an error console log is error of course you can create here any use state and show your error here somewhere here let's try title from react title from react description from react cover from react i'm gonna add and as you can see we are here let's check okay it's not here that because remember when we send any data we are not sending our price i forgot that let's update quickly i'm gonna add and it's here perfect let's take care of this style let's come here and create a style file here i'll say style.css and inside app.js i can import this style.css firstly we are going to use this app class name let's say vh is going to be full screen zero pixels from top and bottom 100 pixels from left and right like that it's gonna be display flex a line item center and just fire content center basically i just centered all my elements and let's say text align center as you can see they are centered what about those books it's gonna be display flex so they are going to be horizontal like that but as you can see this one is smaller this one is bigger to prevent this i will give exactly the same bit to do that i can use flex so all elements will be the same size and as you remember we have an image of course we didn't give any image but if we have image with 200 pixels height 300 pixels and object fit will be cover let's give here a background color and you can see better maybe this one doesn't matter okay they look pretty big that because it's 300 percent as you can see it looks much better of course we don't have any image yet but when we add it's gonna look much better actually let's make this a little bit lighter okay perfect and what i want to do is to create here some buttons that we can delete books or update them so i'm going to open books page and after this price i will say button is going to be delayed button and i will say late and one more is gonna be update like that but as you can see they are horizontal to prevent this i'm gonna come here and i will say display flex flex direction will be column like that and i'm gonna give some gap between them and let's say 10 pixels like that and i want to align item center and for those buttons let's say delete i'm going to delete the border i'm going to give some padding 6 and 3 pixels background color will be white and cursor will be pointer oops border will be no of course not border okay so i will do the same thing for updates so let's give different border for this delete and update and different text colors i will just copy and paste this red color and for the update button it's gonna be this blue color like that of course i'm not gonna spend that much time design is not important for this tutorial but it looks a little bit better compared to begging if you want to you can give some space between those items various books here i will say captain as you can see it's better and that's enough i think i don't want to waste your time let's try to delete our items to do that firstly i'm going to go to my backhand server and create here a new endpoint so i will say app and this time it's going to be a delayed method and our endpoint will be again books but this time we have to give a specific id if we want to delete this item we have to know its id and when we click on this button we are going to send this id here and we are gonna delete this specific book so i will say request response firstly i want to take this id to do this i will say const book id it's gonna be request params.id these params represents this url and this id is this specific id okay let's write our query query is gonna be delayed from our table which is books and i'm gonna write here a condition where id equals this book id but remember we are not writing our values here directly instead we are writing question mark and after that when we make db query we are writing here like that let's do that i'll say db.curry i'm gonna send here my query and finally book id and her data if there is an error send it if there is not book has been deleted successfully let's try i'm gonna go to books component and when we click on this delete button i'm gonna call on click event remember we have to send our id so i'm gonna create here a function handle delayed and we are going to send our book id let's create this function const handle plate async function we are going to take our id here and try catch block console lock this error and i'm going to await axus it's going to be a delayed method and my endpoint books and remember we have to send our id to do that i'm gonna write here plus this id and after that if everything is okay we can refresh the page window location and reload of course in the future projects we are gonna do this using a redux or some other management tools but for now we can refresh and i'm gonna save localhost of course books and its id like that i hope everything is okay let's try i want to delete this one i'm gonna click and as you can see it's not here anymore i will refresh and it's gone perfect it works so what about this update page when we click on this update button we are going to be redirected to update page to do this i'm going to wrap this text with link function we are crowded on link and update and i'll say to update page but it's not enough we have to specify our book id so i'm gonna change this with a backtick and with curly brackets and i'm gonna write here my book id like that let's try i'm gonna click as you can see update two update 3. but there is a problem here that because we didn't specify our id here let's say it can be any id and this time as you can see our component is here update okay it works right now i'm gonna create exactly the same form actually we can just copy here and paste inside update of course i'm gonna change this component name and it's gonna be updates okay and here update to look of course we didn't give any style here let's give quickly i'm going to go to style and if you remember we have a form div i will say display flags flags direction column is going to be vertical and gap between them like that let's take care of this input quickly 150 pixels padding will be 10 pixels and i'm gonna give some order one pixel solid and grey like that it's nothing special for this button form button of course we don't have this class name let's write it here for this add and update okay i will say border none i will give some padding background color will be light coral and font color will be quite like that actually it doesn't match something like that and frankly it will be bold and finally cursor pointer okay it can stay like that doesn't matter and right now we can update our book let's open up our index file and create a new request endpoint and it's going to be a put method then we update any element it will be a put method and again books and specific id we are going to take this id and update our book let's play it here and it's gonna be update books and i'm gonna write here set and which fields i want to change it's gonna be title description price and cover and my condition here where id is question mark let's write our values here i'll say const values and remember how we were doing this it's going to be exactly the same values here i will just copy and paste but remember there is one more value here we should add this when we call our query and what i'm gonna do is to call all these values additionally add here this book id so we have title title description description price cover and finally this book id for this id and book has been updated successfully okay let's try i'm gonna go to update page there is a handle click here but this time instead of adding it's gonna be put methods and we have to specify here our book id but how we can reach this book id it's really easy using ria crowder dom we are going to use use location hook let's come here and const location use location hook and let's see what's inside of course there is a problem here i will just comment this out and as you can see inside this location we have path name and i will try to take only this part of this string to do that we can use javascript split method dot path name and split we are gonna use slash so it's gonna split this string into three pieces before this slash between these two slashes and after this last slash and it's gonna give us an array if i take the last element of this array i'm gonna reach these five so if i say third element remember how indexes work and as you can see five is here perfect so i can use this i will copy this and i'm gonna say const book id it's gonna be this name so i can use it here like that and let's see i'm gonna refresh the page let's close here and i will say updated book title actually updated description 555 updated.png let's update and as you can see it's here it works properly let's create new one test test description 999 test.png i'm going to add it's here i can delete i can update and perfect as you can see it's not that complex if you are not totally beginner it's even easy so in our next projects we can use mysql like that i hope you liked it if you learned something new today please like the video you can support la medium using the link in the description below or by joining the channel don't forget to follow lamada social media accounts i hope i'll see you in the next tutorial goodbye

Channel: Lama Dev

Views: 216,938

Keywords: mysql, mysql crud, mysql create, mysql read, mysql fetch, mysql update, mysql delete, mysql nodejs crud, mysql express crud, mysql react crud, mysql js, mysql javascript, mysql rest api, mysql node rest api, react mysql, react mysql fetch data, lama dev, crud tutorial

Id: fPuLnzSjPLE

Length: 46min 26sec (2786 seconds)

Published: Sun Sep 18 2022