Crud application Nodejs and MySQL: Insert Data (Part 1)

in this chapter we will create a post route where we send the data from the front-end form to our backend SQL Server so let's get into it so first and foremost we will need a form so we can create a form by creating an HTML page so we will create a new file and name it form dot HTML and in a form.html we will create a simple boilerplate we will change the title to crud application in the body I will create a container so I will do div dot container and in the container I will need a form so we will do a form with a method post so the basic difference between post and get method is that a post method sends data from the browser to the server and the get request gets data from the server to the browser it's really simple put yourself in the place of a browser and if you want any information from the server you will have to use the get request and if you have to send data from your browser to the server you will have to use the post request that's it since we are sending the data from the form to the server we will use a post request and the action I will keep a default in this form we will have two fields that is input where the type is text and value is named let's copy this paste the same and change the value to email and to submit this form we will need a submit button so we can do that input submit and the value is submit once you have the form ready we can link it with the app.js so we will go to app.js and in the Home Route we will change the rest dot send to rest.send file so the basic difference between send and send file is that sends a message that will be displayed on the browser and in send file we can send a file that has to be loaded or rendered so we will do rest dot send file and in this file we will give it a location of the file and since we are using related path our server needs to understand the relative path so we can start the path from our application folder by giving underscore underscore directory name that stands for d i r n a m e Plus the location of our form that is the route form dot HTML once we are done with this we can save our application and open our terminal you can also open your terminal by going to terminal and new terminal and the shortcut for it is control and tilde and over here we will run nodemon app.js which is a app engine once we run this we can see a message that our server is up and running so we will open this on our browser so we can go to the location HTTP localhost colon 3000 where 3000 as a port enough forms we have made a small mistake where we set the value and it has to be placeholder foreign by the way you can select multiple Lines by holding alt and clicking in front of the line where you want to edit placeholder let's save it go to our application and refresh this page and yes now we can type on him properly okay so this is a basic form we will style it later so let's go back to our application so once we have the form ready we will need to link it with our database and before that we will need a database so if we can fire up xampp if you don't know how to install xamp we have done it in a previous video you can check that out so once it's done we will start MySQL and Apache and in our minus 12 we will open the admin settings so this will act as a database and for this project we will create a new database so we will click on new that's on this side of the screen on the left side of the screen and we will give it a name so we will give it a name test crud and create this is a GUI where we write SQL commands using a graphical interface if you are familiar with writing commands you can do the same by clicking in the console and writing it over here so we will create a table called test with three columns so the First Column will be ID the second column will be name and the third column will be the email so ID is an integer name will be the wirecare that stands for variable character and email is also badcash which stands for variable character so the length we will specify 200 each and ID has to be set to Auto increment as we don't want to drive the ID from the back end and we want the database to increment the ID every time a value is being generated and once you are done with this we can save and the tables are ready so now we will have to push the data from the front end to these tables so before that we will have to establish a connection with our application so we can do that by creating another file called connection dot Js and in our connection.js we will require SQL module so we can do that by opening our terminal since we have our terminal open over here let me expand it and over here we will open another command prompt so we will click on the plus icon and we will open our Powershell so we will have to install a module called MySQL to interact with the mySQL database so we can do that by installing a package so npm npm I and the package name is MySQL all small so this package will help us interact a node application with the SQL Server so once this is done we can close this and in our connection.js we will establish a connection so we can do const MySQL is equal to require where we'll have to require the module that we just installed that's MySQL foreign so in production it is really important that you secure your API key or your database keys so for that we will use a DOT environment so we will create another file called dot EnV y so this is our environment file and over here we will create all the passwords and save all the passwords so for this we will have to create four variables that is host where the host is equal to localhost since we are locally hosting it on our system and then we will have to create user make sure all these terms are in capital and the user is root password can be empty since we have not set any password and the database name this is really very important this name has to match the Zam database that we have just created so let me go over here and copy this name all right once we are done with the environment file we will close it and we can require it over here so we have to require it by saying require dot EnV because it's a DOT environment dot config once you are done configuring it we can set up a connection by setting that Pawn with stands short for connection is equal to mySQL dot create connection and in this connection we will have to pass all those four parameters so we will set post colon and we can access the dot EnV variables by saying process dot EnV and the name that stands for host comma user and the same process process dot EnV y dot user then password process it's the same for all foreign and once you are done with it we can close this now we will have to establish and check if the database is corrected successfully or not so we can do bytecon dot connect and we will write another anonymous function foreign with the inner parameter of error so it can display an any error if it occurs and within it we will write an if condition if error we will tell it to throw the error and if there is no error we can console log a message on a terminal just so that we know that our database is connected so we can see database foreign and once it's done we will close this and it will close the connection okay so now we will have to export this module to our application so app.js so we can do that by module dot export and we will give it a name.con so we are exporting this function to app.js let's save this and go to our app.js and right below where we have created our application we will establish the connection so here we will link our app.js with our environment variable that we have created so we will do require d again dot EnV y dot config so we have configured a DOT variable dot environment variable and now we will establish a connection with our database so we will do where Pawn is equal to require connection let's save this there's an error over here so you cannot file module dot EnV okay this error is because we haven't installed the module.an B so we can go to our Powershell and install the dot EnV variable so we can do npm I dot EnV and once we are done with this let's resave our application so there's a type over here so it has to be dot e and B let's save it and there's a typo Even in our connection so it has to be dot EnV all right so make sure to enter the name properly or else you will find an error so here we can see that our server is up and running and even our database is connected successfully so now we can push the data from a front end to the back end so let's work on that so we will create a route this time it will be a post row so we will do apps dot post and in this post we will give it a row this is the same Home Route and from here we will write an anonymous function with two parameters that is request and response and inside this function we will get the data from the front end to the back end so we can do that by importing another module which is called body passer let's go to our terminal Powershell and install another package that is body parcel so we can do that by mpm I body hyphen password so what our body person module does is that it takes the data from a front-end a form and gets it to the back end where we can process it and send it to our database so once it's installed you can close this and we have to link it with our app.js so that can be done right after establishing a connection so here we can see const body parser is equal to require foreign we will create all the requiring modules in one place to write under this yes and we will establish the dot A and B right after our Express app yes so once we have installed and linked body password without page we will need to create another express route where it can read the body parser so we will set app.use we will set the extended true extended pulling true that's all and let's save it so now we are ready to use our body parser so first we have to set it up so let's go to our form dot HTML and give it a name attribute so the body parser will search for the name tag and from there it will take the data out so we will give it name is equal to name and yo we will give it a name is equal to email let's save this go to app.js and over here we will have to get the data and save it in our variable so we will do that name is equal to request Dot body we are requesting the body so we are requesting the data from the body of our HTML dot name we will do the same for email where email is equal to request dot body dot email and we can just test it by doing console log and analog we can pass name comma email let's close this and we will link it with our form.html yes it's linked yes we can run this application by saving it Opening Our terminal I'm checking yeah the database is ready so let's go to our browser the cloud application let's refresh it and fill in the form we'll give it my name and an email of test at abc.com so once I submit this form a request is being made let me go to our code so here we can see that the name and email that has entered is being printed so our body parser is getting the data successfully so let me close this go to our app.js and right below this we will create an SQL command where we push this data to our SQL table so we can do that by establishing a connection sync Pawn which is a connection variable dot query and in this we can write our SQL query so before that we will have to create an SQL query so let's set where SQL is equal to since we are inserting we will do insert into test where test is as damp table name so in our test crud we have a table called test so that table name so we will do insert into test and we will pass the parameter that is name comma email these two name has to match the column names so let me check it once so it is username and email so let me just copy it and paste it over here so it's username and email so once we are done with this we will set values question mark where we will pass the value from a front end so we will create another variable values which is equal to name comma email that we have fetched from a front end let me close this and now we can pass it in our con.query so in our query first we will pass our SQL statement after which we will pass our values thank you and right after our values we will write a function to check if the database is being accessible we will write function and in this function we will pass two parameters that is error and result so if there's an error we can print it out using the error variable so once after we are done with it we will put a condition so if foreign true error and if at all there is no error we can console log a message for the admin seeing data uploaded after the data has been uploaded we'll have to redirect the user to a page so we can redirect them to Res dot redirect so res dot redirect redirects the user to a specified route so we will redirect him to the Home Route and once that's done we will save it go to our form so let's refresh this page and enter my name and an email test.com and let me submit this form all right the data has been submitted so let me check it in our database and we can check it by going to browse so over here we can see that my username and the test has been entered let me do another application where we enter Astro and give the email Astro at test.com Let me refresh the page and here we can see that our crowd application that is create has been working perfectly so that's all for this video so in our next chapter we will see how to read the data from a SQL database and print it on our front end

Channel: Bit by Bit Official

Views: 9,214

Keywords: nodejs, node tutorial, node js tutorial, node.js tutorial, nodejs tutorial, learn node.js, learn nodejs, node js, node.js, nodejs beginners, node, what is node.js, what is nodejs, node js tutorial for beginners, server side, mean stack, js, server, web development, javascript, beginner, backend, mysql tutorial, mysql tutorial for beginners, learn mysql, Mysql, php crud, crud javascript, crud application, Crud

Id: 5ZDbWdrDfHU

Length: 22min 34sec (1354 seconds)

Published: Tue Dec 06 2022