Skip to Content
Technical Articles
Author's profile photo Thomas Jung

SAP Tech Bytes: HANA Client Tools for JavaScript Developers – Part 3 Wrappers and Express

Introduction

Going back to our discuss in the previous blog post in this series about @sap/hana-client and hdb, the situation is now even more complex. @sap/hana-client supports promises, but hdb doesn’t. That’s just one of several differences between the two modules.  There are also other functions like connection pooling or calling stored procedures that can be done in both @sap/hana-client and hdb, but perhaps could be made even easier with some additional helper functionality. This is where we start to talk about wrapper modules like @sap/hdbext, another Node.js module that sits on top of @sap/hana-client but provides additional or simplified logic.

Note: In each section, there are code samples to support the tool we are discussing.  All the code for these samples is available in GitHub here: https://github.com/SAP-samples/sap-tech-bytes/tree/2022-03-17-hana-javascript.

@sap/hdbext

The @sap/hdbext module has some interesting history. It originally was a higher-level module that sat on top of the hdb module.  It provides convenience features like SQL parameter and injection utilities. Over time SAP rewrote the module to use @sap/hana-client internally instead. This has an interesting side effect.  Remember back when we first looked at @sap/hana-client vs hdb, one of the differences was the connection parameters each expected.  hdb worked directly with the values returned from @sap/xsenv, but @sap/hana-client needed fields to be mapped and renamed.

@sap/hana-client vs. @sap/hdbext

In the @sap/hdbext example, you get all the expanded feature coverage of the @sap/hana-client but also have the simpler connection handling and compatibility with hdb module because of the historic usage of both modules. It must do the name mapping for the connection settings internally.

But the benefits don’t end there.  Another major advantage of @sap/hdbext is in the handling of stored procedures. With the @sap/hana-client you must first use the createProcStatement before you can execute the call to the stored procedure.

var dbStream = require('@sap/hana-client/extension/Stream');
dbStream.createProcStatement(client, 'CALL PROC_DUMMY (?, ?, ?, ?, ?)', function (err, stmt) {
  if (err) {
    return console.error('createProcStatement error:', err);
  }

  stmt.exec({ A: 3, B: 4 }, function (err, params, dummyRows, tablesRows) {
    if (err) {
      return console.error('exec error:', err);
    }

    stmt.drop(function (err) {
      if (err) {
        return console.error('drop error:', err);
      }

      console.log('C:', params.C);
      console.log('Dummy rows:', dummyRows);
      console.log('Tables rows:', tablesRows);
    });
  });
});

But the @sap/hdbext module provides just loading the procedure by name and returning a kind of proxy JavaScript function that matches the interface of the stored procedure. You can also pass JSON to stored procedure table parameters and the @sap/hdbext module will take care of all the translation. Overall, it’s a far more JavaScript friendly way to interact with HANA Stored Procedures.

/**
 * hdbext procedure example with Callbacks
 * @param {string} [schema] Database Stored Procedure Schema 
 * @param {string} [dbProcedure] Database Stored Procedure Name 
 * @param {object} [inputParams] Database Stored Procedure Input Parameters
 * @param {Callback} [callback] Callback Function to receive results
 */
export function example2(schema, dbProcedure, inputParams, callback) {
    xsenv.loadEnv()
    let hanaOptions = xsenv.getServices({
        hana: {
            label: "hana"
        }
    })

    hdbext.createConnection(hanaOptions.hana, (err, client) => {
        if (err) {
            callback(err)
        } else {
            hdbext.loadProcedure(client, schema, dbProcedure, (err, sp) => {
                if (err) {
                    callback(err)
                } else {
                    sp(inputParams, (err, parameters, tableRows) => {
                        client.disconnect()
                        callback(null, parameters)
                    })
                }
            })
        }
        return null
    })

}

sap-hdbext-promisfied

In several cases @sap/hdbext is a marked improvement over using @sap/hana-client directly. There must be a catch then, right? Yes unfortunately.  It doesn’t expose the new @sap/hana-client promises interface.  So, are you back to using Callbacks if you want the other convenience features of @sap/hdbext?

This is where a further wrapper Node.js module comes into play – sap-hdbext-promisfied. Please note that sap-hdbext-promisfied is a code sample and doesn’t receive the same level of official support from SAP as the other modules mentioned already.

As the name suggests, it wraps the @sap/hdbext module (which in turn still wraps the @sap/hana-client module) but adds a layer with promises. All the advantages are starting to roll up now. You get the full features of the @sap/hana-client, the convenience features of @sap/hdbext but can also continue to use promises and async/await. With the sap-hdbext-promisfied module we even encapsulate the functionality of loading the connection parameters with @sap/xsenv. Connection, query, and results are now down to two lines of code:

