Node MySQL REST MVC API for Beginners

in this video I want to connect to a MySQL database using node so in the previous video we started to create a MVC crud application so if I do a node on the index file where we got to was we could go to a URL localhost 3000 and we'll get served some data groceries at the moment we have our wraps so the user will go to a particular wrap and then they if they go to slash grocery the index.js file will handle this or we'll see the the rat groceries it will call the grep groceries route and this grocery routes it will call the grocery controller to get all the groceries and inside the grocery controller we that's where we fetch all the data and the data is coming from the model and in this case we've just hard-coded fetch or method on the grocery class a static method now what we like to do in this video is get some real data and more specifically get connect to a MySQL database to retrieve this data so if you haven't already installed MySQL make sure you watch a previous video on how to do that so here is MySQL MySQL workbench so go ahead and just log on to that and I will just enter my password here and we'll take you to this interface here now what we'll do is we'll just create a table and we'll create a schema just by right-clicking in this left pane here and creating a schema and we'll just call this database groceries and we can click apply and I will just execute this SQL query to create the schema groceries so we can just apply that now we have this database here we need to create a table and I'll also call this groceries and this will consist of two fields just the ID which will be our primary key and not null and we'll make it auto increment and we can also have our item here and we'll make that a bar chart a 255 that's not null as well and we'll just go ahead and apply and then this will create this table groceries in the schema groceries with the primary key which is not null or the increments and our item the grocer itself is of our child 255 not null so now that we've created that table there let's just go ahead and take a look at it so let's take a look at our this button here and let's just add a bit of data to it so ID let's say bread and 2 that can be milk and I'll just add these rows here and of course you can add desk your queries in this second in this part here I'll just keep it simple we'll insert those and now if we select all of them you know we get these two back or if we wanted to select one where the ID is one you can just add this SQL query here like that and you just get back that one I'll just make is getting all of our data from the database and this will be the first SQL query we want to connect to our API so how can we do that well let's just go ahead and well firstly let's install node one because every time we've been making a change we have to call this node indexed jeaious command so rather than do that we can just do a NPM install and we'll install as a debt developer dependency because it doesn't relate to our project and okay so we'll just wait for that to install and once it's installed you can see it in the dev dependencies here so we can just change this script here to start so you can run NPM start and we can call the command no 1 index dot J's so this will just continuously watch the changes so if I run an NPM start we can see we can retrieve our data and if I make a change to vine let's change this to 3 and we reload now we can see that we've got changed to three so yeah it's just sort of continually watching us but I want to install another package or just cancel out of that I also want to install my SQL - which is the latest version and I also want to download or install something called body parser and now allow us to pass JSON data and we'll see that in a moment so let's just go ahead and install those and this should be added to our package Jason here yep so we've got body pass Express MySQL so we can just go ahead and close that now and ok so what we'll do is we'll connect to our database and to do that I'm going to create two folders here I'm going to create one chord config and inside that I'll have a new flower chord one thing Jason and this will be a this is the configuration file for our MySQL database and you know the host and username and password so this is a typical way to connect to the database is we need to provide the details and it's the client shouldn't wall the client if these it's safe to type your password in here and the host and all that and the host we will use this localhost because we're just in development mode if you're deploying that you'll just change up to the server that you're on though the user is route in my case and these are just that's just the default username in when you install MySQL now the database that's just what we created just then so I created this database here groceries so I'm going to connect to this groceries database and finally you can put in your password yeah and I'll just type in password yeah and you can go ahead and save the file and I'll close that file and I'll close these other files too and just collapse these folders here so we can see that we've got the config folder with which is just a JSON object and we can create another folder here called database oh well let's call it new tool for our utilities and this utility that we want in particular is going to be our database so we'll just create a JavaScript file called database and we're going to need that MySQL package that we installed so let's just create a constant variable MySQL and we can use the require syntax to require from the node module MySQL - and we're going to need those credentials and the reason I put them in a separate folder is just to add one extra layer of security even though these node files aren't accessible by the client just you know an extra security measure it so I'll just call in that configuration file so I've created a variable config now require in from data directory in that config folder the config jason flower so now what we're doing in a API is we have a server that's constantly serving requests back so we're giving data back from the database or posting data to the database or editing it or deleting it or something along those lines so rather than connect to the MySQL database each time for we you know have a query it's likely the user is going to make multiple ones that are going to go get the page they're going to fetch data post data so it's better to create a pool and a pool is essentially it's just a connection with MySQL and there's next details in there but just for simplicity we will create the connection to the pool with this why SQL create pool method that comes with that package there and we can just go ahead and just type in our data file so we can just say host and that's the host we typed in before so that's local host and you could type it in here and there's nothing really to wrong with that it's just as I mentioned before it's slightly better just to have that separation and extra separation so I'll just call the JSON object like that I defined the variable config and then I'm accessing it through object notation so dot host and the same thing will apply to the user the database and the password so we can just go ahead and just say so I was I might use multiple courses here just copy that and for the confit got that and I'll put a comma there so that's how we create our connection to MySQL and these are the four things you need the host local server the user root the database name whatever you named it in our case groceries and the password ever that is for you and we just need to export this because we're going to need to access this pool when we do our queries so our SQL statements and we can do this by calling this promise method on pool and just exporting that like that so okay so now we want to change our models so recall the models is where our data is or we retrieve our data and we currently have this static fetch or method now we want to still have a static method but we want to interact with the database so let's just create this variable DB to represent the database and we can require that connection pool that we just created in the YouTube folder that database file so we can just go ahead and close that and all this we have to do is change this query here so rather than the hard coded data we can just return so we need to access the database pause we call that DB and then this has a method for execute which will execute a SQL query and in this case we'll just select star so selecting everything from groceries and we've sort of seen this in action before where we're just inserting all the rows from this groceries table here to get these ID one and two with bread and milk respectively it should be noted that we shouldn't allow any like when they make a post request or something like that there's a special way to handle that so they you know people can't do anything with our database or anything like that but we'll see that in the next video when we cover all the crud operations how to escape that so just for now let's the query that we got now we this is the data and recall that we've the data we need to interact with our the route that we requested and to do that we can do that through a control so the MVC pattern is all about connecting to a view through a controller to the data so the models the data and we connect to a view and put that data in the view through a controller in a case of an API the view part is a route so we're accessing a route and we're getting data back and that's independent of the application so we'll need to setup that I will just do the groceries controller for now so okay the fetch all method still there but what we're gonna do is we need to access it asynchronously we need to call this fetch method and this fetch method that executes on the database happens asynchronously so we need to await that sort of response so I might make this function async just by putting the Isanti world at before the request response next parameters in this error function and just like normal requests asynchronous requests you know they it's either going to work or it's not going to work so what we can do is we can do a try-catch block to handle any of you know the errors that occur but what we want to do is we want to we want to await the grocery dot fetch or and because we need to await for that we can assign that to a variable so we're waiting for the SQL query to execute and get all the groceries from the grocery database so let's just say constant or groceries and in the current form that's in we're gonna we're gonna want to set up some jason we're going to want it we want all our data to be in the JSON format so one thing we might want to set up with the body parser is that and we'll do that in just a moment but we want to use some destructuring so that we don't return an array with JSON data in it we just want the JSON data so we can just use this destructuring syntax which will create the variable or groceries so I'll show you a familiar destructuring but what the data were expecting back is of the JSON format and there's an extra set of brackets there so we want to assign the jason variable without the outer one out of brackets so that's why we take it out like that and if that's not clear we will see the result in just a moment so we take the response and we want to say okay in the case that it's all good we want to say okay that's a 200 so we assign it a 200 status code and we also want it in the JSON format so we can just take that data that we've destructured and then have it in JSON format so we can just do that like that now we want to catch any errors that occur so we can catch the errors and we can just say if you know if there's no error status code we can assign one so you know you might get a 404 or a 500 so 500 404 is a page not found 500 source of error so let's just say our servers not working and we can assign a status code of 500 and this is just the status code we actually want a separate controller to handle an error and we've the node sort of we got the request we got the response but we can also call this next function and this next is sort of like a middleware that you know can be used so for example if we have this error here like this and inside this catch block it so I never occurred we've set a status code if one hasn't existed already but we can actually handle more specifically the error case by sending it to a error handler function and this next with this error here this will be get sent to a error controller that we define in the index so what we redefining the controller but we call that routing from the index so in this controllers folder we can create a file called Erik jealous and this is where we want to export a few to different cases let's do two different cases so we'll sport the case that they get a 404 and it's the typical request/response signature and I'll just copy that sort of format here in the case that we get a 500 so Cervera and the server error we will allow okay let's just do the 404 first over the 404 first so let's say they come to the index page well I don't actually come to the next page but let's say they type in a URL and it doesn't reach this slash groceries section so there's two things that could occur in our two cases one is that the page is not found or one is that there's a error in the server so we'll handle the case that the page is not found and we can do this by calling our error controller and we need to bring that in to call that so let's bring that in just below the grocery routes so we can require a controller from the controllers folder from the error is file and if it the endpoint doesn't reach the path slash groceries then we can say okay the page doesn't exist or the rat doesn't exist I'm using the API and the MBC sort of language interchangeably here but there is that distinguish between the route end of you so what we can do is we can just say okay we've got the error controller we can just say we can just use and we don't have to specify the route here just say use controller don't get 404 and I am anticipating will handle the 501 as well we can just say use error controller get 500 so first we want to detective visit the rats not found you know maybe maybe that's not the error and it is a server error so then after we'll handle that case by calling me get 500 error yeah okay so in our get 404 error handling middle where we can say okay we need to say there's some sort of error so we'll assign it to a variable called error and this is a new era and we'll just say found and we can give that a status of say for it for and we can also call the next function to check that it's not a 500 error so we can just say next error and it will automatically go to the next one because you know index J s we're using is 404 there's nothing really else to do except to do fizz 500 and we'll make this 500 taking extra parameter and we'll call this error here so that means this next error can be an input in yeah and this is done so we can check that it's not a server error so and it should be noted you know we might not reach this 404 page because we might reach or route but there might be some sort of other error so that other error could get past is 500 as well so it's not just this one here what's been explicitly called node has no does this next error thing sort of behind the scenes and so we'll just handle the cases of other errors and this error that we've explicitly defined so that's why I here I'm gonna say the status is whatever the error status is and that's not necessarily for or for although in this explanation has been a for a for but if that's not there we can say okay or it's a 500 error and we want our responses in Jason so let's just say the response in Jason is this object here and it's an error object which is an object itself and it's gonna have a message and an error message associated with them again the error message has that detail they're handled for us so we can save that and now we have our error handling so just a brief recap the user makes a request let's say they make a request to groceries and we execute a command to get all the data from the database they for whatever reason it fails we can handle that through our controller by catching any errors and calling this error middleware this error middleware we've created two one for the 404 case which happens before the 500 case and it should be noted that this sort of filters down so if he had two routes that were you know let's say you had you know a request to slash and then another one to slash groceries where slash is just you know wo website calm you know the more specific ones it's better to have them up the top because if it goes into the first one and then it gets handled there somehow then you know they can cause some problems where it's not handled correctly so but in this case we've sent it to another rap section and grocery route so anyway there is get handled if there are any and our get request is almost working but we do need to set up and the ability to have that JSON data go through so recall that earlier we installed the body parser package well we need to create a variable and I'll call it body parser and we can require body parser and we need to create some middleware here well this is middleware that you know so our request sort of goes down the chain of you know these are what we're importing here and then you know a request will hit this route if it doesn't hit 300 goes to the next one so we can intercept that and we on our application that we used by using Express we can have this body parser Jason and this will pass all our requests into the JSON format and you know when we retrieve and all that sort of stuff data and post data and stuff the expected format will be in JSON so we get the requests and if the body the body is expected to be in Jason you know if there if it has a body and then it goes down the line here now one thing we want to do is we want to also create some headers so we can use our typical request response next parameters and we need to so the headers we want to set so there's this issue of cause or cross-origin resource sharing and this occurs when you have an API and it's coming from a different website or it's hosted on a different server than your current website so you've got your front end and your back end and let's say your front end was on you know WWE website 1.com and then your api is on the b www.webociti.com well there's a security issue with that and we're in we're just doing development so the case that we're going to have is we're going to have an API that's on localhost 3000 and we're going to have a front end application like an angular application on localhost 4200 so we don't want that so we need to set a custom header here and I can do that good the response by setting and there's this one called access control allow origin and it's all uppercase first letters separated by us - and we what we're also gonna do here is we're gonna type in a stars that means anyone can access this now obviously if you deployed this you wouldn't want this to be chased you might want one or you know one other sort of website if it's not on the same server and you might just type in you know website - dot-com we could even type in localhost 4200 here but because I haven't created that yet I'm just going to wear anything - and it's just a crud application as well so we're not taking it too seriously here but if you're deploying you might want to look into that a bit more now another header we want to add is this sort of we want to control what requests we can make so there's this one called access control allow methods and these are just you know you get you put your post you delete somewhat add those you could have start here as well vomiting with the crud operations and I don't have to specify this but you may as well if you're already making requests it's probably good for other reasons as well and we also have a header and as a whole bunch of headers I think there's a whole list of the Hmong probably on a node or even just general web sort of development website so another last one is we want to weigh out the headers the type of the headers so these are the our methods header the control or our origin header and then that you all had a header so like the content type so we want content type to be a layout of header so we can say access role well headers and we want content type and we may later to change this type of authorization I'll just Chuck that in there and this this is a custom middleware that we've created so we need to go to the next function or the next any point or whatever that is and we need to just call next on it now we didn't have to do this for this body part because it's built into the third party wall body postures pretty common commonly used and that's sort of built in so then this automatically goes to the next one but because we made this ourself we have this specific next we need a course check if it's in the groceries routes and so on so now that we've set up our index flow I guess we can go ahead and test this out so let's do a NPM start and okay so this was from before now if I do it now I get the data one bread two milk now let's just add another one in here so let's just say three what's something else porn and I add this to our database and finish that now we can see that if we go back to the endpoint and we call this API I get requests we'll get all that data back and now we get this item three corn okay so just to recap what we've done here we I might just close all the files as well we created a connection to a database and we made a pull so we can constantly connect to the database to make a request we actually made their MySQL database with just a list basically of ID and items then what we did after we connected by having our hosts the user the database and the password we we allowed our data to execute a to be retrieved for executing method by connecting to the database pool and doing an SQL query to select all of the data from the groceries and to do that we had to have a certain controller and his control was in a try-catch block and it was a single way because we have to wait for the data to come back and then we gave it a 200 status to say it's all good or we said okay you know it's not all good with a sign out of 500 and we'll call out error control an error controller or controllers are either going to be a 4 or 4 or 500 and then it's going to have the error message associated with it if something goes wrong and what else did we do and so we also set up our index file so you know we have all of our packages that we need so we use Express and body parser in particular you know we created the node 1 script which is just the dev dependency if we don't need it for our server and then obviously the my sequel to package and then we set up our sort of MVC get application to get the groceries by having this middleware to pass JSON data add a few headers to pull you know access control purposes handle the routes and listen floor requests so in the next video what we'll do is we'll implement the post put and delete methods so we'll have a complete MySQL or node crud MVC application and then after that we'll connect it to a front-end like angular and it should be know that you can connect it to any client you want it's independent of the framework there's that separation between the database and the back-end and front-end so because it's not server-side rendered or anything so yeah I hope you enjoy it if you enjoyed please subscribe to my youtube channel and I'll see you in the next one Cheers

Channel: Jon Peppinck

Views: 12,512

Keywords: node, nodejs, mysql, node js mysql crud, nodejs mysql, rest api, mvc, nodejs mysql api, node js mysql tutorial, node mysql, nodejs json api, node.js api mysql, api, node api, crud nodejs mysql, node js mysql project, nodejs mysql login, nodejs nodejs mysql, asp.net mvc mysql, connect node js to mysql, nodejs mysql tutorial, mvc crud using mysql, node js to mysql connection, restful api, mvc in nodejs, nodejs mysql connection, how to connect mysql in node js, node api tutorial

Id: 50yHKJAXw1c

Length: 39min 14sec (2354 seconds)

Published: Wed May 20 2020