Step by Step Create Node.js REST API with SQL Server Database

hi welcome to another video by geek9 in this step by step video we are going to learn how to create node.js rest api with sql server database i am jay kumar our api architecture looks like this there's a sql server database and on top of that we are going to create a node.js rest api to create that we would use express and then we are going to test apis in postman so to follow this tutorial you need to have node.js postman vs code and of course sql server installed on your development machine postman for the testing of api vs code we are going to use that for development ide you can use any ide of your choice we are going to create api on top of a database inside sql server but all these code and all these understandings should work perfectly fine with database inside microsoft sql server cloud offering sql azure as well so let us get it started very first let us start with setting up the development environment first let us check whether node.js is installed on the machine or not and you can check that on our windows machine by running a command node v and here you see that i am getting some version number returned it means node.js is installed on this machine if it is not installed on your machine you can go and download it from node.js.org in download and you can find this url in the description of this video so here uh we have you can download node.js from here also you need to download postman as we are going to use that for the testing of api and you can download it from here choose operating system and download it and if you do not have visual studio code downloaded you can download it from code.visualstudio.com after successful download of node.js and installation after choosing a correct operating system version here you should also able to get a version number when you run this command node hyphen v next i'm going to use sql server here which is locally installed on my machine and we would perform operations using the api on this orders table but to work with sql server inside node.js you need to make sure that it is configured for tcp and to do that press ctrl r and in control r on your your machine or sorry windows r on your machine uh run the command co mgmt dot msc once you run that command you should open this window computer management window for you in this computer management or window click on services and applications in services and applications you can see secret server configuration manager click on that and then you can see here sql server network configuration click on that you see here you have protocols for sql express and you need to make sure that this tcp ip for sql express is enabled click here go to the properties and here you can enable it and disable it also you need to go to this ip addresses and in this ip address section go and note down that ip version 10 tcp port we need the cpu this tcp port to create the configuration or connection string from node.js to connect to this sql server uh which is installed on this machine now the configuration is done and let us start with the development so here i have one folder created node.js api and right now this folder does not contain anything so very fast we need to create package.json file where we would enter information about dependencies and to do that run a command npn init and y and here it has created a package.json file for us next open this folder in visual studio code by running command code dot or you can launch visual studio code separately and then open window and then navigate to this folder where you are creating your project here i ran code dot and this got created this got open in visual studio code and here you see that i have package.json file next thing what we need to do we are going to put the dependencies so here my application has dependencies on body parser i need that to pass request body then co rs it is required to enable api for cross origin resource sharing then express to create the api on node.js and msql we are going to use it this library to connect to sql server perform operations on sql server from node.js so these four libraries are body parser to pass request body and response body co rs to enable api fro for cross origin resource sharing means if we deploy this api on uh domainabc.com a client which is on xyg.com should able to access this api express we are going to use that for to create rest uh to the rest api node.js and ms sql is library which would help us to connect to sql server from node.js next i need some dev dependencies so here i'm going to put it and we are using nodemon uh that is for the development and as soon as we save a file in node.js project it will recompile the application and show us the output here in the scripts we are going to add a command for that and here we would say start nodement api.js so the entry point of our app is going to be api.js so this is what all we need configuration in package.json now let us go ahead and install these dependencies and to do that we will say npn install now what npm install does it goes inside this folder and search for package.json file and then it would install all these libraries which are mentioned here from node package manager so let's go here and say npm install and press enter after successful execution of npm installed here would see a message like this and inside the project folder you would find a node underscore models folder got added and this folder contains all the dependencies which we just installed now let us go and very first create a file which would kind of connect contain the connection string or the configuration required to connect to sql server and for that what i'm going to do i'll say that add a file called db config.js and in this file we will add a object called config and this object would contain information such that user this is your sql server user information the username and password you need to connect to instance of sql server so here that user for me is true for you it could be different then password for my system password is full for you it could be different then the server name here either you can give localhost or you can give one to seven point zero point zero point one and if it is sql azure then you need to give the whole server name of sql azure uh instance then here you need to pass database and you can pass the database name i am going to work with database uh here products so i would pass this product database information there p r o d c d s products okay and then you need to conf your certain options and those options are trusted connection i would say true then enable port i would say true and then the instance name and here i need to pass instance name and the instance name is it is trusted connection and enable earth abort is true and the instance name is you can find instance name here for me sql express for you it could be uh something different which you can find by clicking here database engine and what you see this is your connection string okay sorry sequence of instance name so here i'm going to say instance name as sql express all right and then last but not least here we need to pass port on which the secret server is running and on my message is running on 55892 and how you find on which port on your system it is running in the description you can find a sql server command which you can run and check it and also you need to make sure that tcp port is enabled which you just saw a while ago in this video all right so this is this is a kind of connection string object which contains user name password server database name options and the port number then we would go and export it so we'll say module dot exports and we export it to config so here this dbconfig.js file contains the configuration required from node.js to connect to sql server database next let us create a class which would represent the orders table we are going to perform operation on this rs table and let us create a class representing this and for that here add our new file called order.js and then in this file we are creating a class called order and that has a constructor and it contains all the properties corresponding to columns of orders id title quantity message and city and you see that i am matching the types as well here id title quantity message city and this dot id id is dot title title and then we are exporting that this class from this particular file once this is done now let us add one more file called db operations.js and in this file we would write code to perform operations and so to do that very first what we need in this file we need to load the configuration which will be created in dbconfig.js so we'll say dv where config and here we can say require and db configure all right next uh we would need object from ms sql library which we installed here and this library would help us to connect node.js from node.js to sql server so here we say cons sql and let's say require ms sql so here we have loaded a connection string and then we loaded image sql and then what we do we will write a function to fetch all the orders so let's call that at function gate orders let's say try and here we would say lit pull equal to sql dot connection sql.connect i'm sorry sql dot connector and here you need to pass the configuration which we created and that is config okay and this sql.connect is an asynchronous operation so for that you need to wait and since it is the await functionality only works with async function so here we can say okay and then you can say here catch so let pull away sql connect this should connect once it successfully got connected you can say late products equal to pull dot request and here run a query and in the query we can say selective start from orders and this is also going to be asynchronous operation so we can put await here and then from here we can say return product dot record set because that returns a record sheet so here we say record sets if there is any error in the application so here we say catch error and let's say console.log error that's it only this code is required to fetch data from a table in sql server a few things you need to keep in mind that we are using ms sql and all the operations on ms sql such that create connecting and making a request query are asynchronous and for that we are using es6 async and await functionality and here after it's got connected then we are using this pool to create a query where we are passing a normal stars sql query and once data is returned we are returning it from here now let us see that how we can use it in api so here let us go ahead and add a file api.js and in this file start with importing where db is equal to require and here we would uh require db operations and also we may need where order and that can that you that is inside the model order one last thing uh we forget to do here in db operations is we can in the db operations we can come here and say that module dot exports and here uh let's say get orders is get orders okay and then in api.js here let's say that db dot db operations dot get orders and that returns a promise so then and here you can see result let's just print result here to see that whether we are successfully able to get the result from database or not and then we'll create api on top of that okay so here now let's go and say npm start and here you see that we are able to get the data from uh the sql server it means successfully be able to connect to the database which is which we are doing here and then execute this query select a star from orders and then able to uh then returning from here and then here inside that api.js we are just printing that value so now let us see that how we can create an api on top of this and so to do that we need to uh import certain modules and and here we can import it so here i am saying that okay import these models for me express we are going to use that to create api then body parser to to uh parse uh request and response body then coris to enable cross-origin resource sharing then creating an object of express and then creating a express router object once that is done here we would say that uh use for this app use body parser use chorus and our base uri is going to be slash api so if you deploy this api on abc.com then all the api would be available on abc.com api so these uh lines line number five to line number 14 you need that to create the api here just we are importing required modules and then setting this app for to use body parser to use cors and then setting the base api and last what we need to do here we need to open the port to run the api so we are saying that either take the port from environment or a090 and then listen that and then order api is running at port all right so this is what configuration you need to do to create the api and if you look at it here it's pretty simple just requiring it configuring the app and then listening on a particular port then you can see here now you can create a route like app dot so very first let us create a middleware and here you say router dot use okay and inside that request response and next and this is uh going to be your middleware so if you need to perform any authentication authorization and logging you should write that inside this uh use method of the router so this this particular method would be always called this particular route would be always called before any other route getting executed so here i am saying that and router dot use let's close it here okay and then here you can perform all the operations as of now i'm just printing console.log middleware and then don't forget to call next such that you call the next method here okay so once the middleware is created let's go and create router dot now we'll create a route which will return all the orders so for that here let's say routers dot route and when user clicks of user navigate to api slash orders this particular right route would be navigated and in this you would say api slash orders and this route would be called on http gate meter so gate and then here you say request response all right and in this we can we need to write the code and here we already have this code to get the data so we'll say db operations dot get orders dot den and you can do the console.log or here we would say response dot json and then here we need to pass result and the first element of the result because lastly you saw that it is it was the data was in an array all right so here we have created a route which uh would uh api route which would return us all the orders okay so save it and here it is running order api is running at 8090 and since it is a http gate operation we can uh test it in the browser but let us test it in uh in postman directly and let's go to the postman and here create a new request and then perform and then say get and this is going to be from localhost 8090 so we are taking this url here and saying localhost8090 api slash orders and it does not have any authorization or anything in the headers uh it it has all the default headers uh here uh added to that let's go and say send so when we send that here you see that in the postman you are getting all the output from the table as you see that here that uh one books uh should be gift wrapped and all let us go and edit that so here order and say top 200 rows and here instead of books we are saying books fiction okay so here uh we updated the database and right now when we come back to postman and execute it again you see that here you are getting books friction so here we successfully created an api which is sql server and bringing data for us in node.js using express and all so once let me summarize what i have done i configured the connection string then in the db operation i am using ms sql and using the image sql only you need to call the connect method and then to execute a query request dot query it will return you and everything is promise based that's why i'm using async and of it and then exporting it from here then in the api.js imported all the required libraries then configure that express app to use body parser cors and then the base url then here the opening the port configuring the port and listening it on a particular port once that is done we configure two routes one is the middleware this would be always called before any route getting called and why this is because if you want to perform any any authentication or anything you should put it that code inside this and then api slash orders would call this function and here we are just making call to db operations dot get orders and the data which is coming from gate orders sending it as a json to the response and output is in front of you here next let us see that how we can of fetch order of a particular id for that only we need to change the way query is being executed and and the type of the query so for that what i'm going to do i'm adding a new function here gate order which expects order id and similarly like up like get orders here we are making a connection to sql server using sql.connect and then using that a pool to create a request in the request however before executing the query in line number 20 we are adding an input and here the input parameter is the order id which is being passed and then in the query select star from orders where id is equal to this input parameter and then from here returning product dot record sets as we were doing here so only thing you need to do differently is to set the input parameter and update the query and right here we can say get order as gate order exported it here and in the api we can go and add a new route let's say that route is router dot and we can copy paste this one and we can send routers dot route but it is expecting a order id as well and that we can pass like this get request response db operation dot get order and here we need to pass that id so we can pass that id like this request dot params dot id so this you can access these request parameters inside request params dot and then the uh this variable name then result and then we are saying response.json whatever result is returned from there all right so here it is uh only thing you need to keep in mind that how would you read this query parameter inside request and this is the syntax request dot params and then the name which you are providing it here all right now let's save it and run it again so order api is running at 8090 go to postman and here we can see that here return us a particular id for that we would say order and let's say one and send and here you see that you are getting that particular order with the id if you say six we're not getting anything because we may not have an order with id six here okay uh let's pass order with id 12 and it should return as nut bolt and here you say 12 and we are able to get that a particular id uh particular order of that id so here again once uh what we have done we added a function called gate order and in that just adding an input and then executing the query and in the api.js we are we have modified the path for the for this particular route with our id query parameter and then passing that in gate order function and then returning the result next let us see that how we can add our order how we can create a record in the database and for that i'm going to write a function here called add order which expects a order and then uh it makes a connection to sql server using sql.connect and here we are creating a request pull dot request insert product and here we are passing these parameters input for id title quantity message city and when we pass that instead of query we are calling here execute because to insert an order to create a record we are using a stored procedure called insert orders instead of directly running command here insert into table name if you want to run the command insert into table name then instead of execute you need to call query method but since we are executing a stored procedure called insert orders and that stored procedure takes these parameters as input so here we are saying execute and if you want to check this insert orders stored procedure you can find it right here and this stored procedure here deep insert or restore procedure and here you can see the parameters id title quantity message and ct and these are int in where care which we are mapping it here okay so this this particular uh by ex calling the execute method and then passing insert order which is a stored procedure name we should able to insert a record in that in the table now in the api we need to create a route to execute that and what i would do here i would say routers dot route orders but this route would be called on http post operation and http post operation corresponds to create a record and here we would say post request response and http post operation would have a ht request body so here would say db operations dot and before that we forget to export that so add order and add order and here let's say db operations dot add order and that takes an order as a parameter so what i'm going to do here we would say lead order is equal to the order which returns which is coming in request so we will say request dot body and then we would copy that you destructure everything which is coming there the request body in this order and then here we would say order dv operation order then and if it is successful result then say response dot status we need to send a status that order is created and for that 201 and with that we would add the json data with just one result which is being returned and if you see here what it does it returns you the inserted record and we are just returning inserted record with status 201 here and in line number 40 we are destructuring the request body object in order here that's it and let's go ahead and run it so here our application is running we will go to postman in the postman i already have this post here api orders go to the body it has id 11 headphone i'm going to change and change it to let's say id 99 and then the title geek nine seven no js class let's say quantity 60 and here we would say json data and then let's say city is mumbai all right and also one thing you see that in the headers uh if you look at the headers i added content type to application json so here content type application json body is set to id 99 because we do not have any 99 id order here and let's go ahead and here in the is the operations post and then the url api orders let's execute this and here you see that you are getting status 201 created here and also it returns you the created record let's go back to database and in the database say order and select and here you see that 99 id with geek97 node.js class is being added and we can confirm that in postman as well in the postman let's say 99 and here you're getting 99 and if you don't pass id it will return you all the data and here you have 99 so what i have done to add product i'm using a stored procedure it's pretty simple which we are executing using execute method and in that we are passing the input parameters which is expected by the stored procedure which you can find it here the parameters for insert order and it returns and then what we do uh we are doing here we are returning the inserted record the way a stored procedure is written that it is returning it will return you uh the record which is being inserted and then in the api we are reading the request body destructuring that to the order and then just calling add order setting the status and setting the result and here you have the api to insert a record to fetch all the orders to fetch a particular order and and and those things are performing operations on sql server from a node.js we are using express we are using here express and image sql to perform these operations so i hope you find uh this step by step learning video useful you can very much now go and perform update and delete for that you may write a stored procedure and call them from here or you may write the queries directly and use the query method here so so again i hope that this video gives you a good start to go and write node.js api performing operations on sql server database only thing that if you want to perform that on sql as you just go to db config and change it here and if it is on sql azure you need to add one more con option here that encryption is equal to true so once again thank you so much for watching this video i hope you find it useful i'll see you in the next video

Channel: Geek 97

Views: 159,286

Keywords: nodejs, rest, sqlserver, express, api, geek97

Id: Uvy_BlgwfLI

Length: 39min 10sec (2350 seconds)

Published: Mon Aug 24 2020