import dbClass from "sap-hdbext-promisfied"
export async function example1(dbQuery) {
    try {
        let db = new dbClass(await dbClass.createConnectionFromEnv())
        return await db.execSQL(dbQuery)
    } catch (error) {
        throw error
    }
}

Let’s again compare this with the original @sap/hana-client example with Callbacks.  All the same functionality (and underlying modules), just with reduced complexity.

@sap/hana-client%20vs.%20sap-hdbext-promisfied

@sap/hana-client vs. sap-hdbext-promisfied

And the differences between @sap/hdbext and sap-hdbext-promisfied when it comes to calling Stored Procedures.

@sap/hdbext%20vs.%20sap-hdbext-promisfied%20Stored%20Procedures

@sap/hdbext vs. sap-hdbext-promisfied Stored Procedures

Same situation here: same functionality and underlying modules, just less syntax complexity.

sap-hdb-promisfied

We’ve not mentioned the hdb module in a while.  The advantages remain that it has native, lighter weight implementation. But usage of the hdb module now faces some challenges as it lacks the promises interface of @sap/hana-client and the convivence features of @sap/hdbext.

This is where yet another module can be helpful – it’s sap-hdb-promisfied. It attempts to provide the convivence features of @sap/hdbext (particularly the stored procedure features) and it adds promises/async/await functionality but all to the base hdb module. The interface is compatible with @sap/hdbext and sap-hdbext-promisfied, so it can also ease the conversion or adaption when wanting to switch between hdb and @sap/hana-client modules as well.

Please note that sap-hdb-promisfied is a code sample and doesn’t receive the same level of official support from SAP as the other modules mentioned already.

Therefore, the code to use sap-hdb-promisfied looks identical to the sample of sap-hdbext-promisfied. The only difference is which SAP module is used internally to implement the functionality.

import dbClass from "sap-hdb-promisfied"
export async function example1(dbQuery) {
    try {
        let db = new dbClass(await dbClass.createConnectionFromEnv())
        let result = await db.execSQL(dbQuery)
        db.destroyClient()
        return result
    } catch (error) {
        throw error
    }
}

We will compare them side-by-side again:

sap-hdbext-promisfied%20vs.%20sap-hdb-promisfied

sap-hdbext-promisfied vs. sap-hdb-promisfied

Express

One final advantage of the wrapper, @sap/hdbext, (and it’s promisfied wrappers) involves Express middleware. All the examples in this blog series have been standalone Node.js that you can run from the command line and output results to the console. It’s far more likely that you will want to utilize HANA queries from within a web-based application or service.

Express is a very commonly used framework in Node.js to set up the web server and provide HTTP end points. It also has the concept of middleware. Third party modules can be included within Express to provide additional functionality.  @sap/hdbext can function exactly as an Express middleware. This way all the database connection creation, pooling and connection closing is handled automatically for you upon each HTTP Request cycle.  The database connection object will be placed into the Express request object and ready for you to use.

When you start the Express server, you still use the @sap/xsenv module to load the connection parameters from the environment. Then you can tell Express (the app object in this example) to use @sap.hdbext and its middleware function. The rest will be taken care internally by the @sap/hdbext module.

    import express from 'express'
    import hdbext from "@sap/hdbext"
    import * as xsenv from "@sap/xsenv"
    this.app = express()
    /**
     * Start Express Server
     */
    async start() {
        let app = this.app
        xsenv.loadEnv()
        let hanaOptions = xsenv.getServices({
            hana: {
                tag: "hana"
            }
        })
        hanaOptions.hana.pooling = true
        app.use(
            hdbext.middleware(hanaOptions.hana)
        )

        this.httpServer = app.listen(this.port)
    }

In your Express route handlers, you can concentrate on the queries you want to execute. The standard Express router handler function will pass in the request object (req) and the database connection is already container in that object.

    app.get("/rest/hdbext",
        (/** @type {express.Request} */ req, /** @type {express.Response} */ res) => {
            let client = req.db
            client.prepare(
                `SELECT CURRENT_USER, CURRENT_SCHEMA FROM "DUMMY"`,
                (err, statement) => {
                    if (err) {
                        return res.type("text/plain").status(500).send("ERROR: " + err.toString())
                    }
                    statement.exec([],
                        (err, results) => {
                            if (err) {
                                return res.type("text/plain").status(500).send("ERROR: " + err.toString())
                            } else {
                                return res.type("application/json").status(200).send(results)
                            }
                        })
                    return null
                })
        })

We can even use this req.db into the constructor of the sap-hdbext-promisfied wrapper then use Async calls within the router handler.

 app.get("/rest/hdbextAsync",
        async (/** @type {express.Request} */ req, /** @type {express.Response} */ res) => {
            try {
                let db = new dbClass(req.db)
                const statement = await db.preparePromisified(`SELECT SESSION_USER, CURRENT_SCHEMA 
				            								 FROM "DUMMY"`)
                const results = await db.statementExecPromisified(statement, [])
                return res.type("application/json").status(200).send(results)
            } catch (e) {
                return res.type("text/plain").status(500).send(`ERROR: ${e.toString()}`)
            }
        })

