MySQL Node Express API - Walkthrough

in this video I'm going to show you how to create a nodejs API that connects to a my sequel database so we're gonna do a walk through and we're going to use the chirper database as our database that we're going to be hitting in our API so let's take a look I have a database already set up it's got a couple of chirps in here it's pretty basic but we're just gonna create an API that lets us get all the chirps or one chirp and you should be able to extrapolate from there how to do an insert update and delete so let's go ahead and create this I'm going to do it from scratch so you see the whole process and you can follow along and use this as you're creating the lab for your chirper demo alright so right now I just have a blank folder you see there's nothing in here so we need to NPM an it - why that'll get us our package JSON over here and now we're ready to install everything that we need so it's installed Express and my sequel those are the only things we're gonna need for this as far as what's in production and then we'll install node mine so we can watch our server alright now let's we get that all installed so let's go over here and start to set up our environments so what we want to do is create a server folder and under there we want to have a DB folder and under server we also want to have a routes order and we want a file called server jeaious so there we go we have DB routes on server dot j s now I'm going to create a file in DB called index J s and 1 in routes caught into a GIS if you name them index you can just specify the folder whenever you're referencing them from from each other so you'll see what I mean in a minute alright so let's go ahead and start hooking up our server and just get it working so I'm just going to pull in its press like normal like we've been doing I'm going to create an app and then we're gonna say app I used we need to use JSON here so that our body is converted to JSON and then we just want to listen on a port and we'll put process that environment up pour in here just in case you wanted to deploy it or something like that like to Heroku or 3,000 since locally it's gonna use 3,000 so I just need to call back and this callback we're just gonna console out that we're actually listening so server is running board our 3000 all right so that's the basics of our server we got that running so let's go ahead and run node mine on this and what we'll do here is we'll just say well we'll create a package JSON script so let's go here let's say dev annuals npx node mine server / server JIS and we want to watch the server folder so now we can go over to over here into our terminal our command window and say NPM run dev now we're up and running any changes that we make in here will recompile this and keep us going right now nothing happened so we can't really test it but we know that it's running so let's keep going alright so we need to call a route so I'll just get the router hooked up test it make sure everything's working and then we'll put the database up so we need to pull in Express again so appointments press now we're going to create a router that's just Express that router so these are all things that you should know already but I just want to do it from scratch so you can see it I will do router dot get in or do slash and we can put our request response and next in here and here we're just gonna for now just do a response that JSON and we'll just put like a test thing in here just so that we know that this is working and we need to export this so module about sports equals router all right then we go to our server and we're gonna import that so we're gonna require dot slash routes so you'll notice we don't have to put index in there since that's defaulted and then down here we want to do app dot use and we wanted to be on slash API slash chirps and we'll just put routes in here well called let's call this API router just so it's clear so it's actually a router that's coming out and there we go so now this should work if I save this this should have rican powder you can see it's recompiling over and over then we can go to postman and post in to localhost 3000 such api's slash chirps and we send that we should get test back and we see we do get test it's small but you can see it all right so now what we want to do is hook up our database so instead of returning tests we want to actually make a database call so here's where some new stuff is gonna get implemented so the first thing that we want to do is pull in my sequel will just require that and then we want to create a pool now a pool will allow us to make calls to the pool like queries and it will always create a connection for us to know manage a list of connections so what we can do here is say set of connection limit this just is how many connections will node my sequel hold open to our database I'm going to use 10 something small my password is blah blah peruse my user is true for my database is chirper my host is localhost and in my port is 3306 so this is going to create a connection pool for any queries that we want to mate so I don't actually have to say connect here or anything like that what I want to do now is have our let's create a variable that we're going to that sport our chirps DB what we'll call it char / BB and we'll just make it able to an empty object for now then we'll do Sharper DB dot all anymore this is going to be a function that returns all of the chirps in our database and then we want to do module Det sports equal sharper dB alright so now in here what we're gonna need to do because the way my sequel works it does a call back after you make the query so we're gonna want / return a promise anytime that we make a call to the database so that then whenever we're using this database connection we can treat it like a promise rather than a call back because that'll be easier for us when we use async await which I'll show you in a second all right so we're gonna return a new promise and that promise has a resolve and reject method and now in here is where we can start doing our database stuff so we actually need to set this equal to something so say calm con equals pool or I'll call pool so that's gonna create our pool and now what we can do here is just say cool query and then now select star from chirps so that's going to go get all of our chirps and then it works with a callback so it has an air results in fields well we don't need fields so I'm going to pull that in here and when it comes back we want to check and see is there's an air so if there's an air we want to reject this promise and we're gonna pass in the air I do return here just so that we don't continue on executing any more code in here and then if there is no air we're gonna do return resolve results and there we just made a smaller all right so that's pretty much it for our database we're gonna do this for all different types of calls to all one insert update delete I'm not gonna do all of them in this video we're gonna do all in one so that you can see how to pass a parameter in and then you can go create the other ones all right so if we go to our routes now we want to pull in the DB so the cons DB equals require / DB and then down here we want to actually make it call to the DB so I'm gonna do try catch and use async await so I actually need to put a sink right here and then I'll set that results equal weight DB dot all in their response that JSON results and then I got to catch any errors that happen and I'm gonna do response that sends that it's 500 if there are any errors but I'm also gonna console.log it so that we can see it if they're right an error all right so now got this saved and we should be able to go over to postman's let's see this is happening here and we should be able to call our chirps and there we go we pulled out our our chirps out of the database there's the ID one and ninety two so now let's say that we only wanted to pull one chirp out of the database we would want to be able to put up here you know slash one and get just that one chirp so let's go ahead and do that so the first thing that we need to do is create a method on our database to have this work so do shirt / DB dot one and we need to pass in an ID what is the ID of the chirp that we want to get and then we're gonna do the exact same thing it's this so I'm gonna take this out just paste it down here I'll get a copy code when you can and then up here my say where ID equals question mark now the difference here is when you put a question mark you can pass in an array or in this case since we only have one I can pass in the ID that's passed in I said you had multiple question marks here like you're do an insert or something you would pass an array and I can just make this an array so you can see of all the parameters in the order in which they come in this statement you could just do a template string here you know like this but that could be hacked so if you do this and that's called a sequel injection you can look it up if you want but if we do this this will prevent sequel injections if we do the where we pass in two values and then let the my sequel thing build the query for so essentially so it'll put ticks around it if it's a string it'll put it won't do that if it's a number that kind of thing all right so this is all that we have to do now this should still work but the difference here is that we only want to return one row because we're passing in an ID so we're gonna grab that first row out of here and send it back and then we're going to go over to our routes and we'll create another route so I'm going to copy this one put a : ID here and now we'll call got one and we'll pass in with crest grams dot ID and the rest should be exactly the same and we should be good to go so let's go see so still works for pulling all of them and then if I say slash one it will pull the first one say / - it'll pull the second one so there we go that quickly we've created an API that can talk to our database pull back all of our chirps or just one of them now what you're going to want to do in the lab is to come in here and create reality for insert update and delete so you'll want to do a put a router dot put for an update router dot delete for delete in a router dot post for create so go ahead and create all that and you'll have to go into your database here and create the proper you know methods that you need so you'll want to do all one insert update delete and when you get that going you'll be able to call that from your front-end so go ahead get out there get after it and happy coding

Channel: Covalence

Views: 58,584

Keywords: Covalence, Programming, Coding, Software Development, nodejs, node, express, mysql, api, walkthrough, coding bootcamp, javascript

Id: LVfH5FDOa3o

Length: 14min 35sec (875 seconds)

Published: Tue Sep 25 2018