Using Node.js with MySQL – CRUD | XAMPP / PhpMyAdmin

Hola Amigos y Amigas my name is Raddy and you're watching my channel RaddyTheBrand I hope that you're having a fantastic day! Today we're going to explore the Node.Js MySql npm package and we're going to create a simple Beer API, and in particular we're just going to have a look at creating a few queries such as displaying records, displaying a specific record by ID, adding new records, deleting records and updating existing records. To do this i will be using XAMPP to run my Database and so we'll be working in the good old PhpMyAdmin I've also created an article where you can find more details and all the links and the source code will be also there. If you find this video useful make sure that you share it with your friends family and pets, give it a like and consider SUBSCRIBING to my channel and now let's jump on the computer and get started! Welcome everybody let's get started by initializing a new project and then we can have a look at xampp and creating the database. First of all i am already inside my project folder so i can do left shift right click open powershell window here if you're a mac obviously you have to cd to your project folder in your terminal and then initialize the new project by doing npm innate this will ask us a couple of questions and all i'm going to do is give my project a package name of node.js mysql and then just continue pressing enter once we're done this will create the package.json file for us and now i can open my project in visual studio code by doing code and dot this opens visual studio code for me which is great and i have my project files here on the left side so if you were to open package.jsonpal you'll see that we have a very basic project at the moment and we need to install some of the dependencies now i'm going to go back to powershell and install the dependencies that we need so let's clear this one first and let's install the dependencies so let's do npm install and the dependencies that we need are express mysql and the body parser press enter and this should take a couple of seconds to install and as you can see in package.json we have the dependencies body parser expressed in mysql and the last dependency that i want to add is the nodemon so we don't have to restart the server every time we make some changes on our project and to do this we can simply do npm install dash dash save dash dev this is a development dependency one and within this should take a couple of seconds as well and we should be good to go now that we have nodemon installed let's make sure that our project starts with nodemon so under here under scripts we can do start column and then inside quotes we can do norman app.js and then comma and now let's add the app.js file here inside our folder so new file app.js and we're going to be mainly working in this file today so let's press enter and to run our application all we have to do is go to the powershell and do mpm start this should hopefully start a server and as you can see everything is working fine but if you go to the page now nothing will happen because we haven't actually set up anything yet so let's go back and start setting up some of the basics for application so the first few things that we need to do is require express body parser and mysql so let's do const express equals require express in single quotes and it's pretty much up to you whether you want to finish your lines with semicolons or not i'm just going to leave it without just because it looks cleaner to me but i don't think that there is any difference so now let's the const and let's require and let's require the body passer body dash passer like this and we're done and then the last thing that we need to require is the mysql so we can do const mysql and then equals require and then inside here we can just do mysql and we are done now let's setup or express application under the variable of app so we can do const app equals express just like this and then let's set up a port number or app can listen to on so we can do const port equals process dot environment dot port and this is mainly if you want to actually publish your application but today we are mainly going to be developing it on our localhost so we are just going to be using so we're just going to be using the port number of 5000 and that should be fine now as we're here we might as well now let's not forget to make sure that all up is using the body parts because we want to pass uh some json data later on and to do this we need to add two lines first of all let's do app.use body parser just like this and then we can do url encoded and inside here we need to pass this as extended force and then we need to do app.use bodyparser.json and this will help us when we are passing adjacent data to some of our queries okay we are pretty much done with the basics and then here and then inside here is where we'll be adding all mysql codes so let's leave it as it is and the last thing that we need to do inside here is make sure that all app is listening on the uh environment port or the port 5000 in this case because we under host it's going to be 5 000 so let's do that we can do a comment here listen on [Music] environment board or pods 5000 to do this we can do abn.listen and inside here and inside here we have to pass the port number and then this will be another function with console.log and the console.log i just want to say something like listen on port on port and then we can pass the port variable from here so let's copy this and paste it okay we should be good to go so if we save this and run our application as you can see we don't have any errors in here which is good um we did run the application earlier so we don't have to run it again we did do a npm start and nodemon just restarts automatically but sometimes if you get a problem you can always control in c or command and c to just to terminate the job and restart it all right so if you go to the browser now and refresh and go to localhost with the port number of 5000 you should see this message can now get and this is a good thing okay now that we have the basic setup let's have a look at our database first of all and before we do that i just wanted to mention that i will be using postman in this project so if you wanted to download this it will make or life a lot easier when we want to get data post data delay data and so on it's very useful it's free you can download it the link will be in the description below and yeah you can get it on windows mac linux i believe so make sure you get that and for the mysql i will be using xampp which i have already configured so i have a basic xampp configuration where i can run apache and mysql so let me run those services and i will go to localhost phpmyadmin in the exact php and this will allow me to log into the database portal so my username is root and the password is password and here is where we'll be creating our database but of course feel free to use whatever tool you like i think that this is just easy and already had installed first of all let's create a new database and to do this we can go under here databases let me zoom in a little bit and we need to give our database a name so for this i'm just going to use the same one as i did on my blog post so node.js underscore beers and then i will just click create now this is going to ask me how many columns do you want and and i believe that i need five columns but you can always add more or you can delete columns as well let's put five columns and let's give or table a name of beers just because we'll be storing beers in this example so beers with five columns let's click go and this will ask us to and this will basically asked us to set up some of the columns and you can do this multiple ways you can either do it with sql or you can just use this user interface to do it and i'm just going to show you this how to deal with this first and then i'm going to show you the sql command that you can use as well and so use whatever you prefer let's uh focus on our fields and the first field that i want to do is an id because every beer is going to have a unique id and i want to make sure that this id is set to either integer or maybe big int if you're going to have a big database then i'm going to leave this blank this plank this blank on this blonde so might as well just go to the point here so what i want to do in the first field is do auto increment uh tick in here because what i wanted to happen this is basically going to be your primary key every time we add a new beer in this case i want each beer to have unique id uh so that's why i'm doing the auto increment so let's create go and with them with this one the second one that i want to do is name and full name i'm going to go with varchar and we can set this to 255. this is up to you as well so mess around with the numbers and the types and so on the next one i want to do is a tagline for the beer then we can set this as varchar as well and we can just copy the one from above 255 should be sufficient then let's maybe add a description and for the description i'm just going to do text and i think text is just better for big amount of text so let's do that and i'm just going to leave the length as it is so and the last one that i want to do in here is maybe we want to add an image with a beer i mean to be completely honest all this doesn't really matter it's just an example but let's say we have an image and for the image maybe we can have a vacher um a vacher and the voucher can be set to 50 uh in length so this is all looking good let me zoom out a little bit and before i say this i just want to show you the alternative way of doing this and this is if i preview sql this should give me the code and basically you can just create a table give it the table sorry give it the database name then the table name and then list all the fields that you want this will be available on my blog if you just want to copy it and go under the sql and just run it from there but i'm just going to do the article way and press save all right so now that we have our table set up it should look something like this and you should see on id that we have this golden key which is good we could quickly add a record just for example we can go to insert and this gives us the option to add quick to records so let me think of a beer okay so i'm just going to quickly add two beers just so we have some examples when we create some data so for the first one i'm just going to copy this one that i found online from another api so this is the tagline we need a description let me copy and paste and we need uh an image so maybe you can just copy this and then let's add one more so we have two just like these and as you can see i'm skipping the ids because they will be automatically added so this will be one this will be two and so on so let's press go and hopefully if everything worked correctly you should see two rows inserted and if you go to bs you should see the rows inserted and they have the id of one and two and of course you can make this a lot more interesting you can do you can add status you can add date created date update it and so on but we're just going to keep it simple and leave it as it is now that we have the database we can actually focus on our code now let's go back to visual studio code let's close this actually so let's start writing or my scale connection and we're going to be doing it slightly different today so what i'm going to do is actually create a connection pool which is basically supposed to be faster because connections can be reused when future requests to the database are required and they're basically used to enhance the performance of executing commands on the database and it's like a cache of the database connection so let's have a look at how we can do that so to create a pool we can do const equals mysql dot create pull and inside here we need to put the connection settings to our database and we can also specify a connection limit so so let's do connection limit of 10 and this is basically the maximum number of connections to create at once uh you can read a little bit more about the in the documentation in the official documentation and the next few things that we need to do might be familiar to us now we need to specify the host name the user the password and the database that we want to connect to and for the host i'm using localhost so we can do host and then column lurker host like this but it needs to be in single quotes like so then we can tidy this up by moving it like so then comma then we need to do user and the user for me will be root and then we need to specify the password the password for me is actually just password and last but not least we need to specify the database that we want to use and for me this will be the no jsbs that we just created like so let's remove this and let's tidy this up a little bit like this and we should be good to go all right okay now that we have created a pool and we have all the details in here we can actually start writing the first request which will be the get so basically i want to be able to get all beers in your database or get to a beers or get to a rows whatever you prefer and the way we can do this is we can do app dot get and inside here we can specify the url so if we leave it blank which will mean that we can simply go to localhost with the port number and this will be triggered but if you wish you can just put it like bs like this and then you'll have to go to localhost 5000 and then slash bs and this will trigger this get request so i'm just going to leave it blank just because application is to do with this and i don't want to be put in slash bs and so on let's keep it simple so inside here then we need to have the request and response and then this will be an arrow function and everything will leave in this arrow function so to connect to a pool we need to do pull use this connection variable here so pull dot get connection and then we need to open and close with curly brackets like so then we'll have the then we'll have the error and connection like so then this will be um this will be a narrow function like this i think i'm writing this a little bit weird but bear with me it'll make sense and the first thing that we might want to do is check for errors so we could potentially just do if error we can throw error like so or you can do a specific thing that maybe you want to display on your api um but i think this should be good enough just to throw the error in the um powershell and then we can focus on getting the uh connection but before we do the connection query i just wanted to show you that you can also get the connection the connection thread id i mean this won't be useful now but we could do console.log and just display it anyway so just in case you need it as id and we can put plus or we could just do with the slanted um we could just do the slanted single quotes and then do connect it as id and the id would be connection dot thread id and this will give us the fret id but in this case we won't use it so it's a bit so it won't be useful but i'll leave it here as an example anyway okay let's focus now on building our first query and to do this just for example um what we can do the simplest way of using the query is basically we can use the query method and this takes two parameters and the first one is the sql string which will be sql string which will explain in a second and the second one is the callback so let's actually delete this as this is not useful at all i just want to explain it and let's do the query so what we have to do is connection dot query and then inside here if you're familiar with mysql this should be easy for you in single quotes we can do select everything star means everything and then we can select from the table name beers and then so we're going to have a callback of error and rows and this will be an arrow function again so like this and inside here we can do connection release which will return the connection to pull so let's do connection dot release like this and then the last thing that we want to do is check if we have an error maybe so let's see if we have if we don't have an error then maybe we want to response send the rows so the data that we're getting but if we do have an error we can do errors and then we can do console.log log and we can just log the error i guess okay let's save this tidy up a little bit and let's and let's go to the browser and see what happens so if we go back to this url of localhost with the port number 5000 press enter you should see the two records that we added earlier so we have the id of one the name the tagline the description and a link to the image and as you can see they're the same in the database so if we were to maybe let's change this to uh two and save it so if we refresh this we should get logar2 and this seems to be working well now instead of using the browser which is fine for this to get data i'm going to be using postman and let me show you and let me show you postman so in postman if you're not familiar you can like create different collections and save some of the euros that you're using and for example if i uh and for example today i'll be using this euro obviously the localhost of 5000 and i've set it up in here um i'm going to be using another one for to delete to post and to put data and so on so it's pretty handy and we can literally just like get get the request straight away in here and visualize the data a little bit better um i believe so make sure you have this installed as well it will help you massively if you're following along but this is all working now let's continue let's close this and let's go back to app.js now this is a good and to be completely honest we kind of already have done the hard bit of this tutorial so now we are pretty much going to be copying and pasting this and kind of modifying it a little bit so the next bit that i want to do is get a specific beer by id and what i mean by this is if i go to the browser quickly if i go to localhost with a port of 5000 and i do for example slash and i put number of two i want to be able to grab the beer with the id of two and if i put one i want i want to be able to grab just this object here if this makes sense so to be able to do this and by the way you can do all sorts of stuff with this you can maybe like do it by name by tagline on the image whatever you wish so it's quite powerful and this is how you can build on your api so let's have a look at how we can get a specific beer by id so i'm going to copy this code and because it's going to be very similar so let's paste it in here and let's do get a bear by id okay to be able to get the id first of all we need to do inside here we need to do slash column and id so with the body parser we can actually grab this id and insert it into the mysql query and to do this we can use the body parser it's actually fairly simple so in this case we've already done the heartbeat with the body parts here we've included and we've put those two app users in here so what we have to do now is do connection query select everything from bs and now we need to extend on this and do where id this equals question mark and the question mark is basically a placeholder so we're escaping query values and we're basically trying to prevent from any sql injection attacks now that we have the id here we actually need to grab the id with a body parser and to do this is actually fairly simple we can just do in brackets rec dot params id and this should get the id that we're passing from the browser into here and then into a code we could also destructure this and make it a little bit cleaner i guess but i think that this should do the job and last thing that i'm forgetting here is comma so let's add the comma save this and let's see what happens so if you go back to the browser and press enter at the moment we're getting two results just because we only have two results but if we do slash one we should be able to get only the first result and if we do slash two we're getting the result with the id of two and if we do three we shouldn't get anything because in our database we don't have another record and that's absolutely fine and i should have been using postman for this but uh yeah it's the same thing for now anyway so if i do one we can send it and just get the id of one which is here so this is how we pass parameters and this is how we get them using the body parser and now let's continue and have a look at how we can delete the racket so to delete the record will be actually surprisingly very similar to this so what we have to do is let's copy this quickly and paste it in here so maybe we can say delete a record slash pair okay so we actually want to delete a particular record so for example uh we might want to delete record number one or two so i'm going to leave this in here but the main difference on this block is that instead of app.get we actually need to do app.delete now we can leave the id in here and then what we have to do is change and what we have to do is change or query a little bit so instead of select we need to do delete and then we need to remove the star because we just want to delete from biz where id is equals to the id that we're passing in here from the url and hopefully technically speaking this should do the job and to make this a little bit better what we can do is instead of getting the rose we can just maybe display a message saying um i don't know beer with the record id and then we can pass the record id by using the dollar sign and the curly brackets and maybe we can just use this like so and maybe just the pass being has been removed dot okay and then if we get an error we'll just get the error in the console so let's save this look at the console everything is looking fine so far nowadays which is unusual and let's go to postman now and let's create a new tab which i already have created inside here and the important bit about this tab is that you need to set this from get to delete so this one needs to be delete and the error will be exactly the same and then we just need to specify which record we want to delete so it would be nice to have a few more records but we can just work with the two that we have so if i do this okay so we have two records maybe i just want to delete uh record number two so let's test this so inside here we have to pass the number two and send this with the delete method as you can see we have beer with the record id has been removed let's have a look whether this is true so if you go back to the get method and send it you will see that we only have one record left which means that this was successful if you go to the database you will see that if you refresh go to bs you will see that we only have one record okay so this is a good time to actually have a look at how we can add a few more records so to add records let's go back and we can actually copy this and modify slightly so as you can see as soon as we created the first one everything else seems to be very easy now let's do another one and this will be add a record slash there so for this we need to change the delete to post we need to remove this id because we actually won't be passing um any ids in here we'll be getting the parameters uh with the body parser which i'll show you in a second and we're just basically gonna pass an object with some data like the name the tagline the description and image so let's do that so everything else here stays the same what we have to do is get the body parameters which we'll be passing and to do this we can actually create a variable so let's do const of i don't know parameters params to be short and then we can do rec not body so right the body is basically going to get the data that will be passing in a second with postman i will show you uh we could also console log this if you wanted to see it uh maybe we can do [Music] i don't know what can we do maybe we can do in here so we can do unlock and we can do rec dot body just so you can see what it looks like so we need to also change the query a little bit again so instead of delete we're gonna have so insert into bs where insert into beers and we don't need this we actually need to just do set and then a question mark and then we need to pass the parameters that will be getting will be passing through the postman and getting with the body passes so basically that's why we created this variable and i think that we should be good to go but let's change this message as well and let's do beer with the record id of um this is a difficult one because we'll probably have to do because this is will be an object we'll probably have to do i don't know maybe params dot id or you can pass the name or whatever um we'll see if this works has been added okay uh let's save this so basically what happens with postman we're posting a json object which body parser allows us to do inside here then and see how we can post some data okay and for this i've got another tab inside here and what we have to do is first of all we need to make sure that we select post on this one the url stays the same of course and then we need to move to body select raw as will be as we'll be sending raw json file and and the important bit here is to also select json from here not javascript json and then we can pass some data so for example we have the name of let's say budweiser tagline is the king of this then we have a description i believe and maybe we can just leave i'll just copy some text from the internet and then i believe that we had last one i believe that we had an image and for the image will normally pass like a url to the image but i don't have one now so maybe we can just leave it empty for the example and i forgot to do a comma in here and that's why this is underlined okay let's have a look at what happens if we actually pass this raw jason object so if we send this we should see beer but we should get beer with the record id of undefined has been added it doesn't really matter we can get the uh maybe name or whatever we could potentially just do name maybe uh let's save this and try it again so so maybe we can do something else like punk ipa um and then just maybe we can leave this empty for now it doesn't really matter so much uh let's send and okay so we are now getting beer with the records id i need to change this as well but we're getting the name now has been added so let's just change this with name has been added okay so we're good to go in here we can add records and if you go back to postman and query all the records so let's send we should see all the records and because and we have the first one here budweiser and the punk ipa that we just added and of course because the id is a unique identifier uh and we deleted two that's why the id is given but this is not a problem at all this is how the my scale works basically they just basically the ids will just increment but that doesn't matter at all all right the last thing that we need to have a look at is how we can actually update a racket so for example oh the punk api doesn't have tanglai description or an image so maybe we want to update this record and for this let's copy this code again the yard records beer and paste it here and then let's just change the title so we know what it is update the record slash beer and for this one what we have to do is instead of up dot post we have to do app dot put and then we can leave the url as it is and so on and now this one could be a little bit tricky but instead of doing this we're gonna have to change a little bit maybe we can do some data destructuring um and what and to do that potentially with postman we can send the json object here grab it with the body parser and this structure the data like this so we can do const and inside here we can do id then name tagline description and image this will be equals the rec.body and hopefully we'll be able to grab the data and have it stored in those variables so the id we can just di we can just use the id to pass the name the tagline and so on so instead of parameters here we can just do individual individual objects if that makes sense so we can remove this now and let's modify the query a little bit and for example what we could do is for example we could select the beer with the idea for punk apa and maybe update the name or the description as we as you can see we don't have any images or description of timeline for it so let's do that let's have a look at how we can do that so to do this instead of insert you probably guessed it we're gonna have to do an update and then instead of into we're gonna remove this and we're just gonna do update bs set and then for example let's say we want to update the name so what we can do is do name equals question mark where equals question mark and instead of parameters now which doesn't exist anymore we deleted it uh we can just pass the name so let's do instead of uh object let's do name and pass the id so hopefully speaking we're gonna get the name from postman now with the id of four and update it let's have a look whether this works as you can see at the moment we have the punk apa here with no other records so we can go to postman and do another tab in here so this tab will be obviously set to put now the same url uh under body we can do raw json and let's say and let's say we wanted to change the id of four and we can change the name to whatever we like so let's say uh pung apa updated hopefully speaking hopefully if we press send now for some reason this isn't working so let's go to here and error pass passer error you have arranged syntax okay let's have a look oh and this is because where we need to actually put what where is and this will be an id okay save this go back try to resend this again we want to update the name so hopefully if we send this could not request i think the app crashed for some reason so let's restart this and start again all right maybe we can try it one more time and now it doesn't seem to work okay update bears set name question mark my id is question mark okay let's try this again all right so could have been this here i think it might have been because this object didn't exist anymore uh the one that i just removed all right so what we can do in fact we can just do name here anyway in dollar sign with the curly bracket and this would do it so if we go to the database now and refresh you will see that we have punk apa updated and if we do it one more time maybe we can do one kpa one two and send this you will see beer with the name bank api one two three has been update updated and if you go back to the database and refresh you'll see that we get one two three and we can do the same with the other parameters so for example we can do name i believe that we can do let's try tagline as well so maybe we can pass tagline now and let's try it so let's save this go to postman and add a tagline this is a demo tagline let's send this and we gain an error and this is because and this is because i'm getting a little bit tired probably now but this is because tagline needs to be equals a question mark and space okay save this resend this and as you can see we are getting there with the name like it has been updated if we post this you should be able to see the tagline has been updated if we go to the database refresh this you see we have the tagline and maybe we can do the description and the image quickly just for this example and we can wrap it up so as you can imagine you can now do comma and just do description equals question mark make sure you pass the description here just like so we can do view toggle wrap this is looking ugly now but it doesn't matter then the description and we have image which is the last one so let's do image and add the image before after the description sorry save this go to postman and let's post some more data let's update this so we had the description watch and i don't know some beer and let's add image we forgot the comma and then the image would be on your router image obviously this will be a real url but this would do the job so let's update this and we get another error and again i forgot to do equals and question mark save this let's go back send this and hopefully we get punk api 123 has been updated and if we go to here and post it you will see that this record has been updated and let's actually uh try to update another record maybe we can update record number three and let's say we want to let's copy this and let's say we want to update we want to update the image only maybe we can do image dot image1.js or whatever and budweiser is the king but is the king of this so let's update this uh if we go back and resend this we will see that we're getting uh budweiser is the king of beers which is pretty awesome no errors in here as you can see they're locked in in the console as well which pretty cool um the database is working quite well and that's pretty much everything from this tutorial i hope you enjoyed it make sure you smash the like share the video with your friends and family don't forget to subscribe feel free to say hello in the comments below and i will see you in the next one thank you very much for watching as always my name is raleigh and you're watching my channel riley the brand

Channel: RaddyTheBrand

Views: 41,477

Keywords: node js, node js tutorial, node js for beginners, node js mysql, node js mysql express, phpmyadmin, nodejs crud, nodejs mysql pool query, node js api call, xampp, nodejs xampp, nodejs phpmyadmin, nodejs postman, nodejs mysql crud, nodemon, raddy, raddythebrand, raddy the brand, learn nodejs databases, javascript, nodejs development, express, body-parser, nodejs bodyparser, nodejs parse data, nodejs delete dabase record, nodejes add database record, node js mysql crud

Id: f5kye3ESXE8

Length: 48min 18sec (2898 seconds)

Published: Mon Oct 19 2020