Build a Rest Api with NodeJS (JavaScript), Express, and PostgreSQL

today we're going to be creating a rest api with node and postgresql before we start we want to have the following three things installed on our machine node.js that's the javascript runtime we'll be using to write our code in postgres uh that's our database we're gonna use postgresql and postman that's what we're gonna use to make our get uh our crud operation request get put post delete from the api that we're writing um so make sure you have those installed uh they're different for every operating system for the most part so i'm not gonna go through the installation um good to know javascript basics right you wanna have a foundation in writing some javascript code before you take this tutorial that will be helpful the api we'll be creating will be a fully functioning crud application so we'll have the ability to create new students read a sp find a specific student and get that information or all the students will be able to update specific parts of a student in the database and will be able to delete students from a database so to get started go ahead and create a new folder called rest api and open it inside of your code editor in my case i'm using the s code first thing we want to do is open our terminal so we can do that through terminal new terminal or we can do control backtick and that will open it up for us we want to initialize a project so to do that we can do npm init dash y hit enter and that's going to create a package.json file for us and now we're going to want to install two things the first is express so to do that we can say npm i dash dash save express and express is our framework we're going to use for our code so let that install and the next thing we're going to install is pg and that's our postgres kind of connector for our database so npm i pg and let that install now that those are installed we're going to make our folder structure and to start it's going to be pretty simple we're going to create a new folder call it source src hit enter inside of source we're going to create a student folder so we'll just call it student so our rest api is going to be about students hit enter and then outside of our source folder we're going to create a new file and call it server.js in our server.js file is where we're going to create our express server so we can say const express equals require express just like that now we can say const app equals express and const port equals three thousand and at the bottom we're gonna say app dot listen and we're going to listen to our port which is 3000 here so when we go to localhost 3000 this is where our stuff will be displayed and we're going to create a callback function here so we go to the port and we say console.log and this is just for our purposes so we know that our app is running app listening on port port just like that save it okay i'm using prettier so when i save you're gonna see that my lines move that's okay and we're gonna run it down here in the terminal so we can say node server.js and hit enter and you're gonna see app listening on port 3000 now you're going to notice if you go to localhost 3000 on your browser it's going to say cannot get slash it's because we didn't give it something to listen to on the slash root so for example we can say app dot get and we want to listen for the slash so this is like our home route localhost 3000 slash and then we're going to have a request and response req comma res so this is our callback function here and when someone goes to this route localhost 3000 slash so when they hit that route in our case we typed it into the browser right and we hit enter we want to send back something in the browser so to do that we can say res so response dot send and we want to send hello world go ahead and save now this automatically won't work we have to restart our um server so to do that control c and then rerun node server.js just like that now if we go to our local host 3000 and refresh we're going to see hello world now i'm also assuming you've installed postman like we said about in the intro so go ahead and open up postman and this is another way that we can ping that localhost 3000. we can reach out and let our server know hey we want to we're hitting that endpoint so when you open postman you're going to go to this plus sign click on it and you're going to see a get request here and in here we can enter where we want to go to so we're going to say local host and as you can see i've already got it there if you don't go and type it out localhost 3000 just like that and if we click send so this is another way of hitting that endpoint that's on our server this this slash here without having to use our browser so this is just another way to do it so we hit send and you're going to see it responds with hello world so this is kind of how we're going to use postman instead of a browser to do some of this work with the api now before we get any further into routing and things like that we want to go ahead and set up our postgresql database so i'm assuming you have postgres installed on your computer so we want to type in psql into our search bar and we want to open up the sql shell now the first thing we need to do is log in and in our case we can most likely just hit enter enter for the database enter for the port enter for the username if you put a password when you installed in my case was test and i hit enter and that logs us into our shell and if you just want to confirm you're connected you can just say backslash con info and hit enter and it'll probably say you're connected to database postgres as user postgres and this is like the super user we could create a role but for for this example we're just going to stay as the super user for our database so to see databases that already exist we can say backslash l and hit enter and you may have a list here of databases that exist you may have one or two um in my case there were a few that were generated and then there's the test one that i made um and to create your own database we can say create in capital letters here database and then in lowercase we're going to say students because we're going to have students we're going to have a students database and hit enter and you should see this create database message that means it was successful now we can use backslash l to see our new database students the next thing we want to do is connect to our database so that we can create a table in there so to do that we can say backslash c and then we can say students and hit enter and it'll say you are now connected to database students as user postgres and instead of staying at the bottom of the screen here there's a lot of info on it so we can clear our screen with backslash exclamation point if you're on windows and then type cls and hit enter if you're not on windows it's probably clear if you're on windows cls hit enter now that we're connected to our database we can create our table for it so to do that we can say create table and we're going to call our table students so in our students database with our students table and we're going to open a curly bracket and hit enter okay and now is where we add our different fields our first field is going to be an id so we can say id and we're going to say serial and what that's going to do is that's going to auto increment our id every time we add a new student so the first one will be one id will be one the second will be two three four right we don't have to add an id uh it will do that for us so id serial primary key comma and then we're gonna hit enter and now we're going to add our other characteristics of our student so a student has an id probably has a name and we're going to say varchar and how long do we want our name to be in our case we're just going to use 255 they can have a very long name if they want comma enter um email same thing email var char 255 comma enter um we're gonna have an agent date of birth yet so we're going to have an age of type int int hit enter and our last is a date of birth dob and that is of type date and then we can close our parentheses add a semicolon and hit enter and that will create our new table our new students table in the database now to view the table we created and make sure it's there we can use backslash dt display table and hit enter and you're going to see that we have our students table in our database now that we have our database and table we're going to create two students and insert them into our table so to do that we can say insert into and we're going to use the students table and what we want to insert is for each student that we're putting in is a name email age and date of birth dob and the reason we're not putting the id in is because postgres will automatically do that for us that was part of the id serial the serial part of it um and we're going to want to enter then our value so i'm going to hit enter and we're going to say values and now is when we're going to put in our name email agent date of birth for each student so we're gonna have joe and the email is we need to use single quotes here joe the email is joe gmail.com joe is 48 and date of birth is 1973 dash so it's going to be year and then month and then day so we'll say 04 04 okay and that's our first user here and notice that the age is not in quotes because it is uh int and it's not expecting the quotes um we can do a comma and we want to put one more uh student in and in our case it'll be anna comma and it'll just be anna gmail.com we'll say she is 23 and date of birth would be 2 000 hyphen 0 1 0 1. close it and add the semicolon we're going to hit enter and you're going to see this insert here and you're going to see 0 2 and now we can check our table to see that these two values are in our table so to check our users we're going to query our database and we're going to search for everything that's inside of our students table so we can say select star means select everything from select all from and we want our students table so select all from students semicolon hit enter and you're going to see our two students that we've inserted into our students table now that we have our database all set up let's go ahead and go back to vs code and we want to create a new file in our main folder here in our rest api folder and we're going to call it database db.js and this is where we're going to have our database connection so to connect to the database we're going to say const pool equals require pg and that's the name of our package we installed dot pool and then underneath we're going to create a pool object from the class so we're going to say const pull equals new pool so there's a pool class and then we're going to need our user host database password and port so user and our user is just postgres one has host we're going to say localhost database and you have to remember what we called our database when we made it in the shell and we called it students right your password is going to be the password that you used when you installed postgres uh in my case it was test and the port we're gonna be on is port five four three two and go ahead and save that and what we want to do is we want to export this pool this is how we're going to be making our queries so down here we're going to say module module module dot exports equals pool and go ahead and save it the next thing we want to do is create our file structure within our student folder so we're going to come over here and create three new files the first one we're going to call queries and this is where we'll store all of our sql queries we're going to use against our database we'll create another file and we're going to call it routes.js this is where we'll store our student routes and we're going to create one more file and we're going to call this one controller.js and this is where we're going to store our business logic that's related to each route so the first thing we want to do here is set up our routes file so make sure you're in routes.js and we're going to say const router equals require express okay so we're using express's router and then down here we're going to say const router equals router all right so now we have our router object and we're going to create just a one root for a router so router dot get gonna use the slash route and you're gonna see that this looks the same as our slash route that's in our server.js over here we're gonna fix that in just a second so for the moment we'll say slash and we're gonna take the request and response and we're gonna say response dot send using api route just so we know that we're using this specific route that's in our student folder right this is our student route and at the bottom here we're going to say module dot exports router so what we're doing is we're creating router object adding routes to it and then we're going to export this router and we're going to import this router over in our server.js file so save this and now we're going to go back to our server.js and we're going to import this router from our student routes so we're going to go back here and at the top underneath express we're going to say const and we'll call it student routes equals require and we need the path to that js file so dot slash source slash student slash routes just like that and now what we want to do underneath our app.get we're going to say app dot use and we're going to create the route that leads to our student routes and i'll show you what that means in just a second so we're gonna say slash api slash v1 version one slash students or just student yep students and here we're gonna say student routes we're going to pass at our student route so app.use this path and we want to use our student routes so we're going to save this and again what this means this is a little bit different what this means is we're going to use this path slash api b1 students and once we go to that path and we hit just the slash student so just the end of it we're going to send this using api route so to test this go ahead and restart your server if you haven't already ctrl c to stop it node.server.js to start it and make sure it says app listening on port 3000 i'm going to go back to our browser and refresh on just the standard localhost and you'll still see we have our hello world because our server.js file when we hit this just normal slash endpoint it sends hello world now if we go to this endpoint and just the initial slash which would just mean this we're gonna get hopefully we're gonna get this using api route so let's see let's minimize this and we're going to go localhost 3000 slash all of that and hit enter oh and we have an error here we don't want two slashes just one and you're going to see it says using api route now we can also do the same thing in postman which is where we're going to end up most of the time so let's go back to postman and we still have our localhost 3000 right if we send we get hello world and if we go to that slash api slash v1 slash students route and hit send we're going to get our using api route message okay so we're back in the code in our routes.js file and instead of sending this using api route string back this message we want to send some students from our database back to the person requesting it so they ping this they hit this end point this api v1 students endpoint that we have in routes here and when we hit it we want to query our database get the json response with our students and send it back to the person that's pinging it so to do that we're going to go to our controller.js file and we want to create a getstudents function that we're going to export from this file so we can say const get students and what we're going to do is we're going to use this get students function over in our routes.js and i'll show you that in just a second but our get students function is going to take in a request and a response and for now it's just going to console.log getting students and what we want to do is we need to export this so we can say module dot exports and we're going to export it as an object because there's going to be multiple of these functions this is just the first one of quite a few so we're going to say get students and right now that's our only thing inside of our object we're exporting so if we save this and we go back to our routes we can then import this controller inside of our routes so we can say const uh controller equals require dot slash controller and instead of putting this callback function here we can put our get students function so we can do this and it's still a callback function but we'll say controller dot remember this is an object we're exporting and this is the method on it so it's going to be controller dot get students which takes in the request and response right but we can actually shorten this we don't need the callback uh express knows so we can just say controller.getstudents and it knows to call this function with the request and response passed as parameters so when we go to this slash route this api slash v1 student route we call this controller.getstudents function which is in our controller.js file so now let's write the code to get the students from the database right so in our controller.js file we're going to want to import our database or our pool from this db.js file so to do that we can say const pool equals require dot dot slash okay dot dot slash we need to go up another folder and we find our db.js file from here then we're going to get rid of console.log and we're going to use this pool to query our database so we can say pool dot query and this query function query method has two parameters the first is our sql statement the statement we want to use to query the database in our case if you remember we use select all from students to get all the students from our database just like that that's what we're going to want to do here so inside of our excuse me inside of our string here query method select all which is the star from students just like that so that's our method and then it has a callback it takes an error and a results and if there's an error so if this there's something wrong with this query and it doesn't work we're just going to throw an error so if error throw error and underneath that if there is not an error and the response is okay okay so res is for response right not to be confused with results so we can say response dot status res dot status if that's 200 and 200 is the okay status so if it was a successful query then we want to send back the json of all our students in the database and that would be results dot rows so if the response status is okay we're gonna send back all our students from the database one more thing before we test that query is we want to go back to server.js and we want to add a middleware here by saying app.use express.json with parentheses and that's just going to allow us to post and get jason from our endpoints now that we have that we can test our first route right so we have it's our students route and it's just the initial student so to test it um we can go to postman and in post man we already have get and we have our route here and also we need to do is click send and you're going to see it's not going to work for me it's because i forgot to restart my server make sure you restart your server so here we go restarting app listening now we go to postman click send and you're going to see it returns both of our students nicely formatted in json that are in our database so we have joe and anna and if we look here joe and anna so we've made our first route successfully we've queried the database successfully in our api um now you can also do that in your browser if you really want to so you can go ahead and click enter and you're going to get your json data back in your browser also by pinging localhost 3000 slash api slash p1 students in the browser um also thing to note that if you're getting json back and it looks like this and it looks kind of ugly um you can install a json formatter extension for your particular browser and it'll format it nicely like this so it's more readable and you noticed we haven't used our queries.js file yet we're going to use that now so we're going to cut out this select all from students and we're going to put that in our queries.js file so constant get students equals and we're going to put the query there then we're going to say module dot exports equals just like we did in the other file we're going to have an object and this is our get students query so save that now we're going to come back to our controller and we're just kind of separating out the queries from the business logic here we could have left it in the controller but as these queries get bigger and bigger and let's suppose this rest api gets very big and you have a very long query we don't want all that in our business logic so we separate it in queries.js so save it go back to the controller we're going to import that file in here so const queries equals and we're going to require and we're going to go to the queries file so dot slash queries and instead of our sql statement here we're going to put the variable name for our sql statement which is queries the object dot and you're going to see it gives get students is autocomplete so this is doing the same exact thing instead of putting the sql statement directly in we're doing queries dot get students same thing so this is our sql statement this is our callback function and we probably want to test to make sure that this works right so to do that we just restart our server go back to postman send it and you're going to see we still get our students back so the next thing we want to be able to do is instead of getting all students we want to get be able to get a specific student by id you're going to notice i'm back in my shell here going to go ahead and clear this out backslash exclamation point cls or clear if you're not in windows and we want to get a student by id so that query would look like this select all from students where id equals and we type in the id we wanna find right in our case it's one or two one was for joe two is for honest so id equals two colon semicolon enter and you're gonna see it only returns ana now if we did id equals one and enter you're going to see it only returns joe now we're going to take that logic and put it into our queries.js file so this is our new query right querying student by id const get student by id equals select all from students where and this is this part's just a little bit different id equals dollar sign one and the dollar sign one is our parameter um which we'll be passing in to this query so dollar sign one is our parameter it's not like one or two that we did in the shell um this this is kind of a variable name kind of like this is a variable name for that we're going to be able to pass a variable into this string so save this and let's go back to our controller.js file where we'll use it now we want to go to our routes file and we need to create a new route for getting a student by id um so we're in our students folder routes.js file we're going to say router dot get and instead of just slash it's going to be slash and then a colon id and that means if we go slash one slash two that's the route we'll take so for example instead of just slash students it'd be slash student slash one or slash two depending on which student you want to find um so that's what the slash colon id is and the colon is only for the route thing right we don't use the colon here we just say slash two we don't say slash colon two here only in the router portion here okay and then we're going to say controller dot get students student by id only one student right so this is going to be our function controller dot get student by id and our route is slash colon id or when we type it in slash id so save that we're now going to go back to our controller and we're going to create that route right so const get students by id takes in a request and response and this is where we're now going to again uh query the database but first we want to get that id out of the url or out of the parameters so as i was saying we can put in slash one or slash two here into postman and we wanna get that id out of that particular url out of the parameters of the request so to do that we can say const id equals and it's going to come in as a string and so we need to parse that string into an integer so we're going to say parseint and we're going to parse the request dot params um params there we go dot id because we want the id parameter that we established in our routes.js file right here so we want to get that id parameter that's part of our route or part of our url and we want to store it in id and now we're going to use that id to query the database so we can say pool dot query and we have we made our query right it's student queries or queries dot get student by id so that's what we want here queries dot get student by id so that's our sql statement and then we need to pass in the id and when we pass in different variables in we need to use uh array brackets here and we're going to say id if there were multiple we'd add more things but for now it's just id and then we have our callback so we have our query query statement our id that we're going to pass into our query statement and then our callback function and our callback function always has an error and results just like that too many there we go and inside of here is same deal if error throw error if error throw error and underneath that if the status is the 200 okay status so response not again not to be confused with results response request response right response dot status of 200 if the response is okay then we want to send back the json results dot rows just like we did up in the first one except this time it's only going to be the one student that we queried for so we can save that make sure we're exporting get student by id down in here and in routes uh that's where we're importing the object right so that's why we need to export it down here also in our queries make sure we're exporting it i think we forgot to do that get student by id comma save it make sure you're saving all your files right and now we're going to restart the server and we're going to test it out in postman so restart go over to postman and we're going to test it out slash one should give us joe and you see we just get joe back now and if we change that one to a two and send it you're gonna notice we just get back on it right so now our get by id route is getting specific students right and if we just do students we get back all of the students now this is the same pattern we're going to follow with the rest of our routes for adding a student for updating a student and for deleting a student okay so let's get started with those let's go back to our routes.js file and we're going to go ahead and add in our post route so router dot and this time we're sending data to our database right so get requests uh we query we hit the send point and it queries the database and sends back information now for our post request we're saying hey we want to give some information to the database to be stored so we're going to say router.post and we're going to use this slash okay and you're going to notice it's the same route as up here that's okay and we're going to create a controller function in just a second and we're going to call it add student so controller.add student will be our function that will create it's not there yet let's go ahead and put this underneath this one so we have our get slash which gets all the students and if we do a post request to slash we can add a student so we can have the same endpoint for get and post so now let's go back to our controller.js file and create that student method right add student method so in here we're going to say add student just to make sure we remember to export it and we're going to say const add student equals request response all right and when we send a request to our post endpoint uh we're sending jason along with that request because it needs to know what we want to store in the database right and we have to have some way to send that and we send it through jason kind of like we get jason back when we do a get request when we send it we're going to add this to the body of our request and it's going to give that information to the endpoint and that's going to be in our request so we can get that information out of our request body and to do that we can use javascript destructuring so we're going to say const and we have name email age and date of birth right so we're going to have name email age dob equals request dot body so the request dot body is an object and the object has these different uh values in it key values and we want to get those out and we're using destructuring here to get all of these out of the object out of the body um so the first thing we want to do if we're adding a student we want to check if that email already exists right because we have a database full of students with emails and if someone's already registered under an email we don't want uh another student being able to be added with the same email so the first thing we want to do before we just say hey let's take all these values and add them to the database we want to make sure that the email is valid and if the email is valid which means it's not already in the database then we can add the student so our first check is actually going to be a query to check if this specific email exists in the database already and we'll write the query for it in just a second but let's go ahead and create the logic for it first so we'll down here we'll say check if email exists i'll just leave that comment just so we remember it's up to you if you want to add the comment or not we can say pool dot query and this will be our sql statement we don't have it yet but we can say queries spelling queries dot check email exists and then we want to send in something to this right we want to pass a variable um and again we don't have this query statement written yet but there'll be a variable uh where email equals in our case email kind of like we did for id up here we want to pass in the email that they're sending in through the post request and make sure that it doesn't already exist and then error and results same as usual here and then inside of here uh if there is an email it's going to return some rows and if there's not there's not going to be it's just going to be like an empty array or undefined or null or something like that um so we can say if results dot rows dot length which means there's an array and there's something in it uh if there's an array of even one right that'll work but results.rows.length of an array an empty array would be 0 which is falsy um and this if statement would not run so if there are if there is an email that already exists there's going to be an array returned with one row and if it already exists we want to then send back something like email already taken so response dot send email already exists just like that okay we're gonna save this and then we want to write the query right we didn't write the query to check if it exists yet so let's head over to our queries file const check email exists equals select and we're going to use an alias for student here so we can access different properties from that alias and i'll show you what we mean here so select s from students s is the alias s so select s from students s the alias where and here's where we get the property of that alias so s dot email equals and again we're going to use dollar one for the variable that we're passing in to this query so we're s dot email equals dollar one make sure we export this or it won't work check email exists just like that make sure you save and the variable we're passing in is right here it's the email that we got from the destructuring of the request body and i'll show you what that body looks like now as we try it out so make sure we restart our server and let's head over to postman um and in postman make sure you change from a get to a post request because now we're giving information to the endpoint to store in the database so post the route is just the slash right the api student slash and we need to send some data so you're probably in params here make sure you go over to body click on raw and this is raw jason that we're sending um and go ahead and type this out if you haven't already we're going to use a person's email that we already know exists because that's the logic we have written so far so we know joe exists uh he's got an email age date of birth and we're sending a post request so this is the data so we're hitting this end point and we're saying hey take this data and try and store it in the database and we already have the logic for if the email is already existing uh then we want to send back hey that email's already taken and in our case we already know this email exists that's why we're sending it so let's give it a go send so if you're like me and you just click send and it didn't work uh make sure you restart your server and you change this text to jason um that will make a difference because we're sending jason right notice the syntax highlighting here before we're sending text we actually want to make sure we're sending json that's going to make a big difference so cancel it restart your server and now we're going to send this information and we should get a response of email already taken there we go so email already exists we know that this email won't work we can try again we're going to get the same thing email already exists so we know that we need to try a different email right so let's go write the logic for what happens if the email is not taken we now have the email validation here so now let's go ahead and say if the email doesn't exist and we can add the student we need to write the logic for that so add student to database and we can say pool dot query notice how this is nested inside of this one right you want to check the email first and if it's not there then we want to add the student to the database so we're going to query student or queries dot and add student and we haven't made this yet but we will write the query for this in just a minute uh and we want to take in all four of these variables up here so name email age dob and then we want to have our callback function just as we always do so comma and this is the same deal error and results same thing if error we're going to throw an error so if error throw error underneath that if there's not an error then we want to add a student to the database so the query is what's doing the adding of the student right so if the uh student is created successfully that's going to give a 201 status code which means it's created the student has been created so we can say res for response which is up here right not results response dot status if the status is 201 which means a student has been created successfully we want to send a message saying student created successfully okay and if we want to just for our purposes we can also log the facts in here that the student has been created if we wanted to so student created i'm going to leave it out for now but if you want to write a log just so you can see it in here you can do that but we do need the response if it's 201 then we want to send the student has been created successfully so let's save this and let's go test it out again ignore all the formatting here when i save prettier just go ahead goes ahead and formats it for me just realized we forgot to write our query for add students so before we can test it we need to write our queries dot add student query so head over to queries.js and we're going to say const add student and before we write it let's go ahead and make sure we put it down here so we don't forget to export it equals and we're going to say instead of select this time we're going to insert so it's going to be look be like this insert into students their students table and uh our values are going to be name email age and date of birth values and this is we're gonna put our values in our case we're passing variables in so it's just dollar one after dollar one dollar two is our second variable dollar three and dollar four just like that and go and close it close the quotes semicolon so insert into students name email age date of birth and the values are four parameters or four arguments that we're passing into this string so save this and we can then go over to our controller and see just to make sure that we're passing in those four arguments so name is that dollar one right that's our first variable dollar two is our second argument which is email dollar three is age and dollar four is date of birth save it uh restart your server if you haven't already let's head over to postman give it a try we already know if we use joe gmail.com that email already exists so we're gonna get this email already exist response and no student will be created now let's try an email that doesn't exist right so let's create a new student frank frank gmail.com let's make him 34. uh and we don't need this time here we'll just do the date like this and we'll say he was born in 83. if you've been keeping track of the ages and the dates that were born they're not exactly right it's just an example right so we have our post request sending our raw jason uh email that does not exist and so we should get down here our student created successfully response so let's give it a go send could not send request right so sometimes this happens and it's good to go back and check and so i did want to leave one of these errors in here as an example of this let's see what's wrong client was passed a null or undefined query perhaps oh wait cannot set headers after they are sent to client okay so go ahead and restart your server it's just because we tried to do 201 so restart and now we're going to send frank so sometimes you got to read up through the error right to make sure and see what's wrong and this is just one example i wanted to leave in of myself getting an error to kind of show you this so we scroll up into the most recent error and it's saying cannot set headers after they are sent so we already sent the response so in our case we just want to restart our server and then we can go and try this again click send and you're going to see it says student created successfully well how can we prove that right well we can go over here and we can get all students so now this body's here but we're not actually sending it because we're making a get request not a post and let's get all students and let's see you can see we have joe anna and we've added frank here we've also added a third joe at some point along the line accidentally um if you've done that like me we'll get rid of him in a second when we do uh delete but for now we have looks like we have two joes an anna and a frank and just to confirm that we can go here into our shell select all from students okay and we see we have everyone here and before we go to uh delete request let's go ahead and just write a delete query in here to delete this extra joe from the table we don't want to joe's in our table so we can say delete from students the students table where and we can just delete them by id so id equals three id equals three if you don't have the extra joe that's okay if you do this is the query we can write to delete it and you'll notice it says delete one so then if we go back and we select all from students i'm just hitting the up arrow here to get my previous command commands i typed into the shell so if you didn't know that now you do just type just click up to find your previous commands you've typed and hit enter and you're going to see that now we just have joe anna and frank and we can go to postman and send it just to confirm that we've deleted the extra joe so we've covered get and post routes now we want to cover the delete route so what happens if we want to delete a student from our database our students table oh we need a route for that so we can say router dot delete and we can use this slash colon id route again and we're going to say controller dot we'll say remove student could also say delete student there it's up to you so you're going to notice we have this delete route it's on the same route as the get but again that's okay as long as it's a different kind of request right so we have a get post on the slash route and when we have a get and delete on this slash id route and that's okay as long as these two have the same route twice as long as these two are different methods so save that and let's head over to our controller and we're going to create this remove student function so let's go ahead and create our remove student function and down here in the exports let's just go ahead and add it remove student and then we're going to add our function here so const remove student again takes in the request response just like the other ones and to remove a student we're going to look for the student id and remove them by id so we've seen something similar up here in get student by id we want to get this id um from the url or from the parameters of the request in our case it'll be we'll pick select a student here like one two or three and or four in our case and that's how we'll know which student to delete so we want to get the id out of the request parameters so we can say const id equals remember the id comes in as a string so we need to parse it parse int uh request dot params dot id um and that's that id variable that's going to be coming in on the route or that we're adding to the route right so make sure we get the we parse it and we store the id here and then we want to check and so if a student isn't in the database like if we send an id that's not in the database then we want to send a response saying student doesn't exist could not delete student they do not exist um and if they id does exist then we want to delete it and say student deleted or student with id id is deleted so the first thing we need to do is check to make sure that the student exists in the database if not we want to send a student not found so to do that we can say pool pool dot query and we're going to need to create the query right can't forget to do that like last time my fault queries dot you're going to say remove student and then we're going to pass in the id that's here that's our parameter that's going to be be in this remove student query uh we pass into it error results and we want to say if there are results that are returned then a student is found if there are results that if the results are empty or returns null or undefined then there is no student found which means we want to send back no student exists so let's create a variable to make that logic a little easier to understand we'll say const no student found equals if there are not rows rows results dot rows dot length so if there are no results if the length of the results is zero then there is no student found and we want to return a response of hey there's no student found could not delete the student so response dot send student does not exist in the database could not remove could not remove and actually we can just go a student does not exist in the database that'll be fine so and we want an if statement around that if no student found if no student found then we send student does not exist in the database make sure that we export this here we did remove student and now we want to write the query for queries.removestudent right and so to do that let's go over to queries.js and we'll say const remove student don't forget to add it down here remove student and we're going to say delete from students the students is the table right where we'll capitalize where id equals dollar one similar to up here we're searching for the id of whatever id they pass into this query statement in our case uh if we type in one two or four and we're going to delete that row from the students table save this in controller we're set to try out an id that does not exist so we can get this response and then we'll write the logic to actually delete the student so make sure you restart your server if you haven't already then we're going to head over to postman and in postman make sure you change the type of request to delete and so when we hit the endpoint with a delete request and make we're gonna make sure we have an id that doesn't already exist in our case 9 because we want 2 and 4 that do exist but we wrote the logic for an id that does not exist so 9 and it should send back something like student does not exist or whatever we wrote over here student does not exist in the database perfect so send it and we get the response so student does not exist in the database which is perfect now we want to write the logic for actually deleting the student if they do exist so a bit of faulty logic on my part we actually want to get student by id first to make sure that the student exists we don't want to use the remove query we want to use the get student by id query that we already have so we check if the student exists by getting them by id if they do not exist still the same response your student does not exist in the database if they do exist we want to delete the student right so underneath the if statement then we use our delete query that we wrote so queries dot remove student so again the first one is we check to make sure the student exists by getting the student by id and if they do exist we want to remove the student with the query that we wrote so again we pass in the id to remove the student error results and we want to say if error throw error if error throw error if the result that comes back from this is 200 which means it's okay the student has been removed so we want to send a response sorry if the response is 200 so response dot status 200 right don't get response and results mixed up response is up here results is here response status is 200. then we want to send back students removed successfully so we check if the student exists by id if they do we remove the student if they do not we send back does not exist in the database so go ahead and save and restart your server and head over to postman right and just to confirm your student ids go ahead and query your in your shell just to make sure mine are one two and five for joe anna and frank now i want to delete one of these right and you want to delete one of the ids that you have so i want to head over to delete we want to delete request to this endpoint and i'm going to delete student again if i try 9 it will say student doesn't exist so let's try 5 because we know frank has the id of 5 right and if we send it we see student removed successfully now how can we prove that right well we can go back to a get request and get all students and we're going to see just one and two are the only students that exist joe and anna frank has been removed and you can confirm that in the shell if we select all students we see we just have joe and anna so the last route we need to write is our put request which means we're going to update a student that already exists so we can go here and say router dot put and again we're going to use that slash colon id because we're going to use the id passed in and the url as a parameter to get the student and update the student and controller dot update student just like that so update controller.updatestudent will be our function and we're hitting the slash id again again as long as these methods are different it's okay to have the same endpoint save it and let's head over to controller.js in our controller.js file we want to create our update student function update student again takes in request response and the first thing we want to do is get the id out of the url or out of the parameters like we did for the previous um functions so to do that cost id equals parseint request.params dot id just like that and this time we're going to say we can only update the name of a student so we're going to also in the update request when someone passes it in uh like we did for post when we did post we passed in some jason along with the post request so we said hey we're going to hit this end point and we're going to send some json in along as the body of information to post to give to the database to store in the database in our case this time we're going to send some json with just the name so we're going to send some json of a name to update a specific user that already exists in the database um so we have the id uh that we want to update and then we want to get the name that they're sending in the body out of the body and into our code so we can say cost again with the destructuring name because it's going to come in as part of the body equals request dot body and that gets the name out of the body that we want to update and now we need to check if the student exists right so when we're updating a student we want to go in there and if the student id exists if the student exists then we want to continue if not we want to return with no users no student found right in the database with this id or something like that so we can say pool dot query and we've already written this query statement right same one is up here queries dot get student by id pass in the id okay this should be looking starting to see the patterns error results and same deal as above const no student found same deal we can actually copy this paste it down here if no student found uh send let's see student does not exist in the database we'll send the same error that works now if the student does exist right we want to then update the student so underneath our if statement um so if they do exist we want to update the student so pull.query we haven't written this query yet but we will soon uh queries dot update student and in this case we're going to pass in the name because we want to update the name of the student and then we're going to say error results just like always and so the name is what gets passed to our query which is then how it updates the name if error throw error just like always and if there's not an error if the status that comes back from the query update is 200 if the response status is 200 so response dot status 200 which means it's okay a student was updated successfully that's end we're going to send the message student updated successfully just like that and then we can save this and let's go ahead and restart our server and as you can see we got an error here so let's check our error requires a callback function okay so we need to make sure that we add this here always good to check your errors if you get them so update student needs to go here and we also need to add update student in our query so let's go ahead and do that we forgot to do that so over in our queries.js file uh we need an update student variable uh and query string so let's add update student here before we forget and then we're going to say const update student equals and we're going to say update just like it sounds right updates the students table we're going to set name equals dollar one right so the variable uh that we pass in so update the students table and we're going to set the name to one and that's gonna be we have to pick the row right so we can't just say set name the one we have to say where id is id equals the second variable we pass in so update the students table and we're going to set the name to the first thing we pass in where the id equals the id we pass in so save that and let's head back to controller because i don't think we passed id in here so here's id we need to pass it in here [Music] two so our first variable's name that goes into our queries query and the second variable is id that goes into our query just to begin to confirm update student set name equals the first one name where id equals the second variable which is id so now save it all make sure you save everything go down restart the server and head over to postman okay so we're over in postman we want to make sure we change our request type to put so that we're updating something and let's test both ideas right so our first one is an id that doesn't exist should return a response of student doesn't exist right so let's try that send it we're going to try and let's let's pick a different name just in case it would work it won't but let's just pick a different name so in our case elsa since we already have anna in the database and we're going to click send and you're going to see student does not exist in the database that's okay uh let's go ahead and our server stopped so restart it head back to postman and now we're gonna pick a student that does already exist in the database and we already know that anna is there and i believe she's the first one but let's make a get just to confirm nope joe's the first one so we want to use the id 2 and replace anna with elsa so to do that student slash 2 is the id and the new name that we want the student with the id of 2 to b is elsa so and make sure we have a put request right or it won't work send it and you're going to see student updated successfully we confirm that two places we can confirm it by either getting just student to right which is which will show elsa as the new name or we could get all students again it'll show elsa or we could go over to the shell and select all from students so her email is still on gmail.com and her name's elsa so we might want to write some additional things in our code to update that but that would be for a later video for you to try on your own so that's it for this video you've successfully written your first rest api using node and postgresql it had all the features of a fully functioning crud application right you can create a student you can get the students you can update students and you can delete a student so you've successfully created your first rest api if you enjoyed this video please consider subscribing thanks for watching and we'll see you soon take care bye

Channel: Beaufort Tek

Views: 3,454

Rating: 5 out of 5

Keywords: rest api, JavaScript, Web Development, Postgres, SQL, backend, Node, NodeJS, web dev, api

Id: DihOP19LQdg

Length: 67min 52sec (4072 seconds)

Published: Sat Mar 27 2021