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 vs. sap-hdbext-promisfied
And the differences between @sap/hdbext and sap-hdbext-promisfied when it comes to calling Stored Procedures.
@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:
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
}
}) 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 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 3: Wrappers and Express – This blog post
