Binding Node.js Application to HANA Databases

【Introduction】

In this blogs, I will explain about the way of accessing HANA tables from Node.js Application.
Especially, I will explain about issuing certification on SCP and implementing conn_param in app.js

【IT Architechture】

I will show IT Architecture outline below.
Node.js app on your laptop PC connects to the HANA table on the cloud.
You deploy your application on Cloud Foundry environment. The application connects to HANA tables.

【Topic/table of Content】

1. Preparation
2. HANA table Creation
3. Node.js Application Creation
4. Certification
5. app.js implementation
6. HANA and Node.js Application Linkage

【Environment】

You need to prepare Cloud Foundry environments and HANA as a Service.
Cloud Environment ：Cloud Foundry environment.
Database ：HANA as a Service(CF)
PC/Service ：1 PC （※ In my case, I used WindowsPC）

【Version】

WindowsPC Edition ：Windows 10 Enterprise 2016 LTSB
CPU ：Intel(R)Core(TM)i5-7300U CPU @ 2.60GHz 2.71GHz
Memory(RAM) ：8.00GB
HANA version(CF) ：4.10.5

【1. Preparation】

Perhaps, you think about Developing a tool to implement any code.
So, you download any IDE that suits your need such as IntelliJ/Visual Studio Code/WebStorm when necessary.

【2. HANA table Creation】

I will tell you HANA table creation method.

1. Select your Spaces, after you logged in Cloud Foundry envirinment
   
2. Select ‘SAPA HANA Service’ in Service Marketplace and select your instance
3. Open your HANA Dashborad
   (※ Remember HANA endopint)
   
4. Click ‘Go to the HANA cockpit’ in the upper right corner of window
   
5. Click ‘SQL console’ in the upper right corner of window
   
6. You can create any HANA schema and tables
   (※ Remember your schema, user and password)

【3. Node.js Application Creation】

At first, you create simply Node.js application. Any kind of application is fine.
Open your project in the developing tool and select app.js in the src folder.
Implement connection setting to HANA tables in the node.js. I put a sample code below.

var conn_params = {
  serverNode: "zeus.hana.prod.eu-central-1.whitney.dbaas.ondemand.com:[Your Port Number]",
  encrypt: true,
  schema: "[Your Schema]",
  uid: "[Your User]",
  pwd: "[Your Password]"
};

Next step, you implement SQL statement in the same file.
Define a method to connect to HANA tables.
I will show a sample code below.

app.use("/conn/xxx", function (req, res, next) {
  conn.connect(conn_params, function (err) {
    if (err) {
      var msg = [{
        msg: "A connection error has occurred"
      }];
      res.json({
        searchResult: msg
      });
      return;
    }
    var sql = 'SELECT * FROM "[Your Schema]"."[Your Table]";';
    conn.exec(sql, function (err, result) {
      if (err) {
        console.log("DB Error: SQL Execution --- ", err);
      }
      conn.disconnect();
      if (Object.keys(result).length == 0) {
        var msg = [{
          msg: "The result is not found."
        }];
        res.json({
          searchResult: msg
        });
        return;
      }
      res.json({
        searchResult: result
      });
    });
  });
});

After Implementation, you run your Node.js application.
You successfully connect to your HANA Tables.

【4. Certification Creation】

Go back to SAP Cloud Platform Cockpit to generate HANA service keys.
Follow the steps below.

1. Select Instance Service
2. Select Your HANA Instance
   
3. Select Service Keys
   
4. Create Service key
   

Save the service key as you will need it later on.

【5. app.js implimentation】

You add two parameters related to ssl on your source codes
Put a ‘sslCryptoProvider’ key and ‘openssl’ value
Put a ‘sslTrustStore’ key and a ‘BEGIN CERTIFICATE’ value as you memorize

var conn_params = {
  serverNode: "zeus.hana.prod.eu-central-1.whitney.dbaas.ondemand.com:[Your Port Number]",
  sslCryptoProvider: "openssl",
  sslTrustStore: "-----BEGIN CERTIFICATE-----\n...(omitted)...\n-----END CERTIFICATE-----\n",
  encrypt: true,
  schema: "[Your Schema]",
  uid: "[Your User]",
  pwd: "[Your Password]"
};

【6. HANA and Node.js Application Linkage】

After you build source codes and deploy them, you can see your application on SAP Cloud Platform Cockpit.
I will show you an instruction step by step.

1. Open windows command prompt
2. Change the current directory to your application context root
3. Type ‘cf login -a https://api.cf.eu10.hana.ondemand.com’
   
4. Type your e-mail and password
   
5. Select Org
   
6. Type command ‘cf push [Your application name]’
   
7. Open your browser or Click Application Routes on your SCP CP
   
8. Access ‘https://[Your Application Name].cfapps.eu10.hana.ondemand.com/conn/xxx’
   

You can see table records.

【Summary】

To sum up, you need to set sslCryptoProvider and  sslTrustStore to connect HANA tables before you deploy Node.js application to SAP Cloud Foundry.