We can start to combine the distinctive features we’ve learned about so far.  For example, a route handler that calls a stored procedure:

    app.get("/rest/procedures",
        async (/** @type {express.Request} */ req, /** @type {express.Response} */ res) => {
            try {
                let db = new dbClass(req.db)
                let sp = await db.loadProcedurePromisified(hdbext, 'SYS', 'IS_VALID_PASSWORD')
                res.type("application/json").status(200).send(await db.callProcedurePromisified(sp, { PASSWORD: "TEST" }))

            } catch (error) {
                res.type("text/plain").status(500).send(`ERROR: ${error.toString()}`)
                return
            }
        })

Or a stored procedure that takes an input parameter from the incoming HTTP Request:

    app.get("/rest/procedures2/:password",
        async (/** @type {express.Request} */ req, /** @type {express.Response} */ res) => {
            try {
                let password = req.params?.password
                let inputParams = {}
                if (typeof password === "undefined" || password === null) {
                    inputParams = {}
                } else {
                    inputParams = {
                        PASSWORD: password
                    }
                }
                let db = new dbClass(req.db)
                let sp = await db.loadProcedurePromisified(hdbext, 'SYS', 'IS_VALID_PASSWORD')
                res.type("application/json").status(200).send(await db.callProcedurePromisified(sp, inputParams))

            } catch (error) {
                res.type("text/plain").status(500).send(`ERROR: ${error.toString()}`)
                return
            }
        })

Or finally calling two separate Database queries in parallel but only returning the HTTP response once both queries are complete and return results:

    app.get("/rest/queriesParallel/",
        async (/** @type {express.Request} */ req, /** @type {express.Response} */ res) => {
            try {
                let db = new dbClass(req.db)
                let result = {}
                let [outputOne, outputTwo] = await Promise.all([queryOne(), queryTwo()])
                result.outputOne = outputOne
                result.outputTwo = outputTwo
                return res.type("application/json").status(200).send(result)

                async function queryOne() {
                    const statement = await db.preparePromisified(`SELECT SESSION_USER, CURRENT_SCHEMA FROM "DUMMY"`)
                    return await db.statementExecPromisified(statement, [])
                }

                async function queryTwo() {
                    let sp = await db.loadProcedurePromisified(hdbext, 'SYS', 'IS_VALID_PASSWORD')
                    return await db.callProcedurePromisified(sp, { PASSWORD: "TEST" })
                }
            } catch (e) {
                return res.type("text/plain").status(500).send(`ERROR: ${e.toString()}`)
            }
        })

We can again take performance measures to ensure both queries are truly running in parallel. The overall route handler begins at 6.755 seconds and both queries also begin at 6.755. Query one takes 152 milliseconds to complete. Query two takes 222 milliseconds to complete.  The overall runtime of the whole route handler is only 223 milliseconds – just one millisecond longer than the longer of the two queries, proving they executed in parallel.

Parallel%20Query%20Performance%20Measurement

Parallel Query Performance Measurement

Closing

We’ve now discussed in detail multiple aspects of the basic HANA Client options for JavaScript developers.  But this blog series isn’t quite finished yet. There are still two specialized tooling use cases. After we have the historic XSJS approach that still might be useful today for backwards compatibility with legacy code. And more looking to the future we also have the SAP Cloud Application Programming Model. We will explore both of those in the next part.

Part 1: hana-client vs. hdb

Part 2: Promises

Part 3: Wrappers and Express – This blog post

Part 4: XSJS and Cloud Application Programming Model

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Javier Caceres
      Javier Caceres

      Good article

      Author's profile photo Yue Fang
      Yue Fang

      We can always use node.js own util.promisify to wrap either hana-client or hdb. There should be no difference than these promise module, right?

      hdb module use to have problem to handle transactions. That is the main reason we switch to hana-client. Do you know any progress on that?

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      We can always use node.js own util.promisify to wrap either hana-client or hdb.

      Correct. In fact I use that utility within this module: hana-hdbext-promisfied-example/index.js at main · SAP-samples/hana-hdbext-promisfied-example (github.com)

      But I put this originally in a separate module to avoid the repeat boilerplate code of performing this promisification in multiple places.  Then over time the module grew a bit to also provide a few other related utilities and simplifications.

      Things like the execSQL function hana-hdbext-promisfied-example/index.js at main · SAP-samples/hana-hdbext-promisfied-example (github.com) or the callProcedurePromisified hana-hdbext-promisfied-example/index.js at main · SAP-samples/hana-hdbext-promisfied-example (github.com) that either combine multiple operations for simplification or handles complex output that just using the promisfy utility wouldn't do.

      I'm not sure what transaction issue with hdb module you are referring to.