REST API with Node.js and PostgreSQL

hello and welcome in this video we're going to build a rest API using node Express and postgressql if you're not familiar with rest apis there's a playlist here on the Channel with explanations and examples only about rest apis I'll leave the link on the description below so postgressql or just postgress is an advanced relational database and it supports both relational queries using SQL and no relational queries using Json it's free and open source you can download postgress on your personal machine for free by visiting their official website which is postgressql dog and click on download option and you can choose for your operational system here but for this video we're going to use a cloud database from a platform called elephant SQL you can visit elephants ql.com for the official platform website and if you click on pricing there's a free plan here that we can use for studying or or even if you're building a small website that doesn't need to store large amounts of data uh we can start with the free plan and change it later to a paid plan if needed uh I'll get back here later to show how to create an account and create our database to run our queries and interact with postgress in a more visual way uh we're going to use a tool called PG admin uh you can visit PG admin official website which is PG admin. org and you can see here a little how the guwi looks like we can use PG admin on the browser or we can download it on our personal Machine by clicking on download option on the top and we can choose for which operation system we need and to connect our node project to postgress we're going to use a module called node Das postgress or just PG which is a lowlevel module that means it's not a RM like SQL eyes or Prisma and not a query Builder as well like an X so with PG we have to write our SQL commands and there isn't any building functionality you can visit node-postgres docomo install and use and we'll be back on this website later to test our API requests we're going to use a tool called Postman which is really easy to use and it's free as well their official website is post man.com and to download you can click on product here on the top and click on download Postman and here it will show the operational system you're using or uh you can choose one of the options down here for Windows or Linux last to write our code we're going to use vs code from Microsoft which is also free and to download you can visit code. visual studio.com click on download here and we can also choose for which operational system we want to download Okay so let's get back to the elephant SQL website to create our datab base and like I said before we're going to use the free plan so to get started we can click on the try now for free option here on the plan box or we can click on login button on the top to sign in with our personal account and if you don't have an account yet you can click on sign up option here on the bottom and we can sign up with GitHub or Google personal accounts or uh you can type your personal email here on the email input click on sign up and you will receive an email with a link that will lead you to a registration page where you can enter your password and other details to log in later since I already created an account I will log in with one of my accounts here so email and the password click on login and this is the lfn SQL dashboard and it will show all instance we have and as you can see here I don't have any instance already so we need to create one by clicking on create new instance okay so we need to enter a name for our instance so I'll put here a main instance in camo uppercase but you can put any name you like uh now we can select our plan here on the drop down and the only option enable is the tiny turtle plan which is the free one uh because as you can see here on the top we did an inform a credit card or a bill in information so we can leave the free option here as it is next uh we can inform some tags that will help us to navigate between projects if we have more than one instance we can leave this field blank and click on select region here on the bottom on the region page uh on the data center dropdown we can select where our instance will be located we can choose Regin for AWS and if we scroll down we can choose for Google and there's also for Asia here uh since I'm in s Paulo Brazil I'll just leave the default option which is AWS s Pao and we can click on review the last step is just to review if the details we entered are correct and if it is we can go ahead and finalize by clicking on create instance on the bottom and awesome so we got a success dialogue here and our new instance is create so let's click on the main instance and we can see the connection details here like the server host the user password and the database name we're going to use these details to connect to our node project and also to PG admin uh let's do that now actually I'll open PG admin here and here on the top left uh we need to create a server as you can see we don't have any yet so to create a server left click on top of server server then register and then server it will show dialogue and we have to enter a name for our new server we can give any name but since it's going to connect to the elephant SQL platform I'll just write here elephant SQL and on the connection tab here on the top uh here we have to enter the connection details and these details we can grab from the elephant SQL website so let's start here with the host name on the elephant SQL Details page it's the server detail so let's copy this URL here and paste on the host name field okay next we need to get the database name and as you can see here it's the same name as the username which is postgress so back on elephant SQL in here they have the same name as well uh the user and the default database so let's copy this weird string here and paste on the maintenance data database field and on the username field as well next we need the password and as you can see the password is hidden but we can click on the I icon here to show the password and we can also change this password by clicking on rotate password and let's copy this password by clicking on the copy icon here and paste on the password field the port number here we can leave 5432 which is the default port uh great uh we can click on Save now and perfect uh as you can see we have a new server called elephant SQL we can expand this here and we can see our database by expanding the databases option and it actually shows more than 5,000 databases here that's because on the lhant SQL free plan we don't get a dedicated server our database is collocated with other users databases on the same server we are able to see the name of the other databases but we won't be able to access them so if we try to access a database here it will show an error dialogue saying we don't have access to this database so we need to find our database here on this list uh let's just check the name of our database which starts with B KT on the free plan we can't change this database and the username only on pay plans okay so let's search here on list bkt okay here it is uh as you can see it has a different icon which means we have access to it so let's expand and here we have access to all options to manipulate our database but every time we open PG admin uh we have to find our database here on this long list fortunately uh we can configure an option to only show our database so let's go back here on the top and click with the left but button on top of the elephant SQL server and select properties now select the advanced Tab and here's a DB uh restriction field but as you can see this field is disabled to enable this field we need to disconnect our server so let me close this dialogue here and left click again on our server and select disconnect from server option it will show a confirmation dialogue we can click on yes okay with the server disconnected we can open the properties again and on the advanced tab as you can see here now the DB restriction field is enabled and we can type our database name here so let me copy the name here on the details page okay and paste here inside the field click on the create option and there it is we can click on Save now we can expand the elephant SQL Server here and it will ask again for the password so let's copy the password Here on the details page and paste right here and let me check this save password option here click on okay and perfect now it's only showing our database here on the list so we don't need to waste time searching for it now we can create the structure of our database and post grass works with schemas uh which contains all our tables views functions constraints we can have multiple schemas in a single database and as you can see postgress creates by default a schema called public we can create a new schema here by clicking on schemas new schema but for this example uh we can use the default public schema and here we can see all our tables St procedures functions uh everything on a visual way which makes our life a lot easier so let's create the tables of our database and we're going to create two tables the product table and the category table and each product will have a a category which is related to the category table and to create a new table we can click on tables create table and on the dialogue we need to give a name for our table and we can click on the columns tab here to add the columns by clicking on the plus icon and we can enter the name of the column and select the type here from the dropdown but let's create our tables manually by writing some SQL code when uh we're working with the SQL database way uh it is important to know SQL comments we'll use a lot in this video not only to create the structure of our database but also to perform queries insert update and delete data okay so let me close this dialogue here and to write SQL code uh we can click on the tools option here on the top and select query tool this will open a new tab where we can write our SQL comments so we'll start by creating the category table since it's going to be used on the product table as a personal choice I like to write the SQL comments in uppercase just so we have an organized and fancy code and the commment to create a new table is create table and the name of the table which will be category and inside parentheses uh we'll Define The Columns or fields that the category table will have so it's a very good practice to add a primary key field field to every table it must be a unique field for each record and it must be required uh we'll name this field as ID and we want this field to be an autoincrement field that means it's going to be generated automatically when a new record is inserted into the table to do so we have to create a new sequence and every time we insert a new record we have to pass the sequence to the ID field but luckily on post grass there's a type called serial which will take care of everything for us create the sequence and assign to the ID field automatically and this field is required so we'll put here not n and since it's the primary key we'll put here primary key okay perfect uh we must have a name for the category so the next field will be the name and it's going to be string so for Strings uh we can use the VAR Shard type and inside parentheses we must Define the length of this string so I'll put here 60 and this is going to be a required field as well so not null and we want this field to be unique that means we don't want to have duplicate category names here so we can put the unique constraint here and perfect uh it's also a good practice to always store the date uh that a record was inserted and last updated as well so I'll type here the created underscore date and we want to store the date and time that a record was inserted for that we can use the time stamp type and it's also required so not null and we can define a default value if we don't pass anything on this field the default value is new so we can Define the current date and time for this field in this case uh we can use the current underscore time stamp which is a function and Returns the current date and time and we'll create the updated underscore date as well uh which will have the same details from the created date field so I'll just copy everything here and paste for the updated date field so when we insert a new record uh both these fields by default will be filled with the current Tim stamp so I think the columns for the category table looks good we can execute this SQL code by clicking on the execute button here on the top and perfect we got a success dialogue here and the message create table here now here on the tables option we can click on refresh and there it is our new table uh we can see the columns here as well and since we Define the ID field as seral uh we can check here on the sequences option that it created a new sequence for the ID field on the category table and if we open the proper properties of this sequence on the definition tab we can see some details here like the current value the value that gets incremented and it starts with one the minimum is one and the max value is this huge integer value here and we can change this data here anytime okay perfect so we need to create our product table so let me delete this code here and type again create table and this will be product okay inside parentheses uh we'll also Define an ID here as serial not n and primary key just like the ID field for the category table uh the product will also have a name of type varar with the lth of 120 and also not null okay uh next we'll have a description field and this field will also be string but we don't want to specify a lth a limit uh we want to leave uh as a free tax field so we can use the text type and this field is not required so we don't need to Define as not n uh next we'll have the price field and for monetary types postgress have a money type here but personally I prefer to use the numeric type which is widely considered ideal for storying money type and inside parenthesis we have two qualifiers the Precision and the scale for the Precision we'll put 10 and for the scale we'll put two and this is also not null next we have a currency field which is going to be the type of the money used in a particular country uh this will be a varar of five also not nle and we'll add a default value here which is US dollar next we have the quantity field like quantity in stock uh this will be an integer field not null and the default value will be zero uh that means we can insert a product but we might not have on stock yet and next let's add an active field here like if we're still selling this product still working with this product and the type will be bullion and not null and the default for this uh will be true next uh we'll have the category field and it will be a foreign key for the category table with the ID field that means uh we'll start the category ID on the product table so here I'll add category underscore ID and since the category ID is a zero as well uh that means it's an integer type so we can put here integer and it's also not n and to refer to the ID on the category table we can put here references to the table name which will be category and inside parentheses will be the field that's going to be stored in this case it's the category ID so ID here this is a short way to define relationships between tables and Post finally uh we'll have the created date as a time stamp St not null and the default will be the current uncore timestamp and the updated date as well with the same type so I'll just copy the code from here and paste on the updated date just like we did for the category table so let's execute this code and perfect we got a create table message and if we refresh the tables option here we can see our new product table and here are the columns we created as well and if we refresh the sequences here um we can see that we have a sequence for the ID field on the product table as well just like for the category so our tables are created and ready to use I'll delete this SQL code here and let's start by inserting some categories and products to our database of course our rest API will handle all the insert update delete but let's insert some data here just so we can start working with the database so before inserting a product uh we must insert some categories because a product must have a category to insert data we can use the insert into and the name of the table which is category now inside parentheses uh we need to Define which fields are we're going to insert so let's check the fields here uh the ID is already autogenerate by post grass because we Define as hero so we don't need to pass this field so we need the name field here so name and the created and updated date uh we already defined a default value so we don't need to pass these fields as well so we just need a name here now we need to specify the values so values and inside parentheses we only have the name field since we Define as a varar I'll add single codes here and this will be the electronics category okay we can execute this code and great uh here's this success dialogue and the message say insert one record here and to check this we can run a select comment here start to return all fields from the table category now we can execute only the select comment here by selecting all this and clicking on execute and perfect here it is our Electronics category with all the fields filled the ID Auto increment the name we specified and created and updated dates with current date and time awesome uh let's create another category so this one will be books and when we created the electronics category uh we just received a message saying a new record was inserted but we can actually return this new record so here after the values uh we can type returning star which means return all fields of the new record after insert so let's select this entire comment and click on execute and perfect now it return the new books category with all the fields ids2 and created an update dates as well so let's select the select from category command here click on execute and great here are the two categories we inserted uh we can use the returning statement here when we're updating or deleting our record as well okay so we have two categories here and now we can insert a product so let me delete this comment here and we'll type again insert and two now it's the product table and inside parentheses let's check the columns here we have for the products table so we don't need to pass the ID because it's Auto increment so I'll put the name and then we have the description uh it's not required but allow that here anyway and then the price the currency will put us dollars as default so we don't need to pass here as well then we have the quantity which is zero by default but all add a different value here next we have the active which is true by default so we can skip this field as well then we have the category _ ID field which we're going to pass one of the IDS we have here from the category table and finally the created in updated date that we also add a default for these fields so no need to pass here and that's it uh we can add the values now and for the name I'll put laptop for description I'll put laptop description okay for the price so let's say say it's 500 we can put z0 or another value for the sand here like this but we can leave just 500 and post grass automatically will format for us let's leave just 500 here without the sense then for the quantity let's put four and for the category ID we can use the electronics category here which the IDE is one and we want to return all fields for the new product so returning start and let's execute this code and perfect here's the new product uh the ID one autogenerated all other details the price as you can see it is formatted with the scent uh here's the currency US Dollars quan4 the active as the default which is true the electronics category which is one and the created and updated dates with current Tim stamp so perfect everything is working now let's insert another product so for the name here uh I'll put node.js book for the description node.js book description and for the price I'll put 3099 cents and we have 10 of these on stock and for the categ or ID let's run again the select star from category to check the categories we have select here and execute okay so we can use the books category ID which is two so here we'll replace one to two and let's select the entire insert code execute and perfect here's the node.js book product now let's run a select common for the products table so here I'll replace the category to product select the code execute and great now we have two products here all details looks good and now we can work with the data we have so let me delete this comments here and we can get started on coding like I said before I'll be using vs code but you can use any code editor you like and I have an empty folder open here on vs code called r tore API _ nodejs _ postgress let's open the terminal here on the top and we'll create our node project by typing npm in it Dy to enter everything as a default and hit enter and great uh our project is created and as you can see we have a new package Json file with details of our project and we can change these details anytime now let's install the modules we'll be using using so back on the terminal I'll clear this out and npm install and the first module will be Express to help us with routing requests and responses and the other module will be PG which is the node Dash postgress hit enter and great uh as you can see here on the package Json file under the dependencies object we have the express and PG modules installed now up here uh we have a main field pointing to the index.js file so this will be our main file that we need to create so here on the root of our project let's create this file so new file index.js okay so in the index.js main file uh I'll start by importing the express module on the top so const Express equals to required Express next we need to initialize Express so I'll create a new con here call app equals to express function finally Express needs to listen on a specific Port so I'll call appc here. listen and the first parameter will be the poe number so I'll put 3,000 here and the second parameter is a call back function so Arrow function and inside will print a message on the console so console.log and inside server started on Port 3,000 okay now back on the terminal let's run this file by typing node index.js hit enter and great we got the message server started on Port 3000 so our server is running and ready uh now we need to Define some routes here for categories and products uh because if we try to call any route here I'll open Postman to request a new route so here on the top I'll click on the plus icon to open a new request and on the URL input here I'll type Local Host column 3000 which is the port we Define and if we click on send we got a status code here of 404 not found and a HTML response saying cannot get slash that means we don't have this route defined yet so let's create this route on our index .js file right after the express initialization I'll type app do G and the first parameter here is the route name so I'll put here lash which means the Home Route and the next parameter is a call back function uh with two other parameters the request which is everything the client send to the back end and the response uh that the backand needs to send back to the client so I'll add here rack and rest Arrow function here and when the client requests for this route uh let's send a response back so I'll type r. send and inside let's type a message here uh hello world okay uh back on the terminal let's stop the server by typing contrl C and we'll type again node index.js okay server start back on Postman so the URL I'll leave as it is and click on send and great now we receive a 200 status code which means okay and also we got the hello world on the response body so the Home Route is working back on vs code note that when we change some file in our project we need to save stop the server and run again node index.js and we can install a module that will take care of restarting the the server for us automatically this module is widely used for development and it's called nodon so let me stop the server here and clear the terminal and I'll type npm install nodon and I'll add a flag here D- save dashd this flag is to inform that nodemon is only used for development so if you check our package Json file now you can see that under the dev dependencies uh we have no mod install that means that when we push this code to production only the dependencies express npg will be installed and not the dev dependencies Dev dependencies is only used for development now to run node modon we have a script object here on the top and let me delete this test script here because we're not going to use it and I'll create a new one called start and this will receive instead of node index.js we'll put nodemon in index.js and to run this script on the terminal I'll type npm and the name of the script which is start hit enter and now nodemon will restart our server every time we save a file uh let's make sure noemon is working so on the index.js file let's change the response message here from Hello World to testing now once we save this file note on the terminal that node modon we restart the server automatically and there it is now back on Postman I'll just click on Sand here and perfect we got 200 status here and the message testing on the response body so nmon is working perfectly and now we can even close the terminal here on the bottom okay so our server is ready and we can start the connection with our database that we created on elephant SQL here on the route uh I'll create a new folder called services so in a large project uh we might have other services like email push notifications uh payment services and we'll store everything on this folder and in here uh we'll also have the database service so I'll create a new file inside the services folder called database. JS this file will handle the database connection and also uh will export functions and properties that we can use in other files okay on the database file uh we'll start by importing the PG module and to run our SQL queries we need to use a connection pool so here on the top I'll add const and we need the pull class inside the PG module so pull with capital P because it's a class equals to require PG and on the end here dot P class okay so this is one one way to import or we can remove this dot pull here on the end and after the cost I'll add an object and inside the object if we press control space it will show all properties or classes from the PG module and we can select the pull class here okay I'll leave like this and next we need to create a new instance of the pull class so con pull with a lower P because it's an instance of the pull class equals to new pull and inside I'll add an object and inside this object we'll need to pass the connection details like host the user the password the port and the database name so all this information we can get it from the elephant SQL Details page or we can replace all this with a connection string property and this connection string uh we can can also get on the elephant SQL Details page and it's right here on the URL field so let's copy this URL by clicking on the copy icon and paste inside the connection string so in here we have the database we're using which is post grass and then we have the user column the password till the ad here and then we have the host slash the database name with the same name as the username here now we can use this pull instance here to run queries something like pull. query and inside we can write the SQL here so let's export this pull instance so module. exports equals to object and inside the pool instance now we can use this connection pool in other files okay so on the index file uh let's change our home route here to send the categories we have on the database as a response to the client so first thing we need to do is import the database file on the top so right below the Express in part I'll put con database equals to require now the index file is on the root and we need to access the services folder and grab the database file so in here output dot slash and now we have access to all files and folders on the same path of the index file so Services slash database great now inside the route uh let me delete this response here and since we're going to send all categories to the client we need to change the name of this route here to the resource name in this case slash category we can use category or categories in plural just make sure that if you're using plural all other routes like products users and so on should should also be in plural to maintain a standard uh I'll leave as categories but it's a personal choice next inside the route uh I'll call the database file do the pool instance we exported do query now inside I'll type select star from category to return all categories and this function here returns a promise so here in the end I'll put dot then and this returns a call back function so let me put an arrow function here and as a parameter it Returns the result of this query here so I'll put here result now we have to send this result as a response to the client so return rest do status and the status code is the default code which is 200 okay but I'll add explicit here because all our responses will have this status code and then we'll send a Json response so dot Json and inside we'll put the result parameter of the call back function now if something wrong happened we need to add here a DOT catch and this also receives a call back function so Arrow function and as a parameter uh we have an nrow object and we need to send this eror to the client so return rest do status and in here we'll use the 500 status code which means internal server error uh which indicates that something went wrong on the back end and we'll send a Jason response as well so do Jason and inside I'll add an object with an error key that receives the error parameter do message perfect so let's save and test this new route on Postman so here here on the URL uh if I try to request this URL by clicking on send we got the 404 not found uh with the cannot get slash message that's because we changed to slash categories now if we click here on send perfect we got the 200 status okay and let me expand this here a little and we got this huge response here showing the commment we use on the query which is select the row count informing how many rows the query return and here inside the row array is actually the information we want to send to the client containing the categories and the rest of the response uh we might use along the way but we don't want to send all this to the client so inside the categories route uh let's just send the rows from the result so here I'll put dot and as you can see we have all properties here from the response so we just need the row property here back on Postman if we click on send now and perfect now we're only sending an array of categories with all Fields inside the object uh it's very important to send a clean response to the client now here on the route instead of using the Dan and catch callbacks uh we can make this route a synchronous and have a more organized code so let me add some space here and the first thing we need to do is add the async word before the Callback function and inside the route we'll add a tri block and a catch block here with an aror object as a parameter and this error here is the same error we have on the catch of the promise so we can copy this entire response here and paste inside the catch block now on the tri block uh we'll run the query and send the result so this result here uh will add as a constant so cost result equals and for the query we need to wait for the result since it's a promise and since this is a asynchronous function we need to add the await word here and then database dop do query and inside the select star from category now that we have the result here I'll just copy the response we have here and and paste inside the tri block and we can get rid of all this code down here great uh we make this function as a synchronous and let's make sure everything is still working here on Postman I'll just click on send and perfect we got the status 200 okay and the array of categories on the response body so everything is working now let's create a route here to return all products we have on the database So Below the categories route I'll call call app.get and the name of this route it's going to be slash products in plural as well and then we have the asynchronous call back function so async the request and response Arrow function and inside the try and catch blocks here with the eror object inside and inside the catch block uh we can copy the response we have from the catch categories route and paste on the products route here and we can actually copy everything from the tri block as well and past inside the products route and on the query here uh we'll change the select category to product and all the response here will be the same returning the result rows and let's test this on Postman so here on the top I'll add a new request and the URL will be Local Host column 3000 and now it's slash product okay we can click on send and perfect we got the 200 status okay and the array containing all products from the database so everything is working but note here on the category ID field is just returning the ID and the client might not know which category has this ID here so the client have to do another request to the categories endpoint to get the details from the category related to the product and we should send the details of the category inside the product object here on the response so let me open PG admin and let's write a query here to return the products details and the category details related to the product so I'll put here select start from product just like we had before execute and all good here now let's change this query a little so first I'll add an alias here for the product table I'll just call P and on the select I'll add P dot start and if we execute this again still working perfect now we'll add a join Clause here uh we're not going to go over the SQL language here but not that the join Clause is used to combine rows from two or more tables based on a related column between them in our case it's the category ID column here so after the product here uh we can type left join or inner join since the category D field is required uh the difference between them is that the left join will bring the products that has the category ID as null or not null and the inner join will only bring the products that the category ID is not new okay so we can use the the inner join here since the category ID on the product table is set as not n and then after the inner join we'll call the category table and add an alist here as well so C on C do ID that means category. ID which is equals to the product p. category uncore ID and now on the select here uh we need to return the details from the category table as well so let me just format the code here a little back on the select I'll add a comma here category C do star to bring all details from the category related to the product as well uh let's execute this code and great uh we got all details from both products we have and if we scroll here to the right uh we can see all details from the category table related to the product so the ID here from the category is the same from the category ID so we can add this query here inside the product route and build a response object to the client informing only the details we want to send or we can change this query and use some Json functionality that post grass offers to us so here on PG admin uh I'll create another query so select from product and Alp and here we'll call all fields except the category ID field so p. ID and the name the description the price the currency quantity the active and the create and the updated dates okay let me organize here a little okay now for the categories we can return an object column containing all details we need from the category table uh the syntax is a little weird but you get used to it so I'll add a comma here and inside parentheses I'll add select and the common to convert an row to object is row underscore 2core Json and inside parentheses again I'll add the name name of this object and we can call category underscore object and then from and inside parenthesis here uh we'll call a select query to return details from the category so select and we'll bring only the ID and the name field here from category where the ID from the category table is equals to the product P category underscore ID and after the parenthesis here for the from we'll add the object name which is category uncore object and we need to add an alias here for this column so I'll just put s category so like I said before the syntax is a little weird but what we're doing here is returning all fields from the product table and the category details related to the product inside ad Json object so let's execute this query to see the result and okay uh we got all details from the product table and know that the category column here is adjacent object with the ID and the name and that's what is great about postr uh we can write queries to return adjacent object or an array inside the row and let's use this query inside the route so I'll copy here and in the products route I'll replace the query here to back top six and paste the complete query inside uh let me just format this code a little okay let's test this on Postman uh I'll just increase here a little and click on send and great not the category object inside the products details so now the client knows which category is this inside the product and we're sending a complete and organized response to the client awesome so we have r to get all products and to get all categories as well but we created these routes inside the index.js file and we still need to create routes to insert update and delete categories and products as well so it's a good practice to separate these routes in different files one for the categories and another one for products and we'll separate the content inside these routes as well so here on the route uh I'll create a new folder called route and we'll create two files inside this folder one it's the category route. JS and another one called Product R.J and like I said before we'll put the content inside the routes in different files as well so here on the route uh I'll create another folder called controllers and inside a new file called category controller.js and product controller.js perfect let's start with the category route. JS file uh we'll create the categories route here inside so in the index.js file we can copy this G categories route here and paste inside the category route. JS file okay and we don't have the app constant here so instead uh we'll call a function called router from inside Express module so here on the top uh I'll create a new con called router which is equals to require Express and here on the end dot router function okay now we need to replace this app here to router now I need to export the router constant so we can use in other files so here on the bottom I'll put module. exort equals to router perfect now we need to grab the logic from inside the route here and pasting functions inside the controller file so here on the category controller we'll create functions like get all categories create category update category so just reading the name of the function we can understand understand what the function actually does and we'll export these functions so we can use inside the routes file okay and instead of calling module do exports and add all functions inside here we can call exports Dot and the name of the function so the first one here will be get all categories and this will be equals to everything that we wrote inside the route so back on the category route we'll grab everything here inside theout outout from the async to the last curly bracket here and we're going to paste in the get all categories function great uh we just need to import the database file here so right here on the top uh I'll add const database equals to and the category controller file is inside the controllers folder so we need to go back one folder and access the ser Services folder and get the database file to do so I'll put require and inside do dot slash and now we have access to one folder behind which is the root uh select Services SL database perfect now inside the category route we need to import the category controller file so below the router import I'll add cost category controller equals to required so here we need to go back to One Directory and access the controllers folder to get the category controller so inside requir I'll put dot dot slash controllers slash category controller okay now inside the route here after the comma uh we can call the category controller. the get all categories function perfect so we separate the route from the logic inside and place the logic right here inside the controller file so the get all categories contains the logic and the get categories route is calling the get all categories perfect and here on the index file we need to tell Express to use the category route file so right here after the express initialization I'll call app. use and inside requir so the index is on the route and we need to access the category route file from inside the routes folder so here I'll add dot slash and select the routes folder slash again category route great let's just make sure these changes are working so on Postman I'll open the get categories request Tab and the URL is correct so we can click on send and perfect here's the response response returning 200 status okay and the categories array on the response body so everything is working now we need to do the same changes to the products route so let me grab the code here and paste inside the product route file and same thing we need to import the router here on top so const router equals to require Express do router function uh here we will place app to router and on the bottom module. export equals to router okay now we need to create a new function inside product controller so here on the top export Dot and this will be get all product which is equals to everything inside the route here so we'll grab the code from the route here from the curly brackets to the async here and paste inside the get all products function and we need to import the database file as well so on the top con database equals to require and inside dot dot slash now we're inside the route select Services slash database okay the get all products function is created now back on the product route file we'll import the product controller on the top con product controller equals to require do do slash controllers product controller and inside the route here we'll call product controller dot the get all products function function and in the index file below the category routing Port we'll call app.use required and inside slash routes slash product route great uh let me just format here a little and we can get rid of the databasing part here on the top okay and let's test these changes on Postman uh I'll open the get product request tab here and click on send perfect we got the 200 okay and the product array on the response body just like before great so our code looks a lot better the index.js file is a lot cleaner and we're separating the route files from the logic files which is the controllers perfect so in the category controller file we have a function to get get all categories and we'll need a function to create a new category So Below the get all categories function we'll create a new function so export and this will be create category which is equals to async the request and response parameters Arrow function and inside the try and the catch block with the eror object and for the catch response uh we'll copy the response up here and paste inside the catch block of the create category function now inside the tri block uh we want to insert a new category and return this new category to the client so cost result equals to await databasequery now for the insert uh it's going to be a little different uh let's check the PG module document ation here on the website and on the left menu I'll click on queries so to now we only use a select query uh calling directly from inside the query function like this but now the client will send data so the back end can insert on the database for security purposes we need to create a parameterized query to avoid SQL injection and on the examples here uh the SQL statement we have to store inside a con an so the insert statement here is inside the text constant and for the values we need to set parameters with a dollar sign and the number of the parameter and we need to create another constant here uh which is an array containing the values from the parameters we set with a dollar sign on the insert statement here and then we can call the query function passing the two constants one containing the SQL statement and the other the values array uh this is one way to do it or uh if we scroll down a little we can configure an object with the text key containing the SQL statement and the values containing the array of values and pass to the query function and as a personal choice I'll use the object option here but we'll create the object directly inside the query function so here inside the query I'll add an object with the text Key and here we'll write the insert statement so insert into the category table and inside parentheses we'll just add the name because the ID the create date and the update date is already filled by default then values and the parameter will be dollar sign one uh one it's because the first parameter here is the name then returning star because if we don't put the returning statement the rows inside the result will be n so we need to add explicity here and the next key will be the values and this will always be an array even if we only have one parameter in this case the name now to get the name here that the client will send uh we can get it from the request parameter here on the top so we must accept Json request from the client and for that we need to add as small configuration to express so on the index.js file below the express initialization we'll call app.use and inside express. Json function now we're telling Express to accept Json requests from the client so back on the category controller we'll use the request here inside the values array so request. body which is the object the client will send name which is the category name field so here we have a parameterized query now we need to send a response to the client with a new category inside the result here so return r. status and the status here will'll use 2011 which means created every time we create a new resource on the back end it's a good practice to send the 2011 status code and also Json and inside the result constant Rose just like the other route now on the category route file we need to create a new route that we'll call the create category function So Below the get all categories route I'll call router Dot and since this route will create a new resource a new category we can use the post method here okay and the name of this route will also be slash categories so we have the get categories and the post which is the create categories route both with the same name but with different methods comma and the category controller file dot create category function awesome uh let's test this new route so back on Postman uh I'll open a new request here and the URL will be the same Local Host and I'll just select the categories route here from the history and we'll change the method here from get to post now we need to send a Json request body and for that we can click on the body tab here and there are a few ways to send data on the request we'll choose the raw option here and on the drop down here on the end we can choose which body type we're going to send so we'll choose the default which is Json now here on the body let me expand here we need to create the object we want to send and inside we'll have a key keys should be inside double codes and here we're going to send the name field column and since it's a string we'll also add double codes here and this category will be accessories Okay now click consent and perfect we got the 2001 created status code and the new accessories category we just created but note that we just need the object inside the aray because here will always be just one object which is the created one so on the category controller file inside the create category response since the result. rows is an array we can only get the first and only object by adding an array here and the zero index we which means the first object on the array so if we test this again let's add another name here for the category so I'll put clothing and click on send uh note here that it used to be an array but now it will just return an object with the name we entered and the other details so it's easy for the client to use this response instead of an array so let's check the get all categories endpoint I'll open the get category request tab so we had two categories before but now uh let me expand here a little we have two new categories that we just created so our create category endpoint is working and inserting on the database now we set the name field as required by adding a not new operator so if we try to add a category without the name here oh let me remove the name and click on send we got a 500 internal server error and an error message that might not look so clear to the client that's because it fell here on the catch block so we definitely need to handle some validation and send a better response to the client with the correct status code so inside the create category function before the insert query uh I'll add a n statement and inside we'll check if the name is not on the request body so request body do name and on the beginning I'll add an exclamation sign so if the name is not there on the request body uh we'll return a response so return rest. status and here we'll use the 422 which means unprocessable entity which indicates that the client is sent invalid data to the back end and a Json response so Json with an error message saying name is required perfect uh let's test on Postman so here I'll just click on send and great uh we got a 422 unprocessable entity status code and the error message saying the name is required so now the client know what it sent wrong and we also add a unique constraint to the name field so in PG admin uh if we click on the category table and open the properties option and here on the constraints tab we can see all constraints like the primary key for example and we can check the unique values here on the unique Tab and as you can see the name must be unique uh let me close this dialogue and back on Postman uh let's try to insert a category that already exists so let me check here we'll try to insert the Electronics again so here on the request body object output name and electronics click on send and we got again the 500 status code and another message that the client might not understand so we need to check on the database if the category the client is trying to insert or exists so in PG admin we can run a select query using the exists operator uh the exists operator is used to test for the existence of any record in a subquery and returns true if the subquery returns one or more records or false otherwise so let me clear the query editor and the syntax is Select exists now inside parentheses uh we write the query that might return true or false depending if it return any results so select start from category where the name is equals to what the client is sending in this case the electronics category okay so this is the select exist syntax let's execute and as you can see it return it true because Electronics already exist uh let's test with a category name that doesn't exist so food for example execute and as expected it returned false so let's use this query to validate if a category already exists back on the code so first we need to check if the name is on the request body if it is I'll add the validation below here by creating a new cost with name exists result you can add any name here as long as it makes sense with the query result and equals to await database. pull do query and this will be a parameterized query so object and the text will be select exist parentheses select star from category where name is equals to and we'll add a parameter here just like for the insert query so dollar sign one now for the values and inside the array we'll have the name on the request body just like on the insert uh actually I'll just copy the values from here and paste up here now we need to check the result here and it Returns the exist column okay so I'll add an if statement here and exist result dot rolls and we're going to get the first and only object here Dot exists which is the column so if this is true uh we need to send an error message to the client so return r. status and for the status code here uh we use the 409 which means conflict because the category name is already there and also. Json and the eror message uh will add back ticks here and add category dollar sign curly bracket requestbody do name already exists okay and since we added return here the rest of the code won't be executed so back on Postman I'll leave the electronics here and click on send and great we got the status 409 conflict and the error message saying the category Electronics already exists awesome now let's try with a category name that doesn't exist so I'll put here a new category for example and click on send and great we got 2011 and the new category details here on the body so all validations are working uh let's just check on PG admin so I'll type select star from category select this query and execute and okay we have all five categories here and and note that the ID from four here it went to seven that's because we tried to insert a category without the name first in a category that already exists before we handled the validations so the sequence value was incremented but we can change manually if we need it I just suggest leaving like this okay the create category route is done and now uh we need a create product route so on the product controller file We'll add a new function here so export Dot and this will be create product equals to async the request and response parameters Arrow function the try and the catch block with the arror object and we'll copy the ero response from up here and paste down here great now on the tri block we want to return the new product created to the client so cost result equals to and we're going to create a parameterized query just like we did on the creates category function so back on the product controller we're going to put here a wait database. pull do query We'll add an object here and the text property we'll put insert into product and inside parentheses the columns and we don't need the ID so we can add the name here the description price uh the currency uh we add a default value but we'll add a validation later to check if the currency is on the request body so we'll add the currency here as well and the same for the quantity and for the active and last the category ID the created and updated dates we don't need to add here and for the values array so in here we'll add some validations directly inside the array so the name is set as not n that means it's required so we can put here the requestbody do name now the description is optional so we're going to check if this field is inside the request body if not we'll add as null so here uh we'll add a request. body. description and question marks so that means we're checking if the description is there on the request body if it is We'll add request. body. description again so if the description is on the request body and then we're going to get its content else which is represented by column sign n okay so for every field that is not required we're going to add a validation like this inside the values already so the price is required that means we can add the content here directly so requestbody do price now the currency is required but if the client doesn't send on the request body we can add the default value which is US dollar so here uh we'll add requestbody do currency question mark and if it's there then requestbody currency else we're going to add the default value which is US dollar and for the quantity same thing so request. body. quantity question mark requestbody do quantity else the default value which is zero and for the active field uh we're going to do the same as well so requestbody do active question mark requestbody do active else the default which is true but since the active field is a bullan field uh we might have to change this validation here because if the client sends the active is false the compiler will read this condition as false and it will fall in the else statement here inserting the active as true so it will insert wrong information on the database so let's change this validation here to something like active in single or double codes and we're going to use the in operator and then request do body question mark if the key active exists on the request body then we're going to grab its content so request. body. active else the default value which is true so now we're checking if the field active exists on the request body if it is uh we'll get its content otherwise true which is the default value uh we could use this validation for the other fields as well but I just wanted to leave another option here okay now the category ID is required so we'll add directly here so request. body. category uncore ID okay great so after all this validation here uh we want to send the new product to the client so back on the insert statement we need to to add the values uh let me format here a little by adding some back ticks and put this in another line now the values and inside parentheses the parameters so we have let me see here uh seven parameters so dollar sign one dollar sign two dollar sign three four five six and seven okay and on another line the return learning start so as you can see the create product function is a little more complex and now we need to return the result here containing the created product so here on the bottom return rest. status and we'll add the 2011 created Json and inside the result do rows and just the first and only object inside the rows array just just like on the create category response okay uh now we need to add this function to a new route so on the product route file below the get route uh we'll add router. poost because we creating a resource and the name Will be/ products and inside we'll call the product controller do the create product function okay let's test this new route on Postman I'll open a new request tab here on the top and the URL is going to be the Local Host SL product and Method we'll change from get to post now we'll open the body tab select raw and the Json type here now the object and inside We'll add the name field and here I'll put uh JavaScript t-shirt and next is the description and we'll put Javascript t-shirt description now it's the price and it will be 2050 okay uh next uh it's going to be the currency but since we had the validation here uh if we don't pass this field it's going to add the default which is US dollars so we can jump to the quantity column which also have a default value in case we don't pass this field but we'll add here on the object so quantity and we'll have 10 of these on stock and next is the active and we have the validation checking if the active is on the request body if not uh we'll insert as true so we're not going to add it here instead uh we'll add the category underscore ID and let me open the get all categories request here to check which category we can use click on send and we can use the clothing category which the ID is for so back on the create product request we'll add four here okay all good we can click on send and great we got the 2011 created status code and the new product object here on the response body with the ID autogenerated and the currency here uh with the default value and everything looks good now let's insert another product so the name I'll add keyboard and the description I'll delete it since is optional the price uh I'll add 40 the quantity since we add a validation to insert zero as default uh I'll also delete it from here and instead I'll add the active as false to test the validation as well and for the category let's see which category we can use use and let's use the accessories which is three so here we'll replace to three okay all good we can click on send perfect we got the 2011 status code and the new product object with the ID here the description as null because we didn't pass on the body the currency the quantity as zero and the active as false and the rest looks correct as well so the create product post route is working but we still need to add some validation for the required Fields so for example if I remove the name here from the request body and click on send uh we're getting the 500 code and the wrong message here and the same for the price field so put the name back and delete the price click on send uh we got the same error uh just with the price here on the message and the same for the category ID so delete from here and and click on send and same message so we need to send a better response to the client with the correct status code and a friendly error message so here on the create product function uh before we execute the insert query uh we'll let an if statement so if and inside the exclamation sign requestbody do name so if the name is not there or empty uh we're going to return response do status with the 422 unprocessable entity code and adjacent response with the error message saying name is required and we can copy this entire if statement here and paste right after and we'll change here to price and in the message as well so price is required uh next we'll paste once more more and this time it's for the category underscore ID and also let's change here on the message so category ID is required okay so let's test these validations on Postman uh I'll start by removing the name here and click on send okay we got the 422 status code and the name is required error message okay let's put the name back and remove the price field Okay click on send again and perfect we got the prices required and the 422 status code okay let's add the price back and remove the category ID field click on send and great we got the category ID is required and 422 status code as well great now let's add the category ID back and let's create a new product so I'll change the name name here to adapter and I'll leave the rest as it is click on send and awesome we got the 2011 status code and the new product object here okay so everything is working but what if we send a category ID that doesn't exist so let me check on the get categories request tab here and click on send and the last one is id7 so here on the create product request I'll add 10 so this category ID doesn't exist and I'll leave the rest as it is and click on send and we got the 500 status and not a so friendly error message here informing a violation on a foreign key constraint so we definitely need to send a better message here and the correct status code as well so here on the create product function uh this if statement is checking if the category ID is on the request body if not it shows the required message but if it is uh we can add an L statement here and inside we can use the select exit query that we use here inside the create category function to validate if the category name already exists okay so in the else statement uh we'll add cost exists result equals to await databasequery and inside an object with the text key and inside select exist and inside the parentheses uh we'll select if there is any category with the category ID the client is sending so select start from category where the ID equals to dollar sign one which is the parameter and next we'll add the values array and inside we'll copy the category ID from the request up here and paste inside the array great now if the exist returns false that means the category doesn't exist and we need to send a response to the client so below here uh we'll add an if statement and inside uh we'll call the exist result do rows and we'll get the first and only row return it so zero do exist column and we need to check if this is false so exclamation sign here on the beginning and now we'll send a response so return response. status and here uh we could use the 404 not found status code but since it's a validation error I prefer to use the 4 to2 which we're using for validations and also adjacent response with the error message saying category ID not found okay so we're checking if the category is on the request body if it is or we're checking if it exists on the categories table so let's test this new validation on Postman and here we already add a category that doesn't exist so we can click on send and perfect uh we got the 422 un processable entity status code and the error message saying category ID not found so now the client knows what is wrong with the request and corrected okay let's add a new product here so I'll change the name to computer and the price to 50099 and the active we can use the default value so I'll replace with the quantity and and I'll put two here and the category uh we can use the electronics which the IDE is one okay we can click on send great 2011 created status code and the computer object here on the response body note that the ID here is already on 10 because we tried to add some products without the validations we added so the sequence got incremented uh okay so we have routes to get and insert categories and also to get and insert products uh now we need to create routes to update categories and products as well so let's start by the categories uh here on the category controller uh let me collapse the functions we already have and we'll create a new one so export and this will be the update category and it's equals to async request test and response aror function the try and the catch block with the eror object and let me copy an error response here and paste inside the catch here uh we're very familiar with this process here now inside the try uh we'll run the update query and return the update category to the client so let's see how the update query works here on PG admin and let me clear the query editor here and let's update one of the categories we have so let's update the new category here so on the editor I'll add update and the name of the table so category followed by the set operator and here we want to set the columns that we want to update the ID column is not recommended to change because because it's the primary key so here we'll add the name equals 2 and we'll just put updated category so we're updating the name and we also need to update the updated date column to the current timestamp so here we'll add a comma updated underscore date equals to the current underscore timestamp perfect but if we execute this query it will update all categories the name and the updated date columns so we need to add in we condition here uh let me format this query here okay so after the St columns uh we'll add where and we're going to update by the ID primary column so ID equals to 7 and we're going to return this updated category so returning start okay uh let's execute this query and great uh we got the updated category details with the new name and the updated date uh as you can see it's different from the created date the minute here now let's just make sure by running this select start from category select this query and execute and perfect here's the updated category with the fields we updated so we can use this query inside the route since we want to return the updated category object we'll create a new con result equals to await database do pull do query and this will be a parameterized query so object with the Tex key and we'll add back ticks here inside update category and I'll add another line here and set and we can only update the name field so name equals to dollar sign one since it's the first parameter and the updated underscore date equals to the current underscore Tim St stamp and I'll add another line here where ID and dollar sign again now it's the second parameter and returning start now we'll add the values array here and the first parameter is the name which we'll get it from the requestbody dotname and the second parameter is the ID of the category and this parameter will get it from the URL so on the category route file uh let's create the update category here below the create category route so router Dot and we're going to use the put method here uh when we want to update a resource it's a good practice to use the put method or the patch method and the route name will also be slash categories but now we're going to add a parameter here on the URL uh which will expect the ID of the category uh to add a parameter uh we'll add a lash here column and the name of of the parameter which we can put as ID so this ID expects the category ID and we can get it from the request parameter and next uh we'll call the category controller dot the update category function okay so back on the category controller uh we need to pass the category here inside the values aray and to get it uh we can call the request parms doid so it's the same name we added here on the P route okay now we need to return this update category details to the client so return response status and here we can use the default 200 status okay. Json and inside the result cost containing the updated category do rows and we'll just the first and only category returned so array zero great uh let's test this on Postman and I'll open a new request tab here we're going to put the local host and get the categories endpoint here on the history and change the method here from get to put next we need to pass the category ID which we want to update here on the URL so let's choose a category here let me click on send and let's update the number seven here which we already updated on PG admin and as you can see the updated date has the time as 19 and 56 so back on the put request tab I'll add slash and the id7 here as a parameter now we need to send a request body so body tab here and choose the raw option and the Json type and we'll add an object with the name field and we can put here something like uh updated category once again just for testing and click on send great so we got back 200 okay status code and the updated category details note here on the updated date that the time is 202 okay and on the get categories request tab uh here we have the old data before updating and if we click on Sand Scroll down you can see that the category was updated awesome now we need to handle the validations here because if I delete the name here and click on send we got that not so friendly eror and if we try to update a category that doesn't exist so here I'll change it to 100 for example and click on send we got the 200 okay status code but an empty response body here so nothing was updated because this IDE here doesn't exist so the client might get confused and we need to send a better response message and the 404 not found status code now let me put the id7 here again and if we try to change the name to a category that already exists so electronics for example and click on send again we got a confused message and the wrong status code so let's add these three validations uh here on the update category function before we execute the update statement uh first we'll check if the name is not on the request body so if exclamation sign request body do name so if the name is not there return response. status the 422 status code and AD Jason response with an eror message saying name is required now if the name is there uh we need to check if a category with the same name already exists so else here and we already have this code here on the create category function right here so let me copy this entire code and paste inside the else statement okay and the last validation is to check if the category that the client wants to update exists so here on the result constant we can check if the result that row count property if it doesn't return any rows that matches the update statement and then the row count will be zero so we can add this validation right after we try to execute the update statement so let me replace this result here with an if statement and we're going to check if the result. row count is equals to zero and if it is we're going to return a response to the client so return response do status and here uh we can use the 404 not found status code since the resource wasn't found and AD Json response with an error message saying category not found okay uh let's test these validations so let's start by removing the name here on the object and click on send okay uh 422 status code and the error message saying name is required now let's add the name back and let's try to update with a category that already exists so I'll leave electronics here and click on send perfect uh we got the 409 conflict status code and the error message saying category Electronics or exists perfect now let's try to update a category ID that doesn't exist so I'll put a 100 here and let me change the name here otherwise we're going to get the same response from here so I'll change it to test and click on send and perfect we got the four 44 not found status code and the error message saying category not found great all validations are working uh let's just make sure the update is still working so I'll put seven back here and let's leave the name as test click on send and great uh 200 okay status code and the updated category details the name and the updated date as well awesome uh let's just make sure here on the get category request so the old updated date was 202 click on send great uh here's the updated category details with the name and the updated date as 208 so the update category endpoint is working with all validations as well now we need to add an update product endpoint so on the product controller uh let's create a new function uh let me just collapse the other function here so export do update product equals to async the request and response parameters the tri block and the catch with the error object and we'll copy our error response from one of the routes up here and paste inside the catch block and on the tri blog we're basically going to do the same thing we did on the update category so cost result equals to 08 database dop do query but now the products table have more columns to update and we can't do the validations we did here on the create product function inside the values array because we might update the product with wrong data for example if the client doesn't pass one of these fields here it will be filled with the default value and that might lead to unwanted changes so on the update product function the client must pass all details on the body even the details that doesn't need to be updated except of course for the ID the created date and the updated date we're going to update with the current Tim stamp so we'll add the tax key here back Tex and update product set and we'll add the name equals to dollar sign one the description equals to dollar sign two the price dollar sign three uh the currency as well dollar sign 4 the quantity dollar sign five the active dollar sign six the category underscore ID dollar sign 7 and the updated underscore date equals to current underscore timestamp and let me hide this left panel here so we can see the entire update statement okay and now the work condition ID equals to dollar sign 8 uh which will be the last parameter okay and also the returning start and for the values array uh we're going to pass everything from the request body so let me break this in different lines here so request body. name and request body description request body. price request est. body. currency or request body. quantity request. body. active and the request body. category ID and now we need the product ID which is the last parameter here and we'll get it from the request parameter so request. params do ID and now uh we can check the result row count so if the updated product was found just like we did here on the update category function returning the 404 status code okay back on the product controller so if result. row count equals to zero then return response do status 404 not found and the Json response with the error message saying product not found and if the product was found then return response. status 200 okay and the Json response with the result cost do rose array zero to get the only updated product uh let me just check what's wrong here oh the error key here inside the Json response okay uh now we need to create the route so on the product route file uh we'll add router dop put and the name will be slash product slash and the ID parameter here and and we'll call the product controller dot the update product function okay let's test this new route on Postman I will open a new request tab here and the URL will be Local Host SL product URL and we'll change the method here from get to put and for the request body let's copy an entire object from the get all product request tab here uh let me click on send here okay let's choose one of these products to update um let's update some details here for the keyboard so let me copy the enti object and on the new put request tab let's choose the body tab row option and the Json type and we'll paste the object here let me just format this a little so the ID we won't send it so we can remove it and actually let's add here on the URL parameter so slash4 and the description we can change from new to keyboard description and the price we can leave as it is uh the currency as well the quantity will update from 0 to 20 and the active will update to true and we can remove the created and updated dates as well now for the category uh we'll change here to category underscore ID and replace the object here with category ID which is three so we're only updating the description the quantity and the active fields and we can click on send uh and we got an error here uh something wrong near the wear condition let's check here inside the product controller so here on the update statement oh there's a a comma here before the wear condition save the file and back on Postman now we can click on send again and perfect we got the 200 okay status and the product update details like the description the quanity s20 and the active as true awesome uh let's check on the ghetto products request tab so the keyboard product as you can see with the old details here the updated date uh with the time as 1943 and if we click on send scroll down and it seems that it's being ordered by the updated row and here it is uh as you can see the product was updated and the updated date here with the time as 2022 so perfect the update product endpoint is working but as usual uh we need to add the validations now uh back on the products put request tab so for example uh if we send the name field as an empty string like this and click on send we got the 200 okay status and the product was updated with the name as empty but we defined the name field as not null and null is different from an empty string so we need to handle this validation on the update product function and also we need to handle validations for other fields that we Define as not n like the price for example if we remove it here from the body and click on send we get that standard error message that we also need to change so back on the update product function uh before we execute the update query here since the client needs to send all these fields in the request body we can write only one validation for all Fields checking if all these fields are on the request body so I'll put an if statement here and inside we're going to do the same validation we use in other functions so exclamation sign request body. name and now we're going to add an or condition here like this and we're going to check for the next field so exclamation sign again request body dot description or condition again exclamation request body. price and for the next field and this is the currency next the quantity field next will be the active and the last one category ID okay so if any of these fields are not on the request body uh we'll send a validation error response to the client we could write a better validation here checking for each individual fields for example the description here is an optional field just for learning proposes we'll leave it like this and now we return a response to the client so return response stat St us and we'll use the 422 processable entity here and AD Json response with an error message and here we could write something more generical like all fields are required great let's test this validation on Postman so here on the request body object let me put back the price field so price and it was 40 and the name here I'll leave as empty and we can click on send great we got the 422 status code and the error message saying all fields are required even if we remove the name field here from the object and click on send we still got the same error now let me put the name back and if we remove any field here from the object we're still going to get the all fields are required her message so for example let's remove the description here and click on send there it is now let's put the description back and remove the price here click on send there it is the same error message let me put the price back and let's make sure the update statement is still working so I'll change the name here to keyboard and I'll just put inside parentheses here the brand word for example and and click on send great we got the correct status code and the updated product object here on the response awesome now let's try to update a product with an ID that doesn't exist so I'll put a 100 here and click on send perfect we got the 404 not found status and the error message same product not found great so all validations are working but we still need to add one more validation which is if the client tries to update the category ID field with a category ID that doesn't exist and we already have this validation up here on the create product function uh right here so let's copy this entire code here and paste after the fields validation here great so everything looks correct here and we can test the category validation on Postman so here on the category ID field I'll add a category ID that doesn't exist so 100 here and we need to put on the parameter a product ID that exists so I'll put four back here and we can click on send and great we got the 422 code and the error message saying category ID not found great so the validation is working let's just make sure by adding a category ID that exists so so I'll put one here which is the electronics and click on send and great here it is the updated category ID field on the response body so the update product endpoint is working perfectly great so we have routes to create read and update products and categories as well and to complete our recruit application we need routs to thead categories and products so let's start with the categories uh I'll open the category controller here let me collapse the functions we already have and let's create a new function so export and this will be the delete category equals to async the request and response parameters the arrow function the tri block and the catch block with the error object and as usual let me copy an error response from one of the functions here and paste inside the catch block okay now inside the tri block we don't need to send the deleted category back to the client but just like for the other routes we need to check if the category the client is trying to delete exist on the database by checking the row count property so I'll create a constant here with the result equals to await database. pull do query and this will be a parameterized query so object with the text key and the delete statement is really simple we don't even need to see it on PG admin it's delete from and the table name so category now if we execute this SQL command here all categories will be deleted from the database so just like the update statement we need a condition so where ID equals to a parameter so dollar sign one and that's it for the delete statement now we need the values key array and inside we need to pass the category ID parameter here just like we did on the updates category function we need to grab the ID from the request parameters so let me collapse here and inside the values aray request pams do ID okay now if the delete statement doesn't return any rows that means the row count is zero and the category doesn't exist so let's add the validation here if result row count equals to zero the same validation we added in previous functions then return response step status 404 not found and the Json response with an eror message saying category not found and if the category was founded and deleted then return response status and here we can use the 204 code which means no content the 204 is used when the server successfully processes the request but there's no content to return to the client this is typically used in delete requests and since we're not going to send a Json response we can put here dot send perfect now we need to create the delete route so let's open the category route file and let's create below the put route so router Dot and we're going to use the delete method here and the name of the route is also going to be slash categories and the ID parameter just like on the put route and then we're going to call the category controller Dot and the delete category function great let's test this new out on Postman so I'll open a new request here and the URL is going to be the local host and let's get the categories here from the history and change the method from get to delete okay now let's choose a category here so we can test the delete request let me click on send and let's choose the test here with id7 okay so back on the delete category request tab we're going to put here slash S and click on send great we got the 204 no content status code which says that the server successfully proceed the request but is not returning any content and as you can see here there's nothing on the request body let's check on the categories request so the category test is here now if we click on send scroll down and the test category was deleted from the database awesome now what if we try to delete a category that is being used on a product table so let me open the get product tab here click on send and and if we delete the electronics category here that is being used on a product so let's try to delete the electronics category back on the delete request tab so replace the seven here to one and click on send we got the 500 status code in an neor message saying that there is a validation on a foreign key constraint so we need to handle some validation here and send a better response to the client so back on the delete Del category function so before we execute the delete statement what we can do is run a select to count how many products are using the category ID that the client is trying to delete so let's open PG admin and let's run a query using the count function so the count function Returns the number of Records returned by a select query the syntax is simple we have to run a select count and we can pass this start as a parameter from the table name and we can add in wear condition as well and instead of passing a star as a parameter we can pass a specific column name from the table and in this case the new values will not be counted so let's see how the count function works let me clear the query editor here and we're going to run select and then the count function and inside we're going to put star from the product table and where the category underscore ID field is equals to one for example which is the electronics category now let's execute this and as you can see it returned the number of products that are used in the electronics category in this case three Let's test with another category ID so I'll put two here which is the book category and execute and only one product is using the books category so let's execute this query before we execute the delete category statement so I'll create a new constant here call count result and equals to await database pull do query and it's going to be a parameterized query so object and the text key will be select count star from product where the category ID equals to dollar sign one and the values key the array and inside we're going to pass the ID on the request parameters so request ps. ID now we need to check the count column inside the constant we just created so I'll add an if statement here and count result dot rows and we're going to get the first and only row returned so zero dot the count column and we're going to check if it's greater than zero if it is we need to return a response to the client so return response status and here we can use the 409 conflict status because we're trying to delete something that is already being used so we have a conflict and also a Json response with an eror message and we can send to the client how many products are used in the category so I'll put back tick here and we can send something like category is being used in dollar sign and curly bracket and we can copy the count result from here and paste inside the message and then we're going to put products like this so the message will be something like category is being used in three products for example okay let's test this validation so back on Postman we can leave the electronics ID here that's some products are using and click on send and great we got the 409 conflict status code and the error message saying category is being used in three products let's try with a different category ID so I'll put two here and click on send great the 409 status code and the error message saying category is being used in one product so great the validation is working now let's try to delete a category that doesn't exist so I'll put 100 here on the parameter and click on send and awesome we got the 404 not found status code and the error message say category not found so this validation is working as well and the delete category endpoint is complete now we need to create an endpoint to delete the products as well so let's open the product controller let me collapse this functions here and let's create a new function so exports and this will be the delete product and it's equals to async the request and response parameters Arrow function the tri block and the catch block with the error object let's copy the error response from one of the functions here and paste in the catcher now inside the tri block we're going to do the same as we did on the delete category but we don't need to handle any validations before the delete statement so so I'll create the result constant equals to await database do pull do query and the object inside the text key and inside delete from product where ID equals to dollar sign one and the values array and inside the request pam. ID and we're going to check if the row count is zero so if result do row count equals to zero and then return response status the 404 status code and the Jon response with the eror message saying product not found we are already very familiar with this validation and then if the product was deleted return response status and we're also going to use the 204 no content status code do send great now we need to create the route so let's open the product route file and we'll create a new route so router. delete and the name will also be slash product and the parameter ID we're going to call the product controller dot the delete product function okay let's test this new route I'll open a new request here and the URL will be Local Host and select the product URL here from the history and we're going to change the method from get to delete and let's choose a product here to delete let me click on send and let's delete let me see here the adapter product which the ID is8 so back on the delete product request let's put slash eight here on the parameter and click on send perfect we got the 204 no content status code and the response body is empty so the product was deleted let's just make sure by making a request to the ghetto product endpoint so the adapter product is here but if we click on send scroll down and as you can see the adapter was deleted perfect so everything is working now let's try to delete a product ID that doesn't exist so I'll put 100 here on the parameter and click on send and great we got the 404 not found status code and the error message saying product not found so the delete product end point is also complete great so all crude routes are completed for the categories and the products as well but the client might need an endpoint to get details for a specific product so let's say here on the ghetto products request the client might need details just for the laptop product here so we need to create an endpoint to R return only an object containing details of a specific product we could create this endpoint for the categories as well to return details of a specific category but since the categories has so little details uh we're just going to create it for the product so back on the product controller file let me collapse the delete function here and we're going to create a new function so export and this will be get product and the client is going to pass the product ID on the request parameter so we can put here get product by ID and it's equals to async the request and response parameters Arrow function the try and the catch blocks here with the error object and let's copy an error response from one of the functions here and paste inside the catch block here now in the tri block this will be very simple so cost result equals to a weight database do pull do query and here we can actually copy the same select statement from the get products function up here where we are returning all product details and the category details as as well so let's copy this entire query here and here uh we'll add an object with the text key back ticks and we're going to paste the query here inside okay let me just format here a little but since we're going to return details from only one product so we need to put a wear condition here so product P do ID equals to a parameter so dollar sign one and now we need to pass the values array and inside just like on the delete product function we need to pass the ID on the request parameter so inside here request params do ID okay now we need to check the row count from the result result constant so if result dot row count equals to zero and if it is let's return response status 404 not found and adjacent response with an error message saying product not found and if the product was found let's return response status and here we'll use the 200 okay status code and adjon response with the result do row and there will be only one product return it so zero here perfect now we need to create a new route for this function so on the product route file since this new route is a G Route uh let's create below the get all products route we already have just for the code to be more organized so router. getet and the name will also be slash product but this time we expect an ID parameter here then product controller Dot and get product by ID function okay let's test this new route on Postman let me open a new request here and the URL will be Local Host and select the product here from the history and let's choose one of the products here to return the details so let's get the details from the laptop product here which the ID is one so on the get product by ID request let's add one here on the parameter and click on send and great let me expand here and we got the 200 okay status code and the product object on the response body this is very useful for the client uh let's test with another product so here on the parameter I'll put two and click on send and perfect we got the 200 status code and the notebook object on the response body and now let's test with a product ID that doesn't exist so here on the parameter I'll add 100 and click on send and we got the 404 not found status code and the her message saying product not found so the get product by ID end point is complete and like I said before we're not going to create an endpoint to get a category by ID but feel free to create one for learning purposes and I'm pretty sure by this point it will be easy for you guys to create this endpoint now when we try to delete a category that a product was using so for example let me put one here on the parameter which is the electronic and click on send we got the error message saying category is being used in three products but the client might want to see which products are those that are used in the category Electronics so we need to create an endpoint that return all products from a specific category so on the product controller file let's create a new function to return all products by a category ID so export and this will be get products by category ID and equals to async request and response parameters Arrow function the tri block and the catch with the error object let's copy an error response from a function here uh actually uh we can copy the entire code from the get product by ID function because we only need to change the wear condition here instead of product ID we need to pass a wear condition to get the category ID filled and on the values array instead of just ID we might want to change the parameter to refer to the category ID so let's copy this entire code inside the function let me collapse here and paste inside the new function great now all we need to do is change the wear condition here so instead of wear product ID we'll change to product category undor ID and we'll leave the parameter here and on the values instead of request param ID let's change the ID here to category ID in camo case now we can remove this validation checking the row count here because if there aren't any products using the category we should return an empty array uh we'll see this in Postman and on the Json response we can remove the array getting the first object from the row option to send the complete array but we still need to add a validation to check if the category ID exists and we already have this code here on the create product function using the exist operator so let's copy this code from here and paste before we execute the select query so we're checking if the category exists uh we just need to change the values array here to get the category ID from the request parameter just like we did down here and I'll copy the code from here and replace the values array from the exist result here and for the response we can change the status code from instead of 422 unprocessable entity we can change to 44 not found because it's not a validation on the request body it's on the URL parameter so it makes more sense to send the 404 and the rest looks good now let's create this new route on the product route file and we can create below the get product by ID route so router doget and the name will be slash products slash category and the category ID parameter so we're getting the products from a specific category the product controller dot the get products by category ID function awesome Let's test this new route on Postman so I'll open a new request here and the URL will be Local Host and let's select the products URL from the history here and slash category and let's use the electronics category here which is one one the same category we tried to delete here which return that three products are using okay we can click on send and perfect we got the 200 okay status code and let me expand here a little and we got the laptop which has the electronics categories the computer with the electronics category and the keyboard also with the electronics category so it returned the three products from the error message awesome let's try with a different category ID so I'll put two here and click on send and it returned only one product using this category now let's add a category ID that's not being used in any products so I'll put here category ID 3 for example and click on send and great it return the 200 okay status code and an empt array which is the correct response because the category ID exists exists but there aren't any products using it now let's put a category ID that doesn't exist so 100 here on the parameter and click on send and great it returned the 404 not found and the error message saying category ID not found right uh let's just remove this ID here from the message which makes more sense without it so here on the get products by category ID function let's remove the ID from the error message here and back on Postman we can click on send directly and the error message now is category not found which I think it makes more sense but this is just a personal choice great so our rest API is complete and we have endpoints to read create update and delete categories and products as well and our code is very clean and organized separating the route from the logic inside just one last thing we might want to change is that here on the routes file all routes start with a slash product and the same here for the categories so it's better to create this route path on the index.js file where we are importing the routes so here before the require uh we can add an extra parameter which is the path so we can put here the slash categories and then the required category route file and we can do the same for the product so slash products and now here on the category route file we can remove all the slash categories on the beginning of the route name okay now the same for the products so let's open the product route file and remove all slash products from the beginning of the route name as well great now let's just make sure everything is working so back on Postman uh I'll just call the get all categories route and click on send and great now the get all products request click on send and perfect so everything is still working so let's say on the future uh we might want to change to slash something else here so we only need to change in one place perfect so we completed our rest API we learned how to create crud applications for categories and products using the rest principles uh we learn how to create a database on the cloud using elephant SQL we learn about SQL commands and how to connect a postgress database to a node.js project using the node postgress module if you like to learn more about SQL please leave a comment below saying you want a complete SQL video hope this video was useful and if you like it please subscribe to the channel and click on the Thumbs Up Button thank you so much for watching and I'll see you on the next video

Channel: manfra․io

Views: 7,399

Keywords: postgres, nodejs, rest api, node, sql, backend, web dev, javascript, web development, node with express, express, left join, inner join, relationships, row_to_json, crud, endpoints, coding tutorial, node with postgres, node-postgres, pg, npm, postman

Id: IZRHj7givq4

Length: 144min 5sec (8645 seconds)

Published: Wed Feb 14 2024