Skip to Content
Technical Articles
Author's profile photo Siva rama Krishna Pabbraju

My Learnings in SAP-CAP Using MongoDB

Recently I got an opportunity to explore SAP CAP on Mono DB.

I would like to share my learnings here.

 

1. Install SAP-CDS globally by executing below code

npm I -g @sap/cds-dk

 

2. Create a folder for our project, Go to that folder in command prompt and execute below code

cds init

Above code mainly creates sub folders app, db, srv and package.json file

 

3. Open our project folder in Visual Studio Code 

 

3.1 Create Schema file in the db Folder

namespace sap.mongo.db;

entity Orders{
  key orderNo    : String;
  key ItemNo     : String;
  Material       : String;
  Quantity       : Integer;
  QUOM           : String;
  Price          : Double;
  Currency       : String;
  OrderCreatedOn : String
};

entity Materials{
  key Material : String;
  ImageData: String;  
  ImageContentType: String @Core.IsMediaType;
  Description : String 
};

Model for Orders and Material is defined in the Schema

 

3.2 Update Node Modules

Execute below lines of code in Visual Studio Code terminal

npm install mongodb express @sap/cds @sap/cds-odata-v2-adapter-proxy

Above code will install mongodb, express, sap/cds, sap/cds-Odatav2 adapter npm modules

 

npm install

Above code will install all other dependent nom nodules and will update package.json file

 

3.3 Create Service Definition and Implementation files in srv Folder

 

Create “myapp.cds”  Service definition file

using { sap.mongo.db as my } from '../db/schema';

service MyOrders @(path: '/odata/mongodb/MyOrders')
{
   entity Orders as projection on my.Orders;  
}

service MyMaterials @(path: '/odata/mongodb/MyMaterials')
{
   entity Materials as projection on my.Materials; 
}

Service MyOrders and MyMaterials are defined with entities Orders and Materials respectively

 

Create “myapp.js” Service Implementation file

module.exports = cds.service.impl(function() {
    const { Orders, Materials } = this.entities;

    //For Orders
    this.on("READ", Orders, _getOrders);
    this.on("CREATE", Orders, _CreateOrders);

    //For Materials
    this.on("READ", Materials, _getMaterialInfo);
});

Function _getOrders is executed when a read operation is performed on entity Orders. This function call overrides automatic call to databased configured

Similarly _getMaterislInfo is called when read operation is performed on entity Materials

 

Sample code for Funcion _getOrders

async function _getOrders(req) {
    // Connect the client to the server
    await client.connect();

    // Establish and verify connection
    var db = await client.db(db_name);

    //Connect to Collection
    var collection_Orders = await db.collection("Orders");

    var filter, projection, results, date_high, date_low;

    console.log(req.query);

    //Make sure date_low and date_high are in format 2020-02-04T05:06:18.417Z
    //but not as 2020-2-4T5:6:18.417Z
    if (req.query.SELECT.where !== undefined) {
        date_low = req.query.SELECT.where[2].val;
        date_high = req.query.SELECT.where[6].val;
    }

    if (date_low !== undefined) {
        var MyCurLowDate = new Date(date_low);
        var MyCurHighDate = new Date(date_high);
        filter = { OrderCreatedOn: { $gte: MyCurLowDate, $lte: MyCurHighDate } };
    } else {
        filter = {};
    }

//Do not Select ID
//Select OrderCreatedOn in format yyyy-mm-ddThh:mm:ss:lll+z
//Mention all other fields which need to be selected
    projection = {
        _id: 0,
        OrderCreatedOn: {
            $dateToString: {
                format: "%Y-%m-%dT%H:%M:%S.%LZ",
                date: "$OrderCreatedOn",
            },
        },
        orderNo: 1,
        ItemNo: 1,
        Material: 1,
        Quantity: 1,
        QUOM: 1,
        Price: 1,
        Currency: 1,
    };

    var results = await collection_Orders
        .find(filter, { projection: projection })
        .toArray();

//return results as output
    return results;
}

Above function is self explantory to its purpose.

Below mentioned constants are declared prior using above function.

const cds = require("@sap/cds");
const MongoClient = require("mongodb").MongoClient;
const ObjectId = require("mongodb").ObjectID;
const uri = "mongodb://localhost:27017";
const db_name = "ERPData";
const client = new MongoClient(uri);
var response;

 

3.4 Create Server.js file

const express = require("express");
const cds = require("@sap/cds");
const odatav2proxy = require("@sap/cds-odata-v2-adapter-proxy");

const { PORT = 5007 } = process.env
const app = express()

//With below line all cds services can consumed as express serveices
cds.serve("all").in(app)

//convert Odata4 to Odata2 with below line
app.use(odatav2proxy({ path: "v2", port: PORT }))

//Generate a local server "http://localhost" running on port 5007
app.listen(PORT, () => console.info(`server listening on http://localhost:${PORT}`))

 

3.5 Few changes required to project .json file

Mention Node engine

"engines": {  "node": "^8.9"    },

 

Replace npx cds with node server.js

"scripts": {
        "start": "node server.js"
    }

 

Refer to below pacakge.json file for better understanding

 

