On my last few blogs from the series on #XSA, I wrote about creating node js modules, exposing OData, among other concepts. Today, I would like to dive deeper on this topic and provide additional details on creating REST APIs. The same blog can be read in spanish here

*one difference from my previous blogs to now is that I was using SP03 before, now SP04. Further, I was trying to avoid using the XS CLI since I know this is a touchy subject for admins providing Linux access to developers on most companies.

Challenge 1: enabling the UAA service to authenticate the requests on the node module.

Solution: it is actually very simple. The initial steps were documented before, but I will include them here anyways.

Make sure the mta.yaml file has the module dependencies (html, db, node, uaa) this refers to the requires/provides section and making sure they are done correctly Make sure the xs-app.json file contains the "route" authentication type, and actual routes Assuming we will have both XSJS compatibility and node js supported on the node module, go ahead and bootstrap these elements from the server.js file

The server.js file is the entry point file (as per the default start step on the package.json file)

Then, from the server.js file, the (express module) router gets called to analyze the request and eventually pass it to the corresponding function execution. Notice that the routes defined in the ui module’s xs-app.json file MUST match the routes defined in the node router index.js file , otherwise you will run into 404 "Not found" http responses

Thanks Lucia and Craig for your blogs

On the (router folder) index.js file above, we are also importing the demoSvc.js (service implementation) where we will be executing sql scripts or stored procedures (assuming these are already created on the db module and we have the appropriate permissions to execute them). During my exercise, I created a local cds table, a proc and added some data to my cds table.

Once these things were lined up, it was matter of adding the actual node js logic to execute queries or procedures.

IMO Best practices would be to push more into the DB (using procedures) but I was also curious to see how the SQL method was done from a node module – surprisingly easy in my opinion.

First and foremost, check that you can get a request, and you can response correctly. I use json request/response because most web application and rest apis interact that way.

If you are successful on the previous step, lets then go call a SELECT query.

Notice how the req object (contains a db property that eventually is the client) and it is be used for executing our sql statement The prepare function takes in a sql statement as the first param, and a callback that returns errors & statements The err would let us know immediately if we don’t have permissions or if there are any other problems with our query The statement object allows us to execute our query as shown on the next point The statement exec function takes in an initial param, & a call back, again, the call back will return an error and the results (yes!!! Our data set if anything is returned from our query) We can perform additional logic to manipulate the response before we send it back, or in my case, I returned a custom object Finally, the response object (from the original node module request) is set to contain the response type (such as json, plain text, binary, file, etc), (http) status, and then we can send back the actual response to the invoker of this request. This is a very simple, elegant and easy way to understand what is happening on the request and response. I suggest you attach a debugging session to your code and analyze these steps to get additional information from the req/resp, db, connection, results objects.

On the next scenario, I want to share how easy it is to execute a stored procedure

To start with, we must include the @sap/hdbext module to be able to load stored procedures The loadProcedure function takes in several arguments (client, schema, proc name and callback The client is req.db as shown on the var client assignment, I did not include a schema since I used a procedure from my hdi container, however, you could proceed and try using your own schema as the second argument of the function call The proc name shows there as my design time object. I copied that name after I had invoked the same proc name from the sql console – avoid a mistake and just copy paste from your sql console instead of typing the namespace : : name Notice the call back is in ES6 format, rather than plan node/js and uses arrow functions (shorter syntax) Again, the error as the first argument would tell us if something went wrong with our execution Then the sp object that was loaded as a proxy function Keep in mind that if your stored proc will require any input params, we will need to set those somewhere before executing the proxy function. In my case, I passed in query string params in the request and I am getting those from the req.query object (my query string param is "name") The proxy function ( sp() ) takes in an object with any input params and then it will have a callback (arrow function in this case) returning any errors, params or results from the proc itself Eventually we set our response, as I mentioned before, i have a custom object as my response

Other issues I encountered along the way (took me a while to solve each one of these)