 Hola amigos, yeah amigas, my name is Rady and you're watching my channel Rady the Brand. I hope that you have enough 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 XAMP to run my database and so we'll be working in the good old PHP MyAdmin. 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 and 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 XAMP and creating the database. Now 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 a new project by doing npm init. 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-masql 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.json file you will 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 express mysql and the last dependency that I want to add is the node mon 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 def as this is a development dependency mon and within. This should take a couple of seconds as well and we should be good to go. Now that we have node mon installed let's make sure that our project starts with node mon. So under here under scripts we can do start column and then inside quotes we can do node mon app.js and then comma and now let's add the app.js file here inside of folder so new file app.js and we're gonna 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 npm start. This should hopefully start to server and as you can see everything is working fine but if we 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 semicolumns 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 do const body parser and let's require and let's require the body parser body dash parser 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 set up 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 local host so we're 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 our app is using the body parser because we want to pass some JSON data later on and to do this we need to add two lines first of all let's do app dot use body parser just like this and then we can do URL encoded and inside here we need to pass this as extended false and then we need to do app dot use body parser dot JSON and this will help us when we are passing JSON 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 or 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 our app is listening on the environment port or the port 5000 in this case because we are not a host it's going to be 5000 so let's do that we can do a comment here listen on environment port or ports 5000 to do this we can do app dot listen and inside here and inside here we have to pass the port number and then this will be another function with console dot 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 we did run the application earlier so we don't have to run it again we did do npm start and not only just restart automatically but sometimes if you get a problem you can always control and c or command and c 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 i'll get and this is a good thing okay now that we have the basic setup let's have a look at or 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 our 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 exam which I have already configured so I have a basic exam configuration where I can run Apache and mysql so let me run those services and I will go to localhost php admin index dot 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 I already had it installed first of all let's create a new database and 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 nodejs 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 all 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 ask 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 uh use interface to do it and I'm just going to show you this how to do it 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 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 gonna have a big database then I'm going to leave this blank this blank this blank and this blank so might as well just go to the point here so all I want to do in the first field is do auto increment tick in here because what I wanted to happen this is basically going to be our primary key every time we add a new beer in this case I want each beer to have unique id 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 uh 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 birth 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 for the 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 varchar and the varchar can be set to 50 in length so this is all looking good let me zoom out a little bit and before I save this I just want to show you the alternative way of doing this and this is if I preview sql this should be giving 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 it the old school 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 copy and paste and we need an image so maybe we can just copy this and then let's add one more so we have two just like this 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 we 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 updated 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 td 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 pool equals my sql dot create pool 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 a once you can read a little bit more about in the documentation in the official documentation and the next few things that we need to do might be familiar to you so 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 the local host so we can do host and then column local 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 node 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 you know database or get all beers or get all rows whatever you prefer and the way we can do this is we can do app.get and inside here we can specify the URL so if we leave it blank which will mean that we can simply go to local host with the port number and this will be triggered but if you wish you can just put it like beers like this and then you have to go to local host 5000 and then slash beers and this will trigger this get request so I'm just going to leave it blank just because our application is to do with beers and I don't want to be put in slash beers 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 pool use this connection variable here so pool 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 an arrow 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 but I think this should be good enough just to throw the error in the um power shell 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 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 you connect it as ID and the ID would be connection dot thread ID and this will give us the thread 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 are 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 bears 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 else and then we can do console log log and we can just console 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 tag line 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 2 and save it so if we refresh this we should get log 2 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 URLs that you're using and for example if I and for example today I'll be using this URL obviously the localhost of 5000 and I've set it up in here 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 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 get 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 a little bit so the next bit that I want to do is get a specific bear 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 bear 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 or by 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 bear 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 passer we can actually grab this id and insert it into the my square query and to do this we can use the body passer it's actually fairly simple so in this case we've already done the heartbeat with the body pass here we've included and we've put those two uh up uses in here so what we have to do now is do connection query select everything from bears and now we need to extend on this and do where id is 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 passer and to do this is actually fairly simple we can just do in brackets rec.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 yeah it's the same thing for now anyway so if I do one we can send there 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 a record so to delete a 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 we might want to delete record number one or two so I'm going to leave this in here but the 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 base where id is equals to the id that we are 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 rows we can just maybe display a message saying um I don't know bear with the record id and then we can pass the record id by using the dollar sign and the curly brackets maybe we can just use this like so and maybe just do has been 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 now it is 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 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 bear 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 got to be as 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 bear so for this we need to change the delete to post we need to remove this id because we actually won't be passing any id in here we'll be getting the parameters with the body passer which i'll show you in a second and we're just basically going to 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 will be passing and to do this we can actually create a variable so let's do const of parameters to be short and then we can do wreck dot body so wreck the body is basically going to get the data that will be passing in a second with post one i will show you we could also console lock this if you wanted to see it maybe we can do i don't know what can we do maybe we can do in here so we can do lock and we can do wreck 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 bs 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 passer 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 bear 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 this works has been added okay let's save this so basically what happens with postman we're posting a json object which body pass 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 arrest is the same of course and then we need to move to body select raw as will be as will 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 but wiser 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 euro 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 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 json 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 maybe name or whatever we could potentially just do name maybe let's save this and try it again so so maybe we can do something else like punk IPA and then just maybe we can leave this empty for now it doesn't really matter so much on the same and okay so we are now getting beer with the record 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 the name has been added okay so we get to go in here we cannot record 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 and we delete it too that's why the id is given but this is not a problem at all this is how the masquerade 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 record so for example for the punk api doesn't have tangly description or an image so maybe we want to update this record and for this let's copy this code again the art 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 app.post we have to do app.put and then we can leave the arrow 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 and what and to do that potentially with postman we can send the json object here grab it with the body parser and destructure 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 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 bear with the id of four punk apa and maybe update the name or add the description as we as you can see we don't have any images or description of tagline for 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 bears set and then for example let's say we want to update the name so what we can do is the name equals question mark where equals question mark and instead of parameters now which doesn't exist anymore with the letter there we can just pass the name so let's do instead of 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 now the record 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 under body we can do raw jason 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 punk apa updated hopefully speaking hopefully if we press send now there's some reason this isn't working so let's go to here and error pass error you have error in the syntax okay let's have a look oh and this is because where we need to actually put what where is and this would 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 best set name easy 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 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 punk apa 123 and send this you will see beer with the name punk apa 123 has been updated updated and if you go back to the database and refresh you'll see that we get 123 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're getting an error 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 re-send this and as you can see we are getting beer with the name punk apa i've 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'll see we have the tagline and maybe we can do 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 world 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 bear and let's add image we forgot the comma and then image would be an euro to image obviously this will be a real euro 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 a punk api 123 has been updated and if we go to here and post them you will see that this record has been updated and let's actually 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 image one dot jpeg or whatever and Budweiser is the king Budweiser is the king of bears so let's update this and if we go back and resend this we'll see that we're getting Budweiser is the king of bears which is pretty awesome nowhere else in here as you can see they're locked in in in the console is well which is 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 ready and you're watching my channel rally the brand