4. Save files and execute below code in the terminal to start the server

Node server.js

 

5. We can either user Postman or directly consume in browser 

Odata V4 Service

http://localhost:5007/odata/mongodb/MyOrders/Orders

 

Odata V2 Service

http://localhost:5007/v2/odata/mongodb/MyOrders/Orders

 

Example for Filters

Odata V4 Service

http://localhost:5007/odata/mongodb/MyOrders/Orders?$filter=(OrderCreatedOn ge '2020-12-08T17:41:01.103Z' and OrderCreatedOn le '2020-12-09T17:41:01.103Z')

 

6. To Create SAPUI5 App for this

 

6.1 Expose Local Service to SAP Cloud using SAP Cloud Connector

 

6.2 Configure Destination in SAP Cloud Foundry

 

6.3 Develop SAPUI5 Application in Web IDE

Mention Destination path in neo-app.json file

    {
      "path": "/localmongodb",
      "target": {
        "type": "destination",
        "name": "localmongodb"
      },
      "description": "connect to local mongodb"
    }

 

Use URl as below to perform ajax/jquery calls

var url = "/localmongodb/v2/odata/mongodb/MyOrders/Orders?

 

Few Pointers from my project

1. My project extracts images from mongodb and exposes to SAPUI5

2. I have used Standard List in Master page to display master records

Here, I have used Data Range along with Search criteria.  When I user both, scroll bar comes to       entire page instead of only Standard Items. To get scroll bar to only Standard Items write below source in the Controller onIint

var oList = this.byId("idList");
oList.setSticky(["HeaderToolbar"]);

Considering that Search and Date Range are mentioned in the Header ToolBar.

 

Preview of my app:

 

 

Source Code is available at below Github Link

https://github.com/krish469/sap-cap-mongodb/tree/SAPUI5

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah

      Great Article! Keep sharing a lot many more...

      Author's profile photo Marius Obert
      Marius Obert

      Hi Siva,

      that's an interesting POC - thanks for sharing! Have you already heard of cds-pg? That's an open-source project that aims for a similar goal (to support Postgres connections from CAP) but they go a different route with a CAP adapter. Maybe that could be something for your next iteration 🙂

      Author's profile photo Siva rama Krishna Pabbraju
      Siva rama Krishna Pabbraju
      Blog Post Author

      Hi Marius,

      I haven't heard about cds-pg. Thanks for sharing information. I will look into it.

      Thanks,

      Siva

      Author's profile photo Simon Gaudek
      Simon Gaudek

      Support for MongoDB or a driver for MongoDB would be a real benefit.

      I have also connected a CAP-OData service to a MongoDB by implementing an OnRead handler, but in a more generic way. .

      function onRead(req) {
          // Parse condigtions from CAP where clause
          for (let i = 0; i < req.query.SELECT.where ? .length; ) {
              if (
                  req.query.SELECT.where[i] === "(" ||
                  req.query.SELECT.where[i] === ")" ||
                  req.query.SELECT.where[i] === "or" ||
                  req.query.SELECT.where[i] === "and") {
                  i++;
                  continue;
              }
      
              let mongoProperty = req.query.SELECT.where[i].ref[0];
              let mongoOperator = this.mapper.operator(req.query.SELECT.where[i + 1]);
      
              if (!conditions[mongoProperty])
                  conditions[mongoProperty] = {};
      
              if (mongoOperator === "$in") {
                  if (!conditions[mongoProperty][mongoOperator])
                      conditions[mongoProperty][mongoOperator] = [];
                  conditions[mongoProperty][mongoOperator].push(req.query.SELECT.where[i + 2].val);
              } else {
                  conditions[mongoProperty][mongoOperator] = req.query.SELECT.where[i + 2].val;
              }
              i += 4;
          }
      
          // Create sort order from CAP orderBy
          for (let i = 0; i < req.query.SELECT.orderBy ? .length; i++) {
              let mongoProperty = req.query.SELECT.orderBy[i].ref[0];
      
              // exclude non sortable properties
              if (!["timestamp"].includes(mongoProperty))
                  continue;
      
              order[mongoProperty] = req.query.SELECT.orderBy[i].sort;
          }
      
          /** Read via mongoose filter(conditions) and sort(order)*/
      }
      
      class Mapper {
          operators = Operators;
          operatorsRev;
      
          constructor() {
              this.operatorsRev = {};
              for (let key in this.operators) {
                  const value = this.operatorsRev[key];
                  this.operatorsRev[value] = key;
              }
          }
      
          public operator(key : string) {
              return this.operators[key];
          }
          public operatorRev(key : string) {
              return this.operatorsRev[key];
          }
      }
      
      enum Operators {
          "=" = "$in",
          ">" = "$gt",
          ">=" = "$gte",
          "<" = "$lt",
          "<=" = "$lte",
          "!=" = "$ne",
      }
      

      Unfortunately, this doesn't work 100% correctly and still contains a few bugs. But for my use cases this implementation is currently sufficient.

      Since out of the box support for MongoDB is already on CAP's roadmap, I hope something will come here very soon. I'm already waiting for it and could really use it.

      Regards
      Simon